BigQuery合并三查询关联问题：缺失TER数据行无法展示

开发者阿泽

2026-7-17

BigQuery查询合并：补全日志中缺失的Agent TER数据行

问题说明

在BigQuery中合并三个独立查询后，出现数据遗漏问题：6/23存在Agent1的TER数据，但因当天无对应survey数据，当前JOIN逻辑导致该行被过滤。若移除agent_name关联条件，又会出现agent不匹配的TER数据。需求是展示近15天内每个agent每日的survey和TER数据，无数据的字段留空。

当前合并SQL

WITH dates AS (
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY), DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))) AS Date
), survey_query AS(
    SELECT 
      AWS_ID_ AS agent_name,
      IF(REGEXP_CONTAINS(Quality_Date, '/'), FORMAT_TIMESTAMP('%Y-%m-%d', PARSE_DATE('%m/%d/%Y', Quality_Date)), Quality_Date) AS Date,
      CASE
        WHEN COUNTIF(survey1 != '' AND survey1 != 'NA') = 0 THEN ''
        ELSE CAST(COUNTIF(survey1 != '' AND survey1 != 'NA') AS STRING)
      END AS survey1_count,
      CASE
        WHEN COUNTIF(survey1 != '' AND survey1 != 'NA') = 0 THEN ''
        ELSE CAST(COUNTIF(survey1 = '1') AS STRING)
      END AS survey1_sum,
      CASE
        WHEN COUNTIF(survey1 != '' AND survey1 != 'NA') = 0 THEN ''
        ELSE CONCAT(ROUND((COALESCE(COUNTIF(survey1 = '1') / COUNTIF(survey1 != '' AND survey1 != 'NA')) * 100), 2), '%')
      END AS survey1_avg,
      CASE
        WHEN COUNTIF(survey5 != '' AND survey5 != 'NA') = 0 THEN ''
        ELSE CAST(COUNTIF(survey5 != '' AND survey5 != 'NA') AS STRING)
      END AS survey5_count,
      CASE
        WHEN COUNTIF(survey5 != '' AND survey5 != 'NA') = 0 THEN ''
        ELSE CAST(IFNULL(SUM(CASE WHEN SAFE_CAST(survey5 AS FLOAT64) IS NULL THEN 0 ELSE CAST(survey5 AS FLOAT64) END), 0) AS STRING)
      END AS survey5_sum,
      CASE
        WHEN COUNTIF(survey5 != '' AND survey5 != 'NA') = 0 THEN ''
        ELSE CAST(IFNULL(ROUND(AVG(CASE WHEN SAFE_CAST(survey5 AS FLOAT64) IS NULL THEN NULL ELSE CAST(survey5 AS FLOAT64) END), 2), 0) AS STRING)
      END AS survey5_avg
    FROM `combined_quality`
    GROUP BY agent_name, Date
    ORDER BY agent_name, Date DESC
), ter_query AS (
    SELECT 
      AWS_ID_ AS agent_name,
      FORMAT_DATE('%Y-%m-%d', PARSE_DATE('%Y%m%d', CAST(A_Closing_Date AS STRING))) AS Date,
      CAST(COUNT(A_TER_Claims) AS STRING) AS TER_Count,
      CAST(IFNULL(SUM(A_TER_Claims), 0) AS STRING) AS TER_Sum,
      CASE
        WHEN COUNT(A_TER_Claims) = 0 THEN ''
        ELSE CONCAT(ROUND((IFNULL(SUM(A_TER_Claims), 0) / COUNT(A_TER_Claims)) * 100, 2), '%')
      END AS TER_Percentage
    FROM `ter_currentyear`
    GROUP BY agent_name, Date 
)
SELECT 
  d.Date,
  COALESCE(s.agent_name,t.agent_name) AS agent_name,
  CASE
    WHEN IFNULL(s.survey1_count, '') = '0' THEN ''
    ELSE IFNULL(s.survey1_count, '')
  END AS survey1_count,
  IFNULL(s.survey1_sum, '') AS survey1_sum,
  IFNULL(s.survey1_avg, '') AS survey1_avg,
  CASE
    WHEN IFNULL(s.survey5_count, '') = '0' THEN ''
    ELSE IFNULL(s.survey5_count, '')
  END AS survey5_count,
  IFNULL(s.survey5_sum, '') AS survey5_sum,
  IFNULL(s.survey5_avg, '') AS survey5_avg,
  IFNULL(t.TER_Count, '') AS TER_Count,
  IFNULL(t.TER_Sum, '') AS TER_Sum,
  IFNULL(t.TER_Percentage, '') AS `TER%`
FROM dates d
LEFT JOIN survey_query s ON DATE(d.Date) = DATE(s.Date)
LEFT JOIN ter_query t ON d.Date = DATE(t.Date) and t.agent_name=s.agent_name
ORDER BY agent_name, d.Date DESC

当前输出

Date    agent_name  survey1_count   survey1_sum survey1_avg survey5_count   survey5_sum survey5_avg TER_Count   TER_Sum TER%
6/25/2023   Agent1  2   2   100%    1   4   4           
6/24/2023   Agent1  2   2   100%    2   10  5           
6/21/2023   Agent1  2   2   100%    2   10  5           
6/20/2023   Agent1  2   2   100%    2   10  5   2   0   0%
6/19/2023   Agent1  2   2   100%    2   10  5   3   0   0%

期望输出（补充6/23的Agent1 TER数据行）

Date    agent_name  survey1_count   survey1_sum survey1_avg survey5_count   survey5_sum survey5_avg TER_Count   TER_Sum TER%
6/25/2023   Agent1  2   2   100%    1   4   4           
6/24/2023   Agent1  2   2   100%    2   10  5           
6/23/2023   Agent1                              0   1   0%
6/21/2023   Agent1  2   2   100%    2   10  5           
6/20/2023   Agent1  2   2   100%    2   10  5   2   0   0%
6/19/2023   Agent1  2   2   100%    2   10  5   3   0   0%

修正后的SQL

核心思路是先构建近15天日期 + 所有存在的Agent的完整组合，再分别左联survey和TER数据，确保每个Agent每天都有一行记录：

WITH dates AS (
    SELECT Date 
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY), CURRENT_DATE())) AS Date
),
-- 获取所有涉及的Agent（从survey和TER表中去重）
agents AS (
    SELECT AWS_ID_ AS agent_name FROM `combined_quality`
    UNION DISTINCT
    SELECT AWS_ID_ AS agent_name FROM `ter_currentyear`
),
-- 生成日期+Agent的完整笛卡尔积
date_agent AS (
    SELECT d.Date, a.agent_name
    FROM dates d
    CROSS JOIN agents a
),
survey_query AS(
    SELECT 
      AWS_ID_ AS agent_name,
      -- 统一日期格式为DATE类型，避免字符串匹配问题
      DATE(IF(REGEXP_CONTAINS(Quality_Date, '/'), PARSE_DATE('%m/%d/%Y', Quality_Date), PARSE_DATE('%Y-%m-%d', Quality_Date))) AS Date,
      CASE
        WHEN COUNTIF(survey1 != '' AND survey1 != 'NA') = 0 THEN ''
        ELSE CAST(COUNTIF(survey1 != '' AND survey1 != 'NA') AS STRING)
      END AS survey1_count,
      CASE
        WHEN COUNTIF(survey1 != '' AND survey1 != 'NA') = 0 THEN ''
        ELSE CAST(COUNTIF(survey1 = '1') AS STRING)
      END AS survey1_sum,
      CASE
        WHEN COUNTIF(survey1 != '' AND survey1 != 'NA') = 0 THEN ''
        ELSE CONCAT(ROUND((COALESCE(COUNTIF(survey1 = '1') / COUNTIF(survey1 != '' AND survey1 != 'NA'), 0) * 100), 2), '%')
      END AS survey1_avg,
      CASE
        WHEN COUNTIF(survey5 != '' AND survey5 != 'NA') = 0 THEN ''
        ELSE CAST(COUNTIF(survey5 != '' AND survey5 != 'NA') AS STRING)
      END AS survey5_count,
      CASE
        WHEN COUNTIF(survey5 != '' AND survey5 != 'NA') = 0 THEN ''
        ELSE CAST(IFNULL(SUM(SAFE_CAST(survey5 AS FLOAT64)), 0) AS STRING)
      END AS survey5_sum,
      CASE
        WHEN COUNTIF(survey5 != '' AND survey5 != 'NA') = 0 THEN ''
        ELSE CAST(IFNULL(ROUND(AVG(SAFE_CAST(survey5 AS FLOAT64)), 2), 0) AS STRING)
      END AS survey5_avg
    FROM `combined_quality`
    GROUP BY agent_name, Date
),
ter_query AS (
    SELECT 
      AWS_ID_ AS agent_name,
      DATE(PARSE_DATE('%Y%m%d', CAST(A_Closing_Date AS STRING))) AS Date,
      CAST(COUNT(A_TER_Claims) AS STRING) AS TER_Count,
      CAST(IFNULL(SUM(A_TER_Claims), 0) AS STRING) AS TER_Sum,
      CASE
        WHEN COUNT(A_TER_Claims) = 0 THEN ''
        ELSE CONCAT(ROUND((IFNULL(SUM(A_TER_Claims), 0) / COUNT(A_TER_Claims)) * 100, 2), '%')
      END AS TER_Percentage
    FROM `ter_currentyear`
    GROUP BY agent_name, Date 
)
SELECT 
  da.Date,
  da.agent_name,
  CASE
    WHEN IFNULL(s.survey1_count, '') = '0' THEN ''
    ELSE IFNULL(s.survey1_count, '')
  END AS survey1_count,
  IFNULL(s.survey1_sum, '') AS survey1_sum,
  IFNULL(s.survey1_avg, '') AS survey1_avg,
  CASE
    WHEN IFNULL(s.survey5_count, '') = '0' THEN ''
    ELSE IFNULL(s.survey5_count, '')
  END AS survey5_count,
  IFNULL(s.survey5_sum, '') AS survey5_sum,
  IFNULL(s.survey5_avg, '') AS survey5_avg,
  IFNULL(t.TER_Count, '') AS TER_Count,
  IFNULL(t.TER_Sum, '') AS TER_Sum,
  IFNULL(t.TER_Percentage, '') AS `TER%`
FROM date_agent da
LEFT JOIN survey_query s ON da.Date = s.Date AND da.agent_name = s.agent_name
LEFT JOIN ter_query t ON da.Date = t.Date AND da.agent_name = t.agent_name
ORDER BY da.agent_name, da.Date DESC

关键调整点

新增agents CTE：从两个业务表中获取所有存在的Agent并去重，确保覆盖所有需要展示的Agent。
生成date_agent基础组合：用日期表和Agent表交叉连接，生成近15天每个Agent的每日记录，这是保证无数据日期也能展示的核心。
统一日期类型：将survey和TER查询中的日期转换为DATE类型，避免字符串格式不匹配导致的关联错误。
调整JOIN逻辑：基于date_agent分别左联survey和TER数据，关联条件同时匹配日期和Agent，确保数据对应正确。

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

BigQuery合并三查询关联问题：缺失TER数据行无法展示

问题说明 #

当前合并SQL #

当前输出 #

期望输出（补充6/23的Agent1 TER数据行） #

修正后的SQL #