时间戳

首页 > 解决方案 > 以 12 个月滚动的方式遍历客户，并检查客户在过去 12 个月内是否未订购

sql - 以 12 个月滚动的方式遍历客户，并检查客户在过去 12 个月内是否未订购

问题描述

DB-小提琴

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

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

('2020-04-12', 'user_04'),
('2020-04-19', 'user_05'),
('2020-04-23', 'user_06'),


('2021-03-09', 'user_01'),
('2021-03-17', 'user_07'),

('2021-04-03', 'user_02'),
('2021-04-18', 'user_05'),
('2021-04-20', 'user_08');

预期结果：

churn_date    |   customer   |
--------------|--------------|----
2021-03-18    |   user_02    |
2021-03-26    |   user_03    |
--------------|--------------|-----
2021-04-12    |   user_04    |
2021-04-23    |   user_06    |

我想在12 个月滚动的基础上遍历客户，并检查每个月客户的最后一个订单是否是在 12 个月前下达的。

例如：
user_02应该出现在 的结果中March但不应该出现在 的结果中April。
鉴于上March一次订单发生在 12 个月前2020-03-18。
在视图April上发生的最后一个命令2021-04-03。

在一个月内，我可以通过以下查询来做到这一点：

SELECT
(c1.order_date + interval '12 month')::date as churn_date,
c1.customer
FROM customers c1
WHERE c1.order_date BETWEEN '2020-03-01 00:00:00' AND '2020-03-31 23:59:59'
    
AND NOT EXISTS
    
   (SELECT
    c2.customer
    FROM customers c2
    WHERE c2.order_date BETWEEN '2020-04-01 00:00:00' AND '2021-03-31 23:59:59'
    AND c2.customer = c1.customer)

ORDER BY 1,2;

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

因此，我想知道是否有一个迭代解决方案可以一次经历多个月并给我预期的结果？

标签： sqlpostgresql

解决方案

这是上一个带有WHERE子句的查询：

SELECT gs.month, c.customer, MAX(c.order_date + interval '12 month')::date as churn_date
FROM customers c CROSS JOIN
     GENERATE_SERIES('2021-03-01'::date, '2021-04-01'::date, interval '1 month') gs(month)
WHERE c.order_date < gs.month + interval '1 month'
GROUP BY gs.month, c.customer
HAVING DATE_TRUNC('month', MAX(c.order_date)) = DATE_TRUNC('month', gs.month) - interval '12 month';

这是一个 db<>fiddle。

推荐阅读