我想在同一个查询中获得多个计数,但条件不同。
选择EXACT_COUNT_DISTINCT (columnA) as TotalCounts
EXACT_COUNT_DISTINCT (columnA) where sum(columnB + ColumnC) > 0 as Engaged
EXACT_COUNT_DISTINCT(columnA) where sum(columnB + ColumnC) = 0 as NonEngaged
的
理论上,在我的场景中,Engaged+NonEngaged将等同于TotalCounts
发布于 2018-07-28 03:52:34
下面是BigQuery标准SQL (请确保保留下面脚本中的第一行,该脚本设置要使用的标准SQL,即使您没有在UI或您使用的任何工具、库、应用编程接口中明确设置)
#standardSQL
SELECT
COUNT(DISTINCT columnA) AS TotalCounts,
COUNT(DISTINCT IF(flag , columnA, NULL)) AS Engaged,
COUNT(DISTINCT IF(NOT flag , columnA, NULL)) AS NonEngaged
FROM (
SELECT columnA, SUM(columnB + ColumnC) > 0 AS flag
FROM `project.dataset.table`
GROUP BY columnA
)
更新来解决注释中的额外问题-如果我们需要从同一查询中的两个不同的表中获取计数,该怎么办?假设上面同一查询中的第4列应该从tableXYZ
获得columnY的不同计数
最简单的选择如下所示。交叉连接在这里是可以的,因为每个结果只有一行
#standardSQL
SELECT
TotalCounts,
Engaged,
NonEngaged,
distinctY
FROM (
SELECT
COUNT(DISTINCT columnA) AS TotalCounts,
COUNT(DISTINCT IF(flag , columnA, NULL)) AS Engaged,
COUNT(DISTINCT IF(NOT flag , columnA, NULL)) AS NonEngaged
FROM (
SELECT columnA, SUM(columnB + ColumnC) > 0 AS flag
FROM `project.dataset.table`
GROUP BY columnA
)
)
CROSS JOIN (
SELECT COUNT(DISTINCT columnY) distinctY
FROM `project.dataset.tableXYZ`
)
https://stackoverflow.com/questions/51564334
复制相似问题