MS SQL Server在表中查找前4个峰值读数并显示为列

Question

我的数据集

Date_Time   Load
7/24/17 12:00 AM    3987
7/24/17 1:00 AM 3748
7/24/17 2:00 AM 3608
7/24/17 3:00 AM 3526
7/24/17 4:00 AM 3493
7/24/17 5:00 AM 3545
7/24/17 6:00 AM 3683
7/24/17 7:00 AM 3827
7/24/17 8:00 AM 3942
7/24/17 9:00 AM 3956
7/24/17 10:00 AM    3985
7/24/17 11:00 AM    4000
7/24/17 12:00 PM    3917
7/24/17 1:00 PM 3834
7/24/17 2:00 PM 3901
7/24/17 3:00 PM 4132
7/24/17 4:00 PM 4388
7/24/17 5:00 PM 4497
7/24/17 6:00 PM 4675
7/24/17 7:00 PM 4713
7/24/17 8:00 PM 4743
7/24/17 9:00 PM 4704
7/24/17 10:00 PM    4540
7/24/17 11:00 PM    4227

我需要的--所需的输出

Date_Time   Load          Peak-1    Peak-2  Peak-3  Peak-4
7/24/17 12:00 AM    3987                
7/24/17 1:00 AM 3748                
7/24/17 2:00 AM 3608                
7/24/17 3:00 AM 3526                
7/24/17 4:00 AM 3493                
7/24/17 5:00 AM 3545                
7/24/17 6:00 AM 3683                
7/24/17 7:00 AM 3827                
7/24/17 8:00 AM 3942                
7/24/17 9:00 AM 3956                
7/24/17 10:00 AM    3985                
7/24/17 11:00 AM    4000                
7/24/17 12:00 PM    3917                
7/24/17 1:00 PM 3834                
7/24/17 2:00 PM 3901                
7/24/17 3:00 PM 4132                
7/24/17 4:00 PM 4388                
7/24/17 5:00 PM 4497                
7/24/17 6:00 PM 4675                1
7/24/17 7:00 PM 4713        1       
7/24/17 8:00 PM 4743    1           
7/24/17 9:00 PM 4704            1   
7/24/17 10:00 PM    4540                
7/24/17 11:00 PM    4227

下面是我写的内容

这就是乱七八糟的select date_time，system_load FROM dbo.Loads E1 Where E1.Date_Time >= DATEADD(day，-1，convert(date，GETDATE () and E1.date_time< DATEADD(day，+0，convert(date，GETDATE())--前一天) and (4-3) = (Select Count(Distinct(E2.system_load)) From dbo.Loads E2 WHERE E2.Date_Time >= DATEADD(day，-1，convert(date，GETDATE() AND E2.date_time< DATEADD(day，+0，convert(date，GETDATE ())--前一天) and E2.system_load > E1.system_load) UNION select date_time，system_load FROM dbo.Loads E1 WHERE E1.Date_Time >= DATEADD(day，-1，convert(date，GETDATE() AND E1.date_time< DATEADD(day，+0，convert(date，GETDATE ())--前一天) AND (4-2) = (Select Count(Distinct( E2.system_load )) From dbo.Loads E2 Where E2.Date_Time >= DATEADD(day，-1，convert(date，GETDATE () AND E2.date_time< DATEADD(day，+0，convert(date，GETDATE()--前一天) and E2.system_load> E1.system_load) UNION select date_time，system_load FROM dbo.Loads E1 WHERE E1.Date_Time >= DATEADD(day，-1，convert(date，GETDATE () AND E1.date_time< DATEADD(day，+0，convert(日期，GETDATE()--前一天) and (4-1) = (Select Count(Distinct(E2.system_load)) From dbo.Loads E2 Where E2.Date_Time >= DATEADD(day，-1，convert(日期，GETDATE() and E2.date_time< DATEADD(day，+0，convert(date，GETDATE ())--前一天) and E2.system_load > E1.system_load) UNION select date_time，system_load FROM dbo.Loads E1 WHERE E1.Date_Time >= DATEADD(day，-1，convert(date，GETDATE() AND E1.date_time< DATEADD(day，+0，convert(date，GETDATE ()--前一天) and (4-4) = (Select Count(Distinct( E2.system_load )) From dbo.Loads E2 Where E2.Date_Time >= DATEADD(day，-1，convert(date，GETDATE () AND E2.date_time< DATEADD(day，+0，convert(date，GETDATE()--前一天)和E2.system_load> E1.system_load) ORDER BY SYSTEM_LOAD DESC

Answer 1

此查询将给出一列计算最高负载为1，下一列为2，依此类推

我建议您将其封装到一个子查询中，然后使用case when load_rank = Xxx then 1 end的四个实例为每一列添加一个1 (xxx是一个从1到4的数字)-这是一个可以尝试的练习。我们会在这里提供帮助

SELECT
  Date_time, load, row_number() over(partition by convert(date, date_time) order by load desc) as load_rank
FROM 
  Table
ORDER BY date_time