如何用SQL按自定义逻辑为每个id_2分组选行及实现扩展需求？

核心需求 #

按id_2字段分组，每组按以下规则选取一行：

1. 优先选择store='a'的行
2. 若分组内无'a'，则选择store='c'的行
3. 可按需扩展其他优先级规则
   同时为选中的行附加该分组的最小价格（无需来自选中行本身）

窗口函数实现方案 #

不需要自定义窗口函数，利用内置的ROW_NUMBER()窗口函数结合CASE自定义优先级排序即可实现，这是最简洁高效的方式：

WITH ranked_rows AS (
  SELECT
    *,
    -- 定义优先级：a > c > 其他（可根据需求扩展更多store的优先级）
    CASE store
      WHEN 'a' THEN 1
      WHEN 'c' THEN 2
      ELSE 3
    END AS priority,
    -- 计算当前id_2分组的最小价格
    MIN(price) OVER (PARTITION BY id_2) AS group_min_price
  FROM test_table
)
SELECT
  id,
  store,
  price,
  id_2,
  group_min_price
FROM ranked_rows
WHERE row_number() OVER (
  PARTITION BY id_2
  ORDER BY priority ASC, id ASC -- 优先级相同时，可按id或其他字段确定唯一行
) = 1
ORDER BY id_2;

逻辑说明 #

• CASE语句为不同store分配优先级数值，数值越小优先级越高，确保'a'被优先选中
• MIN(price) OVER (PARTITION BY id_2)直接在窗口中计算每个分组的最小价格，无需额外聚合操作
• ROW_NUMBER()按id_2分区，按优先级排序后，取行号为1的记录即为每组符合要求的行

替代实现方式：子查询+UNION ALL #

如果对窗口函数不熟悉，也可以用子查询结合UNION ALL按优先级依次筛选：

WITH group_min AS (
  -- 预计算每个分组的最小价格
  SELECT id_2, MIN(price) AS group_min_price
  FROM test_table
  GROUP BY id_2
),
priority_selection AS (
  -- 第一优先级：选所有store='a'的行
  SELECT tt.*, gm.group_min_price
  FROM test_table tt
  JOIN group_min gm ON tt.id_2 = gm.id_2
  WHERE store = 'a'
  
  UNION ALL
  
  -- 第二优先级：选没有'a'的分组中store='c'的行
  SELECT tt.*, gm.group_min_price
  FROM test_table tt
  JOIN group_min gm ON tt.id_2 = gm.id_2
  WHERE store = 'c'
    AND tt.id_2 NOT IN (SELECT id_2 FROM test_table WHERE store = 'a')
  
  UNION ALL
  
  -- 兜底：选剩下分组中id最小的行（可根据需求调整兜底规则）
  SELECT tt.*, gm.group_min_price
  FROM test_table tt
  JOIN group_min gm ON tt.id_2 = gm.id_2
  WHERE tt.id_2 NOT IN (SELECT id_2 FROM test_table WHERE store IN ('a', 'c'))
    AND tt.id = (SELECT MIN(id) FROM test_table WHERE id_2 = tt.id_2)
)
SELECT * FROM priority_selection ORDER BY id_2;

逻辑说明 #

• 先通过group_min计算所有分组的最小价格
• 按优先级依次筛选记录，用UNION ALL合并结果，确保高优先级的记录不会被低优先级覆盖
• 兜底规则可根据需求调整，比如选价格最低的行、最新的行等

关于自定义窗口函数的疑问 #

不需要自定义窗口函数，PostgreSQL内置的窗口函数结合CASE语句已经能完美实现自定义优先级的筛选逻辑，比编写自定义窗口函数更简单、性能更优。

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