CODEWITHSUNDEEP
mysqlsql
Haider Ali
Haider Ali

Reputation: 15

MySQL query to fetch row count of hourly records inserted and max record per minute in that hour

I have the table with following fields

id CreatedOn(yyyymmddhhmmss)           Channel_id

10 20200617160916792                      13

11 20200617160919792                      13
 
12 20200617170919792                      13

13 20200617170919792                      13

14 20200617160920212                      14

I need a query to check how many total records are inserted in particular hour and maximum records per minutes in that hour based on channel Id. For example if 100 record are inserted at 15:00-16:00 then what is maximum record insert per minutes. Please help me creating a query to find the required solution

I have run this query, Although it is given me total record per hour but I also want to know how maximum are inserted per min.

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;

Need output as

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

Upvotes: 0

Views: 560

Answers (2)

ajay Chaudhary
ajay Chaudhary

Reputation: 1

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

Upvotes: 0

Akina
Akina

Reputation: 42728

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

Upvotes: 2

Related Questions