时间戳

首页 > 解决方案 > 将表名作为参数传递给多个 SELECT

sql - 将表名作为参数传递给多个 SELECT

问题描述

我有一个 SQL 查询，它按照以下方式汇总表table_name（BigQuery 控制台）的列：

选择

(SELECT COUNT(*) FROM `table_name` WHERE A_col = 'Hello') /
(SELECT COUNT(*) FROM `table_name`) * 100 AS A_something,

(SELECT COUNT(*) FROM `table_name` WHERE B_col IS NULL) /
(SELECT COUNT(*) FROM `table_name`) * 100 AS B_something

怎么可能table_name在 SQL 查询中只指定一个位置，所以在所有每列 SELECT 中它是一个参数？例如：

(SELECT COUNT(*) FROM m WHERE B_col IS NULL) /
(SELECT COUNT(*) FROM m) * 100 AS B_something

标签： sqlgoogle-bigquery

解决方案

我只会使用条件聚合：

select avg(case when a_col = 'Hello' then 100.0 else 0 end) as a_something,
       avg(case when b_col is null then 100.0 else 0 end) as b_something
from m

如果要多次使用此表达式，请使用 CTE：

with params as (
      select avg(case when a_col = 'Hello' then 100.0 else 0 end) as a_something,
             avg(case when b_col is null then 100.0 else 0 end) as b_something
      from m
     )
select . . .
from params cross join
     . . .

推荐阅读