如何统计SQL列中特定字母后紧邻的各字母出现次数
letras表中A后续紧邻字符的统计实现
问题背景
现有存储字母序列的letras表,数据严格按id递增顺序排列,存储的字母顺序如给定测试集所示。需求为统计所有位置出现的字母A,其紧邻下一个位置的字母的分布次数,预期统计结果为后续为A共2次、B共8次、C共7次。此前尝试使用COUNT、SUM函数直接统计未得到正确结果,给定测试插入语句如下:
INSERT INTO `letras` (`id`, `letra`) VALUES (1, 'B'), (2, 'A'), (3, 'B'), (4, 'C'), (5, 'B'), (6, 'A'), (7, 'B'), (8, 'C'), (9, 'A'), (10, 'B'), (11, 'A'), (12, 'A'), (13, 'C'), (14, 'B'), (15, 'C'), (16, 'A'), (17, 'B'), (18, 'B'), (19, 'A'), (20, 'C'), (21, 'C'), (22, 'A'), (23, 'B'), (24, 'A'), (25, 'B'), (26, 'C'), (27, 'A'), (28, 'C'), (29, 'A'), (30, 'B'), (31, 'C'), (32, 'B'), (33, 'B'), (34, 'A'), (35, 'C'), (36, 'A'), (37, 'A'), (38, 'C'), (39, 'B'), (40, 'B'), (41, 'B'), (42, 'A'), (43, 'B'), (44, 'B'), (45, 'B'), (46, 'A'), (47, 'C'), (48, 'A'), (49, 'C'), (50, 'B'), (51, 'A');
实现思路
核心逻辑是建立当前行和紧邻下一行的关联关系,筛选出当前行值为A的记录,对其下一行的字母值做分组计数即可。之前用COUNT、SUM失败的核心原因是没有正确匹配到A对应的下一行数据,仅对全表的A、B、C做了整体计数,未建立前后行的位置关联。
可直接运行的SQL实现
写法1:通用自连接兼容所有数据库版本
不依赖特殊语法,在MySQL5.x、SQLite等所有数据库环境都可运行:
SELECT t2.letra AS next_letter, COUNT(*) AS occurrence_times FROM letras t1 -- 匹配当前行id的下一个id对应的行 INNER JOIN letras t2 ON t2.id = t1.id + 1 -- 仅保留当前行是A的记录 WHERE t1.letra = 'A' -- 对下一行的字母分组计数 GROUP BY t2.letra;
运行该SQL得到的结果与预期完全一致:
- 下一个字母为A:2次
- 下一个字母为B:8次
- 下一个字母为C:7次
写法2:窗口函数实现(支持MySQL8.0+、PostgreSQL等)
利用窗口函数的LEAD取值函数直接提取当前行的下一个位置的值,无需自连接,数据量大时性能更优:
WITH sorted_letters AS ( SELECT letra, -- 按id排序,提取当前行后1行的字母值 LEAD(letra, 1) OVER (ORDER BY id) AS next_letter FROM letras ) SELECT next_letter, COUNT(*) AS occurrence_times FROM sorted_letters WHERE letra = 'A' -- 排除最后一行没有后续值的空记录 AND next_letter IS NOT NULL GROUP BY next_letter;
注意点:序列最后一行(id=51)是字母A,它后面没有紧邻的后续字母,统计时需要排除这类无对应后续值的记录,避免统计出无效的空值计数。
内容的提问来源于stack exchange,提问作者Claudio Caetano
相关产品推荐
相关产品推荐

