SQL rewrite - Group by

Database
Enthusiast

SQL rewrite - Group by

The following query gives the higest #number count per day, which is 7 rows each for 1 day. I want to include HOURS column to the results set with changing the resulting result set.So I want to know at which hour the count was the highest? without listing all the hours. Is it possiable?

result should be as this:

 Date hours  Maximum_DWN_Per_Minute

 6-24 04     27

 6-25 02      25

 6-26 03      22

 6-27 03     23

 6-28 01     23

 6-29 23     26

 6-30 02     28

SQL: 

SELECT LOG_DATE, MAX(Downloads_Per_Second) AS Maximum_DWN_Per_Minute

FROM 

(

SELECT 

LOG_DATE,

CAST(CAST(LOG_TIME AS FORMAT 'HH') AS CHAR(2)) AS HOURS, 

CAST(CAST(LOG_TIME AS FORMAT 'HH') AS CHAR(2)) ||':'||CAST(CAST(LOG_TIME AS FORMAT 'MI') AS CHAR(2)) ||':'||  CAST(CAST(LOG_TIME AS FORMAT 'SS') AS CHAR(2))   

AS HOUR_MIN_SEC, 

COUNT(*) AS Downloads_Per_Second

FROM CDN_RQST_EVT

WHERE LLNW_REDRCT_STAT_CODE IN ('DVR%20STATUS%3A%20DOWNLOAD_COMPL','DOWNLOAD_COMPLETED')

AND LOG_DATE BETWEEN '2013-03-11'  AND '2013-03-17' 

--AND LOG_TIME < '09:00:00'

GROUP BY 1,2,3

--ORDER BY 1,2,3

) DHHMD

GROUP BY 1,2

ORDER BY 1,2

thanks in advance.

Tags (2)
4 REPLIES
Senior Apprentice

Re: SQL rewrite - Group by

First i would simplify the calculation on LOG_TIME, why do you cast every part to a string? 

CAST((log_time (FORMAT 'hh:mi:ss')) AS CHAR(8)) AS HOUR_MIN_SEC

You might also avoid typecasting to a string: 

log_time - ((EXTRACT(SECOND FROM log_time) MOD 1) * INTERVAL '1' SECOND)

Based on your query i'm not shure if you want the count per minute, then simply remove the "mod 1" (or change the FORMAT).

And then it's just a QUALIFY:

SELECT 
LOG_DATE,
log_time - ((EXTRACT(SECOND FROM log_time) MOD 1) * INTERVAL '1' SECOND) AS HOUR_MIN_SEC,
COUNT(*) AS Downloads_Per_Second
FROM CDN_RQST_EVT
WHERE LLNW_REDRCT_STAT_CODE IN ('DVR%20STATUS%3A%20DOWNLOAD_COMPL',' DOWNLOAD_COMPLETED')
AND LOG_DATE BETWEEN '2013-03-11' AND '2013-03-17'
--AND LOG_TIME < '09:00:00'
GROUP BY 1,2
QUALIFY
ROW_NUMBER()
OVER (PARTITION BY Log_Date, EXTRACT(HOUR FROM HOUR_MIN_SEC)
ORDER BY Downloads_Per_Second DESC) = 1

Dieter

Enthusiast

Re: SQL rewrite - Group by

Thanks, but this give Syntax error: expected something between '(' and '*'.

Senior Apprentice

Re: SQL rewrite - Group by

I just checked my query in SQLA and it only complained about the missing table :-)

I would expect this error when there was a type in COUNT(*), better check your query.

Dieter

Enthusiast

Re: SQL rewrite - Group by

Thanks, Dieter.