T-SQL: Find the Peak Hour
This article was inspired by a recent question in the UniversalThread forum "Find the Peak Hour".
Problem Definition
The thread starter gave the following definition of the problem:
I have a table containing 24 hour traffic counts. The counts are taken every 15 minutes and the data is stored in columns P1 thru P96. Thus the first hour of the day from midnight to 1:00 AM is stored in P1-P4 and the total for the hour would be (P1+P2+P3+P4). The challenge is to find the one hour block that contains the highest total for the day. The one hour block may begin at any of the 15 minute time periods (any P column up to P93). The desired result would provide the beginning of the time period (P column) and the total for the hour.
Let's use the following script to create a test table and populate it with 10000 random rows:
create table TrafficCounts (Id int identity(1,1) primary key,
TrafficDate date,
p1 int, p2 int, p3 int, p4 int, p5 int, p6 int, p7 int, p8 int, p9 int, p10 int,
p11 int, p12 int, p13 int, p14 int, p15 int, p16 int, p17 int, p18 int, p19 int,
p20 int, p21 int, p22 int, p23 int, p24 int, p25 int, p26 int, p27 int, p28 int,
p29 int, p30 int, p31 int, p32 int, p33 int, p34 int, p35 int, p36 int, p37 int,
p38 int, p39 int, p40 int, p41 int, p42 int, p43 int, p44 int, p45 int, p46 int,
p47 int, p48 int, p49 int, p50 int, p51 int, p52 int, p53 int, p54 int, p55 int,
p56 int, p57 int, p58 int, p59 int, p60 int, p61 int, p62 int, p63 int, p64 int,
p65 int, p66 int, p67 int, p68 int, p69 int, p70 int, p71 int, p72 int, p73 int,
p74 int, p75 int, p76 int, p77 int, p78 int, p79 int, p80 int, p81 int, p82 int,
p83 int, p84 int, p85 int, p86 int, p87 int, p88 int, p89 int, p90 int, p91 int,
p92 int, p93 int, p94 int, p95 int, p96 int);
declare @loop int = 1, @startDate date = dateadd(day,-10000, CURRENT_TIMESTAMP);
while @loop <=10000
begin
with cte as (select top (96) number from dbo.numbers where number >=0
order by NewID())
insert into TrafficCounts (TrafficDate,
p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18,
p19, p20, p21, p22, p23, p24, p25, p26, p27, p28, p29, p30, p31, p32, p33, p34,
p35, p36, p37, p38, p39, p40, p41, p42, p43, p44, p45, p46, p47, p48, p49, p50,
p51, p52, p53, p54, p55, p56, p57, p58, p59, p60, p61, p62, p63, p64, p65, p66,
p67, p68, p69, p70, p71, p72, p73, p74, p75, p76, p77, p78, p79, p80, p81, p82,
p83, p84, p85, p86, p87, p88, p89, p90, p91, p92, p93, p94, p95, p96)
select dateadd(day, @Loop, @StartDate), *
FROM (select *, 'p' + cast(row_number() over (order by newID()) as varchar(10)) as Rn
FROM cte) src PIVOT (max(number) FOR Rn IN (p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18,
p19, p20, p21, p22, p23, p24, p25, p26, p27, p28, p29, p30, p31, p32, p33, p34,
p35, p36, p37, p38, p39, p40, p41, p42, p43, p44, p45, p46, p47, p48, p49, p50,
p51, p52, p53, p54, p55, p56, p57, p58, p59, p60, p61, p62, p63, p64, p65, p66,
p67, p68, p69, p70, p71, p72, p73, p74, p75, p76, p77, p78, p79, p80, p81, p82,
p83, p84, p85, p86, p87, p88, p89, p90, p91, p92, p93, p94, p95, p96)) pvt
set @loop = @loop +1 ;
end
select * from TrafficCounts
Solution
The following script shows two solutions. First solution can be used in SQL 2005-2008 and the second solution can be used in SQL 2012 and up. Including Actual Execution plan shows that the first solution takes 93% and the SQL 2012+ solution takes 7% of the total query.
DECLARE @Id int = 10;
SET STATISTICS TIME ON;
SET STATISTICS IO OFF;
print 'CROSS APPLY Solution'
;
WITH cte
AS (
SELECT *
FROM dbo.TrafficCounts
UNPIVOT(TrafficCount FOR ColumnName IN (
p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, p19, p20,
p21, p22, p23, p24, p25, p26, p27, p28, p29, p30, p31, p32, p33, p34, p35, p36, p37, p38, p39, p40,
p41, p42, p43, p44, p45, p46, p47, p48, p49, p50, p51, p52, p53, p54, p55, p56, p57, p58, p59, p60,
p61, p62, p63, p64, p65, p66, p67, p68, p69, p70, p71, p72, p73, p74, p75, p76, p77, p78, p79, p80,
p81, p82, p83, p84, p85, p86, p87, p88, p89, p90, p91, p92, p93, p94, p95, p96
)) unpvt
WHERE Id = @Id)
,cte2
AS (
SELECT *
,(CAST(SUBSTRING(ColumnName, 2, LEN(ColumnName)) AS INT) - 1) / 4 + 1 AS [Hour]
,(CAST(SUBSTRING(ColumnName, 2, LEN(ColumnName)) AS INT) - 1) % 4 * 15 AS [Minutes]
FROM cte
)
,cte3
AS (
SELECT TrafficDate, Id, TrafficCount
,dateadd(minute, [Minutes], dateadd(hour, [hour] - 1, CAST(TrafficDate as DATETIME))) AS StartTime
FROM cte2
)
SELECT TOP (1)
WITH TIES TrafficCount AS IntervalStartCount
,StartTime
,EndTime
,TotalCount
FROM cte3 c
CROSS APPLY (
SELECT SUM(TrafficCount) AS TotalCount
,DATEADD(minute, 15, MAX(c2.StartTime)) AS EndTime
FROM cte3 c2
WHERE c2.StartTime >= c.StartTime
AND c2.StartTime < dateadd(hour, 1, c.StartTime)
AND c2.Id = c.Id
) X
ORDER BY TotalCount DESC;
print '
SQL 2012+ SUM ORDER BY Solution'
;
WITH cte
AS (
SELECT *
FROM dbo.TrafficCounts
UNPIVOT(TrafficCount FOR ColumnName IN (
p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, p19, p20,
p21, p22, p23, p24, p25, p26, p27, p28, p29, p30, p31, p32, p33, p34, p35, p36, p37, p38, p39, p40,
p41, p42, p43, p44, p45, p46, p47, p48, p49, p50, p51, p52, p53, p54, p55, p56, p57, p58, p59, p60,
p61, p62, p63, p64, p65, p66, p67, p68, p69, p70, p71, p72, p73, p74, p75, p76, p77, p78, p79, p80,
p81, p82, p83, p84, p85, p86, p87, p88, p89, p90, p91, p92, p93, p94, p95, p96
)) unpvt
WHERE Id = @Id)
,cte2
AS (
SELECT *
,(CAST(SUBSTRING(ColumnName, 2, LEN(ColumnName)) AS INT) - 1) / 4 + 1 AS [Hour]
,(CAST(SUBSTRING(ColumnName, 2, LEN(ColumnName)) AS INT) - 1) % 4 * 15 AS [Minutes]
FROM cte
)
,cte3
AS (
SELECT TrafficDate, Id, TrafficCount
,dateadd(minute, [Minutes], dateadd(hour, [hour] - 1, CAST(TrafficDate as DATETIME))) AS StartTime
FROM cte2
)
SELECT TOP (1)
WITH TIES TrafficCount AS IntervalStartCount
,StartTime
,DATEADD(hour, 1, StartTime) as EndTime
,SUM(TrafficCount) OVER (PARTITION BY Id
ORDER BY StartTime ROWS BETWEEN CURRENT ROW
AND 3 FOLLOWING
) AS TotalCount
FROM cte3
ORDER BY TotalCount DESC;
For both solutions we executed the query for a specific ID (or date). The solution can be changed to find maximum total for every date in the table. In this case instead of SELECT TOP we will use one more CTE with RANK() function in both solutions.
Conclusion
For the correctly designed table the first step (unpivoting) would have been not needed. So, the correct design is always important.
For the current table structure we can see that SQL Server 2012 and up offers a simple solution using important enhancements in the SUM () OVER function.
See Also
This article participated in the TechNet Guru Contributions for July 2014.