如果我在 BigQuery 中有這樣的表
id success
-------------
01 true
01 true
02 false
02 true
我想結束這個：
id true false
--------------------
01 2 0
02 1 1
我有這個，但我想知道是否有更優雅的方式來做到這一點？
SELECT
t1.id,
(
SELECT
count(*)
FROM `my_table` t2
WHERE t2.success = true and t2.id = t1.id
) as trueCount,
(
SELECT
count(*)
FROM `my_table` t3
WHERE t3.success = false and t3.id = t1.id
) as falseCount
FROM `my_table` t1
GROUP BY id
uj5u.com熱心網友回復：
考慮應該適用于幾乎所有資料庫的條件聚合：
SELECT
t.id,
SUM(CASE WHEN t.success = true THEN 1 ELSE 0 END) AS trueCount,
SUM(CASE WHEN t.success = false THEN 1 ELSE 0 END) AS falseCount
FROM `my_table` t
GROUP BY t.id
uj5u.com熱心網友回復：
您可以嘗試使用條件聚合函式而不是子查詢。
SELECT id,
COUNT(CASE WHEN success = true THEN 1 END) trueCount,
COUNT(CASE WHEN success = false THEN 1 END) falseCount
FROM T
GROUP BY id
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/448969.html