代码之家 › 专栏 › 技术社区 › Richard B

SQL Server窗口函数基于时间的数据

data-partitioning sql-server-2016 sql-server sql

Richard B · 技术社区 · 6 年前

我运行的是SQL Server 2016,我有一组数据如下所示

Name    Time                    Type
CL1 2018-05-22 10:50:00 -04:00  SampleAndTrade
CL1 2018-05-22 11:00:00 -04:00  TradeOnly
CL1 2018-05-22 11:10:00 -04:00  TradeOnly
CL1 2018-05-22 11:20:00 -04:00  TradeOnly
CL1 2018-05-22 14:10:00 -04:00  SampleAndTrade
CL1 2018-05-22 14:20:00 -04:00  TradeOnly
CL1 2018-05-22 14:30:00 -04:00  TradeOnly
CL1 2018-05-22 14:40:00 -04:00  TradeOnly
CL1 2018-05-22 14:50:00 -04:00  TradeOnly
HO1 2018-05-22 10:50:00 -04:00  SampleAndTrade
HO1 2018-05-22 11:00:00 -04:00  TradeOnly
HO1 2018-05-22 11:10:00 -04:00  TradeOnly
HO1 2018-05-22 11:20:00 -04:00  TradeOnly
HO1 2018-05-22 14:10:00 -04:00  SampleAndTrade
HO1 2018-05-22 14:20:00 -04:00  TradeOnly
HO1 2018-05-22 14:30:00 -04:00  TradeOnly
HO1 2018-05-22 14:40:00 -04:00  TradeOnly
HO1 2018-05-22 14:50:00 -04:00  TradeOnly

交易期以 SampleAndTrade 最后是一个 TradeOnly (在下一个之前 样品和贸易 )你总是至少有一个 TradeOnl A后Y 样品和贸易 . 每次交易的时间间隔都是一致的。我想将此数据转置为如下所示:

Name    StartTime    EndTime   IntervalMin
CL1     10:50        11:20     10
CL1     14:10        14:50     10
HO1     10:50        11:20     10
HO1     14:10        14:50     10

我真的不知道如何正确地划分数据,以便在贸易周期中进行汇总。有人能给我一个提示,告诉我怎样才能做到这一点吗?

4 回复 | 直到 6 年前

MatBailie 6 年前

SELECT
  Name,
  MIN(Time)   AS StartTime,
  MAX(Time)   AS EndTime,
  MIN(Diff)   AS IntervalMin
FROM
(
  SELECT
    *,
    SUM(CASE WHEN type = 'SampleAndTrade' THEN 1 END)
      OVER (PARTITION BY Name
                ORDER BY Time
           )
             AS GroupID,
    DATEDIFF(
      MINUTE,
      LAG(Time)
        OVER (PARTITION BY Name
                  ORDER BY Time
             ),
      Time
    )
      AS Diff
  FROM
    yourTable
)
  AS summary
GROUP BY
  Name,
  GroupID

Gordon Linoff 6 年前

您只需要通过执行 'SampleAndTrade' . 然后聚合:

select name, min(time), max(time)
from (select t.*,
             sum(case when type = 'SampleAndTrade' then 1 else 0 end) over (partition by name order by time) as grp
      from t
     ) t
group by name, grp;

我不知道怎么做 intervalmin 是计算出来的。也许:

datediff(minute, min(time), max(time)) / count(*)

Yogesh Sharma 6 年前

您也可以使用 相关性 旧版本的方法

with tt as (
 select *, 
       (select count(*) 
        from table 
        where Name = t.name and  [type] = 'SampleAndTrade' and time <= t.time
        ) as Seq
 from table t
)

select Name, min(time), max(time), 
       datediff(minute, min(time), max(time)) as IntervalMin
from tt
group by Name, Seq;

paparazzo 6 年前

这应该能让你到达那里

declare @t table (Name varchar(20), tm datetime2, Type varchar(20));
insert into @t values 
('CL1', '2018-05-22 10:50:00 -04:00', 'SampleAndTrade'),
('CL1', '2018-05-22 11:00:00 -04:00', 'TradeOnly'),
('CL1', '2018-05-22 11:10:00 -04:00', 'TradeOnly'),
('CL1', '2018-05-22 11:20:00 -04:00', 'TradeOnly'),
('CL1', '2018-05-22 14:10:00 -04:00', 'SampleAndTrade'),
('CL1', '2018-05-22 14:20:00 -04:00', 'TradeOnly'),
('CL1', '2018-05-22 14:30:00 -04:00', 'TradeOnly'),
('CL1', '2018-05-22 14:40:00 -04:00', 'TradeOnly'),
('CL1', '2018-05-22 14:50:00 -04:00', 'TradeOnly'),
('HO1', '2018-05-22 10:50:00 -04:00', 'SampleAndTrade'),
('HO1', '2018-05-22 11:00:00 -04:00', 'TradeOnly'),
('HO1', '2018-05-22 11:10:00 -04:00', 'TradeOnly'),
('HO1', '2018-05-22 11:20:00 -04:00', 'TradeOnly'),
('HO1', '2018-05-22 14:10:00 -04:00', 'SampleAndTrade'),
('HO1', '2018-05-22 14:20:00 -04:00', 'TradeOnly'),
('HO1', '2018-05-22 14:30:00 -04:00', 'TradeOnly'),
('HO1', '2018-05-22 14:40:00 -04:00', 'TradeOnly'),
('HO1', '2018-05-22 14:50:00 -04:00', 'TradeOnly');
with cte as 
( select t1.* 
       , lead(type) over (partition by name order by tm) as nextType
  from @t t1 
)
select t1.Name, t1.tm, min(t2.tm) 
   --, t1.Type, t2.tm, t2.nextType
  from cte t1 
  join cte t2 
    on t1.Name = t2.Name 
   and t1.Type = 'SampleAndTrade' 
   and (t2.nextType = 'SampleAndTrade' or t2.nextType is null)
   and t2.tm > t1.tm
 group by t1.Name, t1.tm
 order by t1.name, t1.tm