Как написать sql-запрос с агрегацией по сумме, но с учетом нулей

Question

Как написать sql-запрос с агрегацией по сумме, но с учетом нулей

Рейтинг: 0Ответов: 1Опубликовано: 14.03.2023

Всем привет! Есть таблица с производством изделий, временем производства и категорией изделия (всего их 4). Необходимо вывести сумму произведенных товаров по каждой категории за каждый час. Проблема в том, что в каждый час может не быть всех категорий изделий, поэтому в таблице очевидно мы видим данные за час по одной, двум, или трем категориям. Необходимо сделать так, чтобы за каждый час выводились данные по всем категориям, если данных нет, то выводился ноль. Нашел решение подобных кейсов (sum(case when a = 1 then 1 else 0 end)), но они не подходят, потому что в таблице нет (или я не вижу) условия, которое можно было бы отправить в case.

Текущий запрос:

select cast([Период]) as date) as 'Дата'
,datepart(hour,[Период]) as 'Час'
,CASE WHEN THEN END AS 'Участок'
,sum(v.Количество) AS 'Количество'
FROM...
GROUP BY cast([Период]) as date)
,datepart(hour,[Период])
,CASE WHEN THEN END
ORDER BY cast([Период]) as date)
,datepart(hour,[Период])

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

Спасибо!

sql sql-server

Источник: Stack Overflow на русском

Answer 1

▲ 0

Можно рассмотреть такой пример составления запроса. Пример данных:

create table test (period datetime,categoryid int,qty float);
create table category(categoryid int,site varchar(30));
insert into category values
 (1,'Proizvodstvo PVC')
,(2,'Proizvodstvo Alum')
,(3,'Proizvodstvo RSU(PVC)')
,(4,'Proizvodstvo Glass')
;
select * from category;
insert into test values
 (cast('2023-03-15 00:00:00' as datetime),1,12)
,(cast('2023-03-15 00:00:00' as datetime),2,13)
,(cast('2023-03-15 01:00:00' as datetime),2,1)
,(cast('2023-03-15 02:00:00' as datetime),3,1)
,(cast('2023-03-15 03:00:00' as datetime),4,1)
,(cast('2023-03-15 07:00:00' as datetime),1,1)
,(cast('2023-03-15 07:00:00' as datetime),2,1)
,(cast('2023-03-15 08:00:00' as datetime),3,1)
;
select * from test;

В описании вопроса не указана связь строк данных производства с категориями CASE WHEN THEN END AS 'Участок'. Предположим, что там явно задан Id участка производства, и имеем таблицу участков. Связь может быть организована другим образом, в данном случае не имеет значения.
Создадим запрос - структуру отчета, в которой отображены все необходимые даты, участки и часы

with Str as(
select * 
from  -- рабочие дни
(select distinct cast(period as date) workday from test) tdays
-- категории
,category 
-- рабочие часы
cross apply( values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))WorkHours(h)
)

Далее Ваш запрос выборки фактических данных с группировкой по дате, часу и категории
и соединение со структурной таблицей

,data as(  
select cast([period] as date) as 'Дата'
,datepart(hour,[period]) as 'Час'
,categoryid AS 'Участок'
,sum(qty) AS 'Количество'
FROM test
GROUP BY cast([period] as date)
,datepart(hour,[period])
,categoryid
)
select * 
from str s
full join data t  on s.workday=t.[Дата] and s.categoryid=t.[Участок] and s.h=t.[Час]
ORDER BY s.workday,s.h,s.categoryid

Получим ответ в таком виде

workday	categoryid	site	h	Дата	Час	Участок	Количество
2023-03-15	1	Proizvodstvo PVC	0	2023-03-15	0	1	12
2023-03-15	2	Proizvodstvo Alum	0	2023-03-15	0	2	13
2023-03-15	3	Proizvodstvo RSU(PVC)	0	null	null	null	null
2023-03-15	4	Proizvodstvo Glass	0	null	null	null	null
2023-03-15	1	Proizvodstvo PVC	1	null	null	null	null
2023-03-15	2	Proizvodstvo Alum	1	2023-03-15	1	2	1
2023-03-15	3	Proizvodstvo RSU(PVC)	1	null	null	null	null
2023-03-15	4	Proizvodstvo Glass	1	null	null	null	null

Можно подчистить и получить необходимый Вам вид результата

,data as(  
select cast([period] as date) workday 
   ,datepart(hour,[period]) workhour
   ,categoryid
   ,sum(qty) qty
FROM test
GROUP BY cast([period] as date)
   ,datepart(hour,[period])
   ,categoryid
)
select s.workday as 'Дата', s.h as 'Час',s.site AS 'Участок'
  ,isnull(t.qty,0) AS 'Количество'
from str s
full join data t  on s.workday=t.workday and s.categoryid=t.categoryid and s.h=t.workhour
ORDER BY s.workday,s.h,s.categoryid

Пример здесь

Как написать sql-запрос с агрегацией по сумме, но с учетом нулей

Ответы