MySQL查询，以获取插入的每小时记录的行数和该小时内每分钟的最大记录数

Question

我有一个带有以下字段的表

id CreatedOn(yyyymmddhhmmss)           Channel_id
10 20200617160916792                      13
11 20200617160919792                      13

12 20200617170919792                      13
13 20200617170919792                      13
14 20200617160920212                      14

我需要一个查询来检查在特定的小时内插入了多少总记录，以及根据频道Id在该小时内每分钟插入的最大记录。例如，如果在15:00-16:00插入了100条记录，那么每分钟插入最多记录是多少。请帮助我创建查询以查找所需的解决方案

我已经运行了这个查询，虽然它给了我每小时的总记录，但我也想知道每分钟插入的最大值是多少。

SELECT DATE_FORMAT(CreatedOn, '%H:00') as hours,
count(CreatedOn) as total_hit, channel_id
FROM tbl_transaction_flow where DATE_FORMAT(CreatedOn, '%Y-%m-%d')  = DATE(NOW())
GROUP BY DATE_FORMAT(CreatedOn, '%Y-%m-%d %H:00'),channel_id;

需要作为输出

hours      total_hits   channel_id    max_hit_per_minutes
16:00       100           13           20
17:00       10            13            1

Answer 1

WITH cte AS ( SELECT DATE_FORMAT(CreatedOn, '%H:%i') as hours_minutes,
COUNT(CreatedOn) as hits_per_minute, 
Channel_id
FROM tbl_transaction_flow 
WHERE DATE(CreatedOn) = /* CURRENT_DATE */ '2020-06-17'
GROUP BY hours_minutes, Channel_id )
SELECT DISTINCT
CONCAT(LEFT(hours_minutes, 2), ':00') hours,
Channel_id,
SUM(hits_per_minute) OVER win hits_per_hour,
MAX(hits_per_minute) OVER win max_hits_per_minute
FROM cte
WINDOW win AS (PARTITION BY Channel_id, LEFT(hours_minutes, 2));

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b2635180061e45e761864ff7b4b018f3

SELECT CONCAT(LEFT(hours_minutes, 2), ':00') hours,
Channel_id,
SUM(hits_per_minute) hits_per_hour,
MAX(hits_per_minute) max_hits_per_minute
FROM ( SELECT DATE_FORMAT(CreatedOn, '%H:%i') as hours_minutes,
COUNT(CreatedOn) as hits_per_minute, 
Channel_id
FROM tbl_transaction_flow 
WHERE DATE(CreatedOn) = /* CURRENT_DATE */ '2020-06-17'
GROUP BY hours_minutes, Channel_id ) per_minute_data
GROUP BY hours, Channel_id;

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=d7f14899377fc491efb4ca8635e4a4a6

Answer 2

SELECT date_format(created_at,'%H %p') AS hour, count(*) AS total_record 
FROM users 
WHERE CAST(created_at AS DATE) ="2021-12-15" 
GROUP BY date_format(created_at,'%H %p')