主页 / user-34558

vulkoingim's questions

Martin Hope
vulkoingim
Asked: 2017-01-17 07:51:47 +0800 CST
  • 0

我在 SO 上发布了同样的问题，但我想我也可以在这里尝试一下，因为非常感谢任何其他类型的优化和建议 :) 不管怎样，这是我的帖子，逐字逐句：

我需要一些帮助编写/优化查询以按类型检索每行的最新版本并根据类型执行一些计算。我认为最好用一个例子来说明。

给定以下数据集：

+-------+-------------------+---------------------+-------------+---------------------+--------+----------+
| id    | event_type        | event_timestamp     | message_id  | sent_at             | status | rate     |
+-------+-------------------+---------------------+-------------+---------------------+--------+----------+
| 1     | create            | 2016-11-25 09:17:48 | 1           | 2016-11-25 09:17:48 | 0      | 0.500000 |
| 2     | status_update     | 2016-11-25 09:24:38 | 1           | 2016-11-25 09:28:49 | 1      | 0.500000 |
| 3     | create            | 2016-11-25 09:47:48 | 2           | 2016-11-25 09:47:48 | 0      | 0.500000 |
| 4     | status_update     | 2016-11-25 09:54:38 | 2           | 2016-11-25 09:48:49 | 1      | 0.500000 |
| 5     | rate_update       | 2016-11-25 09:55:07 | 2           | 2016-11-25 09:50:07 | 0      | 1.000000 |
| 6     | create            | 2016-11-26 09:17:48 | 3           | 2016-11-26 09:17:48 | 0      | 0.500000 |
| 7     | create            | 2016-11-27 09:17:48 | 4           | 2016-11-27 09:17:48 | 0      | 0.500000 |
| 8     | rate_update       | 2016-11-27 09:55:07 | 4           | 2016-11-27 09:50:07 | 0      | 2.000000 |
| 9     | rate_update       | 2016-11-27 09:55:07 | 2           | 2016-11-25 09:55:07 | 0      | 2.000000 |
+-------+-------------------+---------------------+-------------+---------------------+--------+----------+

预期结果应该是：

+------------+--------------------+--------------------+-----------------------+
| sent_at    | sum(submitted_msg) | sum(delivered_msg) | sum(rate_total)       |
+------------+--------------------+--------------------+-----------------------+
| 2016-11-25 |                  2 |                  2 |              2.500000 |
| 2016-11-26 |                  1 |                  0 |              0.500000 |
| 2016-11-27 |                  1 |                  0 |              2.000000 |
+------------+--------------------+--------------------+-----------------------+

帖子末尾是用于获取此结果的查询。我愿意打赌应该有一种方法来优化它，因为它使用带有连接的子查询，而且从我读到的关于 BigQuery 的内容来看，最好避免连接。但首先是一些背景：

本质上，数据集表示一个仅附加表，其中写入了多个事件。数据的规模在数亿级，并将增长到数十亿+。由于 BigQuery 中的更新不实用，并且数据正在流式传输到 BQ，我需要一种方法来检索每个事件的最新事件，根据特定条件执行一些计算并返回准确的结果。查询是根据用户输入动态生成的，因此可以包含更多字段/计算，但为简单起见已将其省略。

  • 只有一个create事件，但n属于任何其他类型
  • 对于每组事件，在计算时只应考虑最新的事件。
    • status_update - 更新状态
    • rate_update - 更新速率
    • 创建 - 不言自明
  • 每个不是的事件create可能不携带原始的其余信息/可能不准确（除了message_id和事件正在操作的字段）（数据集被简化了，但想象有更多的列，和稍后会添加更多事件）
    • 例如，arate_update可能会或可能不会设置状态字段，或者不是最终值，因此无法对rate_update事件的状态字段进行计算，同样适用于status_update
  • 可以假定该表按日期分区，并且每个查询都将使用分区。现在为了简单起见，省略了这些条件。

所以我想我有几个问题：

  • 如何优化此查询？
  • 将事件放在它们自己的表中而不是create放在它们自己的表中是不是一个更好的主意，其中唯一可用的字段将是与事件相关的字段以及连接所需的字段（message_id，event_timestamp）？这会减少处理的数据量吗？
  • 将来添加更多事件的最佳方式是什么，这些事件将有自己的条件和计算？

实际上，我们非常欢迎任何有关如何高效友好地查询此数据集的建议！谢谢！:)

我想出的怪物如下。根据此资源INNER JOINS，用于检索每一行的最新版本

    select
    sent_at as sent_at,
    sum(submitted_msg) as submitted,
    sum(delivered_msg) as delivered,
    sum(sales_rate_total) as sales_rate_total
    FROM (

      #DELIVERED
        SELECT 
            d.message_id,
            FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
            0 as submitted_msg,
            sum(if(status=1,1,0)) as delivered_msg,
            0 as sales_rate_total
        FROM `events` d
        INNER JOIN
                (
                    select message_id, max(event_timestamp) as ts 
                    from `events` 
                    where event_type = "status_update" 
                    group by 1
                    ) g on d.message_id = g.message_id and d.event_timestamp = g.ts
        GROUP BY 1,2

        UNION ALL

      #SALES RATE
        SELECT 
            s.message_id,
            FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
            0 as submitted_msg,
            0 as delivered_msg,
            sum(sales_rate) as sales_rate_total
        FROM `events` s
        INNER JOIN 
                    (
                    select message_id, max(event_timestamp) as ts 
                    from `events` 
                    where event_type in ("rate_update", "create")  
                    group by 1
                    ) f on s.message_id = f.message_id and s.event_timestamp = f.ts
        GROUP BY 1,2

        UNION ALL

      #SUBMITTED & REST
        SELECT 
            r.message_id,
            FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
            sum(if(status=0,1,0)) as submitted_msg,
            0 as delivered_msg,
            0 as sales_rate_total
        FROM `events` r
        INNER JOIN
                (
                    select message_id, max(event_timestamp) as ts 
                    from `events` 
                    where event_type = "create" 
                    group by 1
                    ) e on r.message_id = e.message_id and r.event_timestamp = e.ts
        GROUP BY 1, 2

    ) k
    group by 1
optimization google-bigquery