带关联列的分组查询：GROUP BY、ARBITRARY与JOIN方案对比

在包含多段计算/CTE的SQL查询中，需按user_id列执行GROUP BY操作，同时最终结果要包含与user_id呈1:1关联的user_name和city列。从性能与代码可读性的双重角度考量，哪种传递这类关联列的方式最优？以下为三种简化方案示例（CTE中可能存在多阶段分组操作）：

方案1：每次分组时都对user_id、user_name、city三列执行GROUP BY #

WITH cte1 AS (
  SELECT user_id, user_name, city, other_grouping_column, my_metrics FROM my_table
), 
cte2 AS (
  SELECT
    user_id,
    user_name,
    city,
    other_grouping_column,
    some_agg_metric1
  FROM cte1
  GROUP BY 
    user_id,
    user_name,
    city,
    other_grouping_column
)
SELECT 
  user_id,
  user_name,
  city,
  some_agg_metric2
FROM cte2
GROUP BY 
  user_id,
  user_name,
  city

方案2：在每次分组时用ARBITRARY()替代对user_name和city的GROUP BY #

WITH cte1 AS (
  SELECT user_id, user_name, city, other_grouping_column, my_metrics FROM my_table
), 
cte2 AS (
  SELECT
    user_id,
    ARBITRARY(user_name) AS user_name,
    ARBITRARY(city) AS city,
    other_grouping_column,
    some_agg_metric1
  FROM cte1
  GROUP BY 
    user_id,
    other_grouping_column
)
SELECT 
  user_id,
  ARBITRARY(user_name) AS user_name,
  ARBITRARY(city) AS city,
  some_agg_metric2
FROM cte2
GROUP BY 
  user_id

方案3：在查询末尾通过JOIN关联原表（或初始CTE）获取对应列 #

WITH cte1 AS (
  SELECT user_id, other_grouping_column, my_metrics FROM my_table
), 
cte2 AS (
  SELECT
    user_id,
    other_grouping_column,
    some_agg_metric1
  FROM cte1
  GROUP BY 
    user_id,
    other_grouping_column
), 
cte3 AS (
  SELECT 
    user_id,
    some_agg_metric2
  FROM cte2
  GROUP BY 
    user_id
)
SELECT 
  cte3.user_id,
  my_table.user_name,
  my_table.city,
  cte3.some_agg_metric
FROM cte3
JOIN my_table
  ON cte3.user_id = my_table.user_id

方案对比与最优选择 #

性能维度

• 方案1：每次分组都要把user_name和city加入分组键，会增大分组计算的开销——分组键的大小直接影响内存占用和排序/哈希分组的效率，数据量越大，性能损耗越明显。
• 方案2：ARBITRARY()（部分数据库如Snowflake支持，类似的还有MySQL的ANY_VALUE()）只要求按user_id分组，避免了额外列的分组开销，性能优于方案1；但要注意函数的数据库兼容性。
• 方案3：分组阶段仅处理user_id和聚合列，数据量更小，分组效率高，但最后的JOIN操作需要依赖user_id的唯一性（否则会产生重复行）；如果原表数据量大且user_id无索引，JOIN的开销可能超过方案2。

可读性维度

• 方案1：逻辑直观，看到GROUP BY就能理解关联列与分组键的关系，但冗余的分组列会让SQL显得繁琐，多阶段分组时重复书写容易出错。
• 方案2：代码更简洁，分组键仅保留user_id，但需要读者理解ARBITRARY()的语义——因为user_name、city和user_id是1:1关联，所以取任意值结果都是正确的，对不熟悉该函数的人有一定认知门槛。
• 方案3：分组逻辑与关联列获取完全分离，分组阶段专注聚合计算，最后统一关联用户信息，逻辑清晰，但需要额外的CTE或JOIN步骤，结构稍显复杂。

最终结论

如果所用数据库支持ARBITRARY()（或同语义函数），方案2是最优选择——既减少了分组开销，又避免了冗余分组列和额外JOIN，平衡了性能与可读性。若数据库不支持这类函数，方案3更合适，将分组与关联列获取分离，保持分组逻辑简洁，同时确保结果正确（前提是user_id在原表中唯一）；方案1不推荐，冗余分组列既影响性能又增加维护风险。

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