当前位置： 文江博客话题详情

如何找到丢失的小时并将数据显示为0

发布于 2025-01-30 04:29:24 字数 581 浏览 5 评论 0原文

该查询每天每天都有数量的数量，但留下了没有计数的任何天和小时。如何填写缺失的日期和在计数中选择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]
原文

This query pulls counts for every hour of every day but leaves off any days and hours that don't have counts. How can I fill in the missing dates and hours between the dates selected with 0 for the counts?

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]

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

甜心 2025-02-06 04:29:24

您需要有一个参考表，该参考表存储所有日期和各个小时。这将是一个日历表，并添加一个小时。

步骤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

注意：使用计数表来得出日期和小时字段是一个差的解决方案，因为它可能是交易表，并且具有大量记录。而是使用日历表。

最终步骤：

使用步骤2 中创建的表作为主表（或用作子查询），左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小提琴：在这里尝试

You need to have a reference table which stores all the dates and respective hour. It will be kind of a Calendar table with hour added to it.

Step 1:

Create table hours.

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);

Step 2:

Create a table which will have the required date and hour field.

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

Note: Using Counts table to derive the date and hour field is a poor solution, As it might be a transactional table and have huge number of records. Instead use a Calendar table.

Final Step:

Use the table created in Step 2 as the main table(or use as subquery) and left join Counts with it based on Date and hour.

Below query should give you the desired output.

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: Try it here

回复 原文
时常饿 2025-02-06 04:29:24

正如Larnu建议的那样，您需要生成一个全天+小时组合的完整数据集，以便将其左JON加入。通过我的计算，您需要30天 * 24小时= 720行。如果您还没有数字表或日历表或a 序列生成函数，您可以使用递归CTES生成此功能，如下所示：

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;

输出：

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
  • 示例

​

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];
  • ​href =“ https://dbfiddle.uk/?rdbms = sqlServer_2017l＆amp; fiddle = b87bf13da6d2c435b6e8b505b505d2ba1f72

”更轻松。这是一个简单的示例，仅包含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;

然后，您可以将其用作核心数据集，以便像上面的示例一样。

As Larnu suggested, you need to generate a full dataset with all day+hour combinations in the range, in order to left join to. By my calculation you need 30 days * 24 hours = 720 rows. If you don't already have a numbers table, or a calendar table, or a sequence generating function, you can generate this using recursive CTEs as follows:

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;

Output:

DayHourDay of the weekHour of the day
2022-04-01 00:00:00.000Friday0
2022-04-01 01:00:00.000Friday1
2022-04-01 02:00:00.000Friday2
... 714 more rows ...
2022-04-30 21:00:00.000Saturday21
2022-04-30 22:00:00.000Saturday22
2022-04-30 23:00:00.000Saturday23

Now, we just need to left outer join that against your existing table:

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];

If you have a numbers table, the generation of dates is a little easier. This is a simple example and only contains 1,000 rows as the largest range of dates you expect, and uses a recursive CTE - there are multiple ways to populate a numbers table initially and the performance there is not important.

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);

Now the query to get all the dates in the range:

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;

You can then use that as the core dataset to left join to just like in the above examples.

回复 原文
~没有更多了~

关于作者

书间行客

暂无简介

文章
评论
28 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

十二

文章 0 评论 0

飞烟轻若梦

文章 0 评论 0

OPleyuhuo

文章 0 评论 0

wxb0109

文章 0 评论 0

旧城空念

文章 0 评论 0

-小熊_

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文