BigQuery：如何统计表格多列中数字的出现频次及批量统计多数字的方法

嘿，这个问题我之前做报表的时候刚好碰到过，给你分享两种实用的解法，分情况来说：

你原来的COUNT(CASE WHEN column_1 = 1 THEN 1 END)只能统计单列，要跨4列统计所有出现次数（包括同一行里多个列命中的情况），只要把每个列的统计结果加起来就行，有两种直观写法：

写法1：清晰的多CASE相加（兼容所有数据库） #

SELECT 
  SUM(
    CASE WHEN column_1 = 1 THEN 1 ELSE 0 END +
    CASE WHEN column_2 = 1 THEN 1 ELSE 0 END +
    CASE WHEN column_3 = 1 THEN 1 ELSE 0 END +
    CASE WHEN column_4 = 1 THEN 1 ELSE 0 END
  ) AS total_count_of_1
FROM your_table;

这种写法逻辑一目了然，不管你的数据库支不支持布尔运算，都能正常运行。而且如果同一行里多个列都是目标数字（比如column1和column2都是1），这里会分别计数，最终得到的是真正的“总出现次数”。

写法2：简洁的布尔值相加（适合MySQL、PostgreSQL等） #

如果你的数据库支持将布尔值自动转为数值（true=1，false=0），可以用更简洁的写法：

SELECT 
  SUM(
    (column_1 = 1) + (column_2 = 1) + (column_3 = 1) + (column_4 = 1)
  ) AS total_count_of_1
FROM your_table;

效果和上面完全一样，只是代码更短。

如果要一次性统计1到90所有数字在这4列中的总频次，一个个写CASE就太繁琐了，这时候可以先生成一个包含1-90的数字序列，再和原表关联统计，效率高还省心。

方法1：用递归CTE生成数字序列（支持CTE的数据库：PostgreSQL、SQL Server、MySQL 8.0+） #

递归CTE可以快速生成连续数字，不需要提前创建额外的表：

WITH numbers AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 FROM numbers WHERE num < 90
)
SELECT 
  n.num,
  SUM(
    (t.column_1 = n.num) + (t.column_2 = n.num) + (t.column_3 = n.num) + (t.column_4 = n.num)
  ) AS frequency
FROM numbers n
LEFT JOIN your_table t ON 
  t.column_1 = n.num OR 
  t.column_2 = n.num OR 
  t.column_3 = n.num OR 
  t.column_4 = n.num
GROUP BY n.num
ORDER BY n.num;

这里numbers CTE生成1到90的所有数字，左连接原表后分组统计每个数字的总出现次数。如果某个数字在所有列中都没出现，frequency会显示0，不会漏掉任何数字。

方法2：用现成的数字表（性能更优） #

如果你的数据库里已经有一个包含连续数字的表（比如叫digits，num列包含1-90），直接用这个表关联统计就行，数据量大的时候性能比递归CTE更好：

SELECT 
  d.num,
  SUM(
    CASE WHEN t.column_1 = d.num THEN 1 ELSE 0 END +
    CASE WHEN t.column_2 = d.num THEN 1 ELSE 0 END +
    CASE WHEN t.column_3 = d.num THEN 1 ELSE 0 END +
    CASE WHEN t.column_4 = d.num THEN 1 ELSE 0 END
  ) AS frequency
FROM digits d
LEFT JOIN your_table t ON 
  t.column_1 = d.num OR 
  t.column_2 = d.num OR 
  t.column_3 = d.num OR 
  t.column_4 = d.num
GROUP BY d.num
ORDER BY d.num;

如果你的数据库不支持布尔转数值，就把上面的布尔表达式换成CASE写法，和单个数字的统计逻辑一致即可。

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