使用lag()窗口函数扩展SQL查询：按唯一parent_id限制结果行数

我们有一张包含id、score、parent_id字段的表，数据默认按score降序排列。之前用lag()窗口函数的方案只能处理parent_id连续出现的场景，但现在parent_id可能分散在多行中，需要实现：按原顺序查询所有列，直到结果包含5个唯一的parent_id（即5859、21064154、51992、3465729、401203），且结果至少有5行。

原始表数据 #

需求说明 #

按score降序的原始顺序返回所有列，需满足：

• 结果包含至少5个唯一的parent_id
• 取到满足上述条件的最小数据集（一旦凑齐5个唯一parent_id，就停止，不需要后续行）

预期结果 #

原方案的局限性 #

原使用lag()的方案只能处理parent_id连续出现的情况，当parent_id非连续时，会错误统计唯一值数量，代码如下：

select id, score, parent_id
from (
  select *, Sum(diff) over(order by score desc)seq
  from (
     select *, 
       case when Lag(parent_id) over(order by score desc) = parent_id then 0 else 1 end diff
    from t
  )t
)d
where seq <= 5
order by score desc;

解决方案 #

要实现非连续场景下的唯一parent_id计数，需要统计累积的唯一值数量，以下是两种可行方案：

通用SQL方案（支持PostgreSQL、SQL Server等）

适用于支持窗口函数中DISTINCT的数据库，通过累积计数找到满足条件的最小数据集：

WITH ranked_data AS (
    SELECT 
        id, score, parent_id,
        -- 计算当前行及之前所有行的唯一parent_id数量
        COUNT(DISTINCT parent_id) OVER (ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS unique_parent_count,
        -- 记录当前行的排序位置
        ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
    FROM t
),
threshold AS (
    -- 找到第一次出现第5个唯一parent_id的行号
    SELECT MIN(rn) AS min_rn
    FROM ranked_data
    WHERE unique_parent_count = 5
)
SELECT id, score, parent_id
FROM ranked_data
WHERE rn <= (SELECT min_rn FROM threshold)
ORDER BY score DESC;

MySQL兼容方案（MySQL 8.0+）

如果MySQL不支持窗口函数中的DISTINCT，可以用自定义变量来跟踪已出现的parent_id并统计唯一数量：

SELECT id, score, parent_id
FROM (
    SELECT 
        t.*,
        @unique_count := CASE 
            WHEN FIND_IN_SET(parent_id, @seen_parents) THEN @unique_count
            ELSE @unique_count + 1
        END AS unique_parent_count,
        @seen_parents := CONCAT(@seen_parents, ',', parent_id) AS seen_parents
    FROM t
    CROSS JOIN (SELECT @unique_count := 0, @seen_parents := '') AS init
    ORDER BY score DESC
) AS temp
WHERE unique_parent_count <= 5
ORDER BY score DESC;

方案说明 #

1. 通用方案中，窗口函数COUNT(DISTINCT parent_id)会计算从第一行到当前行的所有唯一parent_id数量，随后找到第一次达到5的行号，返回该行及之前的所有数据，确保是满足条件的最小数据集。
2. MySQL方案中，用@seen_parents变量记录已经出现过的parent_id，@unique_count统计唯一数量，每遇到新的parent_id就增加计数，最后筛选计数≤5的行，同样符合需求。

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