预查询:将平均值添加为新列

Question

我有以下wage_table:

worker_id, hourly_rate
1      , 20
2      , Null
3      , 30
4      , 40

我想添加一个额外的列mean_hourly_rate，如：

worker_id, hourly_rate, mean_hourly_rate
1      , 20         ,  30
2      , Null       ,  30
3      , 30         ,  30
4      , 40         ,  30

我有以下代码，但出现错误"列worker_id不在GROUP BY子句中">

SELECT
*,
AVG(hourly_rate) AS mean_hourly_rate
FROM wage_table

我想知道做这件事的正确方法是什么？谢谢

Answer 1

您可以使用窗口函数：avg(hourly_rate) OVER ()

例如：

WITH data(worker_id, hourly_rate) AS (
VALUES
(1, 20),
(2, Null),
(3, 30),
(4, 40)
)
SELECT 
worker_id, 
hourly_rate, 
avg(hourly_rate) OVER () AS mean_hourly_rate
FROM data

生产：

worker_id | hourly_rate | mean_hourly_rate
-----------+-------------+------------------
1 |          20 |             30.0
2 |        NULL |             30.0
3 |          30 |             30.0
4 |          40 |             30.0
(4 rows)

Answer 2

您也可以使用交叉连接

SELECT
T1.*, T2. mean_hourly_rate
FROM wage_table T1
CROSS JOIN (select AVG(hourly_rate) AS mean_hourly_rate from wage_table
Where hourly_rate is NOT NULL) T2;

这里有一个db fiddle链接-https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=8c819f2e1e040c74c0f0514e271a4c84