时间戳

首页 > 解决方案 > 使用从某个月份开始的 12 个月期间迭代日期

sql - 使用从某个月份开始的 12 个月期间迭代日期

问题描述

DB-小提琴

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    order_date DATE,
    customer VARCHAR(255)
);

INSERT INTO customers
(order_date, customer)
VALUES 
('2020-05-10', 'user_01'),
('2020-05-15', 'user_01'),
('2020-05-18', 'user_02'),
('2020-05-26', 'user_03'),

('2020-06-03', 'user_04'),
('2020-06-05', 'user_05'),
('2020-06-24', 'user_06'),


('2021-05-02', 'user_01'),
('2021-05-05', 'user_01'),
('2021-05-12', 'user_03'),
('2021-05-20', 'user_07'),

('2021-06-08', 'user_04'),
('2021-06-20', 'user_06'),
('2021-06-21', 'user_08'),
('2021-06-25', 'user_08'),
('2021-06-25', 'user_09');

预期结果：

order_date   |   customer  |  
-------------|-------------|----
2021-05-02   |   user_01   |  
2021-05-05   |   user_01   |  
2021-05-12   |   user_03   |  
-------------|-------------|----
2021-06-08   |   user_04   |  
2021-06-20   |   user_06   |

我想列出某个月份的所有客户

a)在过去12 个月内不存在且 b)在此12 个月之前已经存在。

在一个月内，我可以通过使用此查询来实现这一目标：

SELECT
c1.order_date,
c1.customer
FROM customers c1
WHERE c1.order_date BETWEEN '2021-05-01 00:00:00' AND '2021-05-31 23:59:59'

/* Check if customer exists in the past 12 months */
AND NOT EXISTS

   (SELECT
    c2.customer
    FROM customers c2
    WHERE c2.order_date BETWEEN '2020-06-01 00:00:00' AND '2021-04-30 23:59:59'
    AND c2.customer = c1.customer)

/* Check if customer exists before the past 12 months */
AND EXISTS

   (SELECT
    c2.customer
    FROM customers c2
    WHERE c2.order_date < '2020-06-01 00:00:00'
    AND c2.customer = c1.customer)

ORDER BY 2;

但是，我必须为每个月单独运行此查询。

因此，我想知道是否有一个迭代解决方案可以一次经历多个月。

在上面的示例中，它将运行BETWEEN '2021-05-01 00:00:00' AND '2021-06-30 23:59:59'并计算从 5 月开始的 12 个月，然后在下一步中从 6 月开始计算 12 个月，以获得预期的结果。

你知道这是否可能吗？

标签： sqlpostgresql

解决方案

SELECT 
t1.order_date, 
t1.customer
FROM 

  (SELECT
   c.customer,
   c.order_date,
   LAG(c.order_date) OVER (PARTITION BY c.customer ORDER BY c.order_date) AS prev_order_date
   FROM customers c) t1

WHERE t1.order_date BETWEEN '2021-05-01' AND '2021-06-30' 
AND t1.prev_order_date >= date_trunc('month', t1.order_date) - interval '12 month'
AND t1.prev_order_date < date_trunc('month', t1.order_date)
ORDER BY 1,2;

DB-小提琴

推荐阅读