sql - 在 SQL BigQuery 中展平事件表

Question

我有一个事件表，其中包含每个营销事件和个人的 3 种事件类型。这 3 个事件是“收到的电子邮件”、“打开的电子邮件”和“点击的电子邮件”。我希望能够获取个人/事件的每个事件的时间戳作为表中的新列。最好的方法是什么？

示例表数据:

campaign_id     person_id     event_type     timestamp

1               1             Received Email 2018-01-01
1               1             Opened Email   2018-02-01
1               1             Clicked Email  2018-03-01
1               2             Received Email 2018-01-01
1               2             Opened Email   2018-02-01
1               2             Opened Email   2018-02-02

示例输出:

    campaign_id     person_id     event_type     timestamp     receive_ts     open_ts     click_ts

    1               1             Received Email 2018-01-01    2018-01-01     2018-02-01  2018-03-01
    1               1             Opened Email   2018-02-01    2018-01-01     2018-02-01  2018-03-01
    1               1             Clicked Email  2018-03-01    2018-01-01     2018-02-01  2018-03-01
    1               2             Received Email 2018-01-01    2018-01-01     2018-02-01
    1               2             Opened Email   2018-02-01    2018-01-01     2018-02-01
    1               2             Opened Email   2018-02-02    2018-01-01     2018-02-01

我想到的唯一解决方案是在 marketing_id 和 person_id 上将表与自身连接 3 次，每个事件类型一次，但表包含超过 4 亿行，因此这显然效率不高。

如有任何建议，我们将不胜感激!

Answer 1

您可以在此处尝试使用数据透视查询。例如，如果您想要每个人/事件从收到电子邮件到打开该电子邮件的分钟数差异，您可以尝试以下操作:

SELECT
    campaign_id,
    person_id,
    TIMESTAMP_DIFF(
        MAX(CASE WHEN event_type = 'Opened Email' THEN timestamp END),
        MAX(CASE WHEN event_type = 'Received Email' THEN timestamp END),
        MINUTE) AS diff_in_minutes
FROM yourTable
GROUP BY
    campaign_id,
    person_id;

注意:此答案是针对原始问题的，后来进行了大幅更改。