带有循环的 PostgreSQL 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8918755/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL function with a loop
提问by BPm
I'm not good at postgres functions. Could you help me out?
Say, I have this db:
我不擅长 postgres 函数。你能帮我吗？
说，我有这个数据库：
name | round |position | val
-----------------------------------
A | 1 | 1 | 0.5
A | 1 | 2 | 3.4
A | 1 | 3 | 2.2
A | 1 | 4 | 3.8
A | 2 | 1 | 0.5
A | 2 | 2 | 32.3
A | 2 | 3 | 2.21
A | 2 | 4 | 0.8
I want to write a Postgres function that can loop from position=1to position=4and calculate the corresponding value. I could do this in python with psycopg2:
我想写一个 Postgres 函数，可以从position=1to循环position=4并计算相应的值。我可以用 psycopg2 在 python 中做到这一点：
import psycopg2
import psycopg2.extras
conn = psycopg2.connect("host='localhost' dbname='mydb' user='user' password='pass'")
CURSOR = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cmd = """SELECT name, round, position, val from mytable"""
CURSOR.execute(cmd)
rows = CURSOR.fetchall()
dict = {}
for row in rows:
indx = row['round']
try:
dict[indx] *= (1-row['val']/100)
except:
dict[indx] = (1-row['val']/100)
if row['position'] == 4:
if indx == 1:
result1 = dict[indx]
elif indx == 2:
result2 = dict[indx]
print result1, result2
How can I do the same thing directly in Postgres so that it returns a table of (name, result1, result2)
我怎样才能直接在 Postgres 中做同样的事情，以便它返回一个表 (name, result1, result2)
UPDATE:
@a_horse_with_no_name, the expected value would be:
更新：
@a_horse_with_no_name，预期值为：
result1 = (1 - 0.5/100) * (1 - 3.4/100) * (1 - 2.2/100) * (1 - 3.8/100) = 0.9043
result2 = (1 - 0.5/100) * (1 - 32.3/100) * (1 - 2.21/100) * (1 - 0.8/100) = 0.6535
回答by Erwin Brandstetter
@Glenn gave you a very elegant solution with an aggregate function. But to answer your question, a plpgsql function could look like this:
@Glenn 为您提供了一个带有聚合函数的非常优雅的解决方案。但是要回答您的问题，plpgsql 函数可能如下所示：
Test setup:
测试设置：
CREATE TEMP TABLE mytable (
name text
, round int
, position int
, val double precision);
INSERT INTO mytable VALUES
('A', 1, 1, 0.5)
,('A', 1, 2, 3.4)
,('A', 1, 3, 2.2)
,('A', 1, 4, 3.8)
,('A', 2, 1, 0.5)
,('A', 2, 2, 32.3)
,('A', 2, 3, 2.21)
,('A', 2, 4, 0.8);
Generic function
通用函数
CREATE OR REPLACE FUNCTION f_grp_prod()
RETURNS TABLE (
name text
, round int
, result double precision) AS
$BODY$
DECLARE
r mytable%ROWTYPE;
BEGIN
-- init vars
name := 'A'; -- we happen to know initial value
round := 1; -- we happen to know initial value
result := 1;
FOR r IN
SELECT *
FROM mytable m
ORDER BY m.name, m.round
LOOP
IF (r.name, r.round) <> (name, round) THEN -- return result before round
RETURN NEXT;
name := r.name;
round := r.round;
result := 1;
END IF;
result := result * (1 - r.val/100);
END LOOP;
RETURN NEXT; -- return final result
END;
$BODY$ LANGUAGE plpgsql STABLE;
Call:
称呼：
SELECT * FROM f_grp_prod();
Result:
结果：
name | round | result
-----+-------+---------------
A | 1 | 0.90430333812
A | 2 | 0.653458283632
Specific function as per question
根据问题的具体功能
CREATE OR REPLACE FUNCTION f_grp_prod(text)
RETURNS TABLE (
name text
, result1 double precision
, result2 double precision) AS
$BODY$
DECLARE
r mytable%ROWTYPE;
_round integer;
BEGIN
-- init vars
name := ;
result2 := 1; -- abuse result2 as temp var for convenience
FOR r IN
SELECT *
FROM mytable m
WHERE m.name = name
ORDER BY m.round
LOOP
IF r.round <> _round THEN -- save result1 before 2nd round
result1 := result2;
result2 := 1;
END IF;
result2 := result2 * (1 - r.val/100);
_round := r.round;
END LOOP;
RETURN NEXT;
END;
$BODY$ LANGUAGE plpgsql STABLE;
Call:
称呼：
SELECT * FROM f_grp_prod('A');
Result:
结果：
name | result1 | result2
-----+---------------+---------------
A | 0.90430333812 | 0.653458283632
回答by Glenn
I guess you are looking for an aggregate "product" function. You can create your own aggregate functions in Postgresql and Oracle.
我猜您正在寻找聚合“产品”功能。您可以在 Postgresql 和 Oracle 中创建自己的聚合函数。
CREATE TABLE mytable(name varchar(32), round int, position int, val decimal);
INSERT INTO mytable VALUES('A', 1, 1, 0.5);
INSERT INTO mytable VALUES('A', 1, 2, 3.4);
INSERT INTO mytable VALUES('A', 1, 3, 2.2);
INSERT INTO mytable VALUES('A', 1, 4, 3.8);
INSERT INTO mytable VALUES('A', 2, 1, 0.5);
INSERT INTO mytable VALUES('A', 2, 2, 32.3);
INSERT INTO mytable VALUES('A', 2, 3, 2.21);
INSERT INTO mytable VALUES('A', 2, 4, 0.8);
CREATE AGGREGATE product(double precision) (SFUNC=float8mul, STYPE=double precision, INITCOND=1);
SELECT name, round, product(1-val/100) AS result
FROM mytable
GROUP BY name, round;
name | round | result
------+-------+----------------
A | 2 | 0.653458283632
A | 1 | 0.90430333812
(2 rows)
See "User-Defined Aggregates" in the Postgresql doc. The example above I borrowed from here. There are other stackoverflow responses that show other methodsto do this.
请参阅 Postgresql 文档中的“用户定义的聚合”。上面的例子是我从这里借来的 。还有其他 stackoverflow 响应显示了执行此操作的其他方法。
