如何编写满足三类要求的SQLite窗口聚合函数查询？

需求说明 #

• 必须以窗口聚合函数的形式调用聚合函数
• 查询结果为仅含单个值的单行数据
• 聚合函数仅被SQLite执行一次（单次序列计算）
  预期输出结果：'aaa,bbb,ccc'

预加载测试数据 #

CREATE TABLE tbl(a INTEGER PRIMARY KEY, b TEXT);
INSERT INTO tbl VALUES (1, 'bbb'), (2, 'ccc'), (3, 'aaa');

不符合要求的查询示例及问题 #

1. 多次调用窗口聚合函数

SELECT GROUP_CONCAT(b)
  OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM tbl;

输出结果（重复3行，不符合单行要求）：

aaa,bbb,ccc
aaa,bbb,ccc
aaa,bbb,ccc

2. 未使用窗口聚合函数（结果顺序无官方保障）

SELECT group_concat(b) FROM (SELECT b FROM tbl ORDER BY b);

输出结果：

aaa,bbb,ccc

问题：SQLite官方文档未定义子查询排序后外层聚合的结果顺序，未来版本可能变更。

3. 未使用窗口聚合函数且结果错误

SELECT group_concat(b) FROM tbl ORDER BY b;

输出结果：

bbb,ccc,aaa

问题：ORDER BY仅作用于最终结果行，不会影响group_concat内部的拼接顺序，导致结果不符合预期。

符合所有要求的查询 #

SELECT DISTINCT GROUP_CONCAT(b) OVER (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS concatenated_result
FROM tbl;

或简化窗口范围（因ORDER BY的列无重复值，默认范围与指定范围效果一致）：

SELECT DISTINCT GROUP_CONCAT(b) OVER (ORDER BY b) AS concatenated_result
FROM tbl;

说明

• DISTINCT将窗口函数生成的重复聚合结果压缩为单行，同时保证窗口聚合函数仅被执行一次
• ORDER BY b确保GROUP_CONCAT按指定顺序拼接字符串
• 窗口范围覆盖全表数据，实现全局聚合的效果

内容的提问来源于stack exchange，提问作者Yuri Astrakhan