在一组不变的值中进行聚合

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

如何实现这个结果？

- Bogusz Michałowski

---

你能告诉我们 SQL 的确切版本吗？ - Giannis Paraskevopoulos

您可以使用LAG函数创建一个字段来标识每个组：https://dev59.com/JnLYa4cB1Zd3GeqPTyLD - Bulat

不过这并不重要，但是rowid总是连续的吗？请看Giannis的评论。 - Strawberry

你确定你的数据总是按照你展示的顺序排列吗？ - Rachcha

你可以将其想象为加载到数据库中的文件。Typeid是它们的名称，而value则是行的内容。因此，我想知道从单个文件加载了多少行。 我确定它们的顺序吗？它们是按顺序加载的，所以RowId应该为我提供这种确定性。 - Bogusz Michałowski

Answer 1

这应该可以为您提供一个未更改值组中行数的计数：

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

- Bulat

---

我得到了 Incorrect syntax near ')' - 在最后一个 ) 附近 http://www.sqlfiddle.com/#!6/d7b47/1 - Bogusz Michałowski

计算得很准确，但不幸的是重新排序了。数字 6 应该在第一个 16 下面。 - Bogusz Michałowski

更新了答案，以便结果不会被重新洗牌。 - Bulat

经过短暂的考虑，我决定您的解决方案更好 - 它要快得多。特别是第二个。 - Bogusz Michałowski

Answer 2

请查看此演示。我稍微修改了您的列名。

WITH myCTE AS
         (SELECT row_id,
                 type_id,
                 ROW_NUMBER () OVER (PARTITION BY type_id ORDER BY row_id)
                     AS cnt,
                 CASE LEAD (type_id) OVER (ORDER BY row_id)
                     WHEN type_id THEN 0
                     ELSE 1
                 END
                     AS show
            FROM dummy),
     innerQuery AS
         (SELECT row_id, type_id, cnt
            FROM myCTE
           WHERE show = 1)
SELECT iq1.type_id, iq1.cnt - ISNULL (iq2.cnt, 0) CNT
  FROM innerQuery iq1
       LEFT OUTER JOIN innerQuery iq2
           ON     iq1.type_id = iq2.type_id
              AND EXISTS
                      (SELECT 1
                         FROM innerQuery iq3
                        WHERE     iq3.type_id = iq1.type_id
                              AND iq3.row_id < iq1.row_id
                       HAVING MAX (iq3.row_id) = iq2.row_id)

输出结果完全符合预期。

- Rachcha

---

输出为：3, 2, 2, 1, 2, 2。 - Bogusz Michałowski