BigQuery子查询无结果时总计数为零的问题求助

你当前查询的问题是用逗号连接子查询（笛卡尔积），只要任意一个子查询返回0行，整个结果集就为空，导致所有COUNT计算都返回0。以下是几种可行的解决方法：

方法1：合并后去重统计（推荐） #

这是最简洁高效的方式，把三个表的数据合并后统计去重的LicenseKey数量，还能避免不同表中重复LicenseKey被多次计数：

SELECT COUNT(DISTINCT LicenseKey) AS Number_Of_Licenses
FROM (
    SELECT LicenseKey FROM `table_1` 
    WHERE Customer LIKE {{Customer}} AND Trial = false
    UNION ALL
    SELECT LicenseKey FROM `table_2` 
    WHERE Customer LIKE {{Customer}} AND Trial = false
    UNION ALL
    SELECT LicenseKey FROM `table_3` 
    WHERE Customer LIKE {{Customer}} AND Trial = false
) combined

方法2：单独统计+空值处理 #

如果需要保留各表单独计数再求和的逻辑，用IFNULL或COALESCE处理空结果，确保单个表无数据时返回0而非空：

SELECT
  IFNULL((SELECT COUNT(DISTINCT LicenseKey) FROM `table_1` WHERE Customer LIKE {{Customer}} AND Trial = false), 0) +
  IFNULL((SELECT COUNT(DISTINCT LicenseKey) FROM `table_2` WHERE Customer LIKE {{Customer}} AND Trial = false), 0) +
  IFNULL((SELECT COUNT(DISTINCT LicenseKey) FROM `table_3` WHERE Customer LIKE {{Customer}} AND Trial = false), 0) AS Number_Of_Licenses

如果遇到子查询完全返回空的情况（BigQuery中某些场景下IFNULL可能不生效），可以给每个子查询加兜底行：

SELECT
  COALESCE((SELECT COUNT(DISTINCT LicenseKey) FROM `table_1` WHERE Customer LIKE {{Customer}} AND Trial = false UNION SELECT NULL LIMIT 1), 0) +
  COALESCE((SELECT COUNT(DISTINCT LicenseKey) FROM `table_2` WHERE Customer LIKE {{Customer}} AND Trial = false UNION SELECT NULL LIMIT 1), 0) +
  COALESCE((SELECT COUNT(DISTINCT LicenseKey) FROM `table_3` WHERE Customer LIKE {{Customer}} AND Trial = false UNION SELECT NULL LIMIT 1), 0) AS Number_Of_Licenses

方法3：保留原结构+LEFT JOIN #

如果你想保留原有的子查询结构，用LEFT JOIN关联一个单行列，确保主查询始终有数据，避免笛卡尔积导致的空结果集：

SELECT
  COUNT(DISTINCT x.LicenseKey) +
  COUNT(DISTINCT y.LicenseKey) +
  COUNT(DISTINCT z.LicenseKey) AS Number_Of_Licenses
FROM (SELECT 1 AS dummy) dummy
LEFT JOIN (
    SELECT LicenseKey FROM `table_1` 
    WHERE Customer LIKE {{Customer}} AND Trial = false
) x ON TRUE
LEFT JOIN (
    SELECT LicenseKey FROM `table_2` 
    WHERE Customer LIKE {{Customer}} AND Trial = false
) y ON TRUE
LEFT JOIN (
    SELECT LicenseKey FROM `table_3` 
    WHERE Customer LIKE {{Customer}} AND Trial = false
) z ON TRUE

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