在滚动的12个月上迭代生成的序列.红移的基础

Question

DB-Fiddle

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-months-rolling-basis和对每个月的客户进行迭代。

例如：

user_02应该出现在March的结果中，而不应该出现在April的结果中。

在March看来，上一次订单发生在12个月前的2020-03-18上。

在April看来，最后一次订单发生在2021-04-03上。

在postgresSQL中，我可以通过这个问题的解决方案来实现这一点。

SELECT 
gs.month AS month, 
c.customer AS 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') AS gs(month)

WHERE c.order_date < gs.month + interval '1 month'
GROUP BY 1,2
HAVING DATE_TRUNC('month', MAX(c.order_date)) = DATE_TRUNC('month', gs.month) - interval '12 month';

但是，在amazon-redshift中，我得到一个错误，即GENERATE_SERIES函数不存在。

如何修改查询以使其在amazon-redshift中工作

Answer 1

在Redshift中，您需要显式地定义表。有一种方法是在线的：

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

您还可以玩其他的把戏--例如，如果您有一个日历表或数字表，或者真正有12行的任何表：

FROM customers c CROSS JOIN
     (SELECT (date + (ROW_NUMBER() OVER () - 1) * INTERVAL '1 MONTH') as month 
      FROM (SELECT '2012-03-01'::date as date
            FROM <any table with 12 rows>
            LIMIT 12
           ) gs
      LIMIT 12
     ) gs