如何在Cassandra中实现同组数据分组查询？GROUP BY用法解惑

问题背景 #

现有数据集如下：

("group_1" , uuid , other, columns),
("group_1" , uuid , other, columns),
("group_1" , uuid , other, columns),
("group_2" , uuid , other, columns),
("group_2" , uuid , other, columns),
("group_3" , uuid , other, columns),
("group_3" , uuid , other, columns),

数据存储在定义好的表中：

CREATE TABLE sample(
 group TEXT,
 id TEXT,
 Other,
 columns,
 PRIMARY KEY( group , id)
);

需要将同组数据归为子列表，生成嵌套格式的结果，示例如下：

[ 
 [("group_1" , uuid , other, columns),
  ("group_1" , uuid , other, columns),
  ("group_1" , uuid , other, columns)],
 [("group_2" , uuid , other, columns),
  ("group_2" , uuid , other, columns)],
 [("group_3" , uuid , other, columns),
  ("group_3" , uuid , other, columns)],
]

尝试SELECT * FROM sample GROUP BY group;仅返回每组第一行，且无法提前知晓所有组名，不能逐个按组查询，需要可行的实现方案。

解决方案 #

1. 数据库端聚合实现（推荐） #

不同数据库提供了行转集合的聚合函数，可直接生成嵌套结构：

PostgreSQL

用array_agg生成数组格式的分组结果：

SELECT group, array_agg(row(group, id, other, columns)) AS group_rows
FROM sample
GROUP BY group;

如果需要JSON格式的嵌套列表，使用json_agg：

SELECT json_agg(json_agg(row_to_json(sample))) AS nested_list
FROM sample
GROUP BY group;

MySQL

使用JSON_ARRAYAGG构建JSON数组：

SELECT group, JSON_ARRAYAGG(JSON_OBJECT('group', group, 'id', id, 'other', other, 'columns', columns)) AS group_rows
FROM sample
GROUP BY group;

要直接生成顶层嵌套列表，可嵌套查询：

SELECT JSON_ARRAYAGG(group_rows) AS nested_list
FROM (
    SELECT JSON_ARRAYAGG(JSON_OBJECT('group', group, 'id', id, 'other', other, 'columns', columns)) AS group_rows
    FROM sample
    GROUP BY group
) AS grouped_data;

SQLite

3.38.0及以上版本支持JSON_GROUP_ARRAY：

SELECT group, JSON_GROUP_ARRAY(JSON_OBJECT('group', group, 'id', id, 'other', other, 'columns', columns)) AS group_rows
FROM sample
GROUP BY group;

生成完整嵌套列表：

SELECT JSON_GROUP_ARRAY(group_rows) AS nested_list
FROM (
    SELECT JSON_GROUP_ARRAY(JSON_OBJECT('group', group, 'id', id, 'other', other, 'columns', columns)) AS group_rows
    FROM sample
    GROUP BY group
) AS grouped_data;

2. 客户端侧分组（通用兼容方案） #

如果数据库不支持复杂聚合，可先排序查询所有数据，再在代码中分组：

1. 先执行排序查询：

SELECT * FROM sample ORDER BY group;

2. 以Python为例，在客户端处理分组：

import psycopg2  # 替换为对应数据库的驱动
from collections import defaultdict

# 连接数据库
conn = psycopg2.connect("your_connection_string")
cur = conn.cursor()
cur.execute("SELECT * FROM sample ORDER BY group;")
rows = cur.fetchall()

# 按组分组构建嵌套列表
grouped_data = defaultdict(list)
for row in rows:
    grouped_data[row[0]].append(row)
nested_list = list(grouped_data.values())

print(nested_list)

为什么直接GROUP BY无效？ #

GROUP BY的核心是做聚合统计，默认不搭配聚合函数时，只会返回每组的一条数据（不同数据库行为略有差异），只有配合聚合函数，才能将组内所有行合并为集合类型，实现嵌套列表的效果。

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