Блог

У меня есть таблица, которая содержит данные о продажах на уровне транзакций. Я пытаюсь удовлетворить запрос на отчетность как можно эффективнее, но не думаю, что сейчас мне это удается. Вот некоторые тестовые данные:

 DROP TABLE IF EXISTS TMP_SALES_DATA;
CREATE TABLE TMP_SALES_DATA ([DATE] DATE, [ITEM] INT, [STORE] CHAR(6), [TRANS] INT, [SALES] DECIMAL(8,2));
INSERT INTO TMP_SALES_DATA
VALUES
('9-29-2020',101,'Store1',123,1.00),
('9-29-2020',102,'Store1',123,2.00),
('9-29-2020',103,'Store1',123,3.00),
('9-29-2020',101,'Store1',124,1.00),
('9-29-2020',101,'Store1',125,1.00),
('9-29-2020',103,'Store1',125,3.00),
('9-29-2020',102,'Store1',126,2.00),
('9-29-2020',101,'Store2',88,1.00),
('9-29-2020',102,'Store2',88,2.00),
('9-29-2020',103,'Store2',88,3.00),
('9-29-2020',101,'Store2',89,1.00),
('9-29-2020',101,'Store2',90,1.00),
('9-29-2020',102,'Store2',91,2.00),
('9-29-2020',103,'Store2',91,3.00),
('9-29-2020',101,'Store3',77,1.00);
  

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

 -- Item sales
SELECT [ITEM], SUM([SALES]) AS [SALES]
FROM TMP_SALES_DATA
WHERE [ITEM] IN (101,103) AND [STORE] IN ('Store1','Store2' ,'Store3') AND [DATE] = '9-29-2020'
GROUP BY [ITEM]
  

Возвращает это:

 ITEM    SALES
101     7.00
103     12.00
  

И я могу получить общую сумму транзакций продаж, в которых присутствовал один товар, таким образом:

 -- Total transaction sales in which ITEM 101 exists
SELECT SUM(S1.[SALES]) AS [TTL_TRANS_SALES]
FROM TMP_SALES_DATA S1
WHERE EXISTS (SELECT 1 FROM TMP_SALES_DATA S2 WHERE S2.[DATE]=S1.[DATE] AND S2.[STORE]=S1.[STORE] AND S2.[TRANS]=S1.[TRANS] AND S2.[ITEM]=101 AND S2.[STORE] IN ('Store1','Store2','Store3') AND S2.[DATE] = '9-29-2020')

-- Total transaction sales in which ITEM 103 exists
SELECT SUM(S1.[SALES]) AS [TTL_TRANS_SALES]
FROM TMP_SALES_DATA S1
WHERE EXISTS (SELECT 1 FROM TMP_SALES_DATA S2 WHERE S2.[DATE]=S1.[DATE] AND S2.[STORE]=S1.[STORE] AND S2.[TRANS]=S1.[TRANS] AND S2.[ITEM]=103 AND S2.[STORE] IN ('Store1','Store2','Store3') AND S2.[DATE] = '9-29-2020')
  

Но мне не удается найти чистый, эффективный и динамичный способ вернуть все это в одном запросе. Конечный пользователь сможет указать товары / магазины / даты для этого отчета. Конечный результат, который я хотел бы видеть, таков:

 ITEM    SALES   TTL_TRANS_SALES
101     7.00    20.00
103    12.00    21.00
  

Если я правильно понимаю, вы можете использовать оконные функции для суммирования по транзакциям, а затем агрегировать:

 select item, sum(sales), sum(trans_sale)
from (select ts.*, sum(sales) over (partition by trans) as trans_sale
      from tmp_sales_data ts
     ) ts
group by item;
  

Вот скрипка db<> .

Вы можете добавить соответствующую фильтрацию в подзапрос.