Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
Приветствую.

Есть задачка, требующая реализации и может у кого уже есть наброски по реализации подобного...

Имеем таблицу с остатками по счетам за каждый день. Состоит она из трех колонок (t_date (smalldatetime), brief varchar(20), summ nvarchar(20))

т.е. приблизительно такого содержания:
01/01/2013 70705..... -45297,7564044944
02/01/2013 70704..... -57739,1537078652
03/01/2013 70703..... -65180,5265168539
и т.д. за каждый день по всем счетам.

Нужно получить средний остаток за каждый месяц по каждому счету за период с 01/01/2013 по 01/12/2013 и затем сложить их, сгруппировав эти полученные средние значения по первым числам счета (group by substring(brief, 1,5))

Буду признателен любым подсказкам или тексту запроса, так как времени на реализацию мало, а запросов куча...

С уважением...
6 дек 13, 17:07    [15254756]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
хмхмхм
Guest
Artemy_ch,

а в чем именно у вас затруднения?
выбрать записи за обозначенный период времени? сгруппировать по счетам? или может быть посчитать среднее?
6 дек 13, 17:15    [15254817]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
sdet
Member

Откуда:
Сообщений: 463
хмхмхм,

У автора трудности со временем. Полагаю ему нужно пересмотреть график, а не делать работу за счет других.
6 дек 13, 17:18    [15254844]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
хмхмхм
Guest
Artemy_ch,

кстати, очень оригинально сумму держать в поле nvarchar(20), не находите?
6 дек 13, 17:20    [15254856]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
хмхмхм
Artemy_ch,

а в чем именно у вас затруднения?
выбрать записи за обозначенный период времени? сгруппировать по счетам? или может быть посчитать среднее?


сложность с датами, снова... Я видимо с ними никак не подружусь.

За одни месяц сложить и посчитать - проблем нет. А вот в периоде - есть сложность. Когда я пишу за один месяц, то приблизительно так:
select substring(brief, 1,5), sum(cast(summ as float))/datediff(day, @dt1, @dt2)+1 from t_ostatki
where t_date between @dt1 and @dt2
group by substring(brief, 1,5) 
order by substring(brief, 1,5)


А вот как часть выражения "/datediff(day, @dt1, @dt2)+1" применить на необходимое условие (т.е. сложенные остатки в каждом месяце делить на кол-во дней в этих месяцах за 11 месяцев текущего года), для меня пока успехом не увенчалось.
Отчасти понимаю, что ничего особо сложного тут нет, но видимо какой-то стопор словил...да и время поджимает.

Спасибо.

С уважением.
6 дек 13, 17:23    [15254880]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Glory
Member

Откуда:
Сообщений: 104751
Artemy_ch
А вот как часть выражения "/datediff(day, @dt1, @dt2)+1" применить на необходимое условие

откройте для себя агрегатную функцию AVG
6 дек 13, 17:25    [15254900]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
sdet
хмхмхм,

У автора трудности со временем. Полагаю ему нужно пересмотреть график, а не делать работу за счет других.


Спасибо за Ваш комментарий, но с графиком все ок. Делать за меня мою работу других не прошу, да и не в написании запросов заключается моя работа. Тут вроде как все спрашивают и делятся своими наработками, текстами запросов, прочими знаниями, чтобы у других была возможность их использовать в своих целях, да набираться опыта разбираясь в них.

Поэтому попросил помощи/подсказки, а если есть подобные наработки и не жаль ими поделиться, то буду вдвойне признателен.

Этот запрос лишь "капля в море", по сравнению с тем, что еще предстоит сделать...

Прошу строго не судить. Спасибо.
6 дек 13, 17:33    [15254951]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
хмхмхм
Guest
Artemy_ch,

а сколько в вашей таблице записей?
6 дек 13, 17:38    [15254993]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
Glory
откройте для себя агрегатную функцию AVG


Премного благодарен за наводку, об этой функции я совершенно забыл, но увы, она не решает моих задач. Немного не корректно считает.

Спасибо.
6 дек 13, 17:41    [15255009]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
хмхмхм
Artemy_ch,

а сколько в вашей таблице записей?


Порядка 60 тысяч записей...
6 дек 13, 17:41    [15255012]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Glory
Member

Откуда:
Сообщений: 104751
Artemy_ch
но увы, она не решает моих задач. Немного не корректно считает.

Скорее всего это вы некорректно ее используете
6 дек 13, 17:42    [15255017]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
хмхмхм
Guest
Artemy_ch
хмхмхм
Artemy_ch,

а сколько в вашей таблице записей?


Порядка 60 тысяч записей...


А для чего данные типа Decimal у вас хранятся в таблице как nvarchar(20)? Вы их каждый раз будете конвертировать? Есть какой-то смысл в этом в вашей архитектуре?


Вот например, посмотрите на дисковую активность при таком подходе к проектированию баз данных:
create table #t(d nvarchar(20))

create table #t2(d decimal(23,10))



while (select count(*) from #t) < 60000 --60 тыс.
 begin

insert into #t
		 (d)
select object_id from sys.objects

insert into #t2
		 (d)
select object_id from sys.objects


end

set statistics io on
select sum(cast(d as float)) from #t


select sum(d) from #t2


set statistics io off

drop table #t
drop table #t2


Scan count 1, logical reads 270
против
Scan count 1, logical reads 180
6 дек 13, 17:49    [15255060]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
Glory
Artemy_ch
но увы, она не решает моих задач. Немного не корректно считает.

Скорее всего это вы некорректно ее используете


Она работает корректно, в той части, что делит полученную сумму на кол-во записей, которые складывала.

А задача разделить сумму на кол-во дней в каждом месяце.

Просто внутри месяца в нескольких днях остатка может и не быть.
6 дек 13, 17:51    [15255066]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Glory
Member

Откуда:
Сообщений: 104751
Artemy_ch
А задача разделить сумму на кол-во дней в каждом месяце.

Просто внутри месяца в нескольких днях остатка может и не быть.

Вы же в предыдущей теме получили скрипт создания таблицы календаря
6 дек 13, 17:52    [15255078]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
хмхмхм

А для чего данные типа Decimal у вас хранятся в таблице как nvarchar(20)? Вы их каждый раз будете конвертировать? Есть какой-то смысл в этом в вашей архитектуре?


Досталось в наследство... Работаем с тем, что есть. По возможности буду переделывать. Насколько успел заметить, что там, откуда берутся данные, также суммы лежат в nvarchar(20)... На досуге думаю, почему было сделано именно так и в чем плюсы этого метода...

Спасибо Вам большое!
6 дек 13, 17:55    [15255094]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
хмхмхм
Guest
Artemy_ch
Просто внутри месяца в нескольких днях остатка может и не быть.

А если в where поставить условие выборки только те записи, у которых сумма > 0?
6 дек 13, 17:56    [15255106]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
Glory
Artemy_ch
А задача разделить сумму на кол-во дней в каждом месяце.

Просто внутри месяца в нескольких днях остатка может и не быть.

Вы же в предыдущей теме получили скрипт создания таблицы календаря


Все верно, только это разные задачи, хотя чем-то и связаны между собой. Пойду думать, как увязать этот календарь к этому запросу :)

Спасибо.
6 дек 13, 17:57    [15255117]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Glory
Member

Откуда:
Сообщений: 104751
Artemy_ch
Все верно, только это разные задачи, хотя чем-то и связаны между собой.

Мда. Не чем-то связаны, а напрямую связаны. JOIN-ом
А соеденить таблицу-календарь с датами вашей таблицы вы не пробовали ?
6 дек 13, 17:59    [15255131]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
o-o
Guest
Artemy_ch
... На досуге думаю, почему было сделано именно так и в чем плюсы этого метода...


плюсы опупительны.
например, прежде чем это суммировать, приходится во что-то конвертить
(хотя можно, конечно, и напрямую сложить: '1' + '2' = '12'. модерново + альтернативно)
вот Artemy_ch решил во float.
а другой Artemy_ch1 сконвертит в decimal,
на выходе сравнят оба полученные суммы на 60.000 записей и приятно удивятся.
6 дек 13, 18:02    [15255140]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
хмхмхм
А если в where поставить условие выборки только те записи, у которых сумма > 0?


а у меня итак не попадают в таблицу счета, если по ним остаток ноль. как я писал выше, мне нужно разделить полученную сумму на кол-во дней в месяце, а не на кол-во записей. Т.е. если в месяце 31 день, а записей в остатками 20, то функция avg() разделит сумму на 20, а мне нужно разделить на 31 :)

Пробую привязать таблицу-календарь.

Спасибо.
6 дек 13, 18:05    [15255161]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
хмхмхм
Guest
Artemy_ch
хмхмхм
А если в where поставить условие выборки только те записи, у которых сумма > 0?


а у меня итак не попадают в таблицу счета, если по ним остаток ноль. как я писал выше, мне нужно разделить полученную сумму на кол-во дней в месяце, а не на кол-во записей. Т.е. если в месяце 31 день, а записей в остатками 20, то функция avg() разделит сумму на 20, а мне нужно разделить на 31 :)

Пробую привязать таблицу-календарь.

Спасибо.


Ну ок, а вариант разделить результат функции sum() на:

declare @datestart date = '2013-01-01'
		,@dateend date = '2013-02-01'

select  datediff(day, @datestart, @dateend)


чем не подошел?
6 дек 13, 18:11    [15255192]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
хмхмхм

Ну ок, а вариант разделить результат функции sum() на:

declare @datestart date = '2013-01-01'
		,@dateend date = '2013-02-01'

select  datediff(day, @datestart, @dateend)


чем не подошел?


тем, что это за один месяц, а @dateend будет равен 05/12/2013. Получаем 11 полных месяцев, и 5 дней декабря.
6 дек 13, 18:13    [15255205]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
хмхмхм
Guest
Artemy_ch
хмхмхм
Ну ок, а вариант разделить результат функции sum() на:

declare @datestart date = '2013-01-01'
		,@dateend date = '2013-02-01'

select  datediff(day, @datestart, @dateend)


чем не подошел?


тем, что это за один месяц, а @dateend будет равен 05/12/2013. Получаем 11 полных месяцев, и 5 дней декабря.


ах вот в чем загвоздка
Ну тогда пользуйтесь поиском:

https://www.sql.ru/forum/619772/funkciya-kolichestvo-dney-v-mesyace
6 дек 13, 18:19    [15255244]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
хмхмхм
Guest
o-o
Artemy_ch
... На досуге думаю, почему было сделано именно так и в чем плюсы этого метода...


плюсы опупительны.
например, прежде чем это суммировать, приходится во что-то конвертить
(хотя можно, конечно, и напрямую сложить: '1' + '2' = '12'. модерново + альтернативно)
вот Artemy_ch решил во float.
а другой Artemy_ch1 сконвертит в decimal,
на выходе сравнят оба полученные суммы на 60.000 записей и приятно удивятся.


очень странно, что поле с датой не в varchar
6 дек 13, 18:22    [15255263]     Ответить | Цитировать Сообщить модератору
 Re: Расчет среднего остатка за месяц в периоде  [new]
Artemy_ch
Member

Откуда:
Сообщений: 23
Добрый день, Уважаемые форумчане.

Снова к вам за советом.

С помощью ваших комментариев и подсказок, удалось составить запрос и решить вопрос с расчетом средних остатков за каждый месяц.

Но вот есть следующая задача, усложнить расчет, получая средне хронологические остатки. Там уже другая формула и результат будет сильно отличаться.

Может у кого есть наброски запроса, которые бы направили меня по правильному пути реализации этой задачи? Так как согласно формуле, чтобы рассчитать этот средне хронологический остаток за месяц, надо будет брать первую запись с остатком, делить ее пополам, прибавить оставшиеся записи и прибавить последнюю запись также деленную пополам, затем разделить все это на кол-во записей(дней) - 1.

Буду признателен за любой совет.

С уважением...
6 фев 14, 12:20    [15527572]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить