PostgreSQL函数迭代/作用于具有状态的许多行 [英] PostgreSQL function to iterate through/act on many rows with state
本文介绍了PostgreSQL函数迭代/作用于具有状态的许多行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个数据库,其中的列看起来像这样:
I have a database with columns looking like:
session | order | atype | amt
--------+-------+-------+-----
1 | 0 | ADD | 10
1 | 1 | ADD | 20
1 | 2 | SET | 35
1 | 3 | ADD | 10
2 | 0 | SET | 30
2 | 1 | ADD | 20
2 | 2 | SET | 55
它表示正在执行的操作。每个会话从0开始。ADD添加一个量,而SET设置一个量。我想要一个函数返回会话的结束值,例如
It represents actions happening. Each session starts at 0. ADD adds an amount, while SET sets it. I want a function to return the end value of a session, e.g.
SELECT session_val(1); --returns 45
SELECT session_val(2); --returns 55
是否可以编写这样的函数/查询?我不知道如何使用SQL做任何类似迭代的事情,或者根本不可能。
Is it possible to write such a function/query? I don't know how to do any iteration-like things with SQL, or if it's possible at all.
推荐答案
select sum(amt) as session_val
from (
select segment,
max(segment) over() as max_segment,
amt
from (
select sum(case when atype = 'SET' then 1 else 0 end)
over(order by "order") as segment,
amt
from command
where session = 2
) x
) x
where segment = max_segment
虽然在PL / pgsql中非常简单:
It is quite simple in PL/pgsql though:
create function session_val(session int) returns int stable strict
language plpgsql as $$
declare
value int := 0;
rec command%rowtype;
begin
for rec in select * from command where command.session = session_val.session loop
if rec.atype = 'SET' then
value := rec.amt;
elsif rec.atype = 'ADD' then
value := value + rec.amt;
end if;
end loop;
return value;
end $$;
所以,请选择。
这篇关于PostgreSQL函数迭代/作用于具有状态的许多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文