群并根据一定条件给出了等级

Question

我想根据时间给下面的数据排序(如果时间戳差小于15分钟，那么相同的等级是+1 )。

• 时间: 06:10:32和06:12:38应该是1级
• 时间: 13:36:44和13:40:41和13:44:47应该排在第2位
• 最后四排应该排在第三位

user_id ride_id      createdat_local    
2681233 96783742    2017-10-04 06:10:32 
2681233 96784171    2017-10-04 06:12:38 
2681233 96924751    2017-10-04 13:36:44 
2681233 96925561    2017-10-04 13:40:41 
2681233 96926560    2017-10-04 13:44:47 
2681233 96994651    2017-10-04 18:12:29 
2681233 96995953    2017-10-04 18:18:16 
2681233 96996937    2017-10-04 18:22:15 
2681233 96997195    2017-10-04 18:24:00 

Answer 1

在Server 2012+中：

使用公共表表达式中的window函数公共表表达式获取与createdat_local的上一行值相比较的datediff()，然后使用条件聚合的sum() over()生成秩：

;with cte as (
select *
  , datediff(minute,lag(createdat_local) over (
      partition by user_id
      order by createdat_local
      ),createdat_local) as prev_dat
from t
)
select user_id, ride_id, createdat_local
  , sum(case when coalesce(prev_dat,16)>15 then 1 else 0 end) over (
    partition by user_id
    order by createdat_local
    ) as rank
from cte

rextester演示：http://rextester.com/EQUC48356

返回：

+---------+----------+---------------------+------+
| user_id | ride_id  |   createdat_local   | rank |
+---------+----------+---------------------+------+
| 2681233 | 96783742 | 2017-10-04 06:10:32 |    1 |
| 2681233 | 96784171 | 2017-10-04 06:12:38 |    1 |
| 2681233 | 96924751 | 2017-10-04 13:36:44 |    2 |
| 2681233 | 96925561 | 2017-10-04 13:40:41 |    2 |
| 2681233 | 96926560 | 2017-10-04 13:44:47 |    2 |
| 2681233 | 96994651 | 2017-10-04 18:12:29 |    3 |
| 2681233 | 96995953 | 2017-10-04 18:18:16 |    3 |
| 2681233 | 96996937 | 2017-10-04 18:22:15 |    3 |
| 2681233 | 96997195 | 2017-10-04 18:24:00 |    3 |
+---------+----------+---------------------+------+

Answer 2

能够在红移中达到所需的结果(Psql)

查询:以cte ( select *，(DATEPART('hour'，createdat_local) * 60 +DATEPART(‘when’，createdat_local)) -滞后(DATEPART(‘hour’，createdat_local) * 60 + DATEPART('minute'，createdat_local)以上( user_id order by createdat_local)作为diff_in_minutes从t中选择user_id、ride_id、createdat_local、sum(煤炭时)( 16)>15，则为1其他0结束(按user_id顺序在无界前行行和当前行之间按createdat_local顺序划分)为cte的秩；