MariaDB获得按datetime分组的所有可能日期的计数值

Question

1.48表，看起来像这样:

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

我想有一个专门的设备按日期和小时分组的事件的数量，这样工作得很好:

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

并得到结果:

tbody> <<tr>

ftimestamp	amount
2023-01-04 21	1
2023-01-05 02	2

如果表中有可用的所有日期，则可以使用条件聚合:

select date_format(timestamp, '%Y-%m-%d %H') as timestamp_hr,
sum(device = 'DL_Motion' and reading = 'state' and value = 'motion') as amount
from history
group by date_format(timestamp, '%Y-%m-%d %H')

逻辑是扫描整个表，以便我们获得所有可用的日期，然后使用条件sum()仅计算相关行。

注意，我在select和group by子句中对齐了日期表达式，这在标准SQL中更有意义。