我在 MS SQL Server 中有如下表：
ID | CLIENT
--------------
123 | D
444 | D
555 | E
我需要計算每個客戶有多少個 ID。因此，我需要以下內容：
CLIENT | avg_id_per_client
---------------------------
D | 1.5
E | 3
- D = 1.5 因為有 2 個 CLIENTS "D" 并且一般有 3 個 CLIENTS，所以 3 / 2 = 1.5
- E = 3 因為有 1 個 CLIENT "E" 并且一般有 3 個 CLIENTS，所以 3 / 1 = 3
如何在 MS SQL Server 中做到這一點？
uj5u.com熱心網友回復：
您可以對 COUNT 進行求和。
SELECT CLIENT , COUNT(id) AS client_total_id , CAST(AVG(1.0*COUNT(id)) OVER () AS FLOAT) AS average_count , CAST(1.0*SUM(COUNT(id)) OVER () / COUNT(id) AS FLOAT) AS odd_avg , CAST(100.0*COUNT(id) / SUM(COUNT(id)) OVER () AS DECIMAL(5,2)) AS percentage_id FROM your_table GROUP BY client
| 客戶 | client_total_id | 平均數 | 奇數平均值 | 百分比ID |
|---|---|---|---|---|
| D | 2 | 1.5 | 1.5 | 66.67 |
| 乙 | 1 | 1.5 | 3 | 33.33 |
但你可能想要這個
SELECT CAST(AVG(1.0*client_total_id) AS FLOAT) AS avg_id_per_client FROM ( SELECT client, COUNT(id) AS client_total_id FROM your_table GROUP BY client ) q;
| avg_id_per_client |
|---|
| 1.5 |
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/416749.html
標籤：