当前位置：首页 > news >正文

基于SQL实现分组的文字排序聚合

news 2026/6/29 23:15:18

作为数据工程师，对数据进行处理分析时，时常遇到基于SQL实现分组内的文字列的排序聚合是非常常见的场景，比如按照价格高低进行各类别下商品的汇总、按照成绩进行各班的学生的排名等。当下，支持SQL的数据库生态百花齐放，SQL方言也是多种多样，为了方便记忆，以下总结了常见的几类数据产品或者平台的支持情况（代码经过实际测试可跑通）：

假设数据集为sales，需要展示每个类别下的按照价格排序的商品清单。

CREATE TABLE sales AS SELECT 'Electronics' AS category, 'Laptop' AS product, 1000 AS price UNION ALL SELECT 'Electronics', 'Mouse', 20 UNION ALL SELECT 'Electronics', 'Keyboard', 80 UNION ALL SELECT 'Furniture', 'Desk', 300 UNION ALL SELECT 'Furniture', 'Chair', 150 ；

1，Spark

SELECT category, concat_ws(',', TRANSFORM( SORT_ARRAY(COLLECT_LIST(STRUCT(price, product))), s -> s.product ) ) AS products_sorted FROM sales GROUP BY category ;

2，Impala

SELECT category, regexp_replace( group_concat(concat_ws('|', rn, product), ','), '[0-9]+\\|', '' ) AS products_sorted FROM ( SELECT category, product, cast(row_number() OVER (PARTITION BY category ORDER BY price) AS STRING) AS rn FROM sales ) t GROUP BY category;

3，Oracle

select category , LISTAGG(product, ', ') WITHIN GROUP (ORDER BY price) AS product_sorted from sales t group by category

4，SQL Server

SELECT category, STRING_AGG(product, ', ') WITHIN GROUP (ORDER BY price) AS products_sorted FROM sales GROUP BY category;

5，PostgreSQL

SELECT category, string_agg(product, ', ' ORDER BY price) AS products_sorted FROM sales GROUP BY category;

6，MySQL

SELECT category, GROUP_CONCAT(product ORDER BY price SEPARATOR ',') AS products_sorted FROM sales GROUP BY category;

7、SQLite&DuckDB

SELECT category, string_agg(product, ', ' ORDER BY price) AS products_sorted, group_concat(product, ', ' ORDER BY price DESC) AS products_sorted1 FROM sales GROUP BY category;

8，ClickHouse

--方式一，利用子查询先行排序 SELECT category, arrayStringConcat(groupArray(product), ',') AS products_sorted FROM ( SELECT category, product FROM db_test.sales ORDER BY category, price ASC ) GROUP BY category; --方式二，Lambda表达式 SELECT category, arrayStringConcat( arrayMap( x -> x.2, -- 提取元组的第二个元素，即 product arraySort( x -> x.1, -- 按元组的第一个元素（price）升序排序 groupArray((price, product)) ) ), ',' ) AS products_sorted FROM db_test.sales GROUP BY category;

总结：

大数据体系：
- Spark SQL：需要使用collect_list + sort_array + transform + concat_ws 组合才能实现该功能。
- Impala SQL：不支持 order by 在 group_concat 内，需要在子查询中利用row_number先行排序，代码相对复杂，且该方法只支持Impala 2.3+。
商业产品：
- Oracle：listagg(expr, sep) within group (order by …)
- SQL Server：string_agg(expr, sep) within group (order by …)，只支持2017+。
开源产品：
- PostgreSQL：string_agg(expr, sep order by …)
- MySQL：group_concat(expr order by … separator sep)
嵌入式数据库：
- SQLite&DuckDB：group_concat(expr, sep order by ...), 或者string_agg(expr, sep order by ...)
分析型数据库：
- ClickHouse：较旧的版本中需要使用子查询或者arrayStringConcat + groupArray + arraySort + arrayMap，从24.8版本开始支持groupConcat(sep)(expr ORDER BY ...)
国产信创数据库：
- 达梦DaMeng：可设置兼容模式，一般设置为兼容Oracle，可支持listagg语法
- 海量Vastbase：原生兼容PostgreSQL，支持string_agg

Oracle listagg是商业数据库比较早的实现该功能的函数，使用方便，最为经典并广为人知。SQL Server前期支持较弱，后期弥补了该功能短板，为了方便用户记忆使用，格式与Oracle格式高度相似。开源数据库中，Mysql和PostgreSQL各自使用了独立的格式，相对商业数据库，变化较大，需要额外的记忆。嵌入式数据库中，无论是OLTP的SQLite，还是OLAP的DuckDB，其格式与PG高度一致，说明PG的影响力非常大，SQLite从3.44.0版本开始支持带order by功能，DuckDB本身定位为分析型数据库，从最早的版本即有完整的功能支持。大数据体系的特点是海量存储和非结构化数据处理，针对结构化数据的复杂处理逻辑，支持相对较弱，可以看出Impala和Spark的SQL实现逻辑最为复杂，嵌套使用了多种函数，代码冗长，很难快速记住。分析型数据库中，ClickHouse早期的逻辑比较复杂，Lambda函数类似Spark SQL，后期应该是为了用户使用方便，做了简化，但语法保持了自己的特色。国产信创数据库方面，目前多为兼容Oracle或者PostgreSQL，暂时应该还未发展出独立的语法体系。

以上只记录了语法格式的差异，而由于各个产品的架构设计不一样，实际使用中性能的差异可能会比较大。当然如果数据量不大，没有到亿级的规模，性能应该都是可以接受的。

查看全文

http://www.jsqmd.com/news/1092337/