sql - 在中间用 "holes"分组

Question

给出下表:

create table #Track (id int identity, vehicle int, station varchar(50), pieces int, distance int)
insert into #Track (vehicle, station, pieces, distance)
values 
(1, 'A', 10, 0), (1, 'B', 10, 50), (1, 'C', 11, 23), (1, 'D', 11, 40), (1, 'E', 10, 5)

这是我需要的结果(注意 C 和 D 之间 pieces 字段的变化):

vehicle station_from    station_to  pieces  distance_all
1            A             B          10      50
1            C             D          11      63
1            E             E          10       5

如果我执行此查询:

select  A.vehicle,
        T1.station station_from,
        T2.station station_to,
        A.pieces,
        A.distance_all
from (
select  vehicle,
        min(id) min_id,
        max(id) max_id,
        pieces,
        sum(distance) distance_all
from    #Track
group
by      vehicle,
        pieces
) A join #Track T1 on A.min_id = T1.id
    join #Track T2 on A.max_id = T2.id

我得到了一个错误的结果(distance_all 是正确的，但是起点站和终点站不正确。看起来车辆 1 从 A 到 E 然后从 C 到 D:

vehicle station_from    station_to  pieces  distance_all
1             A              E        10         55
1             C              D        11         63

如何在不使用游标的情况下达到要求的结果(表比较大，几百万条记录)

Answer 1

这是“差距和孤岛”问题的变体。在你的情况下，你可以用不同的行号来解决它:

select vehicle,
       max(case when seqnum_grp = 1 then station end) as station_from,
       max(case when seqnum_grp_desc = 1 then station end) as station_to,
       pieces,
       sum(pieces) as pieces_all
from (select t.*,
             row_number() over (partition by vehicle, pieces, (seqnum - seqnum_p) order by id) as seqnum_grp,
             row_number() over (partition by vehicle, pieces, (seqnum - seqnum_p) order by id desc) as seqnum_grp_desc
      from (select t.*,
                   row_number() over (partition by vehicle order by id) as seqnum,
                   row_number() over (partition by vehicle, pieces order by id) as seqnum_p
           from #Track t
          ) t
     ) t
group by vehicle, pieces, (seqnum - seqnum_p);

要理解这是如何工作的，您需要理解为什么行号的差异可以识别组。为此，您只需运行最里面的子查询并查看结果。

这比大多数此类问题都要棘手一些，因为您需要沿途的第一个和最后一个站点。因此有一个额外的子查询。