获取每天最大值的记录

Question

我想得到每天最大功率值的记录，见下表

CREATE TABLE [dbo].[HourData]
(
    [ID] [bigint] NOT NULL,
    [Powers] [decimal](18, 4) NOT NULL,
    [DataHour] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,

    CONSTRAINT [PK_HourData] 
        PRIMARY KEY CLUSTERED ([ID] ASC, [HourShow] ASC, [StartDate] ASC)
                    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
                          ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) 

下面的查询将获得每天最大权限的使用情况，但是如何获得记录本身，假设如果我需要DataHour，如何获得它，我不能将它包含在GroupBy子句中。

SELECT
    CAST(t.[StartDate] as DATE), 
    MAX(t.Powers) as 'Peak Power Each Day'
FROM 
    [HourData] t  
WHERE
    CONVERT(date, [StartDate]) BETWEEN CONVERT(date, '2016-12-01 09:45:59.240') 
                                   AND CONVERT(date,'2016-12-08 09:45:59.240')
 GROUP BY 
     CAST(t.[StartDate] AS DATE) 
 ORDER BY
     CAST(t.[StartDate] AS DATE)

Answer 1

您可以使用窗口函数获取整个记录。以下是每天获取所有最大值的信息：

SELECT hd.*
FROM (SELECT hd.*,
             MAX(hd.Powers) OVER (PARTITION BY CAST(hd.StartDate as Date)) as maxpowers
      FROM HourData hd 
     ) hd
WHERE CONVERT(date, StartDate ) between '2016-12-01' and '2016-12-08' AND
      powers = maxpowers;

如果您想要一个(任意)：

SELECT hd.*
FROM (SELECT hd.*,
             ROW_NUMBER() OVER (PARTITION BY CAST(hd.StartDate as Date) ORDER BY hd.powers DESC) as seqnum
      FROM HourData hd 
     ) hd
WHERE CONVERT(date, StartDate ) between '2016-12-01' and '2016-12-08' AND
      seqnum = 1;

注意，我从between比较中删除了time组件。无论如何，到日期的转换将移除时间组件。这更简单。

Answer 2

返回的内容在查询的SELECT部分中。SELECT *返回整个条目。