MariaDB获取所有可能日期的按日期时间分组的计数值

z8dt9xmd  于 2023-01-09  发布在  其他
关注(0)|答案(1)|浏览(172)

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

结果是:
| f时间戳|数量|
| - ------| - ------|
| 二○二三年一月四日|1个|
| 二○二三年一月五日|第二章|
出于可视化的目的,如果我能得到这样的结果就太好了:
| f时间戳|数量|
| - ------| - ------|
| 二○二三年一月四日|1个|
| 二○二三年一月四日|无|
| 二○二三年一月四日|无|
| 二○二三年一月四日|无|
| 二○二三年一月四日|无|
| 二○二三年一月五日|第二章|
因此,所有没有可用数据的可能日期和小时均应为零。
表中有足够的数据生成所有可能日期和小时的列表,选择以下选项:

SELECT
    DATE_FORMAT(TIMESTAMP,"%Y-%m-%d %H") AS ftimestamp
FROM
    history
GROUP BY
    YEAR(TIMESTAMP),MONTH(TIMESTAMP),DAY(TIMESTAMP),HOUR(TIMESTAMP);

我不知道如何把这两个要求结合起来才能得到我需要的结果。

smtd7mpg

smtd7mpg1#

如果表中的所有日期都可用,则可以使用条件聚集:

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()只计算相关的行。
注意,我在selectgroup by子句中对齐了日期表达式,这在标准SQL中更有意义。

相关问题