BigQuery两表关联统计：解决无匹配记录的报表生成问题

问题描述 #

使用BigQuery生成报表，需展示各应用的以下统计数据：

• 200状态码的请求数（successfulOps）
• 非200状态码的请求数（failedOps）
• 事件数（incidents）

现有两张表：

• requests表：存储应用请求记录，字段包括requestId(字符串)、operationStatus(整数)、timestamp(整数)、directoryName(字符串，即应用名称)
• incidents表：存储应用事件记录，字段包括incidentId(字符串)、directoryProviderId(字符串，关联应用名称)、timestamp(整数)

原查询能实现基础统计，但存在缺陷：当某应用有事件记录但无请求记录时，无法返回该应用的信息及事件数，需修改查询以显示该应用的请求数为0，同时正确统计事件数。

原查询语句：

SELECT
  directoryName AS directoryProviderId, -- this is the app name
  COUNTIF(operationStatus = 200) AS successfulOps,
  COUNTIF(operationStatus <> 200) AS failedOps,
  (
    SELECT
      COUNT(*) AS incidentCount
    FROM
      `incidents` i
    WHERE
      r.directoryName=i.directoryProviderId
      -- Only get incidents from yesterday (starts from yesterday at 00:00 until yesterday at 23:59 Lima time)
      AND DATETIME(TIMESTAMP_MILLIS(i.timestamp), "America/Lima") >= DATE_SUB(CURRENT_DATE('America/Lima'), INTERVAL 1 DAY)
      AND DATETIME(TIMESTAMP_MILLIS(i.timestamp), "America/Lima") < CURRENT_DATE('America/Lima')
  ) AS incidents
FROM
  `requests` AS r
WHERE
  -- Only get requests from yesterday (starts from yesterday at 00:00 until yesterday at 23:59 Lima time)
  DATETIME(TIMESTAMP_MILLIS(r.timestamp), "America/Lima") >= DATE_SUB(CURRENT_DATE('America/Lima'), INTERVAL 1 DAY)
  AND DATETIME(TIMESTAMP_MILLIS(r.timestamp), "America/Lima") < CURRENT_DATE('America/Lima')
GROUP BY
  directoryName

修改后的查询语句 #

WITH yesterday_incidents AS (
  SELECT
    directoryProviderId,
    COUNT(*) AS incidentCount
  FROM
    `incidents`
  WHERE
    DATETIME(TIMESTAMP_MILLIS(timestamp), "America/Lima") >= DATE_SUB(CURRENT_DATE('America/Lima'), INTERVAL 1 DAY)
    AND DATETIME(TIMESTAMP_MILLIS(timestamp), "America/Lima") < CURRENT_DATE('America/Lima')
  GROUP BY
    directoryProviderId
),
yesterday_requests AS (
  SELECT
    directoryName,
    COUNTIF(operationStatus = 200) AS successfulOps,
    COUNTIF(operationStatus <> 200) AS failedOps
  FROM
    `requests`
  WHERE
    DATETIME(TIMESTAMP_MILLIS(timestamp), "America/Lima") >= DATE_SUB(CURRENT_DATE('America/Lima'), INTERVAL 1 DAY)
    AND DATETIME(TIMESTAMP_MILLIS(timestamp), "America/Lima") < CURRENT_DATE('America/Lima')
  GROUP BY
    directoryName
)
SELECT
  COALESCE(r.directoryName, i.directoryProviderId) AS directoryProviderId,
  COALESCE(r.successfulOps, 0) AS successfulOps,
  COALESCE(r.failedOps, 0) AS failedOps,
  COALESCE(i.incidentCount, 0) AS incidents
FROM
  yesterday_incidents i
FULL OUTER JOIN
  yesterday_requests r
ON
  i.directoryProviderId = r.directoryName
ORDER BY
  directoryProviderId

修改说明 #

1. 拆分统计逻辑：用CTE分别提前统计昨天的事件数据和请求数据，避免原查询中关联子查询的性能问题和数据遗漏。
2. 全外连接关联：通过FULL OUTER JOIN关联两张统计后的结果表，确保无论应用有没有请求或事件记录，都能被包含在结果中。
3. 空值替换处理：用COALESCE函数将空值替换为0，保证有事件无请求的应用请求数显示为0，有请求无事件的应用事件数显示为0。

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