如何用SQL统计连续相同值的出现次数并按序列输出结果

现有如下数据表：

期望得到的结果：

Beşiktaş    2
Galatasaray 2
Beşiktaş    1
Istanbul    3
Galatasaray 2
Fenerbahçe  3
Istanbul    1

尝试的SQL语句及错误结果：
创建表和插入数据的代码：

CREATE TABLE football (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO football VALUES (1, 'Beşiktaş');
INSERT INTO football VALUES (2, 'Beşiktaş');
INSERT INTO football VALUES (3, 'Galatasaray');
INSERT INTO football VALUES (4, 'Galatasaray');
INSERT INTO football VALUES (5, 'Beşiktaş');
INSERT INTO football VALUES (6, 'Istanbul');
INSERT INTO football VALUES (7, 'Istanbul');
INSERT INTO football VALUES (8, 'Istanbul');
INSERT INTO football VALUES (9, 'Galatasaray');
INSERT INTO football VALUES (10, 'Galatasaray');
INSERT INTO football VALUES (11, 'Fenerbahçe');
INSERT INTO football VALUES (12, 'Fenerbahçe');
INSERT INTO football VALUES (13, 'Fenerbahçe');
INSERT INTO football VALUES (14, 'Istanbul');

查询语句：

SELECT name, 
RANK() OVER() 
FROM football

得到的错误结果：

Beşiktaş|1
Beşiktaş|1
Galatasaray|1
Galatasaray|1
Beşiktaş|1
Istanbul|1
Istanbul|1
Istanbul|1
Galatasaray|1
Galatasaray|1
Fenerbahçe|1
Fenerbahçe|1
Fenerbahçe|1
Istanbul|1

可行解决方案 #

核心思路是通过窗口函数生成连续分组标识，将连续相同的俱乐部归为同一组，再分组统计数量并按原始顺序输出：

WITH grouped_data AS (
    SELECT 
        name,
        id,
        -- 计算连续分组ID：全局行号减去组内行号，相同连续段的结果一致
        ROW_NUMBER() OVER (ORDER BY id) - 
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS group_id
    FROM football
)
SELECT 
    name,
    COUNT(*) AS count
FROM grouped_data
GROUP BY name, group_id
ORDER BY MIN(id); -- 按分组内最小ID排序，保证输出顺序与原始序列一致

代码说明 #

1. ROW_NUMBER() OVER (ORDER BY id)：生成全局连续行号，对应原始数据的顺序。
2. ROW_NUMBER() OVER (PARTITION BY name ORDER BY id)：按俱乐部分组后，生成组内的连续行号。
3. group_id：两者的差值，对于连续相同的俱乐部，这个值会保持一致，不同的连续段会有不同的值，以此区分不同的连续分组。
4. 最后按name和group_id分组统计数量，并用MIN(id)排序，确保输出顺序和原始数据的连续段顺序一致。

执行上述查询后，即可得到期望的结果。

内容的提问来源于stack exchange，提问作者Felipe Felix