代码之家 › 专栏 › 技术社区 › the_darkside

在SQL中按条件对连续值进行分组和排名

prestodb sql

the_darkside · 技术社区 · 7 年前

我有一张桌子 mytable 我想在其中添加两列

我的目标是分组 user_id 和 mobile_id 只有如果存在连续的值序列, difftime > - 600 . 序列必须在中连续 created_at (时间戳),并给出排名,如果是同一用户和移动ID,但 difftime 发生<-600。每个单独的组将被分配一个增量值。例如:

> mytable
            created_at user_id mobile_id   status difftime
1  2019-01-02 22:01:38 1227604     68409 finished      \\N
2  2019-01-03 04:08:29 1227604     68409 finished     -366
3  2019-01-03 15:16:38 1227604     68409  timeout     -668
4  2019-01-04 00:34:40 1227604     68409   failed     -558
5  2019-01-04 00:27:37 1227605     68453   failed      \\N
6  2019-01-04 00:35:56 1227605     68453 finished       -8
7  2019-01-04 01:39:52 1227605     68453 finished      -63
8  2019-01-04 02:05:53 1227605     68453  timeout      -26
9  2019-01-04 02:17:17 1227605     68453  timeout      -11
10 2019-01-04 16:51:39 1227605     68453  timeout     -874

将创建的输出

> output
            created_at user_id mobile_id   status difftime group rank
1  2019-01-02 22:01:38 1227604     68409 finished      \\N    NA   NA
2  2019-01-03 04:08:29 1227604     68409 finished     -366     1    1
3  2019-01-03 15:16:38 1227604     68409  timeout     -668    NA   NA
4  2019-01-04 00:34:40 1227604     68409   failed     -558     2    1
5  2019-01-04 00:27:37 1227605     68453   failed      \\N    NA   NA
6  2019-01-04 00:35:56 1227605     68453 finished       -8     3    1
7  2019-01-04 01:39:52 1227605     68453 finished      -63     3    2
8  2019-01-04 02:05:53 1227605     68453  timeout      -26     3    3
9  2019-01-04 02:17:17 1227605     68453  timeout      -11     3    4
10 2019-01-04 16:51:39 1227605     68453  timeout     -874    NA   NA

当我简单地尝试分配一个列组时,下面的查询会抛出一个错误: WHERE clause cannot contain aggregations, window functions or grouping operations

虽然我使用的是Presto SQL,但是这里的任何SQL解决方案都将有助于思考如何重新构造查询。

SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY user_id, mobile_id ORDER BY created_at) as rank
    from mytable
    WHERE DATE_DIFF('minute', created_at, lag(created_at) OVER (PARTITION BY user_id, mobile_id ORDER BY user_id, created_at)) > -600
    ORDER BY user_id, mobile_id, created_at

1 回复 | 直到 7 年前

Gordon Linoff 7 年前

要标识组,请对“无效”的值进行累积和。然后使用 dense_rank() 指定一个值。

我不知道您的查询与您的问题有什么关系,但逻辑如下:

select t.*, grp,
       (case when difftime > -600
             then row_number() over (partition by user_id, mobile_id order by created_at)
        end) as rank
from (select t.*,
             dense_rank() over (partition by user_id, mobile_id order by grouping) as grp
      from (select t.*,
                   sum(case when difftime > -600 then 1 else 0 end) over (partition by user_id, mobile_id order by created_at) as grouping
            from t
            ) t
     ) t