时间戳

首页 > 解决方案 > 按年份选择 2 个最高计数（值）

sql - 按年份选择 2 个最高计数（值）

问题描述

我有一个 postgresql-9.6 数据库，我们给它命名sales，类似如下：

sale_id   customer_id   sale_date    price
1         20            2017-01-05   2000
2         150           2017-05-26   1500
3         121           2017-07-07   2560
4         121           2017-12-25   3000
5         214           2018-02-11   2550
6         17            2018-04-21   2500
7         20            2018-07-01   3000
8         121           2019-07-01   2568

我需要每年找到前 2 名客户。

我被困在这样的事情上：

SELECT 
   date_part('year', sale_date) AS year, 
   customer_id, 
   sum(price) AS Total 
FROM 
   sales 
GROUP BY 1,2 
ORDER BY 1,3 DESC 
LIMIT 2

我试图得到这样的东西：

year     customer_id      Total
2017     121              5560
2017     20               2000
2018     20               3000
2018     214              2550
2019     121              2568

标签： sqlpostgresqlgreatest-n-per-group

解决方案

您可以使用聚合来计算每个客户和每年的总销售额，然后使用窗口函数ROW_NUMBER（自 Postgres 9.4 起可用）过滤每年的前 2 个客户：

SELECT
    sale_year,
    customer_id,
    total_price
FROM (
    SELECT 
        x.*,
        ROW_NUMBER() OVER(PARTITION BY sale_year ORDER BY total_price DESC) rn
    FROM (
        SELECT
            date_part('year', sale_date) AS sale_year, 
            customer_id, 
            sum(price) AS total_price 
        FROM sales
        GROUP BY date_part('year', sale_date), customer_id
    ) x
) y 
WHERE rn <= 2
ORDER BY sales_year, rn

推荐阅读