MariaDB按日期小时分组统计含零值结果的实现方法

现有表结构 #

CREATE TABLE `history` (
    `TIMESTAMP` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `DEVICE` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `TYPE` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `EVENT` VARCHAR(512) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `READING` VARCHAR(64) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `VALUE` VARCHAR(128) NULL DEFAULT NULL COLLATE 'utf8_bin',
    `UNIT` VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8_bin'
)
COLLATE='utf8_bin'
ENGINE=InnoDB;

示例数据 #

INSERT INTO `history` (`TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT`) VALUES ('2023-01-04 21:16:06', 'DL_Motion', 'CUL_HM', 'state: motion', 'state', 'motion', '');
INSERT INTO `history` (`TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT`) VALUES ('2023-01-04 22:31:09', 'CD_Motion', 'CUL_HM', 'state: motion', 'state', 'motion', '');
INSERT INTO `history` (`TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT`) VALUES ('2023-01-04 23:24:58', 'AB_Motion', 'CUL_HM', 'state: motion', 'state', 'motion', '');
INSERT INTO `history` (`TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT`) VALUES ('2023-01-05 00:25:58', 'XY_Motion', 'CUL_HM', 'state: motion', 'state', 'motion', '');
INSERT INTO `history` (`TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT`) VALUES ('2023-01-05 01:27:58', 'XY_Motion', 'CUL_HM', 'state: motion', 'state', 'motion', '');
INSERT INTO `history` (`TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT`) VALUES ('2023-01-05 02:27:58', 'DL_Motion', 'CUL_HM', 'state: motion', 'state', 'motion', '');
INSERT INTO `history` (`TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT`) VALUES ('2023-01-05 02:29:02', 'DL_Motion', 'CUL_HM', 'state: motion', 'state', 'motion', '');

当前查询的局限 #

现有统计SQL仅能返回目标设备有事件数据的时段，无数据的时段不会出现在结果中，无法满足可视化需展示所有存在的日期小时、无数据时段显示0的需求：

SELECT 
  DATE_FORMAT(TIMESTAMP,"%Y-%m-%d %H") AS ftimestamp,
  COUNT(TIMESTAMP) AS amount
FROM 
    history
WHERE  
  DEVICE = 'DL_Motion' 
  AND READING = 'state'
  AND VALUE = 'motion'
GROUP BY 
    YEAR(TIMESTAMP),MONTH(TIMESTAMP),DAY(TIMESTAMP),HOUR(TIMESTAMP); 

解决方案 #

通过生成全时段列表并左关联统计数据的方式，实现全时段展示，无数据时段用IFNULL填充为0。

完整SQL #

SELECT 
  all_hours.ftimestamp,
  IFNULL(stat.amount, 0) AS amount
FROM 
  -- 生成所有存在的日期小时列表
  (SELECT 
     DATE_FORMAT(TIMESTAMP,"%Y-%m-%d %H") AS ftimestamp
   FROM 
     history
   GROUP BY 
     YEAR(TIMESTAMP), MONTH(TIMESTAMP), DAY(TIMESTAMP), HOUR(TIMESTAMP)
  ) AS all_hours
LEFT JOIN 
  -- 统计目标设备的事件数
  (SELECT 
     DATE_FORMAT(TIMESTAMP,"%Y-%m-%d %H") AS ftimestamp,
     COUNT(TIMESTAMP) AS amount
   FROM 
     history
   WHERE  
     DEVICE = 'DL_Motion' 
     AND READING = 'state'
     AND VALUE = 'motion'
   GROUP BY 
     YEAR(TIMESTAMP), MONTH(TIMESTAMP), DAY(TIMESTAMP), HOUR(TIMESTAMP)
  ) AS stat
ON all_hours.ftimestamp = stat.ftimestamp
ORDER BY all_hours.ftimestamp;

逻辑说明 #

1. 子查询all_hours：从history表中提取所有出现过的日期小时，生成完整的时段列表。
2. 子查询stat：按原逻辑统计目标设备的事件数，仅包含有数据的时段。
3. 左关联：将全时段列表与统计结果关联，确保所有时段都出现在结果中。
4. IFNULL(stat.amount, 0)：将无匹配的时段（即无数据）的amount字段替换为0。

示例结果 #

针对示例数据，查询结果如下：

内容的提问来源于stack exchange，提问作者Peter H