Postgres窗口函数新表保存

Question

我的数据如下所示。

表A

acccount_id | value | timestamp 
-------------------------------
a12         | 122   | jan 1
a13         | 133   | jan 1
a14         | 443   | jan 1
a12         | 251   | jan 2
a13         | 122   | jan 2
a14         | 331   | jan 2
a12         | 412   | jan 3
a13         | 323   | jan 3
a14         | 432   | jan 3

我有一个窗口函数，它可以获得运行平均值并附加一列

select account_id, value, "timestamp", 
avg(value) over (partition by account_id order by "timestamp") as average
from the_table
order by account_id, "timestamp";

并输出该表。

acccount_id | value | timestamp | Average
-----------------------------------------
a12         |  122  | jan 1     | 122
a13         |  133  | jan 1     | 133
a14         |  443  | jan 1     | 443
a12         |  251  | jan 2     | 188.5
a13         |  122  | jan 2     | 222.5
a14         |  331  | jan 2     | 387
a12         |  412  | jan 3     | 261.6
a13         |  323  | jan 3     | 192.6
a14         |  432  | jan 3     | 402

我的问题是：

如何将此新列保存到表A？

我想这样做的一种方法是：

(开始伪代码(

BEGIN;
SELECT window_function FROM table_a INTO temp
ALTER TABLE table_a RENAME TO table_old;
ALTER TABLE temp RENAME TO table_a;
DROP table_old
COMMIT;

你能告诉我postgresql语句是什么样子吗？

Answer 1

您可以添加一个新列，然后按如下方式更新：

alter table the_table add column average decimal(10, 5);
update the_table
set average = t.average
from (
select 
account_id, 
"timestamp", 
avg(value) over (partition by account_id order by "timestamp") as average
from the_table
) t
where account_id = t.account_id and "timestamp" = t."timestamp";

这假设(account_id, timestamp)元组在整个表中是唯一的。

Answer 2

我开始工作了。

BEGIN;
DROP TABLE IF EXISTS temp;
SELECT account_id, value, TO_TIMESTAMP(timestamp, 'YYYY-MM-DD HH24:MI:SS'),   
avg(CAST(value as FLOAT)) over (partition by account_id order by "timestamp") as average
INTO temp
from volume_temp
order by account_id, "endts";
ALTER TABLE volume_temp RENAME TO volume_temp_old;
ALTER TABLE temp RENAME TO volume_temp;
DROP TABLE IF EXISTS temp;
DROP TABLE IF EXISTS volume_temp_old;
COMMIT;

Answer 3

经过一些工作，我完成了上述工作，但实际上我是以一种更规范的方式完成的。所以我只是创建了一个名为vol_stats的新表，并从另一个表中引入pk，并将其命名为fk.

然后在下游，我将把两张桌子连在一起，而不是一张大桌子。

这是我的sql

BEGIN;
DROP TABLE IF EXISTS vol_stats;
SELECT pk as fk, 
avg(CAST(volume as FLOAT)) over (partition by account_id order by "endts") as average,
count(endts) over (partition by account_id order by "endts") as count,
stddev(CAST(volume as FLOAT)) over (partition by account_id order by "endts") as standard_deviation,
variance(CAST(volume as FLOAT)) over (partition by account_id order by "endts") as var,
sum(CAST(volume as FLOAT)) over (partition by account_id order by "endts") as running_sum,
CAST(volume as FLOAT)/CAST(average as FLOAT) as vta 
INTO vol_stats
from volume_temp
order by account_id, "endts";
COMMIT;