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

Откуда: Кемеровская обл.
Сообщений: 250
Если упростить и свести к сути вопроса, то он выглядит так.
Есть таблица:
Поле1 Поле2 Поле3
Запись1 2 58
Запись2 33 548
Запись3 8 89
Запись1 -2 -60
Запись3 -30 -600
Запись1 5 120

Нужно сгруппировать в запросе записи таким образом, чтобы получилось:
Поле1 Поле2 Поле3
Запись1 0 -2
Запись2 33 548
Запись3 -22 511
Запись1 5 120

Т.е. группировка происходит по полю "Поле1", Поле2 и Поле3 суммируются, но при этом каждый раз, когда сумма Поле2 равна 0 - то это получается отдельная группа. Что-нить понятно? Ни фига не могу понятно объяснить! )))))
Т.е. в примере получилось после группировки две строки по Запись1, т.к. 2 + -2 = 0 (выделяется в одну строку)
15 окт 09, 12:29    [7790177]     Ответить | Цитировать Сообщить модератору
 Re: Интересная группировка  [new]
DionX
Member

Откуда: Кемеровская обл.
Сообщений: 250
Есть таблица:
Поле1 Поле2 Поле3
Запись1 | 2 | 58
Запись2 | 33 | 548
Запись3 | 8 | 89
Запись1 | -2 | -60
Запись3 | -30 | -600
Запись1 | 5 | 120

Нужно сгруппировать в запросе записи таким образом, чтобы получилось:
Поле1 Поле2 Поле3
Запись1 | 0 | -2
Запись2 | 33 | 548
Запись3 | -22 | 511
Запись1 | 5 | 120

Т.е. группировка происходит по полю "Поле1", Поле2 и Поле3 суммируются, но при этом каждый раз, когда сумма Поле2 равна 0 - то это получается отдельная группа. Что-нить понятно? Ни фига не могу понятно объяснить! )))))
Т.е. в примере получилось после группировки две строки по Запись1, т.к. 2 + -2 = 0 (выделяется в одну строку)
Такая же результирущая таблица должны получиться, если, например, будет в исходной не:
Запись1 | -2 | -60
а двумя строками:
Запись1 | -1 | -30
Запись1 | -1 | -30

Возможно такое?
15 окт 09, 12:33    [7790224]     Ответить | Цитировать Сообщить модератору
 Re: Интересная группировка  [new]
Glory
Member

Откуда:
Сообщений: 104760
И в каком же порядке вы предлагаете производить это суммирование ?
15 окт 09, 12:35    [7790239]     Ответить | Цитировать Сообщить модератору
 Re: Интересная группировка  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
А что делать если Поле2 = 0? В какую группу его пихать, в верхнюю, в нижнюю или отдельно?

Ощущение, что если мелко-мягкие реализовали бы ORDER BY для агрегатов (типа Sum), то рай для постояльцев этого форума стал бы в 10 раз ближе.
Невероятное число похожих задач.
;WITH ТаблицаПеренос AS (
	SELECT	 Поле1
		,CASE	WHEN Sum(Поле2)OVER(PARTITION BY Поле1 ORDER BY Поле4) - Поле2 = 0	-- Отдельно
		--	 AND Поле2 != 0								-- В верхнюю
		--	WHEN Sum(Поле2)OVER(PARTITION BY Поле1 ORDER BY Поле4)         = 0
		--	 AND Поле2 != 0								-- В нижнюю
			THEN 1
			ELSE 0
			END	AS Поле2Перенос
		,Поле2
		,Поле3
		,Поле4
	FROM	Таблица
), ТаблицаГруппа AS (
	SELECT	 Поле1
		,Sum(Поле2Перенос)OVER(PARTITION BY Поле1 ORDER BY Поле4     )	AS Поле2Группа	-- В верхнюю или отдельно
	--	,Sum(Поле2Перенос)OVER(PARTITION BY Поле1 ORDER BY Поле4 DESC)	AS Поле2Группа	-- В нижнюю
		,Поле2
		,Поле3
	FROM	ТаблицаПеренос
)
	SELECT	 Поле1
		,Sum(Поле2)	AS Поле2
		,Sum(Поле3)	AS Поле3
	FROM	ТаблицаГруппа
	GROUP BY Поле1
		,Поле2Группа
Может заставить каждого топикастера, по подобному вопросу, голосовать за данную реализацию на сайте микрософта? :)
15 окт 09, 23:44    [7794213]     Ответить | Цитировать Сообщить модератору
 Re: Интересная группировка  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Из слов
автор
каждый раз, когда сумма Поле2 равна 0 - то это получается отдельная группа
сделал вывод, что в стартовом топике указаны нарастающие итоги, но не хватает поля, по которому одна запись считается младше/старше другой (в пределах одного и того же значения f1). Поэтому решил просто добавить поле pk (типа "первичный ключ") и сравнивать записи по нему, иначе не ясно, как считать нарастающие итоги.

Нижеследующие рассуждения есть лишь предположение того, что хотел сказать автор. Если я неверно всё понял - пардон.
Для каждой строки известна "главная" группа - поле f1. Но еще надо определить, есть ли ниже её строка, нарастающий итог по достижении которой становится равным нулю. Эту строку для краткости обозначим Z. Если таких Z-строк в пределах одного и того же f1 будет несколько, то брать следует БЛИЖАЙШУЮ "снизу" к текущей строке Z-строку. Если же текущая строка сама содержит накопительный итог = 0, то она САМА ДЛЯ СЕБЯ и является Z-строкой. След-но, на вопрос "куда относить строку с нулем" ответ - к группе строк, расположенной ВЫШЕ неё.

В итоге, получилось следующее.
1) тестовый набор от автора:
+
declare @t table(pk int identity,f1 varchar(10), f2 int, f3 int)
insert into @t
select 'rec1',  2,   58 union all
select 'rec2', 33,  548 union all
select 'rec3',  8,   89 union all
--select 'rec1', -2,  -60 union all
select 'rec1', -1,  -30 union all
select 'rec1', -1,  -30 union all

select 'rec3',-30, -600 union all
select 'rec1',  5,  120
2) этот же тестовый набор, но дополненный данными для f1='rec1' так, что Z-строк будет НЕСКОЛЬКО (след-но, будет НЕСКОЛЬКО новых групп):
+
declare @t table(pk int identity,f1 varchar(10), f2 int, f3 int)
insert into @t
select 'rec1',  2,   58 union all
select 'rec2', 33,  548 union all
select 'rec3',  8,   89 union all
--select 'rec1', -2,  -60 union all
select 'rec1', -1,  -30 union all
select 'rec1', -1,  -30 union all

select 'rec3',-30, -600 union all
select 'rec1',  5,  120

insert into @t
select 'rec4', 33,  548 union all
select 'rec5',  8,   89 union all
select 'rec6', 33,  548 union all
select 'rec7',  8,   89 union all
select 'rec1', -1,  -14 union all
select 'rec1', -1,  -15 union all
select 'rec1', -1,  -16 union all
select 'rec1', -1,  -17 union all
select 'rec1', -1,  -18 union all
select 'rec1', 19,   19 union all
select 'rec1',  7,  -59 union all
select 'rec1', -8,  -48 union all
select 'rec1', -9,  -37 union all
select 'rec1', -5,  -26 union all
select 'rec1', -4,   25 union all
select 'rec1', -3,   35 union all
select 'rec1', -2,   45 union all
select 'rec1', -6,   55

3) сам запрос:
;with
cg as
( -- наращиваемые итоги (поле f2runTotal) по полю f2, в порядке возрастания поля pk для групп строк с одинаковым f1
  select t1.pk,t1.f1,t1.f2,t1.f3,sum(t2.f2) f2runTotal
    from @t t1
    join @t t2 on t1.f1=t2.f1 and t2.pk<=t1.pk
   group by t1.pk,t1.f1,t1.f2,t1.f3
)
--select * from cg order by f1,pk

,cg2 as
( -- для каждой строки, относящейся к группе строк с одним и тем же f1 и имеющую накопительный итог <>0,
  -- определяем ближайшую к ней запись,  расположенную "снизу" (т.е. с бОльшим значением pk) и имеющую 
  -- накопительный итог = 0. Эта найденная "внизу" строка + её pk будет "номером" группы, к которой относится
  -- текущая строка.
  -- Если же текущая строка содержит накопительный итог = 0, то для неё "номером группы" будет её же f1+pk
  select c1.pk,c1.f1,c1.f2,c1.f3,c1.f2runTotal
        ,zeroRTKey=
          isnull(  min(c2.f1+right(cast(1000000000+c2.pk as varchar),9))
                  ,c1.f1+case when c1.f2runTotal=0 then right(cast(1000000000+c1.pk as varchar),9) else '' end
                )
    from cg c1
    left join cg c2 on c1.pk<=c2.pk and c1.f1=c2.f1 and c2.f2runTotal=0 and (c1.f2runTotal<>0)
   group by c1.pk,c1.f1,c1.f2,c1.f3,c1.f2runTotal 
)
--select * from cg2 order by f1,zeroRTKey

select f1 /*,zeroRTKey*/
     ,f2sum=sum(f2)
     ,f3sum=sum(f3)
from cg2
group by f1,zeroRTKey
Результат для авторского набора данных:
f1f2sumf3sum
rec15120
rec10-2
rec233548
rec3-22-511


PS. Всё-таки топикстартеру не мешало бы дать более полный пример с данными. Есть непонятки, чего там на самом деле ему нужно :-/
16 окт 09, 01:57    [7794441]     Ответить | Цитировать Сообщить модератору
 Re: Интересная группировка  [new]
DionX
Member

Откуда: Кемеровская обл.
Сообщений: 250
Mnior
А что делать если Поле2 = 0? В какую группу его пихать, в верхнюю, в нижнюю или отдельно?

Такое не возможно, т.к. в Поле2 - это кол-во покупки/продажи
16 окт 09, 03:39    [7794481]     Ответить | Цитировать Сообщить модератору
 Re: Интересная группировка  [new]
DionX
Member

Откуда: Кемеровская обл.
Сообщений: 250
2 Ozzy-Osbourne
Отлично!)))
Ты всё правильно понял.
В качестве поля для сравнения у меня будут поля с датой и временем. Что то тупанул, не указал это при формировании вопроса.
16 окт 09, 03:55    [7794482]     Ответить | Цитировать Сообщить модератору
 Re: Интересная группировка  [new]
DionX
Member

Откуда: Кемеровская обл.
Сообщений: 250
2 Mnior
Тоже молодец.
Буду заниматься изучением предложенных вариантов. ))
16 окт 09, 03:57    [7794483]     Ответить | Цитировать Сообщить модератору
 Re: Интересная группировка  [new]
aleks2
Guest
DionX
2 Mnior
Тоже молодец.


Рядовые, ВОЛЬНО! Разойдись!
16 окт 09, 06:40    [7794534]     Ответить | Цитировать Сообщить модератору
 Re: Интересная группировка  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
DionX
в Поле2 - это кол-во покупки/продажи
Так и думал что данные промежуточные. Возможно окончательный запрос сильно-сильно преобразиться, и возможно упростится.
Или я не так понял - вы так круты, что обязательно есть покупка и продажа. Короче раскройте тайну Поля 3.

DionX
2 Mnior
Тоже молодец.
За мячты спасибо? Но проголосовать за ORDER BY желательно.
16 окт 09, 08:34    [7794641]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить