sql - 如何使用postgresql获取每个月的第三个订单

Question

如何获取每个月的第 3 个订单？

订单详情

order_id  || customer_name  || order_Date
--------     ------------      ----------
101             rahul          2019-06-12  
102             naveen         2019-07-24
103             naveen         2019-08-03
104             naveen         2019-09-15 
105             rahul          2019-06-27   
106             rahul          2019-08-21   

这是每个月第 3 个订单客户下订单的输出。我做的是一阶的。但是，我不能进行第三次订购。

以下 sql 查询用于查找每个月客户级别的第一个订单。

查询

SELECT billing_address_full_name, email,
  MIN(CASE WHEN month = 'March' THEN 1 ELSE NULL END) March,
  MIN(CASE WHEN month = 'April' THEN 1 ELSE NULL END) April,
  MIN(CASE WHEN month = 'May' THEN 1 ELSE NULL END) May,
  MIN(CASE WHEN month = 'June' THEN 1 ELSE NULL END) June,
  MIN(CASE WHEN month = 'July' THEN 1 ELSE NULL END) July,
  MIN(CASE WHEN month = 'August' THEN 1 ELSE NULL END) August,
  MIN(CASE WHEN month = 'September' THEN 1 ELSE NULL END) September
FROM ord_details
WHERE email NOT IN (
  SELECT email 
  FROM cus_details 
  WHERE customer_groups = 'internal'
) 
GROUP BY billing_address_full_name, email;

结果

          JUNE  JULY  AUGUST SEPTEMBER
rahul      0      0     1        0
Naveen     0      0     0        1       (Just for example)

Answer 1

我不是 100% 遵循您的示例，但如果您想为每个客户获取当月的第三个订单，您可以使用窗口函数来实现:

SELECT *
FROM (
  SELECT o.*, 
    ROW_NUMBER() OVER( -- Group by (customer, month)
      PARTITION BY customer_name, EXTRACT(MONTH FROM order_date)
      ORDER BY order_date -- Order rows from oldest to newest
    )
  FROM ord_details o
) src
WHERE RowNum = 3 -- Get third row from each group

这不处理当月没有“第 3 个”订单的情况。在那种情况下你想返回什么？

这是您要找的吗？