如何用SQL计算2022年及以后的12个月滚动活跃客户数？

我有一张包含order_date和customer_id字段的orders表，需要计算2022年及以后每个日期的12个月滚动去重活跃客户数。我尝试了如下SQL代码：

SELECT
  order_date,
  COUNT(DISTINCT customer_id) OVER (ORDER BY order_date RANGE INTERVAL '360' day PRECEDING) AS customers_12m
  -- 用窗口函数创建360天的时间范围，统计该时段内的去重客户数
FROM
  orders
WHERE
  order_date >= '2021-01-01'
GROUP BY
  order_date
HAVING
  order_date >= '2022-01-01'

但在不同DBMS平台（Redshift、Exasol、Trino(Presto)）上遇到了不同错误（如不支持DISTINCT窗口聚合、不支持RANGE INTERVAL语法等）。请问仅用SQL能否实现该需求？

示例输入 #

示例输出（以1天滚动代替360天） #

当然可以实现，以下是兼容Redshift、Exasol、Trino等多平台的通用方案，核心思路是用日期维度表+条件聚合规避窗口函数的兼容性限制：

具体SQL代码 #

-- 1. 生成需要统计的目标日期范围（已有日期维度表可直接替换）
WITH target_dates AS (
  SELECT generate_series(
    DATE '2022-01-01',
    (SELECT MAX(order_date) FROM orders),
    INTERVAL '1 day'
  )::DATE AS order_date
),
-- 2. 先对订单按日期+客户去重，减少后续计算量
distinct_customer_dates AS (
  SELECT DISTINCT order_date, customer_id
  FROM orders
)
-- 3. 计算每个日期的12个月滚动去重客户数
SELECT
  td.order_date,
  COUNT(DISTINCT dcd.customer_id) AS customers_12m
FROM target_dates td
LEFT JOIN distinct_customer_dates dcd
  ON dcd.order_date BETWEEN td.order_date - INTERVAL '12 months' AND td.order_date
GROUP BY td.order_date
ORDER BY td.order_date;

针对不同平台的微调 #

• Redshift：generate_series需用递归CTE替代：

  WITH target_dates AS (
    SELECT DATE '2022-01-01' AS order_date
    UNION ALL
    SELECT order_date + INTERVAL '1 day'::DATE
    FROM target_dates
    WHERE order_date < (SELECT MAX(order_date) FROM orders)
  )
  

• Trino(Presto)：日期生成语法调整为：

  SELECT date_add('day', i, DATE '2022-01-01') AS order_date
  FROM unnest(sequence(0, date_diff('day', DATE '2022-01-01', (SELECT MAX(order_date) FROM orders)))) t(i)
  

• Exasol：无需调整，直接使用通用代码即可。

方案优势 #

• 完全规避了窗口函数中COUNT(DISTINCT)和RANGE INTERVAL的兼容性问题；
• 提前去重减少了关联计算的数据量，提升性能；
• 左连接确保无订单的日期也会被输出（若需过滤可加HAVING COUNT(DISTINCT dcd.customer_id) > 0）。

内容的提问来源于stack exchange，提问作者Danyal Imran