Postgresql中的分组限制10

Question

我有一个疑问：

select
    a.kli,
    b.term_desc,
    count(distinct(a.adic)) as count,
    a.partner_id

from
    ad_delivery.sgmt_kli_adic a
    join wand.wandterms b on a.kli = b.term_code
    join wand.wandterms c on b.term_desc=c.term_desc
    join dwh.sgmt_clients e on a.partner_id::varchar = e.partner_id
    join dwh.schema_names f on e.partner_id::integer = f.partner_id::integer
where
    a.partner_id::integer in (f.partner_id)
    and c.class_code = 969
group by a.partner_id, b.term_desc, a.kli
order by partner_id, count desc;

这会为每个partner_id带来特定术语的计数。我希望能够按照计数顺序显示每个~40 partner_id的前10位。

查询结果如下所示

db=#从xxx中选择*；pid \x{e76f}\x{e76f}+

Answer 1

您可以添加一个秩列，然后按级别筛选结果：

select
    a.kli,
    b.term_desc,
    count(distinct(a.adic)) as count,
    a.partner_id,
    RANK() OVER (PARTITION BY a.partner_id order by a.partner_id DESC) AS r
from
    ad_delivery.sgmt_kli_adic a
    join wand.wandterms b on a.kli = b.term_code
    join wand.wandterms c on b.term_desc=c.term_desc
    join dwh.sgmt_clients e on a.partner_id::varchar = e.partner_id
    join dwh.schema_names f on e.partner_id::integer = f.partner_id::integer
where
    a.partner_id::integer in (f.partner_id)
    and c.class_code = 969
group by a.partner_id, b.term_desc, a.kli
HAVING r < 11
order by partner_id, count desc;

我还没有测试代码，但是诀窍是对GROUP BY的每一行进行排序，并使用HAVING子句过滤结果集，只保留级别低于11的项(每个组将得到10项)。