sql - 最大计数 - PostgreSQL

Question

我想从表中提取某些东西的最大值(计数(*))。

实际上，我想做的是推出客户最喜欢的品牌。所以他们每年购买 300 block 肥皂，但我想知道他们最喜欢哪一 block 。所以 max(count(brand_id) 基本上。

我想这样做:

    SELECT
 transaction.customer_id,
 max(occ) 
 FROM
 (  SELECT 
    transaction.customer_id,
    count(transaction.brand_id) as occ,
    FROM
    transaction

    GROUP BY
    transaction.customer_id,

) AS foo
GROUP BY
transaction.customer_id

提前致谢

Answer 1

你可以这样做:

with cte as (
    select customer_id, brand_id, count(*) as cnt
    from test1
    group by customer_id, brand_id
)
select distinct on (customer_id)
    customer_id, brand_id, cnt
from cte
order by customer_id, cnt desc

请记住，如果有多个品牌对某个客户具有相同的计数，您最终会得到一个任意记录。如果要获取所有记录，使用dense_rank()函数:

with cte1 as (
    select customer_id, brand_id, count(*) as cnt
    from test1
    group by customer_id, brand_id
), cte2 as (
    select
        customer_id, brand_id,
        dense_rank() over(partition by customer_id order by cnt desc) as rn
    from cte1
)
select customer_id, brand_id
from cte2
where rn = 1

sql fiddle demo

对于 PostgreSQL 8.3:

select distinct on (customer_id)
    customer_id, brand_id, cnt
from (
    select customer_id, brand_id, count(*) as cnt
    from test1
    group by customer_id, brand_id  
) as c
order by customer_id, cnt desc;

sql fiddle demo