sql - 在一组未更改的值中聚合

Question

我有示例数据:

RowId TypeId Value
    1      1    34
    2      1    53
    3      1    34
    4      2    43
    5      2    65
    6     16    54
    7     16    34
    8      1    45
    9      6    43
   10      6    34
   11     16    64
   12     16    63

我想计算每种类型的行数(值对我来说无关紧要)，但仅针对...邻居 TypeId

TypeId Count
     1     3
     2     2 
    16     2
     1     1
     6     2
    16     2

如何实现这个结果？

Answer 1

这应该为您提供一组未更改值中的 COUNT 行:

SELECT TypeId, grp, COUNT(*) FROM (
  SELECT RowId, TypeId , Value, gap, SUM(gap) over (ORDER BY RowId ) grp
  FROM (SELECT RowId, TypeId , Value,
                CASE WHEN TypeId = lag(TypeId) over (ORDER BY RowId )
                   THEN 0
                   ELSE 1
                END gap 
        FROM dummy
    ) t
) tt
GROUP BY TypeId, grp;

如果您更喜欢 WITH 而不是无尽的子查询包含:

WITH dummy_with_groups AS (
  SELECT RowId, TypeId , Value, SUM(gap) OVER (ORDER BY RowId) grp
  FROM (SELECT RowId, TypeId , Value,
          CASE WHEN TypeId = lag(TypeId) OVER (ORDER BY RowId)
          THEN 0 ELSE 1 END gap 
        FROM dummy) t
)
SELECT TypeId, COUNT(*) as Result 
FROM dummy_with_groups
GROUP BY TypeId, grp;

http://www.sqlfiddle.com/#!6/f16e9/34