window-functions - 在 Presto 中进行基于范围的交叉连接的最佳方法是什么？
问题描述
我event_log在 Athena 中有一个表，其中包含从事件处理系统收集的日志。系统中有多个阶段，每个阶段按顺序处理这些事件。start_time列表示事件进入系统end_time的时间，是它退出的时间。该系统每天处理数百万个事件。而且，我们在下表中有一年的数据。
| event_id | 事件类型 | 开始时间 | 时间结束 |
|---|---|---|---|
| E1 | A型 | T1 | T4 |
| E2 | B型 | T2 | T6 |
| M1 | M型 | T2 | T6 |
| E3 | A型 | T3 | T7 |
| E4 | B型 | T4 | T7 |
| E5 | A型 | T5 | T8 |
| M2 | M型 | T5 | T8 |
| E6 | B型 | T6 | T9 |
| E7 | A型 | T7 | T10 |
| E8 | B型 | T8 | T11 |
| M3 | M型 | T8 | T11 |
有特殊类型的事件TypeM（标记事件）。我必须从这些日志中计算出这些特殊事件的处理延迟。从上表中，这可以通过过滤该类型的事件并将延迟计算为 来实现end_time - start_time。除此之外，我想通过附加信息来增加延迟 - 处理此事件时在系统的各个阶段正在积极处理的事件数量。
-- sample event_log table
CREATE TABLE event_log AS
SELECT * FROM (
VALUES
('E1','TypeA', 1, 4),
('E2','TypeB', 2, 6),
('M1','TypeM', 2, 6),
('E3','TypeA', 3, 7),
('E4','TypeB', 4, 7),
('E5','TypeA', 5, 8),
('M2','TypeM', 5, 8),
('E6','TypeB', 6, 9),
('E7','TypeA', 7, 10),
('E8','TypeB', 8, 11),
('M3','TypeM', 8, 11)
) AS t (event_id, event_type, start_time, end_time)
-- filtered marker table
CREATE TABLE marker_table AS
SELECT * FROM event_log
WHERE event_type = 'TypeM'
-- Join with the filtered marker table on markers start and end time
SELECT mark.*,count(processed_events_in_band.event_id) AS events_processed_count
FROM event_log processed_events_in_band
JOIN marker_table mark
ON processed_events_in_band.end_time between mark.start_time AND mark.end_time
WHERE processed_events_in_band.event_type != 'TypeM'
GROUP BY mark.event_id
预期结果
| event_id | 事件类型 | 开始时间 | 时间结束 | events_processed_count |
|---|---|---|---|---|
| M1 | M型 | T2 | T6 | 2 E1, E2 |
| M2 | M型 | T5 | T8 | 4 E2、E3、E4、E5 |
| M3 | M型 | T8 | T11 | 4 E5、E6、E7、E8 |
end_time（每天）有分区。一直在使用它们来减少数据扫描。单日数据最长可达10m。查询应该扩展到那个。查询具有 18K 行的标记表和具有 10m 行的事件日志大约需要 17 分钟。大约有 2K parquet 文件要扫描这 10m 行。不要认为这里有 S3 读取延迟会导致问题。
如何优化此查询？有效获取这些数据的最佳方法是什么？
解决方案
要提高性能：
- 请注意，这
CREATE TABLE会将查询的输出写入磁盘(doc)。考虑改用公用表表达式：
with marker_table as (SELECT * FROM event_log
WHERE event_type = 'TypeM')
select ...