将每日数据聚合为每月数据SQL

Question

我有一份道路事故列表，根据类型(故障、撞车、火灾、危险等(而有所不同。目前我将其作为每日数据，但我需要将其汇总为月度数据。一个月被定义为每30天一次，因此如果我的日期范围为2021年3月3日至2021年5月27日，则预期输出为：

期开始日期结束类型事件计数分>011年5月1日1.05.21001年5月1日1.05.21

月份
1	03.03.21	01.04.21		2	21年4月2日	细分	30
3	02.05.21		1	03.03.21	01.04.21	崩溃
2	21年4月2日	崩溃	30
3	02.05.21

解决方案包括使用回避CTE查询构建一个日历，并将该日历连接到偶发事件表并执行GROUP BY。

以下是一个完整的数据生成示例(最后(：

DECLARE @startDate date = DATEFROMPARTS(2021, 3, 3);
DECLARE @endDate date = DATEFROMPARTS(2021, 12, 30);
WITH dateRange AS
(
SELECT  1 AS MonthNumber,
@startDate AS MonthStartDate,
IIF(DATEADD(DAY, 29, @startDate) > @endDate, @endDate, DATEADD(DAY, 29, @startDate))  AS MonthEndDate
UNION ALL
SELECT  1 + dra.MonthNumber,
DATEADD(DAY, 1, dra.MonthEndDate),
IIF(DATEADD(DAY, 29, DATEADD(DAY, 1, dra.MonthEndDate)) > @endDate, @endDate, DATEADD(DAY, 29, DATEADD(DAY, 1, dra.MonthEndDate)))  AS MonthEndDate
FROM dateRange dra
WHERE dra.MonthEndDate < @endDate AND dra.MonthNumber < 100
)
SELECT  dra.MonthNumber,
dra.MonthStartDate,
dra.MonthEndDate,
inc.IncidentType,
COUNT(1) AS IncidentCount
FROM #incident inc
INNER JOIN dateRange dra ON inc.IncidentDate BETWEEN dra.MonthStartDate AND dra.MonthEndDate
GROUP BY dra.MonthNumber, dra.MonthStartDate, dra.MonthEndDate, inc.IncidentType;

数据生成：

CREATE TABLE #incident
(
IncidentDate    date NOT NULL,
IncidentType    varchar(50) NOT NULL
);
INSERT INTO #incident
VALUES  (DATEFROMPARTS(2021, 3, 3), 'Breakdown'),
(DATEFROMPARTS(2021, 3, 4), 'Crash'),
(DATEFROMPARTS(2021, 3, 5), 'Fire'),
(DATEFROMPARTS(2021, 3, 6), 'Breakdown'),
(DATEFROMPARTS(2021, 3, 7), 'Breakdown'),
(DATEFROMPARTS(2021, 3, 7), 'Breakdown'),
(DATEFROMPARTS(2021, 3, 8), 'Crash'),
(DATEFROMPARTS(2021, 3, 9), 'Fire'),

(DATEFROMPARTS(2021, 5, 3), 'Breakdown'),
(DATEFROMPARTS(2021, 5, 4), 'Crash'),
(DATEFROMPARTS(2021, 5, 5), 'Fire'),
(DATEFROMPARTS(2021, 5, 6), 'Breakdown'),
(DATEFROMPARTS(2021, 5, 7), 'Breakdown'),
(DATEFROMPARTS(2021, 5, 7), 'Breakdown'),
(DATEFROMPARTS(2021, 5, 8), 'Crash'),
(DATEFROMPARTS(2021, 5, 9), 'Fire'),
(DATEFROMPARTS(2021, 7, 3), 'Breakdown'),
(DATEFROMPARTS(2021, 7, 4), 'Crash'),
(DATEFROMPARTS(2021, 7, 5), 'Fire'),
(DATEFROMPARTS(2021, 7, 6), 'Breakdown'),
(DATEFROMPARTS(2021, 7, 7), 'Breakdown'),
(DATEFROMPARTS(2021, 7, 7), 'Breakdown'),
(DATEFROMPARTS(2021, 7, 8), 'Crash'),
(DATEFROMPARTS(2021, 7, 9), 'Fire');

独立日历创建：

DECLARE @startDate date = DATEFROMPARTS(2021, 3, 3);
DECLARE @endDate date = DATEFROMPARTS(2021, 12, 30);
WITH dateRange AS
(
SELECT  1 AS MonthNumber,
@startDate AS MonthStartDate,
IIF(DATEADD(DAY, 29, @startDate) > @endDate, @endDate, DATEADD(DAY, 29, @startDate))  AS MonthEndDate
UNION ALL
SELECT  1 + dr.MonthNumber,
DATEADD(DAY, 1, dr.MonthEndDate),
IIF(DATEADD(DAY, 29, DATEADD(DAY, 1, dr.MonthEndDate)) > @endDate, @endDate, DATEADD(DAY, 29, DATEADD(DAY, 1, dr.MonthEndDate)))  AS MonthEndDate
FROM dateRange dr
WHERE dr.MonthEndDate < @endDate AND dr.MonthNumber < 100
)
SELECT  MonthNumber,
MonthStartDate,
MonthEndDate
FROM dateRange;