当前位置：首页 > article >正文

如何在Mysql中生成0-23完整的小时数据

article 2024/11/19 2:58:58

在数据分析中，我们经常需要对特定时间段内的数据进行统计和分析。
例如，在名片进线的场景中，我们可能需要了解一天内每小时的名片进线数量。
然而，由于某些时间点可能没有数据记录，这会导致数据的不连续性。为了解决这个问题，我们需要生成一个0-23小时的完整数据集，即使在没有进线的情况下，也显示为0，以确保数据的完整性和分析的准确性。

1. 创建表

首先，我们需要创建一个表来存储0-23小时的数据。这个表将只包含一个hour列，用于表示小时数。

CREATE TABLE IF NOT EXISTS hourly_data (
    hour INT NOT NULL,
    PRIMARY KEY (hour)
);

2. 插入0-23小时的数据

接下来，我们将0-23小时的数据插入到hourly_data表中。这里我们使用23条INSERT语句，每条语句插入一个小时的数据。

INSERT INTO hourly_data (hour) VALUES (0);
INSERT INTO hourly_data (hour) VALUES (1);
INSERT INTO hourly_data (hour) VALUES (2);
INSERT INTO hourly_data (hour) VALUES (3);
INSERT INTO hourly_data (hour) VALUES (4);
INSERT INTO hourly_data (hour) VALUES (5);
INSERT INTO hourly_data (hour) VALUES (6);
INSERT INTO hourly_data (hour) VALUES (7);
INSERT INTO hourly_data (hour) VALUES (8);
INSERT INTO hourly_data (hour) VALUES (9);
INSERT INTO hourly_data (hour) VALUES (10);
INSERT INTO hourly_data (hour) VALUES (11);
INSERT INTO hourly_data (hour) VALUES (12);
INSERT INTO hourly_data (hour) VALUES (13);
INSERT INTO hourly_data (hour) VALUES (14);
INSERT INTO hourly_data (hour) VALUES (15);
INSERT INTO hourly_data (hour) VALUES (16);
INSERT INTO hourly_data (hour) VALUES (17);
INSERT INTO hourly_data (hour) VALUES (18);
INSERT INTO hourly_data (hour) VALUES (19);
INSERT INTO hourly_data (hour) VALUES (20);
INSERT INTO hourly_data (hour) VALUES (21);
INSERT INTO hourly_data (hour) VALUES (22);
INSERT INTO hourly_data (hour) VALUES (23);

3. 查询并合并数据

最后，我们需要查询并合并数据。我们将使用SELECT语句，通过LEFT JOIN将hourly_data表和business_cards表结合起来，以确保每个小时都有数据，即使某些小时没有进线数据也显示为0。

SELECT a.hour, IFNULL(b.business_card_count, 0) AS business_card_count
FROM hourly_data a
LEFT JOIN (
    SELECT HOUR(create_time) AS hour, COUNT(*) AS business_card_count
    FROM business_cards
    GROUP BY HOUR(create_time)
) b ON a.hour = b.hour
ORDER BY a.hour;