function - 带循环的 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