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

Откуда:
Сообщений: 162
Мне надо написать сложный запрос с уклоном в статистику. Надо сгруппировать по атрибутам и значениям. При этом атрибут Counter не группируется. Его значение суммируется для группы. Если попадается атрибут Counter один в группе то его удаляем. Исходные данные на коринке в приложении. Столбец Property_ID имеет два значения 22 и 33. 22 это атрибут quantity который нам надо суммировать.
Вот табличка:
attribute value count Property_ID INSTANCE_ID
quantity 2 3 22 9722
BagID bag 3 33 9722
ItemID item 3 33 9722
quantity 2 3 22 9723
BagID bag 3 33 9723
ItemID item 3 33 9723
quantity 5 2 22 9724
BagID bag 2 33 9724
quantity 10 2 22 9725
ItemID item 2 33 9725
BagID bag 1 33 9727

Тут SQL который формирует таблицу:

Проблема в группировке и сложении. BagID - bag группируются с ItemID - item, quantity суммируются = 4
Это группа INSTANCE_ID 9722 + 9723.
Из INSTANCE_ID 9724 получаем только BagID - bag с quantity = 5 итд ..
Для последнего случая с INSTANCE_ID 9727 quantity = 1 с ним нет проблем с помощью проверки по полю count.

Какой SQL нужен перед трансформацией PIVOT в котором будут attribute(columns), value, quantity
В приложении результирующея таблица:

К сообщению приложен файл. Размер - 6Kb
10 ноя 17, 16:55    [20944364]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и pivot  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
ErikI,

я если честно нихрена не понял с Вашего описания.
у вас по каким столбцам должна происходить группировка?

Это группа INSTANCE_ID 9722 + 9723.

вот это поясните почему группа 9722 + 9723 а не к примеру 9722 + 9725?
10 ноя 17, 17:19    [20944441]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и pivot  [new]
ErikI
Member

Откуда:
Сообщений: 162
Группировка значения двух полей attribute value. Если трансформировать в PIVOT то всё логично получается. Но поля динамические и их количество неизвестно.
10 ноя 17, 17:26    [20944467]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и pivot  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
ErikI,

ну и что у вас получится если группировать вашу таблицу по attribute value - пять групп.

BagID bag
ItemID item
quantity 10
quantity 2
quantity 5

приложенный кстати скрин не освещает где там Bag или Item
10 ноя 17, 17:37    [20944509]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и pivot  [new]
ErikI
Member

Откуда:
Сообщений: 162
Разумеется так и получается. Правильный результат для известных колонок.
В нем я указал BagID, ItemID далее оборачиваем в ещё один запрос и делаем группировку по двум полям BagID, ItemID и sum() для quantity. Но число полей не известно!
SELECT *
FROM 
    (
        SELECT  base.attribute
                , base.value
                , base.INSTANCE_ID
          FROM 
            (
                SELECT IIF(propLink.PROPERTY_ID=22, propName.ALIAS, ta.LABEL) AS attribute
                        , tiav.value
                      , COUNT(*) OVER (PARTITION BY tiav.INSTANCE_ID) AS count
                      , propLink.PROPERTY_ID
                      , tiav.INSTANCE_ID
                FROM ITEM
                  INNER JOIN CONTAINER_ITEM_LINK conItemLink ON item.ID = conItemLink.ITEM_ID
                    AND conItemLink.ID = (SELECT MAX(link.ID) FROM CONTAINER_ITEM_LINK link WHERE link.ITEM_ID = conItemLink.ITEM_ID AND link.CONTAINER_TYPE_ID = 3 AND link.IN_FLAG = 'Y')
                  INNER JOIN ITEM_STATUS_HISTORY ish ON conItemLink.ITEM_ID = ish.ITEM_ID
                    AND ish.id = (SELECT MAX(ID) FROM ITEM_STATUS_HISTORY WHERE ITEM_ID = conItemLink.ITEM_ID) AND ish.ITEM_STATUS_ID NOT IN (3, 4)
                  INNER JOIN TEMPLATE2_INSTANCE ti ON ITEM.TEMPLATE_INSTANCE_ID = ti.ID
                  INNER JOIN TEMPLATE2 temp ON ti.TEMPLATE_ID = temp.ID
                  INNER JOIN TEMPLATE_ATTRIBUTE ta ON temp.ID = ta.TEMPLATE_ID
                  INNER JOIN TEMPLATE_ATTRIBUTE_PROP propLink ON ta.ID = propLink.ATTRIBUTE_ID AND propLink.PROPERTY_ID IN (33, 22)
                  INNER JOIN TEMPLATE_ATTRIBUTE_PROP_DEF propName ON propLink.PROPERTY_ID = propName.ID
                  INNER JOIN TEMPLATE_INSTANCE_ATTR_VAL tiav ON tiav.ATTRIBUTE_ID = ta.ID AND tiav.INSTANCE_ID = item.TEMPLATE_INSTANCE_ID
                WHERE item.HID.GetLevel() = 1
                  AND conItemLink.CONTAINER_ID = 182258
            ) base
        WHERE NOT (base.PROPERTY_ID = 22 AND base.count = 1)
    ) pivotBase
PIVOT (
      	MAX(pivotBase.value)
      	FOR pivotBase.attribute IN ([BagID], [ItemID], [quantity]) 
      ) main


К сообщению приложен файл. Размер - 5Kb
10 ноя 17, 17:45    [20944545]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и pivot  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1364
ErikI,

declare @t table (
attribute varchar(255),
value varchar(255),
cnt int,
propID int,
instID int
)

insert into @t values ('quantity','2',3,22,9722)
insert into @t values ('BagID','bag',3,33,9722)
insert into @t values ('ItemID','item',3,33,9722)
insert into @t values ('quantity','2',3,22,9723)
insert into @t values ('BagID','bag',3,33,9723)
insert into @t values ('ItemID','item',3,33,9723)
insert into @t values ('quantity','5',2,22,9724)
insert into @t values ('BagID','bag',2,33,9724)
insert into @t values ('quantity','10',2,22,9725)
insert into @t values ('ItemID','item',2,33,9725)
insert into @t values ('BagID','bag',1,33,9727)

select attribute, value from @t group by attribute, value

;with x as (
select 
instID,
max(case when attribute = 'BagID' then value end) As [BagID],
max(case when attribute = 'ItemID' then value end) As [ItemID],
try_convert(int, max(case when attribute = 'quantity' then value end)) As [quantity]
from @t 
group by instID
)
,y as (
select BagID, ItemID, sum(quantity) as [summary]
from x
where quantity is not null
group by BagID, ItemID
)
select * from y


я честно попытался вкурить что вы хотите, но то ли пятница то ли вы не до конца описываете что хотите получить в итоге.
у вас там что возможно что потом группировка будет не по BagID - ItemID а по AssID1 - AssID2?
10 ноя 17, 18:14    [20944645]     Ответить | Цитировать Сообщить модератору
 Re: Группировка и pivot  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
ErikI
Если трансформировать в PIVOT то всё логично получается. Но поля динамические и их количество неизвестно.
Динамические поля ==> динамический SQL
11 ноя 17, 00:25    [20945215]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить