PostgreSQL fonction avec une boucle

Je ne suis pas bon à postgres fonctions. Pourriez-vous m'aider?
Dites, j'ai cette db:

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

Je veux écrire une Postgres fonction de boucle de position=1 à position=4 et calculer la valeur correspondante. Je pourrais le faire en python, avec psycopg2:

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

Comment puis-je faire la même chose directement dans Postgres de sorte qu'elle retourne un tableau de (name, result1, result2)

Mise à JOUR:
@a_horse_with_no_name, la valeur attendue serait:

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

C'est qu'une somme par la valeur de "tour"? Pouvez-vous nous montrer la sortie attendue pour votre échantillon de données?
Salut, reportez-vous à la section mise à Jour 😀 merci
tl;dr mais apparemment, vous pourriez être à la recherche for PostgreSQL est les fonctions de la fenêtre. Voir postgresql.org/docs/current/static/tutorial-window.html pour plus d'info.

OriginalL'auteur BPm | 2012-01-18

@Glenn vous a donné un très élégant solution avec une fonction d'agrégation. Mais pour répondre à votre question, une fonction plpgsql pourrait ressembler à ceci:

Configuration de Test:

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

Fonction générique

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;

Appel:

SELECT * FROM f_grp_prod();

Résultat:

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

Fonction spécifique, comme par 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    := $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;
$BODY$      LANGUAGE plpgsql STABLE;

Appel:

SELECT * FROM f_grp_prod('A');

Résultat:

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

merci de nous montrer 2 façons de le faire. il est extrêmement utile!!!

OriginalL'auteur Erwin Brandstetter

Je suppose que vous êtes à la recherche pour un agrégat de "produit" de la fonction. Vous pouvez créer vos propres fonctions d'agrégation dans Postgresql et 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)

Voir "Agrégats Définis par l'Utilisateur" dans la doc de Postgresql. L'exemple ci-dessus, j'ai emprunté de
ici. Il y a d'autres stackoverflow réponses qui montrent d'autres méthodes pour ce faire.

merci pour me montrer une autre façon de faire cela! 😀

OriginalL'auteur Glenn

Vous devez vous connecter pour publier un commentaire.