如何用SQL计算连续行时间差？Redshift中统计休眠两年复购客户

嗨，作为SQL新手碰到这些问题太正常了，我来帮你逐个梳理解决～

问题1：如何查看连续两行数据的时间差？ #

当然可以！Redshift支持窗口函数，用LAG()或者LEAD()就能轻松获取相邻行的日期，再结合DATEDIFF()计算时间差。

举个例子，假设你要按客户分组，查看每个客户每次购买和上一次购买的时间间隔（以天为单位）：

SELECT
  cust_id,
  purchase_date,
  -- 获取上一行的purchase_date
  LAG(purchase_date) OVER (PARTITION BY cust_id ORDER BY purchase_date) AS prev_purchase_date,
  -- 计算当前行和上一行的天数差
  DATEDIFF(day, LAG(purchase_date) OVER (PARTITION BY cust_id ORDER BY purchase_date), purchase_date) AS days_since_last_purchase
FROM
  tickets
ORDER BY
  cust_id, purchase_date;

• PARTITION BY cust_id：确保只在同一客户的记录内找相邻行
• ORDER BY purchase_date：保证行是按购买时间顺序排列的，这样相邻行才是连续的购买记录
• 如果想计算和下一行的时间差，把LAG()换成LEAD()就行

问题2：统计休眠两年后再次购买的客户（修正你的SQL） #

你的现有SQL里DATEDIFF(year, t.purchase_date, t.purchase_date)这部分肯定不对——用同一个日期计算差值，结果永远是0，根本筛选不出休眠两年的情况。我们需要先找到每个客户每次购买和上一次购买的间隔，再判断间隔是否≥2年。

下面是修正后的完整脚本，分步骤实现：

-- 第一步：为每个客户的购买记录标记上一次购买日期
WITH customer_purchases AS (
  SELECT
    t.cust_id,
    t.purchase_date,
    LAG(t.purchase_date) OVER (PARTITION BY t.cust_id ORDER BY t.purchase_date) AS prev_purchase_date
  FROM
    tickets t
  LEFT JOIN d_customer c ON c.cust_id = t.cust_id
),
-- 第二步：筛选出间隔≥2年的复购记录
reactivated_customers AS (
  SELECT
    DISTINCT cust_id
  FROM
    customer_purchases
  WHERE
    prev_purchase_date IS NOT NULL -- 排除客户的第一次购买（没有上一次记录）
    AND DATEDIFF(year, prev_purchase_date, purchase_date) >= 2
)
-- 第三步：统计符合条件的客户数量
SELECT COUNT(DISTINCT cust_id) AS reactivated_customer_count
FROM reactivated_customers;

关键说明： #

1. WITH子句用来拆分逻辑，让代码更清晰
2. DISTINCT cust_id：避免同一个客户多次复购被重复统计
3. Redshift的DATEDIFF(year, start, end)会计算两个日期之间的年份差，比如2020-01-01到2022-02-01会返回2，符合“休眠两年后”的要求

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