我有一張桌子：
CREATE TABLE schools (
ID int,
type varchar(255)
);
INSERT INTO schools (ID, type)
VALUES (1, NULL),
(2, 'primary'),
(3, 'secondary'),
(4, 's'),
(5, 'p'),
(5, 'p');
| ID | 型別 |
|---|---|
| 1 | 空值 |
| 2 | '基本的' |
| 3 | '次要' |
| 4 | 's' |
| 5 | 'p' |
| 5 | 'p' |
我需要制作一個這樣的表格：
| 型別 | 體積 | % |
|---|---|---|
| 基本的 | 2 | 50 |
| 中學 | 2 | 50 |
Type Volume %
Primary 2 50
Secondary 2 50
到目前為止，我使用查詢獲得了前兩列：
SELECT CASE
WHEN type IN ('primary','p') THEN 'Primary'
WHEN type IN ('secondary','s') THEN 'Secondary'
END Type,
count(distinct ID) as Volume
FROM t
我無法弄清楚如何以百分比形式獲得音量。反復使用磁區會不斷拋出聚合錯誤。有人可以解釋我如何做到這一點嗎？
另外，我希望百分比不包括 NULL，因此為什么它有 50%。
uj5u.com熱心網友回復：
使用SUM()視窗函式得到總數：
SELECT CASE
WHEN type IN ('primary','p') THEN 'Primary'
WHEN type IN ('secondary','s') THEN 'Secondary'
END type,
COUNT(*) AS Volume,
100 * COUNT(*) / SUM(COUNT(*)) OVER () AS percentage
FROM (SELECT DISTINCT ID, type FROM schools) s
WHERE type IS NOT NULL
GROUP BY 1;
請參閱演示。
uj5u.com熱心網友回復：
您可以使用以下查詢
SELECT Type,
Volume,
CAST(Volume AS Float) / SUM(Volume) OVER(PARTITION BY seq ORDER BY seq) * 100 AS '%'
FROM
(SELECT Type,
COUNT(DISTINCT ID) AS Volume,
RANK() OVER(ORDER BY (SELECT NULL)) as seq
FROM
(SELECT ID,
CASE
WHEN type IN ('primary','p') THEN 'Primary'
WHEN type IN ('secondary','s') THEN 'Secondary'
END Type
FROM t) t
WHERE Type IS NOT NULL
GROUP BY Type) t
db<>fiddle 中的演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/400988.html
上一篇：如何動態創建包含手動資料的表
下一篇：如果條件為假，則添加空行