此查詢會提取每天每一小時的計數，但會忽略任何沒有計數的日期和時間。如何填寫以 0 為計數選擇的日期之間缺少的日期和小時數？
SELECT CAST(RecordTime AS date) AS Date, DATENAME(dw, RecordTime) AS [Day of the week], DATEPART(hour, RecordTime) AS [Hour of the day], COUNT(*) AS [Hourly Count]
FROM Counts
WHERE (RecordTime >= CONVERT(DATETIME, '2022-04-01 00:00:00', 102)) AND (RecordTime < CONVERT(DATETIME, '2022-05-01 00:00:00', 102)) AND (MachineNum = 11) AND (Cavity = 1)
GROUP BY CAST(RecordTime AS date), DATEPART(hour, RecordTime), DATENAME(dw, RecordTime)
ORDER BY Date, [Hour of the day]
uj5u.com熱心網友回復：
正如 Larnu 建議的那樣，您需要生成一個完整的資料集，其中包含該范圍內的所有天 小時組合，以便左連接。根據我的計算，您需要 30 天 * 24 小時 = 720 行。如果您還沒有數字表、日歷表或序列生成函式，則可以使用遞回 CTE 生成它，如下所示：
DECLARE @StartDate datetime = '20220401',
@AfterLastDate datetime = '20220501';
;WITH days(d) AS
(
SELECT 0 UNION ALL SELECT d 1 FROM days
WHERE d < DATEDIFF(DAY, @StartDate, @AfterLastDate) - 1
), hours(h) AS
(
SELECT 0 UNION ALL SELECT h 1 FROM hours WHERE h<23
),
dates(DayHour, h) AS
(
SELECT DATEADD(HOUR, hours.h, DATEADD(DAY, days.d, @StartDate)), hours.h
FROM days CROSS JOIN hours
)
SELECT d.DayHour, DATENAME(WEEKDAY, DayHour), d.h
FROM dates AS d
ORDER BY d.DayHour;
輸出：
日時 一周中的天 一天中的小時 2022-04-01 00:00:00.000 星期五 0 2022-04-01 01:00:00.000 星期五 1 2022-04-01 02:00:00.000 星期五 2 ... 714 更多行 ... 2022-04-30 21:00:00.000 周六 21 2022-04-30 22:00:00.000 周六 22 2022-04-30 23:00:00.000 周六 23
- 示例db<>fiddle
現在，我們只需要針對您現有的表離開外部連接：
DECLARE @StartDate datetime = '20220401',
@AfterLastDate datetime = '20220501';
;WITH days(d) AS
(
SELECT 0 UNION ALL SELECT d 1 FROM days
WHERE d < DATEDIFF(DAY, @StartDate, @AfterLastDate) - 1
), hours(h) AS
(
SELECT 0 UNION ALL SELECT h 1 FROM hours WHERE h<23
),
dates(DayHour, h) AS
(
SELECT DATEADD(HOUR, hours.h, DATEADD(DAY, days.d, @StartDate)),
hours.h FROM days CROSS JOIN hours
)
SELECT [Date] = CONVERT(date, d.DayHour),
[Day of the week] = DATENAME(WEEKDAY, d.DayHour),
[Hour of the day] = d.h,
[Hourly Count] = COUNT(c.RecordTime)
FROM dates AS d
LEFT OUTER JOIN dbo.Counts AS c
ON c.RecordTime >= d.DayHour
AND c.RecordTime < DATEADD(HOUR, 1, d.DayHour)
AND c.MachineNum = 11
AND c.Cavity = 1
GROUP BY CONVERT(date, d.DayHour), DATENAME(WEEKDAY, DayHour), d.h
ORDER BY [Date], [Hour of the day];
- 示例db<>fiddle
如果你有一個數字表，日期的生成會更容易一些。這是一個簡單的示例，僅包含 1,000 行作為您期望的最大日期范圍，并使用遞回 CTE - 最初有多種方法可以填充數字表，那里的性能并不重要。
CREATE TABLE dbo.Numbers(n int PRIMARY KEY);
;WITH x(x) AS
(
SELECT 0 UNION ALL SELECT x 1 FROM x
WHERE x < 1000
)
INSERT dbo.Numbers(n)
SELECT x FROM x OPTION (MAXRECURSION 0);
現在查詢獲取范圍內的所有日期：
DECLARE @StartDate datetime = '20220401',
@AfterLastDate datetime = '20220501';
;WITH dates(d) AS
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @AfterLastDate)) n
FROM dbo.Numbers ORDER BY n
),
hours(h) AS
(
SELECT TOP (24) n FROM dbo.Numbers ORDER BY n
)
SELECT DayHour = DATEADD(HOUR, hours.h,
DATEADD(DAY, dates.d, @StartDate))
FROM dates CROSS JOIN hours
ORDER BY DayHour;
然后，您可以將其用作左連接的核心資料集，就像上面的示例一樣。
uj5u.com熱心網友回復：
您需要有一個存盤所有日期和相應時間的參考表。這將是一種添加了小時的日歷表。
第1步：
創建餐桌時間。
CREATE TABLE hours
(
hour_key INTEGER
);
INSERT INTO hours
VALUES (0),
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12),
(13),
(14),
(15),
(16),
(17),
(18),
(19),
(20),
(21),
(22),
(23);
第2步：
創建一個包含所需日期和時間欄位的表。
SELECT Cast(recordtime AS DATE) AS Date_key,
Datename(dw, recordtime) week_key,
hour_key
FROM hours
CROSS JOIN counts
GROUP BY Cast(recordtime AS DATE),
Datename(dw, recordtime),
hour_key
注意：使用Counts表派生日期和小時欄位是一個糟糕的解決方案，因為它可能是一個事務表并且有大量記錄。而是使用日歷表。
最后一步：
使用在步驟 2中創建的表作為主表（或用作子查詢），left join Counts并根據日期和小時使用它。
下面的查詢應該為您提供所需的輸出。
SELECT date_key AS Date,
week_key AS [Day of the week],
hour_key AS [Hour of the day],
Count(c.recordtime) AS [Hourly Count]
FROM (SELECT Cast(recordtime AS DATE) AS Date_key,
Datename(dw, recordtime) week_key,
hour_key
FROM hours
CROSS JOIN counts
GROUP BY Cast(recordtime AS DATE),
Datename(dw, recordtime),
hour_key)cal
LEFT JOIN counts c
ON cal.date_key = Cast(c.recordtime AS DATE)
AND cal.hour_key = Datepart(hour, c.recordtime)
GROUP BY date_key,
week_key,
hour_key
ORDER BY date,
[hour of the day]
SQL Fiddle：在這里試試
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/476566.html