-------a类、b类、c类
a类——10
b类——10
c类--0 0 1

对于静态类别集，这是可能的：

with your_data as(
select stack (6, 
'user1', 'categoryA',
'user1', 'categoryB',
'user2', 'categoryC',
'user2', 'categoryC',
'user3', 'categoryA',
'user4', 'categoryA'                  
) as (`user`, category)
)

select 
      category, sum(catA) as CategoryA, sum(catB) as CategoryB, sum(catC) as CategoryC
from
(
  select `user` , category, --each user counted once per category
          max(case when category='categoryA' then 1 else 0 end) over (partition by `user`) as catA,
          max(case when category='categoryB' then 1 else 0 end) over (partition by `user`) as catB,
          max(case when category='categoryC' then 1 else 0 end) over (partition by `user`) as catC
  from your_data
   group by  `user` , category
)s
group by Category
order by category

结果：

category    categorya   categoryb   categoryc
categoryA      3           1           0
categoryB      1           1           0
categoryC      0           0           1