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);
得出的结论是:
| ftimestamp | amount |
|---|---|
| 2023-01-04 21 | 1 |
| 2023-01-05 02 | 2 |
为了可视化的目的，如果我能得到这样的结果就太好了:
| ftimestamp | amount |
|---|---|
| 2023-01-04 21 | 1 |
| 2023-01-04 22 | 0 |
| 2023-01-04 23 | 0 |
| 2023-01-04 00 | 0 |
| 2023-01-04 01 | 0 |
| 2023-01-05 02 | 2 |
So all possible dates and hours where no data available shall have a zero.
There are enough data in the table to generate the list of all possible dates and hours with the following select:
SELECT
DATE_FORMAT(TIMESTAMP,"%Y-%m-%d %H") AS ftimestamp
FROM
history
GROUP BY
YEAR(TIMESTAMP),MONTH(TIMESTAMP),DAY(TIMESTAMP),HOUR(TIMESTAMP);
我不知道如何将这两个要求结合起来才能得到我想要的结果.