獲取SQL中每行d天內過去x個事件的平均值的有效方法（大資料）

我想找到最好和最有效的方法來計算 7 天內過去 2 個事件的分數的平均值，我需要每一行。我已經有一個可以處理 60M 行的查詢，但是在 100%（~500M 行）的資料上它會崩潰（可能效率不高或可能缺乏資源）。你能幫我嗎？如果您認為我的解決方案不是最好的方法，請解釋。謝謝

我有這張表：

user_id  event_id     start        end       score    
---------------------------------------------------
   1       7       30/01/2021   30/01/2021     45       
   1       6       24/01/2021   29/01/2021     25 
   1       5       22/01/2021   23/01/2021     13    
   1       4       18/01/2021   21/01/2021     15
   1       3       17/01/2021   17/01/2021     52 
   1       2       08/01/2021   10/01/2021     8    
   1       1       01/01/2021   02/01/2021     36

我想要每行（用戶 ID 事件 ID）：獲得過去 7 天內過去 2 個事件的平均分數。

示例：對于這一行：

user_id  event_id     start        end       score    
---------------------------------------------------
   1       6       24/01/2021   29/01/2021     25 


user_id  event_id     start        end       score past_7_days_from_start   event_num  
--------------------------------------------------------------------------------------
   1       6       24/01/2021   29/01/2021     25             null              null
   1       5       22/01/2021   23/01/2021     13              yes               1  
   1       4       18/01/2021   21/01/2021     15              yes               2  
   1       3       17/01/2021   17/01/2021     52              yes               3     
   1       2       08/01/2021   10/01/2021     8               no                4      
   1       1       01/01/2021   02/01/2021     36              no                5   

所以我會只為 group by 選擇這一行，然后 avg(score)：

user_id  event_id     start        end       score past_7_days_from_start   event_num  
--------------------------------------------------------------------------------------
   1       5       22/01/2021   23/01/2021     13              yes               1  
   1       4       18/01/2021   21/01/2021     15              yes               2  

結果：

user_id  event_id   start      end     score avg_score_of_past_2_events_within_7_days   
--------------------------------------------------------------------------------------
   1       6    24/01/2021 29/01/2021   25                  14

我的查詢：

SELECT user_id, event_id, AVG(score) as avg_score_of_past_2_events_within_7_days
FROM (
    SELECT 
        B.user_id, B.event_id, A.score,
        ROW_NUMBER() OVER (PARTITION BY B.user_id, B.event_id ORDER BY A.end desc) AS event_num,
    FROM
        "df" A
    INNER JOIN
        (SELECT user_id, event_id, start FROM "df") B 
            ON B.user_id =  FTP.user_id
            AND (A.end BETWEEN DATE_SUB(B.start, INTERVAL 7 DAY) AND B.start))
WHERE event_num >= 2
GROUP BY user_id, event_id

對更好的方法有什么建議嗎？

uj5u.com熱心網友回復：

我不相信你的情況，有一個更有效的查詢。

我可以建議您執行以下操作：

1. 確保您的基表按 start磁區，按 user_id集群

2. 將查詢拆分為創建磁區表和聚簇表的 3 個部分：

• 第一個表：只有內連接 O(n^2)
• 第二個表：添加 ROW_NUMBER O(n)
• 第三個表：分組依據

3. 如果它仍然是一個問題，我建議進行批量預處理并按日期運行查詢。

標籤：sql 表现 加入 谷歌大查询 大数据

上一篇：帶有LEFTJOIN和MAX的LibreOfficeHSQLDBWHERE子句？

下一篇：將最后一個元素與javscriptregex匹配而不以貪婪開頭