Обсуждение: Request over partition and join

Поиск
Список
Период
Сортировка

Request over partition and join

От
Michèle Garoche
Дата:
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

Re: Request over partition and join

От
Michèle Garoche
Дата:
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