基于Impala计算客户-产品维度的购买间隔天数及频率问题咨询

我来帮你搞定这个计算购买间隔频率的需求，咱们一步步拆解实现：

首先，你的date_purchase字段是字符串类型，第一步得把它转换成日期格式才能进行天数计算。接下来我们需要用窗口函数获取每个客户每个产品的上一次购买日期，然后算出相邻购买的天数差，最后统计这些间隔的频率。

第一步：获取相邻购买的间隔天数 #

先运行这个查询，得到每个客户每个产品的每两次相邻购买之间的天数：

WITH purchase_dates AS (
    -- 把字符串日期转成日期类型
    SELECT 
        id_customer,
        id_product,
        to_date(date_purchase, 'dd/MM/yyyy') AS purchase_date
    FROM example_date
),
ranked_purchases AS (
    -- 用LAG窗口函数获取同一客户同一产品的上一次购买日期
    SELECT 
        id_customer,
        id_product,
        purchase_date,
        LAG(purchase_date) OVER (PARTITION BY id_customer, id_product ORDER BY purchase_date) AS prev_purchase_date
    FROM purchase_dates
)
-- 计算两次购买的天数差，过滤掉只有一次购买的记录
SELECT 
    id_customer,
    id_product,
    purchase_date,
    prev_purchase_date,
    DATEDIFF(purchase_date, prev_purchase_date) AS days_between_purchases
FROM ranked_purchases
WHERE prev_purchase_date IS NOT NULL;

第二步：统计购买间隔的频率 #

根据你的需求，有两种常见的统计方式：

方式1：统计所有客户产品组合的间隔天数整体频率

这个查询会告诉你整个数据集中，不同间隔天数出现的次数：

WITH purchase_dates AS (
    SELECT 
        id_customer,
        id_product,
        to_date(date_purchase, 'dd/MM/yyyy') AS purchase_date
    FROM example_date
),
ranked_purchases AS (
    SELECT 
        id_customer,
        id_product,
        purchase_date,
        LAG(purchase_date) OVER (PARTITION BY id_customer, id_product ORDER BY purchase_date) AS prev_purchase_date
    FROM purchase_dates
),
purchase_intervals AS (
    SELECT 
        DATEDIFF(purchase_date, prev_purchase_date) AS days_between_purchases
    FROM ranked_purchases
    WHERE prev_purchase_date IS NOT NULL
)
SELECT 
    days_between_purchases,
    COUNT(*) AS frequency
FROM purchase_intervals
GROUP BY days_between_purchases
ORDER BY days_between_purchases;

方式2：按客户产品组合分别统计间隔频率

这个查询会展示每个客户每个产品各自的购买间隔分布：

WITH purchase_dates AS (
    SELECT 
        id_customer,
        id_product,
        to_date(date_purchase, 'dd/MM/yyyy') AS purchase_date
    FROM example_date
),
ranked_purchases AS (
    SELECT 
        id_customer,
        id_product,
        purchase_date,
        LAG(purchase_date) OVER (PARTITION BY id_customer, id_product ORDER BY purchase_date) AS prev_purchase_date
    FROM purchase_dates
),
purchase_intervals AS (
    SELECT 
        id_customer,
        id_product,
        DATEDIFF(purchase_date, prev_purchase_date) AS days_between_purchases
    FROM ranked_purchases
    WHERE prev_purchase_date IS NOT NULL
)
SELECT 
    id_customer,
    id_product,
    days_between_purchases,
    COUNT(*) AS frequency
FROM purchase_intervals
GROUP BY id_customer, id_product, days_between_purchases
ORDER BY id_customer, id_product, days_between_purchases;

注意事项 #

• 确保你的SQL引擎支持窗口函数（比如Hive、Spark SQL、MySQL 8.0+、PostgreSQL等都支持）；
• to_date和DATEDIFF函数的参数顺序可能因SQL引擎略有不同，如果是MySQL的话，DATEDIFF的参数顺序是DATEDIFF(start_date, end_date)，需要调整一下；
• 只有当客户对同一产品有至少两次购买时，才会计算出间隔天数，所以我们用WHERE prev_purchase_date IS NOT NULL过滤掉了只有一次购买的记录。

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