Optimization

如何根據行類型獲取每種類型的最新行並執行計算？

  • January 17, 2017

我在 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 和事件正在操作的欄位）（數據集被簡化，但想像還有更多列，並且稍後將添加更多活動）

    • 例如，rate_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

1：我在 rextester 中使用 SQL-SERVER 來研究您的數據，但我認為它可以應用於 google-bigquery。

  1. 我從未使用過 google-bigquery。

3：英語不是我的第一語言。

  1. 我可以吃阿司匹林嗎？

首先，我認為您的結果表中有問題。使用您的子查詢獲取銷售率：

在這裡查看：http ://rextester.com/CHX54701

select e.*
from events e
   inner join (
               select message_id, max(event_timestamp) as event_timestamp 
               from events 
               where event_type in ('rate_update', 'create')  
               group by message_id        
              ) t 
              on t.message_id = e.message_id and t.event_timestamp = e.event_timestamp;

+----+-------------+---------------------+------------+---------------------+--------+------+
| id |  event_type |   event_timestamp   | message_id |       sent_at       | status | rate |
+----+-------------+---------------------+------------+---------------------+--------+------+
|  1 |    create   | 25.11.2016 09:17:48 |      1     | 25.11.2016 09:17:48 |    0   |  0,5 |
+----+-------------+---------------------+------------+---------------------+--------+------+
|  9 | rate_update | 27.11.2016 09:55:07 |      2     | 25.11.2016 09:55:07 |    0   |   2  |
+----+-------------+---------------------+------------+---------------------+--------+------+
|  6 |    create   | 26.11.2016 09:17:48 |      3     | 26.11.2016 09:17:48 |    0   |  0,5 |
+----+-------------+---------------------+------------+---------------------+--------+------+
|  8 | rate_update | 27.11.2016 09:55:07 |      4     | 27.11.2016 09:50:07 |    0   |   2  |
+----+-------------+---------------------+------------+---------------------+--------+------+

2016-11-25 的 sum(rate) 應為 3.0 而不是 2.5

這是正確的嗎？因為如果不是全部到此為止。

在我的拙見中，您在每個子查詢中對所有記錄進行分組，然後根據message_id獲取不同值的 sum() 來獲取。max(event_timestamp)``event_type

然後我的第一次嘗試是讓所有 max(event_timestamp) 按 message_id 分組：

select message_id, event_type, max(event_timestamp) event_timestamp
from events
group by message_id, event_type;

+------------+---------------+---------------------+
| message_id |   event_type  |   event_timestamp   |
+------------+---------------+---------------------+
|      1     |     create    | 25.11.2016 09:17:48 |
|      2     |     create    | 25.11.2016 09:47:48 |
|      3     |     create    | 26.11.2016 09:17:48 |
|      4     |     create    | 27.11.2016 09:17:48 |
|      2     |  rate_update  | 27.11.2016 09:55:07 |
|      4     |  rate_update  | 27.11.2016 09:55:07 |
|      1     | status_update | 25.11.2016 09:24:38 |
|      2     | status_update | 25.11.2016 09:54:38 |
+------------+---------------+---------------------+

然後，不要使用 3 個查詢 + 子查詢的 3 個 UNION，我認為可以通過這樣的單個 JOIN 來完成：

with ct as
(
 select message_id, event_type, max(event_timestamp) event_timestamp, convert(varchar(20),max(sent_at),112) st
 from events
 group by message_id, event_type
)
select 
      max(e.sent_at) sent_at, 
      sum(case when e.event_type='create' and status=0 then 1 else 0 end) as submitted_msg,
      sum(case when e.event_type='status_update' and status=1 then 1 else 0 end) as delivered_msg,
      sum(case when (e.event_type='create' or e.event_type='rate_update') and status=0 then rate else 0 end) as sum_rate  
from events e
    inner join ct on ct.message_id = e.message_id and ct.event_timestamp = e.event_timestamp
group by st
order by sent_at

我已經重新搜尋了有關 googe-bigquery 的資訊，它允許使用 CTE 句子，但您可以將其重寫為 JOIN (SUBQUERY….

如您所見，我使用了 3 種不同的 CASE 來求和所需的值。

最終結果是：

+---------------------+---------------+---------------+----------+
|       sent_at       | submitted_msg | delivered_msg | sum_rate |
+---------------------+---------------+---------------+----------+
| 25.11.2016 09:55:07 |       2       |       2       |     3    |
+---------------------+---------------+---------------+----------+
| 26.11.2016 09:17:48 |       1       |       0       |    0,5   |
+---------------------+---------------+---------------+----------+
| 27.11.2016 09:50:07 |       1       |       0       |    2,5   |
+---------------------+---------------+---------------+----------+

在此處查看最終結果：http ://rextester.com/FDIWA74637

引用自：https://dba.stackexchange.com/questions/161237

相關問答

Sql-Server

Select * Order by vs Select column 按性能排序

  • September 3, 2022
檢視問答
Sql-Server

測試時使用記憶體數據執行儲存過程不佳

  • August 20, 2022
檢視問答
Query-Performance

優化 500 萬條記錄的 SQL 查詢（使用與否？！）

  • August 15, 2022
檢視問答
Postgresql

優化查詢以在分區表中跨多天獲取數據

  • August 4, 2022
檢視問答
Oracle

查詢優化OracleDBM小號○r一個Cl和D乙米小號Oracle DBMS

  • July 17, 2022
檢視問答
Postgresql

FROM 子句中的相關函式是否針對每一行執行？

  • July 10, 2022
檢視問答