sql - 聚合函数 - 有没有办法在SQL中对组执行操作?

  显示原文与译文双语对照的内容

所以我对SQL很陌生,我很可能没有描述清楚我想要做什么。 我有一个包含三列的表,我想按一列分组,看看每个组中有多少百分比在另一列中有一定值。 例如在表中:


id col1 col2


----------------


0 A 1


1 A 2


2 B 2


3 B 2


4 A 1



我想 GROUP BY col1,看看每个组( A 或者 B )的百分比在 col2 中的值 1. 我想得到的结果是:


col1 percentage_col2_equals_1


------------------------------


 A 66.7


 B 0.0



到目前为止,我有:


SELECT col1,


((SELECT COUNT(*) FROM my_table


WHERE col2 = 1


GROUP BY col1)/


(SELECT COUNT(*) FROM my_table


GROUP BY col1) * 100)


FROM my_table


GROUP BY col1;



但这不起作用。 任何帮助都是值得感激的 !

时间:

使用 case when


SELECT col1,(coalesce(count(case when col2=1 then col2 end),0)*100.00)/count(*)


from tablename


group by col1



与每个人一样的答案,只是因为Postgres的表达性而把它放在这里:)

现场测试:https://www.db-fiddle.com/f/goL488VaPuZYii7Wik3pFk/0


select


 col1,


 count(*) filter(where col2 = 1)::numeric/count(*)



from tbl 


group by col1;



输出:


| col1 |?column? |


| ---- | ---------------------- |


| A | 0.66666666666666666667 |


| B | 0.00000000000000000000 |



以下查询将返回你预期的结果:


SELECT col1, 


 CAST(((SUM(IIF(col2 = 1, 1, 0))) * 100.0)/COUNT(*) AS DECIMAL(5, 1)) AS percentage_col2_equals_1


FROM my_table


GROUP BY col1;



示例执行示例数据:


DECLARE @my_table TABLE (id INT, col1 CHAR(1), col2 INT);



INSERT INTO @my_table (id, col1, col2) VALUES


(0, 'A', 1),


(1, 'A', 2),


(2, 'B', 2),


(3, 'B', 2),


(4, 'A', 1);



SELECT col1, CAST(((SUM(IIF(col2 = 1, 1, 0))) * 100.0)/COUNT(*) AS DECIMAL(5, 1)) AS percentage_col2_equals_1


FROM @my_table


GROUP BY col1;



输出:


col1 percentage_col2_equals_1


---------------------------------


A 66.7


B 0.0




 CREATE TABLE #TEMP


 (ID INT, 


 COL1 VARCHAR(10), 


 COL2 INT


 );


 INSERT INTO #TEMP


 SELECT 0, 'A',1


 UNION


 SELECT 1, 'A',2


 UNION


 SELECT 2, 'B',2


 UNION


 SELECT 3, 'B',2


 UNION


 SELECT 4, 'A',1;


 SELECT T.COL1, 


 ROUND((CAST(COUNT(CASE


 WHEN T.COL2 = 1


 THEN T.COL2


 ELSE NULL


 END) AS DECIMAL)/(S.COL2)) * 100.0, 2) AS Percentage_1


 FROM #TEMP T


 JOIN


 (


 SELECT COUNT(COL2) COL2, 


 COL1


 FROM #TEMP


 GROUP BY COL1


 ) S ON S.COL1 = T.COL1


 GROUP BY T.COL1, 


 S.COL2;



...