sql通过将唯一值计数到单独的列中来分组

hlswsv35  于 2021-07-24  发布在  Java
关注(0)|答案(3)|浏览(374)

我有两个专栏, a 以及 b ,两者都有绝对值。假设数据库看起来像这样,

a    b
a1   b1
a2   b2
a3   b1
......

我想分组 a 并计算 b 例如,分成不同的列,

Value    b1    b2    b3
a1       5     10    3
a2       4     6     7
....

我试过了 SELECT a, b, count(b) FROM table GROUP BY a, b 得到类似的结果:

a1    b1    5
a1    b2    10
....

什么是sql查询来生成所需的输出?谢谢。

dddzy1tm

dddzy1tm1#

下面是bigquery标准sql

SELECT
  a,
  COUNTIF(b = 'b1') AS b1,
  COUNTIF(b = 'b2') AS b2,
  COUNTIF(b = 'b3') AS b3
FROM t
GROUP BY a
-- ORDER BY a
snvhrwxg

snvhrwxg2#

可以执行条件加法。例如:

select
  a,
  sum(case when b = 'b1' then 1 else 0 end) as b1,
  sum(case when b = 'b2' then 1 else 0 end) as b2,
  sum(case when b = 'b3' then 1 else 0 end) as b3
from t
group by a
order by a
t3irkdon

t3irkdon3#

实现这一目标的简单方法是:

Select a, 
count(CASE WHEN b = 'b1' THEN 1 ELSE NULL END) b1,
count(CASE WHEN b = 'b2' THEN 1 ELSE NULL END) b2,
count(CASE WHEN b = 'b2' THEN 1 ELSE NULL END) b3
from table
group by a
order by 1

相关问题