SQL中同手机号行按属性分组排序的实现方法及效率探究

需求说明 #

现有一张存储相同手机号、不同属性的数据库表，要求不修改表结构，实现以下效果：

1. 相同手机号的记录归为一组
2. 每组内按organizationid进行子分组
3. 每个子组内按createdat字段降序排列

同时需要对比仅按手机号、organizationid分组（不排序）与上述需求的效率差异。

实现方法 #

方法1：直接使用多字段ORDER BY（最简洁） #

这是最直接的实现方式，利用SQL的多字段排序特性，先按手机号聚合，再按组织ID子分组，最后对每个子组内的记录按创建时间降序排列。适用于所有主流关系型数据库（MySQL、PostgreSQL、SQL Server等）。

示例SQL（假设表名为user_records）：

SELECT id, phone, createdat, audienceid, organizationid
FROM user_records
ORDER BY phone DESC, organizationid DESC, createdat DESC;

• 逻辑：phone DESC确保相同手机号的记录集中展示；organizationid DESC实现同手机号下的子分组；createdat DESC完成子组内的降序排序。
• 优势：代码简洁，执行效率高（若有合适索引）。

方法2：窗口函数辅助排序（逻辑更清晰） #

通过窗口函数ROW_NUMBER()标记子组内的排序序号，外层再按手机号、组织ID排序，适合需要对每个子组做额外处理（如取前N条）的场景，也能直观体现分组排序逻辑。

示例SQL：

SELECT id, phone, createdat, audienceid, organizationid
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY phone, organizationid ORDER BY createdat DESC) AS group_row_num
    FROM user_records
) AS sorted_groups
ORDER BY phone DESC, organizationid DESC, group_row_num;

• 逻辑：子查询中用PARTITION BY按手机号+组织ID分组，ORDER BY createdat DESC给子组内记录编号；外层按手机号、组织ID排序，再按编号升序即可得到子组内降序的结果。
• 优势：分组逻辑明确，扩展性强（可快速修改为取子组前N条、计算组内排名等）。

方法3：GROUP_CONCAT聚合展示（仅用于聚合场景） #

如果只需要查看每个子组的聚合结果，而非返回单条记录，可使用GROUP_CONCAT结合内部排序，将子组内的记录拼接为字符串。仅适用于MySQL等支持该函数的数据库。

示例SQL：

SELECT phone, organizationid,
       GROUP_CONCAT(
           CONCAT(id, '|', createdat, '|', audienceid) 
           ORDER BY createdat DESC 
           SEPARATOR ';'
       ) AS group_details
FROM user_records
GROUP BY phone, organizationid
ORDER BY phone DESC, organizationid DESC;

• 逻辑：按手机号+组织ID分组，将每个子组内的记录按创建时间降序拼接成字符串，方便快速查看分组内容。
• 局限：无法返回原表的单条记录，仅用于聚合展示场景。

效率差异分析 #

仅分组（无排序）的情况 #

这里的“仅分组”指仅将相同手机号、organizationid的记录聚合展示（或用GROUP BY返回聚合结果），不对createdat排序：

1. 若存在(phone, organizationid)联合索引，数据库可直接按索引顺序遍历，无需额外排序操作，IO和CPU开销极低；
2. 若使用GROUP BY聚合，数据库仅需分组聚合，不需要处理组内排序，执行计划中不会出现文件排序（Using filesort），性能最优。

分组加排序的情况 #

对比仅分组，加createdat排序会增加额外开销，差异取决于索引情况：

1. 有合适联合索引：若存在(phone, organizationid, createdat DESC)联合索引，数据库可直接通过索引有序遍历返回结果，排序几乎无额外开销，效率与仅分组接近；
2. 无合适索引：数据库会触发文件排序（Using filesort），需要在内存或磁盘中对数据排序，数据量越大，CPU和IO开销越高，性能远低于仅分组；
3. 窗口函数实现：窗口函数的PARTITION BY和ORDER BY会触发组内排序，即使有索引，开销也略高于直接ORDER BY，但比无索引的文件排序好。

总结：仅分组的效率始终高于分组加排序，差距大小取决于是否有优化排序的联合索引。

内容的提问来源于stack exchange，提问作者GABRIEL R GARCIA-AVILES