SQL - Grouping SQL output in 5 minute intervals

1

I have a dataset that has a date column every 1 minute. I wanted to group the information to have the data "organized" every 5 minutes and average the SellCount column.

For example, I have 5 records, 1 for minute 1, another for minute 2, another for minute 3, another for minute 4 and finally another for minute 5. I want to group in a single line where the date is present will be the 5th minute.

I send the code to create the table and insert the data.

CREATE TABLE SELL (ProductID int,Description varchar(10),Timestamp_int  int, SellCount  int,Timestamp_datetime datetime)

INSERT INTO SELL VALUES(1,'Coke',1676430060,1,'2023-02-15 03:01:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430120,2,'2023-02-15 03:02:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430180,0,'2023-02-15 03:03:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430240,2,'2023-02-15 03:04:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430300,5,'2023-02-15 03:05:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430360,4,'2023-02-15 03:06:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430420,3,'2023-02-15 03:07:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430480,1,'2023-02-15 03:08:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430540,1,'2023-02-15 03:09:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430600,1,'2023-02-15 03:10:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430660,0,'2023-02-15 03:11:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430720,0,'2023-02-15 03:12:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430780,0,'2023-02-15 03:13:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430840,7,'2023-02-15 03:14:00.000') INSERT INTO SELL VALUES(1,'Coke',1676430900,1,'2023-02-15 03:15:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430060,1,'2023-02-15 03:01:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430120,3,'2023-02-15 03:02:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430180,5,'2023-02-15 03:03:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430240,2,'2023-02-15 03:04:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430300,1,'2023-02-15 03:05:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430360,1,'2023-02-15 03:06:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430420,1,'2023-02-15 03:07:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430480,2,'2023-02-15 03:08:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430540,2,'2023-02-15 03:09:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430600,2,'2023-02-15 03:10:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430660,6,'2023-02-15 03:11:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430720,5,'2023-02-15 03:12:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430780,3,'2023-02-15 03:13:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430840,1,'2023-02-15 03:14:00.000') INSERT INTO SELL VALUES(2,'7 Up',1676430900,2,'2023-02-15 03:15:00.000')

image
image977×612 16.7 KB

2 

SELECT ProductID, Description, 
    CAST(SUM(CAST(SellCount AS decimal(11, 1))) / 5 AS decimal(9, 1)) AS SellCount,
    DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, Timestamp_datetime) / 
        5.0) * 5, 0) AS Timestamp_datetime
FROM SELL
GROUP BY ProductID, Description, DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, Timestamp_datetime) /
    5.0) * 5, 0)
ORDER BY ProductID, Timestamp_datetime
3

Hi,
If I want to average the Sell Count column I just replace SUM with AVG, right?

4

I'd divide by COUNT(*) rather than use AVG, but I guess you could use AVG.

5

hi

hope this helps

this SQL takes advantage of Newer SQL Features like Rows Preceding , Rows Following

yes i am aware of the decimal points issue
but let me know if this ok ? we can fix that

; with cte as 
(
select 
     ROW_NUMBER() over(ORDER BY productid,Timestamp_datetime) as rn
   , ProductID
   , Description 
   , Timestamp_int
   , AVG(sellcount) OVER (ORDER BY productid,Timestamp_datetime ROWS between 5 preceding and current row) SellCount
   , Timestamp_datetime
    FROM sell 
	)
select 
     ProductID
   , Description 
   , Timestamp_int
   , SellCount
   , Timestamp_datetime
from 
    cte 
where 
    rn%5 = 0

image
image988×236 7.52 KB

6

@harishgg1 ,
That doesn't work exactly like expected because it requires the time entries to start at a certain time. Try it using the following test data and see (I used #Sell for the table name).

--=====================================================================================================================
--      Create readily consumable test data.
--=====================================================================================================================
--===== Create the test table in the nice, safe place known as TempDB
   DROP TABLE IF EXISTS #Sell;
GO
 CREATE TABLE #Sell 
        (
         ProductID          int
        ,Description        varchar(10)
        ,Timestamp_int      int      --Old UNIX Timestamp based on Seconds and is "whole minute" based.
        ,SellCount          int
        ,Timestamp_datetime datetime --The UNIX Timestamp converted to DATETIME and is also "whole minute" based.
        )
;
GO
--===== Populate the table with the test data the OP provided but cleaned up and with some added "proof" rows.
 INSERT INTO #SELL
        (ProductID,Description,Timestamp_int,SellCount,Timestamp_datetime) 
 VALUES   (1,'Coke',1676429940,10,'2023-02-15 02:59:00.000') --Added for proof
         ,(1,'Coke',1676430000, 8,'2023-02-15 03:00:00.000') --Added for proof
         ,(1,'Coke',1676430060, 1,'2023-02-15 03:01:00.000')
         ,(1,'Coke',1676430120, 2,'2023-02-15 03:02:00.000')
         ,(1,'Coke',1676430180, 0,'2023-02-15 03:03:00.000')
         ,(1,'Coke',1676430240, 2,'2023-02-15 03:04:00.000')
         ,(1,'Coke',1676430300, 5,'2023-02-15 03:05:00.000')
         ,(1,'Coke',1676430360, 4,'2023-02-15 03:06:00.000')
         ,(1,'Coke',1676430420, 3,'2023-02-15 03:07:00.000')
         ,(1,'Coke',1676430480, 1,'2023-02-15 03:08:00.000')
         ,(1,'Coke',1676430540, 1,'2023-02-15 03:09:00.000')
         ,(1,'Coke',1676430600, 1,'2023-02-15 03:10:00.000')
         ,(1,'Coke',1676430660, 0,'2023-02-15 03:11:00.000')
         ,(1,'Coke',1676430720, 0,'2023-02-15 03:12:00.000')
         ,(1,'Coke',1676430780, 0,'2023-02-15 03:13:00.000')
         ,(1,'Coke',1676430840, 7,'2023-02-15 03:14:00.000')
         ,(1,'Coke',1676430900, 1,'2023-02-15 03:15:00.000')
         ,(2,'7 Up',1676430060, 1,'2023-02-15 03:01:00.000')
         ,(2,'7 Up',1676430120, 3,'2023-02-15 03:02:00.000')
         ,(2,'7 Up',1676430180, 5,'2023-02-15 03:03:00.000')
         ,(2,'7 Up',1676430240, 2,'2023-02-15 03:04:00.000')
         ,(2,'7 Up',1676430300, 1,'2023-02-15 03:05:00.000')
         ,(2,'7 Up',1676430360, 1,'2023-02-15 03:06:00.000')
         ,(2,'7 Up',1676430420, 1,'2023-02-15 03:07:00.000')
         ,(2,'7 Up',1676430480, 2,'2023-02-15 03:08:00.000')
         ,(2,'7 Up',1676430540, 2,'2023-02-15 03:09:00.000')
         ,(2,'7 Up',1676430600, 2,'2023-02-15 03:10:00.000')
         ,(2,'7 Up',1676430660, 6,'2023-02-15 03:11:00.000')
         ,(2,'7 Up',1676430720, 5,'2023-02-15 03:12:00.000')
         ,(2,'7 Up',1676430780, 3,'2023-02-15 03:13:00.000')
         ,(2,'7 Up',1676430840, 1,'2023-02-15 03:14:00.000')
         ,(2,'7 Up',1676430900, 2,'2023-02-15 03:15:00.000')
;
7

This question was also asked over on SQLServerCentral.com. Using the test data that I posted above, here's one of the answers I gave. It doesn't matter what time of day the data starts at and doesn't matter if there are missing entries... it still calculates the 5 minute average accurately and you can see the reasons why in the proof columns that the OP didn't ask for.

--===== Solve the problem using integer math on the Timestamp_datetime column.
 SELECT  ProductID
        ,[Description]
        ,PeriodMinutes          = COUNT(*)       --Comment out this "proof" column if you don't want it.
        ,PeriodTotalSellCount   = SUM(SellCount) --Comment out this "proof" column if you don't want it.
        ,PeriodAvgSellCount     = CONVERT(DECIMAL(9,1),AVG(SellCount+0.0))
        ,PeriodEndTime          = CONVERT(CHAR(16),MAX(Timestamp_datetime),120)
   FROM #Sell
  GROUP BY ProductID, [Description], (DATEDIFF(mi,0,Timestamp_datetime)-1)/5
  ORDER BY ProductID, PeriodEndTime
;

The results from that code using the previous test data I cited are...

image
image859×211 5.66 KB

1 Like
8

Got you Jeff

Thank You for pointing it out

:+1: :+1:

1 Like