sql - 如何使用 SQL 忽略组中的相同连续记录

Question

我有以下数据:

ItemID | ProdID | ItemTypeID
=======+========+===========
1001   | 100    | A
1002   | 100    | B
1003   | 100    | A
1004   | 100    | B
1005   | 100    | B  <- successive itemtype (should be excluded)
1006   | 100    | C
1007   | 200    | C
1008   | 200    | A

我想在每个组中列出 ItemTypeID (LISTAGG)，但没有相同的连续 ID。所以我的结果应该是这样的:

ProdID | ItemTypes
=======+==========
100    | A,B,A,B,C <- not A,B,A,B,B,C (successive B)
200    | C,A

Answer 1

这很棘手。 Listagg() 甚至不允许 distinct，所以所有的工作都需要在子查询中完成。

您可以使用行号差异方法来识别连续的 ItemTypeId。然后，一旦确定了组，您就可以按组聚合，然后执行 listagg():

select ProdId,
       listagg(ItemId, ',') within group (order by seqnum) as items
from (select ProdId, ItemId, count(*) as NumItems,
             row_number() over (partition by ProdId order by min(ItemId)) as seqnum
      from (select t.*,
                   (row_number() over (partition by ProdId order by ItemId) -
                    row_number() over (partition by ProdId, ItemTypeId order by ItemId)
                   ) as grp
            from t
           ) t
      group by ProdId, ItemTypeId, grp
     ) t
group by ProdId;

编辑:

解决此问题的另一种方法是使用 lag() 来查找新组的开始位置。前面的方法允许您获取计数。这种方法可能更容易理解:

select ProdId,
       listagg(ItemId, ',') within group (order by ItemId) as items
from (select t.*
      from (select t.*,
                   lag(ItemTypeId) over (partition by ProdId order by ItemId) as prev_ItemTypeId
            from t
           ) t
      where prev_ItemTypeId is null or prev_ItemTypeId <> ItemTypeId
     ) t
group by ProdId;