每天按小时分组并获取所有记录

I have few requirements to analyze data from table below,

我几乎没有要求分析下表中的数据,

SELECT MId,SId,PId,DataHour,
 CAST(t.[SDate] as DATE), MAX(t.Powers) as PeakPower
 FROM [HourData] t  where CONVERT(date,[SDate]) between 
 CONVERT(date,'2016-12-01 09:45:59.240') and CONVERT(date,'2016-12-08 09:45:59.240')
 GROUP BY MId,SId,PId, CAST(t.[SDate] AS DATE),DataHour 
 order by  CAST(t.[SDate] AS DATE),DataHour,PeakPower

He above query will get sum of all MId,SId,PId for each hour in a day. But I few other requirements, I need which PId has most used in an hour for each day i.e from the data link http://ideone.com/IW4FUA I need the data of most used PId for each hour like below.

以上查询将获得一天中每小时的所有MId,SId,PId的总和。但我几乎没有其他要求,我需要每天一小时内最常用的PId,即数据链接http://ideone.com/IW4FUA我需要每小时使用最多PId的数据,如下所示。

 Power      PId
 5163.316   6
 5135.371   6

PId 6 has highest value for hour 0 and hour 1 in the day. The sum of all pid in each datahour and get the max hour of it for each day.

PId 6在当天0小时和小时1的最高值。每个数据小时中所有pid的总和,并获得每天的最大小时数。

This query will give details for each hour data

此查询将提供每小时数据的详细信息

 SELECT MId,SId,PId,DataHour,
 CAST(t.[SDate] as DATE), MAX(t.Powers) as PeakPower
 FROM [MonataHourData] t  where CONVERT(date,[SDate]) between 
  CONVERT(date,'2016-12-01 09:45:59.240') and CONVERT(date,'2016-12-08   09:45:59.240')
 GROUP BY MId,SId,PId, CAST(t.[SDate] AS DATE),DataHour 
 order by  CAST(t.[SDate] AS DATE),DataHour,PeakPower

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

1 个解决方案

#1

There are many ways to do this, here are some of them:

有很多方法可以做到这一点,以下是其中一些方法:

rextester link to test them all on your data: http://rextester.com/KBO56228

rextester链接在您的数据上测试它们:http://rextester.com/KBO56228

--declare @from datetime = '2016-12-01 09:45:59.240';
--declare @thru datetime = '2016-12-08 09:45:59.240';
/* the above can be simplified to: */
declare @from date = convert(date,'2016-12-01 09:45:59.240', 121);
declare @thru date = convert(date,'2016-12-08 09:45:59.240', 121);

cross apply version:

交叉申请版本:

select distinct
      x.Mid
    , x.Sid
    , x.Pid
    , t.DataHour
    , Date = convert(date, t.SDate)
    , x.Powers
  from HourData t
    cross apply (
      select top 1
          Mid
        , Sid
        , Pid
        , Powers
        from HourData c
        where convert(date, c.sdate) = convert(date, t.sdate)
          and c.DataHour = t.DataHour
        order by c.Powers desc
       ) as x;

top with ties version:

顶部带领带版本:

select top 1 with ties
      t.Mid
    , t.Sid
    , t.Pid
    , t.DataHour
    , Date = convert(date, t.SDate)
    , t.Powers
  from HourData t
  where convert(date, sdate) >= @from
    and convert(date, sdate) <  @thru
  order by row_number() over (partition by convert(date, t.SDate),t.DataHour order by t.Powers desc);

common table expression with row_number() version:

使用row_number()版本的公用表表达式:

with PowerRN as (
select 
      t.Mid
    , t.Sid
    , t.Pid
    , t.DataHour
    , Date = convert(date, t.SDate)
    , t.Powers
    , rn = row_number() over (partition by convert(date, t.SDate),t.DataHour order by t.Powers desc)
  from HourData t
  where convert(date, sdate) >= @from
    and convert(date, sdate) <  @thru
)
select Mid, Sid, Pid, DataHour, Date, Powers
  from PowerRN
  where rn=1
  order by Date, DataHour;

max() over() version:

max()over()版本:

with PeakPower as (
  select 
        t.Mid
      , t.Sid
      , t.Pid
      , t.DataHour
      , Date = convert(date, t.SDate)
      , t.Powers
      , PeakHourPower = max(t.Powers) over (partition by convert(date, t.SDate),t.DataHour)
    from HourData t
    where convert(date, sdate) >= @from
      and convert(date, sdate) <  @thru
    group by 
        t.Mid
      , t.Sid
      , t.Pid
      , convert(date, t.SDate)
      , t.DataHour
      , t.Powers
)
select Mid, Sid, Pid, DataHour, Date, Powers
  from PeakPower
  where Powers = PeakHourPower
  order by Date, DataHour;

inner join version:

内连接版本:

select 
      t.Mid
    , t.Sid
    , t.Pid
    , t.DataHour
    , Date = convert(date, t.SDate)
    , t.Powers
  from HourData t
    inner join (
      select 
          DataHour
        , Date = convert(date, t.SDate)
        , PeakHourPower = max(t.Powers) 
        from HourData t 
        where convert(date, sdate) >= @from
          and convert(date, sdate) <  @thru
        group by 
            convert(date, SDate)
          , DataHour 
       ) as x on x.Date          = convert(date, sdate)
            and x.DataHour      = t.DataHour 
            and x.PeakHourPower = t.Powers

  where convert(date, sdate) >= @from
    and convert(date, sdate) <  @thru;
  order by t.Date, t.DataHour;

#1