如何对一组行进行分组，直到找到不同的值并保持顺序

Question

我有以下表格

ref_id  |  sku  | ordered
===========================
16      | 39562 |   0
16      | 39562 |   0
16      | 39562 |   0
16      | 91355 |   0
16      | 39562 |   0
16      | 39562 |   0
17      | 39562 |   0
17      | 39562 |   1
17      | 39562 |   1

并且我想遍历行，其中它对在行中具有相同值的元素进行分组，直到找到不同的值。因此，上面表格的输出看起来像这样:

ref_id  |  sku  | ordered
===========================
16      | 39562 |   0
16      | 91355 |   0
16      | 39562 |   0
17      | 39562 |   0
17      | 39562 |   1

我面临的挑战是"列，其中我使用MAX(ordered)分组。

SELECT DENSE_RANK() OVER (ORDER BY id) AS ref_id, sku, MAX(ordered)
FROM db
GROUP BY 1,2,3
ORDER BY ref_id, timeInfo

Answer 1

您可以使用lag()。假设timeinfo指定列的顺序:

select t.*
from (select t.*,
             lag(timeinfo) over (partition by refid order by timeinfo) as prev_timeinfo,
             lag(timeinfo) over (partition by refid, sku, ordered order by timeinfo) as prev_timeinfo_rso
      from db t
     ) t
where prev_timeinfo_rso is null or
      prev_timeinfo_rso <> prev_timeinfo;