sql中类别之间的间隔天数

Question

是否可以计算每个类别的第一次购买和第二次购买之间的差异？

例如，我这里有一个数据集：

trx_id  user_pin category    date
    1   "jak"   "smartphone" "2018-01-01"
    1   "jak"   "groceries"  "2018-01-01"
    1   "jak"   "health"     "2018-01-01"
    1   "jak"   "virtual"    "2018-01-01"
    2   "mei"   "groceries"  "2018-01-01"
    2   "mei"   "virtual"    "2018-01-01"
    4   "jak"   "smartphone" "2018-05-02"
    8   "mei"   "groceries"  "2018-09-02"
    8   "mei"   "virtual"    "2018-09-02"

我想知道购买和结果的不同日期是这样的：

trx_id  user_pin     category     date        next_buy      diff
        1   "jak"   "smartphone" "2018-01-01" "2018-05-02"  121
        1   "jak"   "groceries"  "2018-01-01" "2018-05-02"  121
        1   "jak"   "health"     "2018-01-01" "2018-05-02"  121
        1   "jak"   "virtual"    "2018-01-01" "2018-05-02"  121
        4   "jak"   "smartphone" "2018-05-02"  "null"       null
        2   "mei"   "groceries"  "2018-01-01"  "2018-09-02" 244
        2   "mei"   "virtual"    "2018-01-01"  "2018-09-02" 244
        5   "mei"   "groceries"  "2018-09-02"  "null"       null
        5   "mei"   "virtual"    "2018-09-02"  "null"       null

查询结果如何？

Answer 1

因此，假设SQL Server可以使用CTE和outer apply执行类似操作，以获取下一条记录：

 WITH mydataranked AS (
      Select trx_id,user_pin, category, purchase_date, 
      DENSE_RANK() OVER (Partition by user_pin, category ORDER by purchase_Date asc) as myrank
      from mydata
      )


 Select trx_id, user_pin, category, md.purchase_date, aa.purchase_date as next_buy, datediff(day, md.purchase_date, aa.purchase_date) as diff  
 from mydataranked md

  outer apply 
 (Select purchase_date from mydataranked md2 
  where md.user_pin=md2.user_pin and md.category=md.category and md2.myrank=md.myrank+1) aa