我有这个数据集:
id | block_number | value | name
----+--------------+-------+------
2 | 47 | 100 | one
3 | 52 | 200 | one
4 | 58 | 120 | one
5 | 60 | 180 | one
创建自
create table test (id serial, block_number int, value int, name varchar);
和
insert into test (block_number, value, name) values (47, 100, 'one'), (52, 200, 'one'), (58, 120, 'one'), (60, 180, 'one');
我想填充行(实际上不是将它们添加到表中,而是将它们用于进一步处理),以便列“block_number”使用前一行的值连续显示“value”,如下所示:
block_number | value | name
-------------+-------+------
47 | 100 | one
48 | 100 | one
49 | 100 | one
50 | 100 | one
51 | 100 | one
52 | 200 | one
53 | 200 | one
54 | 200 | one
55 | 200 | one
56 | 200 | one
57 | 200 | one
58 | 120 | one
59 | 120 | one
60 | 180 | one
然后我想将这些行中的每一行乘以一个给定的数字并求和总和,例如给定的数字是 2 那么上面的结果就是 4,240。
我在这方面还没有走得太远,我唯一能想到的就是生成缺失行的语句。但是,我没有找到如何将每一行的值设置为前一行的值:
SELECT x.block_number, x.value, t.value
FROM (
SELECT generate_series(min(block_number), max(block_number)) AS block_number
FROM my_table
) x
LEFT JOIN my_table t USING (block_number)
ORDER BY x.block_number;
非常感谢任何帮助。
更新:
我试图在此处将此与@mustaccio 的提示相协调:https://dba.stackexchange.com/posts/327530/edit# :~:text=Carry%20over%20long%20sequence%20of%20missing%20values%20with%20Postgres和我想出了这个:
SELECT block_number
, first_value(value) OVER (PARTITION BY block_number, grp ORDER BY block_number) AS value
FROM (
SELECT
count(value) OVER (PARTITION BY block_number ORDER BY block_number) AS grp
, value
, block_number
FROM (
SELECT x.block_number, t.value
FROM (
SELECT generate_series(min(block_number), max(block_number)) AS block_number
FROM test
) x
LEFT JOIN test t USING (block_number)
ORDER BY x.block_number
) test
) sub;
但它仍然没有让我得到缺失值:
block_number | value
--------------+-------
47 | 100
48 |
49 |
50 |
51 |
52 | 200
53 |
54 |
55 |
56 |
57 |
58 | 120
59 |
60 | 180
有什么建议么?
更新 2
感谢@mustaccio 的回答,第一部分已解决。我试图修改该语句,以便它进行上述计算:将每行中的“值”乘以给定数字(此处:2),然后将所有这些值相加。我尝试了什么:
SELECT SUM(a.value * 2)
FROM (
SELECT block_number, value, first_value(value) OVER (PARTITION BY grp)
FROM (
SELECT
sum(CASE WHEN value IS NOT NULL THEN 1 END) OVER (ORDER BY block_number) AS grp
, value
, block_number
FROM (
SELECT x.block_number, t.value
FROM (
SELECT generate_series(min(block_number), max(block_number)) AS block_number
FROM test
) x
LEFT JOIN test t USING (block_number)
) y
) z
) a;
但是,这给了我 1,200 个结果,而不是预期的 4,060 个。
有任何想法吗?
你几乎在那里:
为了使用这个
first_value()
函数,我们需要确定第一个值应该来自哪个组(分区)。在您从 中填写缺失的block_number
s之后generate_series
,每个组都包含一些非 NULLvalue
后跟 NULLvalue
s,按block_number
;排序 下一组从下一个非 NULL 开始value
。窗口
sum() OVER ...
函数计算从范围开始到当前行的值之和,包括在内;由于我们不在PARTITION BY
此处包含该子句,因此范围表示整个行集。CASE WHEN value IS NOT NULL THEN 1 END
确保我们只在遇到下一个非 NULL 时才增加总和value
。因此,我们grp
为每组block_number
s 分配连续的数字 ( )。现在我们可以识别每个组,我们可以使用
first_value(value) OVER (PARTITION BY grp)
将“last good”值分配给同一grp
分区中的所有行。小提琴