BigQuery中SQL子查询Join后SUM结果异常问题排查

API 探路者

2026-8-6

问题排查：BigQuery周统计数据翻倍异常

我在BigQuery中有一张多数据行的表，需要生成报表展示每周下载页面访问人数、下载次数及转化率。编写SQL后发现2022年第52周统计结果异常：nb_download_per_week实际应为529，但查询返回1058；page_download_view实际应为3069，返回却为6138。单独查询单条数据结果正确，不清楚SUM计算出错原因，请求排查。

原SQL语句如下：

SELECT Q1.week_of_year, #We display the week
       Q1.year, #We display the year
       SUM(page_download_views) as page_download_views,
       SUM(nb_download) as nb_download_per_week,
       (SUM(nb_download) * 100/SUM(page_download_views)) as percentage_of_download #We calculate the percentage between page landing and download
       
FROM

          #We get the number of people clicking on the button download
          (SELECT 
              DISTINCT EXTRACT (WEEK from (PARSE_DATE('%Y%m%d', event_date))) as week_of_year,
              EXTRACT (YEAR from (PARSE_DATE('%Y%m%d', event_date))) as year,
              count(event_name) as nb_download
          FROM `XXXXX-e78a5.analytics_247657392.events_*` WHERE event_name = 'landing_event_download_apk'  GROUP BY event_date) AS Q1

          RIGHT JOIN

          #We get the number of people arriving on the page download
          (SELECT 
              DISTINCT EXTRACT (WEEK from (PARSE_DATE('%Y%m%d', event_date))) as week_of_year,
              count(event_name) as page_download_views
          FROM `XXXXX-e78a5.analytics_247657392.events_*` WHERE event_name = 'page_download_view'  GROUP BY event_date) AS Q2
    
    

ON Q1.week_of_year = Q2.week_of_year GROUP BY week_of_year, year order by year ASC;

问题原因分析

JOIN引发笛卡尔积，数据重复计算
Q1和Q2都是按event_date（日期）分组统计每日数据，再提取周数。当某一周包含多个日期时，Q1中该周会有N条记录（N为该周天数），Q2中该周也会有M条记录（M为该周天数）。RIGHT JOIN后，同一周内的每日记录会两两匹配，导致数据行数膨胀，最终SUM时把每日统计值重复累加了多次。
DISTINCT关键字完全多余
子查询已经按event_date分组，每个日期只会返回一条记录，DISTINCT不会改变结果，反而混淆逻辑。

修正后的SQL语句

正确思路是先在子查询中按周+年分组统计总量，再进行JOIN，从根源避免笛卡尔积：

SELECT 
    COALESCE(Q1.week_of_year, Q2.week_of_year) AS week_of_year,
    COALESCE(Q1.year, Q2.year) AS year,
    COALESCE(Q2.page_download_views, 0) AS page_download_views,
    COALESCE(Q1.nb_download_per_week, 0) AS nb_download_per_week,
    CASE 
        WHEN Q2.page_download_views = 0 THEN 0
        ELSE (Q1.nb_download_per_week * 100 / Q2.page_download_views) 
    END AS percentage_of_download
FROM
    (SELECT 
         EXTRACT(WEEK FROM PARSE_DATE('%Y%m%d', event_date)) AS week_of_year,
         EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d', event_date)) AS year,
         COUNT(event_name) AS nb_download_per_week
     FROM `XXXXX-e78a5.analytics_247657392.events_*` 
     WHERE event_name = 'landing_event_download_apk'
     GROUP BY week_of_year, year) AS Q1
FULL OUTER JOIN
    (SELECT 
         EXTRACT(WEEK FROM PARSE_DATE('%Y%m%d', event_date)) AS week_of_year,
         EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d', event_date)) AS year,
         COUNT(event_name) AS page_download_views
     FROM `XXXXX-e78a5.analytics_247657392.events_*` 
     WHERE event_name = 'page_download_view'
     GROUP BY week_of_year, year) AS Q2
ON Q1.week_of_year = Q2.week_of_year 
   AND Q1.year = Q2.year  -- 必须关联年份，避免跨年同周数混淆
ORDER BY year ASC, week_of_year ASC;

关键修正点说明

子查询直接按week_of_year+year分组，统计每周总数据，避免每日记录JOIN时的重复匹配。
使用FULL OUTER JOIN确保某一周只有下载/只有页面访问数据时也能被统计，用COALESCE处理NULL值。
增加年份作为JOIN条件，解决不同年份同周数（比如2022年第52周和2023年第52周）的关联错误。
移除多余的DISTINCT，简化逻辑。

内容的提问来源于stack exchange，提问作者Teddy Kossoko