如何基于各客户create_date次日起算周数并为购买日期分配周号

需求说明 #

我们有一张存储客户消费数据的表，每周按7天计算，规则如下：从每位客户的create_date次日开始，单独对该客户的周数从1开始计数，将cust_purchase_date划分到对应的week_number中，不同客户的周数划分规则相互独立。

原始数据表样例 #

cust_id       create_date      cust_purchase_date     purchase_amount  
  111          7/5/2021            7/6/2021              85.00      
  111          7/5/2021            7/8/2021              25.00      
  111          7/5/2021            7/15/2021             35.00      
  111          7/5/2021            7/25/2021             15.00      
  222          7/9/2021            7/10/2021             10.00      
  222          7/9/2021            7/18/2021             25.00      
  222          7/9/2021            7/25/2021             31.00      
  222          7/9/2021            7/27/2021             41.00      
  333          7/11/2021           7/15/2021             51.00      
  333          7/11/2021           7/21/2021             65.00      
  444          7/15/2021           7/16/2021             100.00     
  444          7/15/2021           7/24/2021             78.00      
  444          7/15/2021           7/30/2021             87.00      
  555          8/20/2021           8/24/2021             71.00      
  555          8/20/2021           8/30/2021             55.00      
  555          8/20/2021           9/3/2021              36.00      
  555          8/20/2021           9/8/2021              25.00      

周数划分逻辑参考表（模拟） #

cust_id  create_date  Wk 1 Start    Wk 1 End     Wk 2 Start   Wk 2 End    Wk 3 Start  Wk 3 End....
 111      7/5/2021     7/6/2021     7/12/2021    7/13/2021    7/19/2021   7/20/2021   7/26/2021...
 222      7/9/2021     7/10/2021    7/16/2021    7/17/2021    7/23/2021   7/24/2021   7/30/2021...
 333      7/11/2021    7/12/2021    7/18/2021    7/19/2021    7/25/2021   7/26/2021   8/1/2021...
 444      7/15/2021    7/16/2021    7/22/2021    7/23/2021    7/29/2021   7/30/2021   8/5/2021...
 555      8/20/2021    8/21/2021    8/27/2021    8/28/2021    9/3/2021    9/4/2021    9/10/2021...

期望输出结果 #

cust_id       create_date      cust_purchase_date     purchase_amount    week_number            
  111          7/5/2021            7/6/2021              85.00               1
  111          7/5/2021            7/8/2021              25.00               1
  111          7/5/2021            7/15/2021             35.00               2
  111          7/5/2021            7/25/2021             15.00               3
  222          7/9/2021            7/10/2021             10.00               1
  222          7/9/2021            7/18/2021             25.00               2
  222          7/9/2021            7/25/2021             31.00               3
  222          7/9/2021            7/27/2021             41.00               3
  333          7/11/2021           7/15/2021             51.00               1
  333          7/11/2021           7/21/2021             65.00               2
  444          7/15/2021           7/16/2021             100.00              1
  444          7/15/2021           7/24/2021             78.00               2
  444          7/15/2021           7/30/2021             87.00               3
  555          8/20/2021           8/24/2021             71.00               1
  555          8/20/2021           8/30/2021             55.00               2
  555          8/20/2021           9/3/2021              36.00               3
  555          8/20/2021           9/8/2021              25.00               3

实现方案 #

核心逻辑为计算购买日期和客户注册日期的天数差，基于差值计算所属周数：

1. 计算cust_purchase_date与create_date的间隔天数
2. 间隔天数减1后对7取整，再+1即为对应week_number

通用SQL写法示例 #

SELECT 
    cust_id,
    create_date,
    cust_purchase_date,
    purchase_amount,
    FLOOR((DATEDIFF(cust_purchase_date, create_date) -1 ) / 7) + 1 AS week_number
FROM customer_purchase_table;

不同SQL方言可调整日期差计算和取整函数：

• MySQL：直接使用上述写法即可
• Hive/Spark SQL：将FLOOR替换为CAST(xxx AS INT)或者使用整数除法DIV
• PostgreSQL：使用(DATEDIFF('day', create_date, cust_purchase_date) - 1) // 7 +1

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