Power BI：多行数据场景下计算客户H1、H2产品购买间隔天数

核心逻辑梳理 #

• 先过滤全量数据，仅保留产品为H1、H2的记录，排除其余28种无关产品降低运算量
• 单独提取每个客户ID的H1最早购买日期
• 统计每个客户在H1首次购买之后产生的H2购买记录，按时间排序后取第二次的购买日期
• 计算两个日期的自然日差值即可

方案1：SQL实现（适配数据库存储的560万行数据，性能最优） #

WITH filtered_data AS (
    -- 筛选需要的产品记录
    SELECT 
        customer_id,
        product_type,
        purchase_date
    FROM purchase_table
    WHERE product_type IN ('H1', 'H2')
),
h1_first_purchase AS (
    -- 取每个客户H1的首次购买日期
    SELECT 
        customer_id,
        MIN(purchase_date) AS h1_first_date
    FROM filtered_data
    WHERE product_type = 'H1'
    GROUP BY customer_id
),
h2_ordered AS (
    -- 给H1购买后产生的H2购买记录按时间排序
    SELECT 
        f.customer_id,
        f.purchase_date AS h2_purchase_date,
        ROW_NUMBER() OVER (PARTITION BY f.customer_id ORDER BY f.purchase_date ASC) AS h2_buy_order
    FROM filtered_data f
    INNER JOIN h1_first_purchase h1 
        ON f.customer_id = h1.customer_id
    WHERE f.product_type = 'H2' 
        AND f.purchase_date > h1.h1_first_date
)
-- 最终计算日期间隔
SELECT 
    h1.customer_id,
    h1.h1_first_date,
    h2.h2_purchase_date AS h2_second_date,
    DATEDIFF(day, h1.h1_first_date, h2.h2_purchase_date) AS daydiff
FROM h1_first_purchase h1
INNER JOIN h2_ordered h2 
    ON h1.customer_id = h2.customer_id
WHERE h2.h2_buy_order = 2

注：不同数据库的日期差计算函数语法略有差异，可根据实际使用的数据库调整DATEDIFF部分的写法。如果实际需求为统计首次买H1后首次买H2的间隔，将h2_buy_order = 2修改为h2_buy_order = 1即可。

方案2：Python Pandas实现（适配本地结构化数据集处理） #

560万行数据Pandas可正常处理，读取时仅加载需要的字段可大幅降低内存占用。

import pandas as pd

# 读取数据时指定usecols仅加载需要的3个字段，减少内存开销
df = pd.read_csv('你的数据文件路径', usecols=['customer_id', 'product_type', 'purchase_date'])
# 转换日期格式
df['purchase_date'] = pd.to_datetime(df['purchase_date'])

# 1. 过滤仅保留H1、H2的购买记录
df_filter = df[df['product_type'].isin(['H1', 'H2'])].copy()

# 2. 计算每个客户H1的首次购买日期
h1_first = df_filter[df_filter['product_type'] == 'H1']\
    .groupby('customer_id')['purchase_date'].min()\
    .reset_index()
h1_first.columns = ['customer_id', 'h1_first_date']

# 3. 筛选H1购买后产生的H2记录，按客户分组排序取第二次购买的记录
h2_df = df_filter[df_filter['product_type'] == 'H2'].merge(h1_first, on='customer_id')
h2_df = h2_df[h2_df['purchase_date'] > h2_df['h1_first_date']]
h2_df['h2_buy_order'] = h2_df.groupby('customer_id')['purchase_date']\
    .rank(method='first', ascending=True).astype(int)
h2_second = h2_df[h2_df['h2_buy_order'] == 2][['customer_id', 'purchase_date']]
h2_second.columns = ['customer_id', 'h2_second_date']

# 4. 合并计算日期间隔
result = h1_first.merge(h2_second, on='customer_id')
result['daydiff'] = (result['h2_second_date'] - result['h1_first_date']).dt.days

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