获取每天最大值的记录

I want to get the record with max power value for each day, See the table below

我想获得每天最大功率值的记录,请参阅下表

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]
)

The below query will get max Powers usage for each day, but how can I get the record itself, Suppose if I need DataHour how can I get it, I can't include it in GroupBy clause.

以下查询将获得每天的最大功率使用量,但我如何获得记录本身,假设我是否需要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)

2 个解决方案

#1

You can get the entire record using window functions. The following gets all the maxima on a each day:

您可以使用窗口函数获取整个记录。以下内容获取每天的所有最大值:

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;

If you want one (arbitrarily):

如果你想要一个(任意):

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;

Note that I removed the time component from the between comparisons. The conversion to date removes the time component anyway. This is simpler.

请注意,我从比较之间删除了时间组件。到目前为止,转换会删除时间组件。这更简单。

#2

What is returned is in the SELECT part of the query. SELECT * returns the entire entry.

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

#1