如何在BigQuery中生成含0计数的全维度组合查询结果？

现有查询中并非每个国家的每个月份都有对应数据，原因是部分国家业务量较低，无可统计的ID。但需要年份、月份、国家、计划类型、交易类型的所有组合都显示，不存在的组合计数显示为0。请问能否在BigQuery中实现该需求？

原查询SQL：

select
extract(year from closedate) as year,
extract (month from closedate) as month,

case billing_country__c
when 'USA' THEN 'United States' ELSE billing_country__c
END AS country,

case
when account_type_sold__c = 'Starter' then 'Starter'
when account_type_sold__c = 'Business' then 'Business'
when account_type_sold__c in('Enterprise','Legacy - Enterprise') then 'Enterprise'
else 'Other'
end as plan_type,

case
when type = 'web_trial' then 'Web Trial'
when type = 'buy_now' then 'Buy Now'
else 'other'
end as transaction_type,

count(distinct(id)) as opps_all

from `company.sales_table`
group by year, month, billing_country__c, plan_type, transaction_type

可以实现，核心思路是先生成所有维度的全量组合，再左连接原统计数据并将空值补0，具体实现如下：

实现步骤与SQL代码 #

WITH 
-- 1. 生成日期维度（若需固定年月范围，可替换为GENERATE_DATE_ARRAY生成序列）
date_dim AS (
    SELECT DISTINCT
        EXTRACT(YEAR FROM closedate) AS year,
        EXTRACT(MONTH FROM closedate) AS month
    FROM `company.sales_table`
),
-- 2. 生成国家维度（包含原表转换后的值）
country_dim AS (
    SELECT DISTINCT
        CASE billing_country__c
            WHEN 'USA' THEN 'United States' 
            ELSE billing_country__c
        END AS country
    FROM `company.sales_table`
),
-- 3. 生成计划类型维度
plan_dim AS (
    SELECT DISTINCT
        CASE
            WHEN account_type_sold__c = 'Starter' THEN 'Starter'
            WHEN account_type_sold__c = 'Business' THEN 'Business'
            WHEN account_type_sold__c IN('Enterprise','Legacy - Enterprise') THEN 'Enterprise'
            ELSE 'Other'
        END AS plan_type
    FROM `company.sales_table`
),
-- 4. 生成交易类型维度
transaction_dim AS (
    SELECT DISTINCT
        CASE
            WHEN type = 'web_trial' THEN 'Web Trial'
            WHEN type = 'buy_now' THEN 'Buy Now'
            ELSE 'other'
        END AS transaction_type
    FROM `company.sales_table`
),
-- 5. 生成所有维度的全组合
all_combinations AS (
    SELECT
        d.year,
        d.month,
        c.country,
        p.plan_type,
        t.transaction_type
    FROM date_dim d
    CROSS JOIN country_dim c
    CROSS JOIN plan_dim p
    CROSS JOIN transaction_dim t
),
-- 6. 计算原数据的统计值
original_stats AS (
    SELECT
        EXTRACT(YEAR FROM closedate) AS year,
        EXTRACT(MONTH FROM closedate) AS month,
        CASE billing_country__c
            WHEN 'USA' THEN 'United States' 
            ELSE billing_country__c
        END AS country,
        CASE
            WHEN account_type_sold__c = 'Starter' THEN 'Starter'
            WHEN account_type_sold__c = 'Business' THEN 'Business'
            WHEN account_type_sold__c IN('Enterprise','Legacy - Enterprise') THEN 'Enterprise'
            ELSE 'Other'
        END AS plan_type,
        CASE
            WHEN type = 'web_trial' THEN 'Web Trial'
            WHEN type = 'buy_now' THEN 'Buy Now'
            ELSE 'other'
        END AS transaction_type,
        COUNT(DISTINCT id) AS opps_all
    FROM `company.sales_table`
    GROUP BY year, month, country, plan_type, transaction_type
)
-- 7. 左连接全组合与统计数据，空值补0
SELECT
    ac.year,
    ac.month,
    ac.country,
    ac.plan_type,
    ac.transaction_type,
    COALESCE(os.opps_all, 0) AS opps_all
FROM all_combinations ac
LEFT JOIN original_stats os
    ON ac.year = os.year
    AND ac.month = os.month
    AND ac.country = os.country
    AND ac.plan_type = os.plan_type
    AND ac.transaction_type = os.transaction_type
ORDER BY ac.year, ac.month, ac.country, ac.plan_type, ac.transaction_type

关键补充说明 #

• 若需要包含无任何业务数据的年月（比如业务启动前的月份），可修改date_dim为固定序列生成：

  date_dim AS (
      SELECT
          EXTRACT(YEAR FROM date_val) AS year,
          EXTRACT(MONTH FROM date_val) AS month
      FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', CURRENT_DATE(), INTERVAL 1 MONTH)) AS date_val
  )
  

• 各维度也可手动指定固定值集合，无需从原表提取，比如国家维度直接用SELECT 'United States' AS country UNION ALL SELECT 'Canada' UNION ALL ...定义。
• COALESCE函数负责将左连接后缺失的统计值替换为0，确保所有维度组合都有有效计数。

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