Re: Request over partition and join - Mailing list pgsql-novice
From | Michèle Garoche |
---|---|
Subject | Re: Request over partition and join |
Date | |
Msg-id | 9183B25E-81E6-4F79-BE8A-DF66DA2A5B38@gmail.com Whole thread Raw |
In response to | Request over partition and join (Michèle Garoche <migatine@gmail.com>) |
List | pgsql-novice |
Answering to myself for the record: [code] SELECT DISTINCT ON (societe,date_ord,numero_ordre,libelle) ligne_ordres.id,societe,date_ord,numero_ordre,libelle, SUM(CASE WHEN operation_id!=2 AND operation_id!=21 AND operation_id!=23 THEN quantite ELSE -quantite END) OVER w AS pqte, SUM(CASE WHEN operation_id!=2 AND operation_id!=21 AND operation_id!=23 THEN prix_brut ELSE -round(quantite*cumpb,2) END) OVER w AS pprix_brut, SUM(CASE WHEN operation_id!=2 AND operation_id!=21 AND operation_id!=23 THEN frais ELSE -round(quantite*cump,2)+round(quantite*cumpb,2) END) OVER w AS pfrais, SUM(CASE WHEN operation_id!=2 AND operation_id!=21 AND operation_id!=23 THEN prix_net ELSE -round(quantite*cump,2) END) OVER w AS pprix_net FROM ligne_ordres INNER JOIN titres ON ligne_ordres.titre_id=titres.id INNER JOIN operations ON ligne_ordres.operation_id=operations.id WHERE (date_ord<='2011-03-01' AND societe='WHATEVER' AND ligne_ordres.id BETWEEN 12 AND 3690) WINDOW w AS (PARTITION BY societe,date_ord,numero_ordre,libelle) ORDER BY societe,date_ord,numero_ordre,libelle,ligne_ordres.id [/code] Not sure if it is the best way to do it, but at least it works. Le 7 juil. 11 à 00:59, Michèle Garoche a écrit : > Hello, > > I'm trying to get a report using partition and joins. > > The involved tables are: > > ligne_ordres with > - non unique composite key titre_id, date_ord, numero_ordre, > operation_id > - foreign key titre_id to table titres > - foreign key operation_id to table operations > > titres with > - field societe > > operations with > - field libelle > > I need to get the following report: > > id societe date_ord numero_ordre libelle ppb pf ppn > tpb tf tpn > > With the following code I get: > > id titre_id date_ord numero_ordre operation_id ppb pf ppn > tf tpn > > How to integrate societe and libelle into the report instead of > titre_id and operation_id? > > [code] > WITH calculate_parts AS ( > SELECT id, titre_id, date_ord, numero_ordre, operation_id, > CASE > WHEN operation_id != 2 and operation_id != 21 and > operation_id != 23 THEN > prix_brut > ELSE > -round(quantite * cumpb - 0.005, 2) > END AS prix_brut, > CASE > WHEN operation_id != 2 and operation_id != 21 and > operation_id != 23 THEN > frais > ELSE > -round(quantite * cump, 2) + round(quantite * cumpb, 2) > END AS frais, > CASE > WHEN operation_id != 2 and operation_id != 21 and > operation_id != 23 THEN > prix_net > ELSE > -round(quantite * cump, 2) > END AS prix_net > FROM ligne_ordres > WHERE date_ord >= '2011-03-01') > SELECT DISTINCT ON(titre_id, date_ord, numero_ordre, operation_id) > id, titre_id, date_ord, numero_ordre, operation_id, > SUM(prix_brut) OVER (PARTITION BY titre_id, date_ord, numero_ordre, > operation_id) AS ppb, > SUM(frais) OVER (PARTITION BY titre_id, date_ord, numero_ordre, > operation_id) AS pf, > SUM(prix_net) OVER (PARTITION BY titre_id, date_ord, numero_ordre, > operation_id) AS ppn, > SUM(prix_brut) OVER () AS tpb, > SUM(frais) OVER () AS tf, > SUM(prix_net) OVER () as tpn > FROM calculate_parts > ORDER BY titre_id, date_ord, numero_ordre, operation_id, id > [/code] > > Thanks in advance for any help. > > Cheers, > Michèle > > > Cheers, Michèle
pgsql-novice by date: