Блог

У меня есть база данных 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
  

Вы можете использовать агрегацию для вычисления общего объема продаж на клиента и за год, а затем использовать функцию window 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