sql - 在 Redshift 中对备用记录进行分组

Question

我正在尝试为 Redshift 中的备用记录生成序列号

输入表

| id   | previousid | idtype | timestamp          |
|----- |------------|--------|--------------------|
| aaaa |            | INPUT  | 2020-07-03 9:09:23 |
| bbbb | aaaa       | OUTPUT | 2020-07-03 9:09:23 |
| cccc | bbbb       | INPUT  | 2020-07-03 9:09:24 |
| dddd | cccc       | OUTPUT | 2020-07-03 9:09:24 |
| ffff | eeee       | OUTPUT | 2020-07-03 9:09:25 |
| eeee | dddd       | INPUT  | 2020-07-03 9:09:25 |
| hhhh | gggg       | OUTPUT | 2020-07-03 9:09:25 |
| gggg | ffff       | INPUT  | 2020-07-03 9:09:25 |

期望的输出

| id   | previousid    | idtype | timestamp          | GroupNo |
|------|---------------|--------|--------------------|---------|
| aaaa |               | INPUT  | 2020-07-03 9:09:23 | 0       |
| bbbb | aaaa          | OUTPUT | 2020-07-03 9:09:23 | 0       |
| cccc | bbbb          | INPUT  | 2020-07-03 9:09:24 | 1       |
| dddd | cccc          | OUTPUT | 2O2O-07-03 9:09:24 | 1       |
| eeee | dddd          | INPUT  | 2020-07-03 9:09:25 | 2       |
| ffff | eeee          | OUTPUT | 2020-07-03 9:09:25 | 2       |
| gggg | ffff          | INPUT  | 2020-07-03 9:09:25 | 3       |
| hhhh | gggg          | OUTPUT | 2020-07-03 9:09:25 | 3       |

我现在在做什么

我目前正在 row_number() over (partition by eventtype order by timestamp desc) as GroupNo 生成 GroupNo 属性。

当前解决方案的问题

因为，时间戳字段不是唯一的，我不应该在这个字段上排序。

我想做的事

1. 我想根据previousid生成行号。
2. 我想检查每个输入事件是否都有相关的输出事件。

任何帮助表示赞赏。

标签： sqlamazon-redshift

Answer 1

您可以使用“sql sessionization”技术在 Redshift 中完成您的过程。

本质上，您使用许多 LAG() 语句来比较特定窗口上的数据，然后比较结果以完成最终分类。

• https://sonra.io/2017/08/14/redshifts-window-functions-advanced-use-case-sessionization/
• https://www.dataiku.com/learn/guide/code/reshaping_data/sessionization.html
• https://blog.modeanalytics.com/finding-user-sessions-sql/