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

作为数据工程师对数据进行处理分析时时常遇到基于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 1SparkSELECT category, concat_ws(,, TRANSFORM( SORT_ARRAY(COLLECT_LIST(STRUCT(price, product))), s - s.product ) ) AS products_sorted FROM sales GROUP BY category ;2ImpalaSELECT 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;3Oracleselect category , LISTAGG(product, , ) WITHIN GROUP (ORDER BY price) AS product_sorted from sales t group by category4SQL ServerSELECT category, STRING_AGG(product, , ) WITHIN GROUP (ORDER BY price) AS products_sorted FROM sales GROUP BY category;5PostgreSQLSELECT category, string_agg(product, , ORDER BY price) AS products_sorted FROM sales GROUP BY category;6MySQLSELECT category, GROUP_CONCAT(product ORDER BY price SEPARATOR ,) AS products_sorted FROM sales GROUP BY category;7、SQLiteDuckDBSELECT 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;8ClickHouse--方式一利用子查询先行排序 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。商业产品Oraclelistagg(expr, sep) within group (order by …)SQL Serverstring_agg(expr, sep) within group (order by …)只支持2017。开源产品PostgreSQLstring_agg(expr, sep order by …)MySQLgroup_concat(expr order by … separator sep)嵌入式数据库SQLiteDuckDBgroup_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_aggOracle 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暂时应该还未发展出独立的语法体系。以上只记录了语法格式的差异而由于各个产品的架构设计不一样实际使用中性能的差异可能会比较大。当然如果数据量不大没有到亿级的规模性能应该都是可以接受的。