Sql 按分类标准检索记录

发布于12月15日

我遇到了以下问题--用这种 struct 做桌子.

user_id	permission
25	Denied
25	Permitted
139	Denied
139	Limited
139	Limited
139	Limited

需要根据分类标准(3个类别)将记录分组为one line个"user_id"，计算不同的"权限"状态.它应该如下所示:

user_id	denied_count	limited_count	permitted_count
25	1	0	1
139	1	3	0

我try 使用GROUP BY和CASE对以下脚本进行分组:

select user_id, 
       permission, 
       case when permission = 'PERMITTED' then count("permission") else 0 end as permitted_count,
       case when permission = 'LIMITED' then count("permission") else 0 end as limited_count,    
       case when permission = 'DENIED' then count("permission") else 0 end as denied_count                                       
                     from table
                     group by 1,2
                     order by user_id

但用这种方式记录下来:

user_id	denied_count	limited_count	permitted_count
25	1	0	0
25	0	0	1
139	1	0	0
139	0	3	0

请告诉我我哪里错了.

推荐答案

我对BigQuery不太熟悉.如果您从select和group by中删除权限，那么您正在使用的语法可能完全没有问题.

不过，这是一种非常标准的方法:

https://dbfiddle.uk/liKLlSxH个

CREATE TABLE permissions (
    user_id INT,
    permission VARCHAR(50)
);

INSERT INTO permissions (user_id, permission) VALUES (25, 'Denied');
INSERT INTO permissions (user_id, permission) VALUES (25, 'Permitted');
INSERT INTO permissions (user_id, permission) VALUES (139, 'Denied');
INSERT INTO permissions (user_id, permission) VALUES (139, 'Limited');
INSERT INTO permissions (user_id, permission) VALUES (139, 'Limited');
INSERT INTO permissions (user_id, permission) VALUES (139, 'Limited');

select user_id,
       sum(case when permission = 'Denied' then 1 else 0 end) as denied_count,
       sum(case when permission = 'Permitted' then 1 else 0 end) as permitted_count,
       sum(case when permission = 'Limited' then 1 else 0 end) as limited_count
  from permissions
 group
    by user_id;