如何高效实现按周分组并统计用户总流量及最频繁位置？

原数据表 #

eventdate   userid  traffic location    
18.09.2023  user_1  10      A
18.09.2023  user_1  20      A
18.09.2023  user_2  10      B
18.09.2023  user_2  20      B
18.09.2023  user_2  30      B
18.09.2023  user_3  100     A
19.09.2023  user_1  50      B
19.09.2023  user_2  10      B
19.09.2023  user_2  20      B
19.09.2023  user_3  150     C
19.09.2023  user_3  250     C
20.09.2023  user_1  50      A
20.09.2023  user_1  20      A
20.09.2023  user_2  30      B
20.09.2023  user_3  110     C
20.09.2023  user_3  120     C

需求说明 #

查询结果需包含：

• 每周起始日期eventdate
• 每周唯一用户ID userid
• 总流量traffic
• 该周用户出现最频繁的位置location

示例结果：

eventdate   userid  traffic location
18.09.2023  user_1  150    A
18.09.2023  user_2  120    B
18.09.2023  user_3  730    C

现有实现SQL #

SELECT t1.eventdate, t1.userid, t1.traffic, t2.location
  FROM (SELECT TO_CHAR(TRUNC(TO_DATE('2023-09-18', 'yyyy-mm-dd'), 'IW'),
                       'yyyy-mm-dd') AS eventdate,
               tk.userid,
               SUM(tk.traffic) AS traffic
          FROM test_kt tk
         GROUP BY tk.userid) t1
  JOIN (
         WITH cte AS 
        (
         SELECT tk2.userid,
                tk2.location,
                ROW_NUMBER() OVER 
                (PARTITION BY tk2.userid ORDER BY COUNT(tk2.location) DESC) rn
           FROM test_kt tk2
          GROUP BY tk2.userid, tk2.location
        )
        SELECT userid, location 
          FROM cte 
         WHERE rn = 1
       ) t2 
    ON t1.userid = t2.userid;

优化方案 #

现有SQL存在两个核心问题：一是硬编码固定周起始日期，未按实际数据的周维度分组统计；二是对表进行了两次全表扫描，IO开销较高。以下是更高效的实现方式：

优化后的SQL（方式一：单次扫描+窗口函数） #

WITH user_weekly_stats AS (
    SELECT
        -- 转换日期格式并计算每周起始日期
        TO_CHAR(TRUNC(TO_DATE(eventdate, 'dd.mm.yyyy'), 'IW'), 'dd.mm.yyyy') AS week_start_date,
        userid,
        location,
        -- 计算用户每周总流量
        SUM(traffic) OVER (PARTITION BY userid, TRUNC(TO_DATE(eventdate, 'dd.mm.yyyy'), 'IW')) AS total_traffic,
        -- 统计每个位置在用户每周数据中的出现次数
        COUNT(*) OVER (PARTITION BY userid, TRUNC(TO_DATE(eventdate, 'dd.mm.yyyy'), 'IW'), location) AS loc_count,
        -- 按用户每周分组，按位置出现次数降序排序，标记最高频位置
        ROW_NUMBER() OVER (PARTITION BY userid, TRUNC(TO_DATE(eventdate, 'dd.mm.yyyy'), 'IW') ORDER BY COUNT(*) OVER (PARTITION BY userid, TRUNC(TO_DATE(eventdate, 'dd.mm.yyyy'), 'IW'), location) DESC) AS rn
    FROM test_kt
)
SELECT DISTINCT
    week_start_date AS eventdate,
    userid,
    total_traffic AS traffic,
    location
FROM user_weekly_stats
WHERE rn = 1
ORDER BY eventdate, userid;

优化后的SQL（方式二：分组后筛选） #

WITH user_weekly_loc AS (
    SELECT
        TO_CHAR(TRUNC(TO_DATE(eventdate, 'dd.mm.yyyy'), 'IW'), 'dd.mm.yyyy') AS week_start_date,
        userid,
        location,
        SUM(traffic) AS total_traffic,
        COUNT(*) AS loc_occurrences,
        -- 按用户每周分组，筛选出现次数最多的位置
        ROW_NUMBER() OVER (PARTITION BY userid, week_start_date ORDER BY loc_occurrences DESC) AS rn
    FROM test_kt
    GROUP BY userid, week_start_date, location
)
SELECT
    week_start_date AS eventdate,
    userid,
    total_traffic AS traffic,
    location
FROM user_weekly_loc
WHERE rn = 1
ORDER BY eventdate, userid;

优化亮点 #

• 减少IO开销：仅对表进行一次全表扫描，避免原SQL两次扫描的重复开销，数据量越大效率提升越明显。
• 符合需求逻辑：正确按周维度分组统计，不再依赖硬编码的固定日期。
• 逻辑紧凑：在同一计算流程中完成总流量统计与高频位置筛选，执行计划更高效。

额外优化建议 #

如果表数据量较大，建议创建复合索引加速分组和窗口函数计算：

CREATE INDEX idx_test_kt_user_date_loc ON test_kt(userid, eventdate, location);

内容的提问来源于stack exchange，提问作者Koke Abeke