从给定日期开始周数

Question

假设我们有一个存储客户数据的表。考虑到每周包含7天，我渴望能够分别在每个客户的之后的第二天从1开始计算create_date周数。之后，将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

Answer 1

您可以使用

select abs('7/5/2020'::DATE - '7/15/2021'::DATE) / 7 as weeks;
 weeks 
-------
    53

使用abs()，这样日期的顺序就无关紧要了。

Answer 2

我不知道您的date_table的用途，但它似乎试图预先确定每个客户的周号，每周有一列。这种方法的问题是要生成几个星期的列？当客户在create_date之后5年(或更多)购买时，会发生什么情况？此外，这是不必要的。Postgres允许日期减法将天数表示为整数。因为你的“一周包含7天”简单的除法给了这个星期。(见演示)。

select cust_id         
     , create_date   
     , purchase_date  
     , amount 
     , (purchase_date - create_date)/7 + 1 week_num
  from customer_purchases;