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

Откуда: Latvija
Сообщений: 145
При простом селекте имеются следующие данные

Имя месяца | ID контракта | Сумма конктракта | Часть оплаты по контракту | Разница в месяцах между датой выдачи контракта и датой частичной оплаты

Январь | 1 | 100,00 | 25,00 | 0
Январь | 1 | 100,00 | 35,00 | 1
Январь | 1 | 100,00 | 35,00 | 2
Январь | 2 | 200,00 | 25,00 | 2
Февраль | 3 | 50,00 | 25,00 | 3
Февраль | 4 | 750,00 | 100,00 | 0
Февраль | 4 | 750,00 | 100,00 | 1
Февраль | 4 | 750,00 | 100,00 | 2
Февраль | 4 | 750,00 | 150,00 | 3


Что нужно получить в итоге : (без ID контракта, его я проказывала для того, чтобы можно было увидеть, что это идут разные контракты)
Январь | 300,00 | 25,00 | 0
Январь | 300,00 | 35,00 | 1
Январь | 300,00 | 60,00 | 2

Февраль | 800,00 | 100,00 | 0
Февраль | 800,00 | 100,00 | 1
Февраль | 800,00 | 100,00 | 2
Февраль | 800,00 | 175,00 | 2


Т.е. нужно показать динамику оформления и оплаты контрактов - сколько контрактов было оформлено, сколько было уплачено (в январе/феврале/марте и так далее) за ЭТИ (выданные в январе, например) в каждый последующий месяц.

Моя проблема - у меня вместо 300,00 за январь, ДЛЯ ВСЕХ ЯНВАРСКИХ записей, получается

Январь | 100,00 | 25,00 | 0
Январь | 100,00 | 35,00 | 1
Январь | 300,00 | 60,00 | 2

Т.е. суммируются суммы контрактов, по которым были произведена оплата для каждой разница в месяцах между датой выдачи контракта и датой частичной оплаты (последняя колонка)

Группировку делаю по названию месяца и по разнице в месяцах.

Или надо изначально запрос переделывать?
Сами контракты и оплата по контрактам, естественно, в разных файлах.
Или можно как-то корректнее оформить group by?
5 мар 13, 14:51    [14014813]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Naile
Группировку делаю по названию месяца и по разнице в месяцах.

Ну так вам сервер и группирует
Январь | 0
Январь | 1
Январь | 2

Naile
Или надо изначально запрос переделывать?

Да
5 мар 13, 14:55    [14014838]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
Naile
Что нужно получить в итоге
Январь | 300,00 | 25,00 | 0
Январь | 300,00 | 35,00 | 1
Январь | 300,00 | 60,00 | 2
откуда число 300 ?
5 мар 13, 14:56    [14014841]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Паганель,

Январь | 1 | 100,00 | 25,00 | 0
Январь | 1 | 100,00 | 35,00 | 1
Январь | 1 | 100,00 | 35,00 | 2
Январь | 2 | 200,00 | 25,00 | 2


За январь было 2 контрактаб с ID = 1 и ID = 2. Их общая сумма - 100 + 200 = 300
5 мар 13, 14:58    [14014865]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Так же и за февраль было 2 контракта, ID = 3 и ID = 4

Февраль | 3 | 50,00 | 25,00 | 3
Февраль | 4 | 750,00 | 100,00 | 0
Февраль | 4 | 750,00 | 100,00 | 1
Февраль | 4 | 750,00 | 100,00 | 2
Февраль | 4 | 750,00 | 150,00 | 3

50 + 750 = 800
5 мар 13, 14:59    [14014873]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
попробуйте получить это число подзапросом
или sum(...) over(partition by ...)
5 мар 13, 14:59    [14014884]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Может быть для наглядности покажу пример данных :

Table 1
Contract_ID | Amount | Date_Table1
1 | 100 | 02.01.2012
2 | 200 | 15.01.2012
3 | 50 | 04.02.2012
4 | 750 | 16.02.2012



Table 2
Payment_ID | Date_Table2 | Payment_summa | Contract_ID
1 | 15.01.2012 | 25 | 1
2 | 15.02.2012 | 35 | 1
3 | 05.03.2012 | 35 | 1
4 | 05.03.2012 | 25 | 2
5 | 05.05.2012 | 25 | 3
6 | 17.02.2012 | 100 | 4
7 | 17.03.2012 | 100 | 4
8 | 17.04.2012 | 100 | 4
9 | 17.05.2012 | 150 | 4
5 мар 13, 15:24    [14015055]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Паганель,

Честно сказать - не получается.
Могли бы, пожалуйста помочь?
5 мар 13, 15:25    [14015063]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Naile
Может быть для наглядности покажу пример данных :

Table 1
Contract_ID | Amount | Date_Table1
1 | 100 | 02.01.2012
2 | 200 | 15.01.2012
3 | 50 | 04.02.2012
4 | 750 | 16.02.2012



Table 2
Payment_ID | Date_Table2 | Payment_summa | Contract_ID
1 | 15.01.2012 | 25 | 1
2 | 15.02.2012 | 35 | 1
3 | 05.03.2012 | 35 | 1
4 | 05.03.2012 | 25 | 2
5 | 05.05.2012 | 25 | 3
6 | 17.02.2012 | 100 | 4
7 | 17.03.2012 | 100 | 4
8 | 17.04.2012 | 100 | 4
9 | 17.05.2012 | 150 | 4


При котором результат :
Январь | 300,00 | 25,00 | 0
Январь | 300,00 | 35,00 | 1
Январь | 300,00 | 60,00 | 2

Февраль | 800,00 | 100,00 | 0
Февраль | 800,00 | 100,00 | 1
Февраль | 800,00 | 100,00 | 2
Февраль | 800,00 | 175,00 | 2
5 мар 13, 15:27    [14015070]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Паганель
попробуйте получить это число подзапросом
или sum(...) over(partition by ...)


при изменении
sum(Table1.Amount)
на
sum(Table1.Amount) OVER (Partition by Table1.ID) as Total

sql требует указать Table1.ID в group by :
Column 'Table1.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Ну а при добавлении Table1.ID туда, естественно, нарушается агрегация...


GROUP BY у меня идет по:

group by DATENAME (month, Date_Table1), DATEDIFF(month, cTable1.Date_Table1, Table1.Date_Table2)
5 мар 13, 15:44    [14015197]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
попробуйте сначала вычислить эту колонку, а другие взять как есть
потом завернуть то что получилось в derived table
а потом - группировать, включив эту новую колонку в group by

да, и partition by должен быть по месяцу

кстати, а что будет если данные за несколько лет?
5 мар 13, 16:09    [14015385]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Naile
Может быть для наглядности покажу пример данных :

Table 1
Contract_ID | Amount | Date_Table1
1 | 100 | 02.01.2012
2 | 200 | 15.01.2012
3 | 50 | 04.02.2012
4 | 750 | 16.02.2012



Table 2
Payment_ID | Date_Table2 | Payment_summa | Contract_ID
1 | 15.01.2012 | 25 | 1
2 | 15.02.2012 | 35 | 1
3 | 05.03.2012 | 35 | 1
4 | 05.03.2012 | 25 | 2
5 | 05.05.2012 | 25 | 3
6 | 17.02.2012 | 100 | 4
7 | 17.03.2012 | 100 | 4
8 | 17.04.2012 | 100 | 4
9 | 17.05.2012 | 150 | 4

Т.е. таблиц все таки 2, а не одна ?
5 мар 13, 16:14    [14015418]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Паганель
кстати, а что будет если данные за несколько лет?

Ну если контракт оформлен, например, в январе 2010 года. И оплата по нему идет в течении 3 лет, то 36 строк для января 2010 года и будет. Я не вдавалась в подробности, но и разделение на года у меня тоже присутствует. И сначало будут идти все месяцы 2010 года, потом 2011 и так далее.
5 мар 13, 16:15    [14015423]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
declare @payments table(mnt varchar(50),contractID int,payment numeric(24,2), paydiff numeric(24,2),monthdiff int)
insert into @payments(mnt,contractID,payment,paydiff,monthdiff)
values(
'Январь' , 1 , 100.00 , 25.00 , 0)
,('Январь',  1 , 100.00 , 35.00 , 1)
,('Январь' , 1 , 100.00 , 35.00 , 2)
,('Январь' , 2 , 200.00 , 25.00 , 2)
,('Февраль' , 3 , 50.00 , 25.00 , 3)
,('Февраль' , 4 , 750.00 , 100.00 , 0)
,('Февраль' , 4 , 750.00 , 100.00 , 1)
,('Февраль' , 4 , 750.00 , 100.00 , 2)
,('Февраль' , 4 , 750.00 , 150.00 , 3) 


			
;with contractSum as(
select
pp.mnt
,sum(pp.csum) csum
from  
(select
p.mnt
,p.contractID
,min(p.payment) csum
from @payments p
group by	p.mnt
			,p.contractID) pp
group by pp.mnt
)
,preSel as(
select  
epaydiff = SUM(paydiff) over(partition by p.mnt,p.monthdiff)
,epayment = cs.cSum
,p.*
from	@payments p
		inner join contractSum cs
		on cs.mnt = p.mnt
)
select 
s.mnt
,min(epaydiff)
,min(epayment)
,s.monthdiff

from preSel s
group by mnt,monthdiff
5 мар 13, 16:33    [14015574]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Мистер Хенки,

Спасибо за помощь! Переделываю запрос по Вашему образцу.
Сначало попыталась своими данными (insert из реальных таблиц) заполнить таблицу @payments. У меня результат инсерта 143880 записей. Но запрос выполняется уже более 6 минут. Потому я прервала его.


Попробовала напрямую работать с данными, заменила from @payments pcontractSum и в preSel) на свою выборку. Но тогда я получаю удвоенные суммы оплат по контракту. Но сумма по контрактам считается правильно.
5 мар 13, 17:58    [14016161]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Naile
Сначало попыталась своими данными (insert из реальных таблиц) заполнить таблицу @payments.

А сделать 2 запроса к отдельным таблицам и соединить их не пробовали ?
5 мар 13, 18:02    [14016192]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Glory,

Думала уже.
По идее оба этих запроса у меня уже есть.
Один запрос возвращает месяц, год и сумму оформленных контрактов, второй - месяц, год, сумму выплат для данной разницы в месяцах, ну и саму разницу в месяцах.

Т.е. одной строке в первом запросе может соотвествовать много строк во втором - в зависимости от выбранного диапазона дат.


Если честно - не знаю как их можно объединить.
5 мар 13, 18:12    [14016223]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Naile
Если честно - не знаю как их можно объединить.

"Один запрос возвращает - месяц, год и сумму оформленных контрактов"
"второй запрос возвращает - месяц, год, сумму выплат для данной разницы в месяцах, ну и саму разницу в месяцах"
Как вы думаете, какие поля из 7 семи перечисленных годятся для соединения ?
Неужели "сумма оформленных контрактов" или "сумма выплат" ?
5 мар 13, 18:16    [14016243]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и суммирование данных - вопрос  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Спасибо всем огромное!
Я сделала запрос!!!

Все данные тут советы, что называется, заставили повернуть мозги в нужную сторону.

Итак... Как я все оформила...

Я изменила начальный запрос.
Вместо того, чтобы напрямую брать SUM(сумма контрактов) я сделала INNER JOIN (SELECT...) amount ... результатом которого я УЖЕ получала сумму контрактов для данного месяца. А связка этого JOIN была именно по месяцу (конечно с учетом года).
И в главном SELECT я уже брала не SUM(сумма контрактов), а результат JOINa.
5 мар 13, 18:36    [14016302]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить