带循环的 PostgreSQL 函数

Question

我不擅长 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

我想编写一个 Postgres 函数，它可以从 position=1 循环到 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

如何直接在 Postgres 中执行相同的操作，以便它返回 (name, result1, result2)

更新：
@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

Answer 1

@Glenn 为您提供了一个带有聚合函数的非常优雅的解决方案。但要回答您的问题， plpgsql 函数可能如下所示：

测试设置：

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)
;

通用函数

CREATE OR REPLACE FUNCTION f_grp_prod()
  RETURNS TABLE (name text
               , round int
               , result double precision)
  LANGUAGE plpgsql STABLE AS
$func$
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
$func$;

调用：

SELECT * FROM f_grp_prod();

结果：

name | round |  result
-----+-------+---------------
A    | 1     | 0.90430333812
A    | 2     | 0.653458283632

根据问题

CREATE OR REPLACE FUNCTION f_grp_prod(text)
  RETURNS TABLE (name text
               , result1 double precision
               , result2 double precision)
  LANGUAGE plpgsql STABLE AS
$func$
DECLARE
   r      mytable%ROWTYPE;
   _round integer;
BEGIN
   -- init vars
   name    := $1;
   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
$func$;

调用的特定函数：

SELECT * FROM f_grp_prod('A');

结果：

name | result1       |  result2
-----+---------------+---------------
A    | 0.90430333812 | 0.653458283632

Answer 2

我猜您正在寻找聚合的“产品”功能。您可以在 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)  

请参阅 Postgresql 文档中的“用户定义的聚合”。上面的例子是我借用的
此处。还有其他 stackoverflow 响应显示了其他方法做这个。