每月总购买金额排名前3位的客户

Question

我想查找每个月总购买金额排名前3位的客户名称

表1

Product
Product_id  product_name product_price
P1           ABC           20
P2           DEF           30

2号表

Sales
Cust_id     Date     Quantity    Product_id
C1         1-Jan-21     3           P1
C1         2-Feb-21     4           P2
C2         5-Jan-21     6           P1
C2         7-Jan-21     1           P1
C3         9-feb-21     5           P2

纤维表

Customer
ID     Name
C1     JKL
C2     MNO
C3     OPQ

Answer 1

首先计算客户每月购买金额(CTE)然后对每月的金额进行排序(CTE r)最后按等级和顺序过滤。

with t as 
(
select 
s.cust_id,
date_trunc('month', "Date") as period,
s.quantity * p.product_price as amount 
from sales s join product p on s.product_id = p.product_id
group by period, s.cust_id
), 
r as
(
select *,
row_number() over (partition by period order by amount desc) as rank
from t
)
select r.period, c.name as cust_name, r.rank as period_rank
from r join customer c on r.cust_id = c.id
where rank <= 3 
order by r.period, r.rank;

这个查询可以写得不那么冗长，但我认为清晰度和可读性会受到影响。