一列上的组，基于另一列中不同值的汇总计数

Question

我有一个具有类似值的表：

CREATE TABLE myTable
    (`ts` varchar(2), `usr_id` varchar(1), `data` varchar(1))
;
    
INSERT INTO myTable
    (`ts`, `usr_id`, `data`)
VALUES
    ('11', 'A', 'x'),
    ('11', 'A', 'x'),
    ('11', 'B', 'x'),
    ('11', 'C', 'x'),
    ('11', 'C', 'x'),
    ('11', 'C', 'x'),
    ('22', 'B', 'x'),
    ('33', 'C', 'x'),
    ('33', 'C', 'x'),
    ('33', 'D', 'x'),
    ('33', 'A', 'x')  
;

我想根据TS进行组，并显示以下usr_id的不同值的计数。

t	的TS计数	11	计数
2	1	3	22
0	1	0	33
1	0	3	<

a href =“ http://www.sqlfiddle.com/#！9/bbbf4fc/2” “> http://www.sqlfiddle.com/#！9/bbf4fc/2

我无法取得太大的进步通过TS进行组：从Mytable组中选择TS，USR_ID，count（*）由TS，usr_id; 但是我希望在同一行中使用A，B和其他人的行计数，无法弄清楚如何实现这一目标。

Answer 1

这是您的查询

SELECT 
    DISTINCT ts, 
    COUNT(CASE WHEN usr_id = 'A' THEN 1 END) AS count_a,
    COUNT(CASE WHEN usr_id = 'B' THEN 1 END) AS count_b,
    COUNT(CASE WHEN usr_id != 'A' AND usr_id != 'B' THEN 1 END) AS count_others
FROM myTable
GROUP BY ts
ORDER BY ts

Answer 2

PostgreSQL允许过滤聚合：

select ts, 
       count(1) filter (where usr_id = 'A') as "count of A",
       count(1) filter (where usr_id = 'B') as "count of B",
       count(1) filter (where usr_id not in ('A', 'B')) as "count of others"
  from mytable
 group by ts;

db＆lt;＆gt; fiddle 此处

Answer 3

您的示例链接是ybout mysql，但是

SELECT
  ts
  , SUM(`usr_id` = 'A') CountA
  , SUM(`usr_id` = 'B') CountB
  , SUM(`usr_id` <> 'B' AND `usr_id` = 'B') CountElse
  
from myTable group by ts

sample

和posdgres

select 
  ts
  , SUM(CASE WHEN "usr_id" = 'A' THEN 1 ELSE 0 END) CountA
  , SUM(CASE WHEN "usr_id" = 'B' THEN 1 ELSE 0 END) CountB
  , SUM(CASE WHEN "usr_id" <> 'B' AND "usr_id" = 'B' THEN 1 ELSE 0 END) CountElse
  
  from myTable group by ts

请参见示例