时间戳

首页 > 解决方案 > sql中类别之间的间隔天数

postgresql - sql中类别之间的间隔天数

问题描述

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

例如，我在这里有数据集：

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

查询情况如何？

标签： postgresql

解决方案

因此，假设 SQL Server 您可以使用 CTE 和外部应用执行类似的操作来获取下一条记录：

 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

推荐阅读