sql - sql在不同的情况下自定义列名并按条件分组

Question

我的 column1 具有不同的值（a，b，c）。我需要根据 column2 (1,2) 和 column3 (true,false) 的值计算该列中的每个值。

最后，我需要一个输出列，其名称是不同组合的名称（numberof_a_and_1_and_true、numberof_a_and_1_and_false、numberof_a_and_2_and_true、numberof_b_and_2_and_true 等

我怎样才能做到这一点 ？我考虑过使用countif，但它太长了。

标签： sqlgroup-bygoogle-bigquery

Answer 1

在 BigQuery 中，您将使用countif()：

select name,
       countif(col1 = 'a' and col2 = 1 and col3) as a_1_true,
       countif(col1 = 'a' and col2 = 1 and not col3) as a_1_false,
       countif(col1 = 'a' and col2 = 2 and col3) as a_2_true,
       countif(col1 = 'a' and col2 = 2 and not col3) as a_2_false,
       countif(col1 = 'b' and col2 = 1 and col3) as b_1_true,
       countif(col1 = 'b' and col2 = 1 and not col3) as b_1_false,
       countif(col1 = 'b' and col2 = 2 and col3) as a_b_true,
       countif(col1 = 'b' and col2 = 2 and not col3) as b_2_false,
       countif(col1 = 'c' and col2 = 1 and col3) as c_1_true,
       countif(col1 = 'c' and col2 = 1 and not col3) as c_1_false,
       countif(col1 = 'c' and col2 = 2 and col3) as c_2_true,
       countif(col1 = 'c' and col2 = 2 and not col3) as c_2_false
from t
group by name;