sql - LIMIT 基于 COUNT of DISTINCT `foreign_key` 到目前为止

Question

我有 2 个表“sites”和“trucks”的联合(按 distance 排序)。记录集如下所示:

我需要从第 1 行开始获取所有行，直到达到特定 (n) 个唯一的 company_id。

所以，如果我得到如下内容:

然后我可以进行一个简单的查询，例如:

SELECT * FROM union_recordset where distinct_company_id_count_so_far < (3 + 1);
-- where n = 3

并得到想要的结果:

Answer 1

如果您的数据库支持 count(distinct) 作为窗口函数:

select ur.*,
       count(distinct company_id) over (order by distance) as cnt
from union_recordset ur
order by distance;

如果没有，您可以计算第一次出现的次数:

select ur.*,
       sum(case when seqnum = 1 then 1 else 0 end) over (order by distance) as cnt
from (select ur.*,
             row_number() over (partition by company_id order by distance) as seqnum
      from union_recordset ur
     ) ur
order by distance;

在 Postgres 中，sum() 可以简化为:

       sum( (seqnum = 1)::int ) over (order by distance) as cnt

然后要获取前三个公司的数字，您需要:

select ur.*
from (select ur.*,
             sum( (seqnum = 1)::int ) over (order by distance) as cnt
      from (select ur.*,
                   row_number() over (partition by company_id order by distance) as seqnum
            from union_recordset ur
           ) ur
     ) ur
where cnt <= 3
order by distance;