sql - Redshift : Find max level of previous mode

Question

我有一个存储每个类别完成级别的用户表。
完成第一类关卡后，可以解锁第二类和第三类。

我的目标是找到他们解锁其他类别的级别(在第一类中)。

注:数据非原创!

例子:

|               times        | users | levels    |    mode | 
|----------------------------|-------|-----------|---------|
| 2019-07-30 10:39:55.000000 | A     |  1        |  First  |  
| 2019-07-30 10:43:16.000000 | A     |  2        |  First  |  
| 2019-07-30 10:45:03.000000 | A     |  3        |  First  |  
| 2019-07-30 10:47:20.000000 | A     |  999      |  Second |  
| 2019-07-30 10:49:50.000000 | A     |  999      |  Second | 
| 2019-07-30 20:21:39.000000 | B     |  1        |  First  |  
| 2019-07-31 11:10:35.000000 | B     |  2        |  First  |  
| 2019-07-31 11:11:51.000000 | B     |  3        |  First  |  
| 2019-07-31 11:13:01.000000 | B     |  4        |  First  |  
| 2019-07-31 11:15:11.000000 | B     |  5        |  First  | 
| 2019-07-31 11:17:24.000000 | B     |  999      |  Third  | 
| 2019-08-01 02:16:13.000000 | B     |  999      |  Second | 
| 2019-08-01 02:29:31.000000 | A     |  4        |  First  | 
| 2019-08-01 08:04:01.000000 | A     |  5        |  First  | 
| 2019-08-01 08:06:27.000000 | A     |  999      |  Third  |
| 2019-08-01 08:10:02.000000 | A     |  1        |  First  |
| 2019-08-01 08:12:29.000000 | A     |  999      |  Second |
| 2019-08-02 04:45:43.000000 | A     |  999      |  Third  |
| 2019-08-02 07:42:35.000000 | C     |  1        |  First  |
| 2019-08-02 08:12:30.000000 | C     |  2        |  First  |
| 2019-08-02 08:15:53.000000 | C     |  3        |  First  |
| 2019-08-02 08:17:24.000000 | D     |  1        |  First  |

所以

User A unlocked the second category after complete the First category Level 3, unlocked  the third category after Level 5
User B unlocked both second and third category after level 5
User C No any unlock category
User D No any unlock category 

注意:只有第一类有等级，其他类都是固定等级(999)

我尝试使用 Lag() over partition by 并取第一个类别级别的最新值:

SELECT users,
       times,
       levels,
       mode,
       rnk,
       lag(levels, 1) OVER (PARTITION BY users ORDER BY times, mode) last_story_level
FROM (
       SELECT users
              times,
              CASE WHEN mode = 'First' THEN levels ELSE NULL END levels,
              mode,
         -- rnk will I use rnk=1 for the first value of each mode
         row_number() OVER (PARTITION BY mode, users ORDER BY times) rnk
       FROM my_table
       ORDER BY times
     )

结果是:

|               time        | user | level    |    mode | rnk| last_story_level|
|----------------------------|-----------------|---   |-------|---|---|
| 2019-07-30 10:39:55.000000 | A    |  1    |  First   | 1 | NULL |
| 2019-07-30 10:43:16.000000 | A    |  2    |  First   | 2 | 1   |
| 2019-07-30 10:45:03.000000 | A    |  3    |  First   | 3 | 2   |
| 2019-07-30 10:47:20.000000 | A    |  NULL  |  Second | 1  |3  |
| 2019-07-30 10:49:50.000000 | A    |  NULL  |  Second | 2 |NULL|
| 2019-07-30 20:21:39.000000 | B    |  1    |  First   | 1 |NULL|
| 2019-07-31 11:10:35.000000 | B    |  2    |  First   | 2 |1  |
| 2019-07-31 11:11:51.000000 | B    |  3    |  First   | 3 |2  |
| 2019-07-31 11:13:01.000000 | B    |  4    |  First   | 4 |3  |
| 2019-07-31 11:15:11.000000 | B    |  5    |  First   | 5 |4  |
| 2019-07-31 11:17:24.000000 | B    |  NULL |  Third   | 1 |5  |
| 2019-08-01 02:16:13.000000 | B    |  NULL |  Second  | 1 |NULL|
| 2019-08-01 02:29:31.000000 | A    |  4    |  First   | 4 |NULL|
| 2019-08-01 08:04:01.000000 | A    |  5    |  First   | 5 |4  |
| 2019-08-01 08:06:27.000000 | A    |  NULL |  Third   | 1 |5  |
| 2019-08-01 08:10:02.000000 | A    |  1    |  First   | 6 |NULL|
| 2019-08-01 08:12:29.000000 | A    |  NULL | Second   | 3 |1  |
| 2019-08-02 04:45:43.000000 | A    |  NULL | Third    | 2 |NULL|
| 2019-08-02 07:42:35.000000 | C    |  1    | First    | 1 |NULL|
| 2019-08-02 08:12:30.000000 | C    |  2    | First    | 2 |1  |
| 2019-08-02 08:15:53.000000 | C    |  3    | First    | 3 |2  |
| 2019-08-02 08:17:24.000000 | D    |  1    | First    | 1 |NULL|

问题是当用户再次重复较低级别时，最后一个值不再是最高级别，

所以我想这样做:

|               time         | user    | last_story_level|    mode |  
|----------------------------|-------|----------|--------   |
| 2019-07-30 10:47:20.000000 | A    |  3       |  Second     | 
| 2019-08-01 08:06:27.000000 | A    |  5       |  Third     | 
| 2019-07-31 11:17:24.000000 | B    |  5       |  Third     | 
| 2019-08-01 02:16:13.000000 | B    |  5       |  Second    |
| 2019-08-02 08:15:53.000000 | C    |  3       |  Not open any category    | 
| 2019-08-02 08:17:24.000000 | D    |  1       | Not open any category    |  

Answer 1

如果我理解正确的话，你想要之前的最高值 level为每个用户第一次进入“下一个”模式时的“第一个”。

您可以使用累积最大值来获得“第一”的前一个级别，然后是 distinct on每个用户/模式只获取一行:

select distinct on (user, mode) t.*
from (select t.*,
             max(case when mode = 'First' then level end) over 
                 (partition by user
                  order by time
                  rows between unbounded preceding and current row
                 ) as prev_first_level
      from my_table t
     ) t
order by user, mode, time;

编辑:

在 Redshift 中，您可以执行以下操作:

select t.*
from (select t.*,
             max(case when mode = 'First' then level end) over 
                 (partition by user
                  order by time
                  rows between unbounded preceding and current row
                 ) as prev_first_level,
           row_number() over (partition by user, mode order by time) as seqnum
      from my_table t
     ) t
where seqnum = 1;