将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. 我想在previouid的基础上生成行号
2. 我想检查每个输入事件是否都有相关的输出事件

如有任何帮助，我们将不胜感激。

Answer 1

您的流程可以使用"sql会话"技术在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/