sql - 表本身的完全外部连接并运行一些窗口函数

Question

背景

我有一些 ETL 作业每小时处理一次实时日志文件。每当系统产生一个新事件时，它都会对所有历史事件摘要(如果存在)进行快照，并将其与当前事件一起记录下来。然后将数据加载到 Redshift 中。

示例

表格如下所示:

+------------+--------------+---------+-----------+-------+-------+
| current_id | current_time | past_id | past_time | freq1 | freq2 |
+------------+--------------+---------+-----------+-------+-------+
|          2 |        time2 |       1 |     time1 |    13 |     5 |
|          3 |        time3 |       1 |     time1 |    13 |     5 |
|          3 |        time3 |       2 |     time2 |     2 |     1 |
|          4 |        time4 |       1 |     time1 |    13 |     5 |
|          4 |        time4 |       2 |     time2 |     2 |     1 |
|          4 |        time4 |       3 |     time3 |     1 |     1 |
+------------+--------------+---------+-----------+-------+-------+

上表是这样的:

1. 时间 1:事件 1 发生了。系统拍了一张快照，但没有任何记录。
2. 时间 2:事件 2 发生了。系统拍摄快照并记录事件 1。
3. 时间 3:事件 3 发生了。系统拍摄快照并记录事件 1 和 2。
4. 时间 4:事件 4 发生了。系统拍摄快照并记录事件 1、2 和 3。

期望的结果

我需要将数据转换为以下格式以便进行一些分析:

+----+------------+-------+-------+
| id | event_time | freq1 | freq2 |
+----+------------+-------+-------+
|  1 |      time1 |     0 |     0 |
|  2 |      time2 |    13 |     5 |  --     13 |     5
|  3 |      time3 |    15 |     6 |  -- 13 + 2 | 5 + 1
|  4 |      time4 |    16 |     7 |  -- 15 + 1 | 6 + 1
+----+------------+-------+-------+

基本上，新的 freq1 和 freq2 是滞后的 freq1 和 freq2 的累加和。

我的想法

我正在考虑对 current_id 和 past_id 进行 self 完全外部联接，并首先实现以下结果:

+----+------------+-------+-------+
| id | event_time | freq1 | freq2 |
+----+------------+-------+-------+
|  1 |      time1 |    13 |     5 |
|  2 |      time2 |     2 |     1 |
|  3 |      time3 |     1 |     1 |
|  4 |      time4 |  null |  null |
+----+------------+-------+-------+

然后我可以执行 lag over() 和 sum over() 的窗口函数。

问题

1. 这是正确的方法吗？有没有更有效的方法来做到这一点？这只是实际数据的一小部分样本，因此性能可能是一个问题。
2. 我的查询总是返回很多重复的值，所以我不确定哪里出了问题。

---

解决方案

@GordonLinoff 的回答对于上述用例是正确的。我正在添加一些小的更新，以便让它在我的实际表上工作。唯一的区别是我的 event_id 是一些 36 个字符的 Java UUID 而 event_time 是时间戳。

select distinct past_id, past_time, 0 as freq1, 0 as freq2
from (
    select past_id, past_time,
           row_number() over (partition by current_id order by current_time desc) as seqnum
    from t
) a
where a.seqnum = 1
union all
select current_id, current_time,
       sum(freq1) over (order by current_time rows unbounded preceding) as freq1,
       sum(freq2) over (order by current_time rows unbounded preceding) as freq2
from (
    select current_id, current_time, freq1, freq2,
           row_number() over (partition by current_id order by past_id desc) as seqnum
    from t
) b
where b.seqnum = 1;

Answer 1

我认为您需要 union all 以及窗口函数。这是一个例子:

select min(past_id) as id, min(past_time) as event_time, 0 as freq1, 0 as freq2
from t
union all
(select current_id, current_time,
        sum(freq1) over (order by current_time),
        sum(freq2) over (order by current_time)
 from (select current_id, current_time, freq1, freq2,
              row_number() over (partition by current_id order by past_id desc) as seqnum
       from t
      ) t
  where seqnum = 1
);