BigQuery中计算上月销售额：缺失数据场景处理问询

原查询仅从已有销售记录的行出发计算上月销售额，意大利Company A在2023-01-31没有销售数据，导致(Europe, Italy, Company A, 2023-01-31)这个组合根本不会出现在分组结果中，它对应的2022-12-31销售额（155）自然也不会被计入2023-01-31的sales_previous_month汇总值里。

核心思路是先构建所有维度（大洲、国家、公司）与所有存在的月份日期的完整组合，确保每个维度在每个月份都有一行记录（哪怕当月销售额为0），再关联原数据计算销售额和上月数据。

方法1：关联子查询补全维度 #

WITH data AS (
  SELECT *
  FROM UNNEST ([
    STRUCT('Europe' AS continent, 'Germany' AS country, 'Company A' AS company, DATE('2023-01-31') AS date, 120 AS sales),
    ('Europe', 'Germany', 'Company A', '2022-12-31', 198),
    ('Europe', 'Germany', 'Company A', '2022-11-30', 167),
    ('Europe', 'Germany', 'Company A', '2022-10-31', 8),
    ('Europe', 'Italy', 'Company A', '2022-12-31', 155),
    ('Europe', 'Italy', 'Company A', '2022-11-30', 93),
    ('Europe', 'Italy', 'Company A', '2022-10-31', 95),
    ('Asia', 'China', 'Company A', '2023-01-31', 128),
    ('Asia', 'China', 'Company A', '2022-12-31', 177),
    ('Asia', 'China', 'Company A', '2022-11-30', 153),
    ('Asia', 'China', 'Company A', '2022-10-31', 3),
    ('America', 'USA', 'Company A', '2023-01-31', 15),
    ('America', 'USA', 'Company A', '2022-12-31', 59),
    ('America', 'USA', 'Company A', '2022-11-30', 116),
    ('America', 'USA', 'Company A', '2022-10-31', 122),
    ('Europe', 'Germany', 'Company B', '2023-01-31', 70),
    ('Europe', 'Germany', 'Company B', '2022-12-31', 94),
    ('Europe', 'Germany', 'Company B', '2022-11-30', 24),
    ('Europe', 'Germany', 'Company B', '2022-10-31', 143),
    ('Europe', 'Italy', 'Company B', '2023-01-31', 0),
    ('Europe', 'Italy', 'Company B', '2022-12-31', 11),
    ('Europe', 'Italy', 'Company B', '2022-11-30', 189),
    ('Europe', 'Italy', 'Company B', '2022-10-31', 131),
    ('Asia', 'China', 'Company B', '2023-01-31', 157),
    ('Asia', 'China', 'Company B', '2022-12-31', 34),
    ('Asia', 'China', 'Company B', '2022-11-30', 112),
    ('Asia', 'China', 'Company B', '2022-10-31', 97),
    ('America', 'USA', 'Company B', '2023-01-31', 146),
    ('America', 'USA', 'Company B', '2022-12-31', 41),
    ('America', 'USA', 'Company B', '2022-11-30', 83),
    ('America', 'USA', 'Company B', '2022-10-31', 127)
  ])),
-- 提取所有唯一的维度组合
dimensions AS (
  SELECT DISTINCT continent, country, company FROM data
),
-- 提取所有存在的月份日期
dates AS (
  SELECT DISTINCT date FROM data
),
-- 生成维度与日期的完整笛卡尔积
full_combinations AS (
  SELECT d.continent, d.country, d.company, dt.date
  FROM dimensions d
  CROSS JOIN dates dt
)
SELECT 
  fc.continent, 
  fc.country, 
  fc.company, 
  fc.date, 
  COALESCE(SUM(d.sales), 0) AS sales,
  -- 关联上月数据，COALESCE处理无数据情况
  (SELECT COALESCE(SUM(sales), 0)
   FROM data
   WHERE date = LAST_DAY(DATE_ADD(fc.date, INTERVAL -1 MONTH), MONTH)
     AND continent = fc.continent
     AND country = fc.country
     AND company = fc.company) AS sales_previous_month
FROM full_combinations fc
LEFT JOIN data d 
  ON fc.continent = d.continent 
  AND fc.country = d.country 
  AND fc.company = d.company 
  AND fc.date = d.date
GROUP BY fc.continent, fc.country, fc.company, fc.date
ORDER BY fc.continent, fc.country, fc.company, fc.date DESC;

方法2：窗口函数简化计算 #

补全维度组合后，用LAG()窗口函数可以更简洁地获取上月销售额：

WITH data AS (
  SELECT *
  FROM UNNEST ([
    STRUCT('Europe' AS continent, 'Germany' AS country, 'Company A' AS company, DATE('2023-01-31') AS date, 120 AS sales),
    ('Europe', 'Germany', 'Company A', '2022-12-31', 198),
    ('Europe', 'Germany', 'Company A', '2022-11-30', 167),
    ('Europe', 'Germany', 'Company A', '2022-10-31', 8),
    ('Europe', 'Italy', 'Company A', '2022-12-31', 155),
    ('Europe', 'Italy', 'Company A', '2022-11-30', 93),
    ('Europe', 'Italy', 'Company A', '2022-10-31', 95),
    ('Asia', 'China', 'Company A', '2023-01-31', 128),
    ('Asia', 'China', 'Company A', '2022-12-31', 177),
    ('Asia', 'China', 'Company A', '2022-11-30', 153),
    ('Asia', 'China', 'Company A', '2022-10-31', 3),
    ('America', 'USA', 'Company A', '2023-01-31', 15),
    ('America', 'USA', 'Company A', '2022-12-31', 59),
    ('America', 'USA', 'Company A', '2022-11-30', 116),
    ('America', 'USA', 'Company A', '2022-10-31', 122),
    ('Europe', 'Germany', 'Company B', '2023-01-31', 70),
    ('Europe', 'Germany', 'Company B', '2022-12-31', 94),
    ('Europe', 'Germany', 'Company B', '2022-11-30', 24),
    ('Europe', 'Germany', 'Company B', '2022-10-31', 143),
    ('Europe', 'Italy', 'Company B', '2023-01-31', 0),
    ('Europe', 'Italy', 'Company B', '2022-12-31', 11),
    ('Europe', 'Italy', 'Company B', '2022-11-30', 189),
    ('Europe', 'Italy', 'Company B', '2022-10-31', 131),
    ('Asia', 'China', 'Company B', '2023-01-31', 157),
    ('Asia', 'China', 'Company B', '2022-12-31', 34),
    ('Asia', 'China', 'Company B', '2022-11-30', 112),
    ('Asia', 'China', 'Company B', '2022-10-31', 97),
    ('America', 'USA', 'Company B', '2023-01-31', 146),
    ('America', 'USA', 'Company B', '2022-12-31', 41),
    ('America', 'USA', 'Company B', '2022-11-30', 83),
    ('America', 'USA', 'Company B', '2022-10-31', 127)
  ])),
dimensions AS (
  SELECT DISTINCT continent, country, company FROM data
),
dates AS (
  SELECT DISTINCT date FROM data
),
full_combinations AS (
  SELECT d.continent, d.country, d.company, dt.date
  FROM dimensions d
  CROSS JOIN dates dt
),
-- 先计算每个组合的当月销售额
combined AS (
  SELECT 
    fc.continent, 
    fc.country, 
    fc.company, 
    fc.date, 
    COALESCE(SUM(d.sales), 0) AS sales
  FROM full_combinations fc
  LEFT JOIN data d 
    ON fc.continent = d.continent 
    AND fc.country = d.country 
    AND fc.company = d.company 
    AND fc.date = d.date
  GROUP BY fc.continent, fc.country, fc.company, fc.date
)
SELECT 
  *,
  -- 按维度分组，按日期排序取上月销售额
  LAG(sales) OVER (PARTITION BY continent, country, company ORDER BY date) AS sales_previous_month
FROM combined
ORDER BY continent, country, company, date DESC;

按日期汇总后，2023-01-31的sales_previous_month将包含意大利Company A的155，总和从614变为769，符合需求。

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