PostgreSQL 9.4 – Problem Query in PostgreSQL

postgresqlpostgresql-9.4

I have problems when I want to generate a query in posgretsql, I was trying and I do not know how to calculate the following

My table has the following data:

CREATE TABLE "abc-ventas"
AS
  SELECT *
  FROM ( VALUES
    ( '0001', 'sandía' ,  2.000, 3.000 ),
    ( '0002', 'lápiz'  ,  3.000, 5.000 ),
    ( '0003', 'manzana',  1.000, 2.000 ),
    ( '0004', 'naranja',  2.000, 3.000 ),
    ( '0005', 'arroz'  ,  1.000, 5.000 )
  ) AS t(CODIGO, DESCRIPCION, COSTO, NETO);

I need to calculate the following information

PARTIC= NETO / SUM(TOTAL NETO)

CODIGO   DESCRIPCION    COSTO     NETO   PARTIC PARTIC-ACUM
0001     sandía        2.000      3.000   17%
0002     lápiz         3.000      5.000   28%
0003     manzana       1.000      2.000   11%
0004     naranja       2.000      3.000   17%
0005     arroz         1.000      5.000   28%
                                 -------------
                                 18.000   100%

Now to calculate the "partic-acum" order DESC "partic"

PARTIC-ACUM= PARTIC + PREVIOUS PARTIC

CODIGO   DESCRIPCION    COSTO     NETO   PARTIC PARTIC-ACUM CLASIF
0002     lápiz         3.000      5.000   28%          28%
0005     arroz         1.000      5.000   28%          56%
0001     sandía        2.000      3.000   17%          72%
0004     naranja       2.000      3.000   17%          89%
0003     manzana       1.000      2.000   11%          100%

Once generated the result I want to classify it in A B C

  • If the value is 0 to 80: A
  • If the value is 81 to 95: B
  • If the value is 95 to 100: C

This is my desired output

CODIGO   DESCRIPCION   COSTO      NETO    PARTIC PARTIC-ACUM CLASIF
0002     lápiz         3.000      5.000   28%          28%    A
0005     arroz         1.000      5.000   28%          56%    A 
0001     sandía        2.000      3.000   17%          72%    A
0004     naranja       2.000      3.000   17%          89%    B
0003     manzana       1.000      2.000   11%          100%   C

The query I have is the following

SELECT
  codigo,
  descripcion,
  cantidad,
  costo,
  neto,
  (SELECT sum(neto) FROM "abc-ventas") as total,
  (round(neto /(SELECT sum(neto) FROM "abc-ventas"),4)*100) AS participacion
FROM "abc-ventas"
ORDER BY participacion DESC;

Best Answer

As a first note, you should never quote table names or any identifiers in PostgreSQL. This is a bad practice.

SELECT
  *,
  CASE
    WHEN "partic-acum" > 95 THEN 'C'
    WHEN "partic-acum" > 80 THEN 'B'
    ELSE 'A'
  END AS clasif
FROM (
  SELECT
    codigo,
    descripcion,
    costo,
    neto,
    round(partic) AS partic,
    round(sum(partic) OVER (ORDER BY partic DESC, codigo)) AS "partic-acum"
  FROM (
    SELECT
      codigo,
      descripcion,
      costo,
      neto,
      neto/sum(neto) OVER ()*100 AS partic
    FROM "abc-ventas"
  ) AS t
) AS t;

Outputs

 codigo | descripcion | costo | neto  | partic | partic-acum | clasif 
--------+-------------+-------+-------+--------+-------------+--------
 0002   | lápiz       | 3.000 | 5.000 |     28 |          28 | A
 0005   | arroz       | 1.000 | 5.000 |     28 |          56 | A
 0001   | sandía      | 2.000 | 3.000 |     17 |          72 | A
 0004   | naranja     | 2.000 | 3.000 |     17 |          89 | B
 0003   | manzana     | 1.000 | 2.000 |     11 |         100 | C
(5 rows)

Explanation

First we generate partic with a window function. We do not round yet.

SELECT
  codigo,
  descripcion,
  costo,
  neto,
  neto/sum(neto) OVER ()*100 AS partic
FROM "abc-ventas"

This outputs

 codigo | descripcion | costo | neto  |         partic          
--------+-------------+-------+-------+-------------------------
 0001   | sandía      | 2.000 | 3.000 | 16.66666666666666666700
 0002   | lápiz       | 3.000 | 5.000 | 27.77777777777777777800
 0003   | manzana     | 1.000 | 2.000 | 11.11111111111111111100
 0004   | naranja     | 2.000 | 3.000 | 16.66666666666666666700
 0005   | arroz       | 1.000 | 5.000 | 27.77777777777777777800
(5 rows)

Then we generate the cumulative partic.

  SELECT
    codigo,
    descripcion,
    costo,
    neto,
    round(partic) AS partic,
    round(sum(partic) OVER (ORDER BY partic DESC, codigo)) AS "partic-acum"
  FROM (
    SELECT
      codigo,
      descripcion,
      costo,
      neto,
      neto/sum(neto) OVER ()*100 AS partic
    FROM "abc-ventas"
  ) AS t;

Generating this. Notice this is where we round.

 codigo | descripcion | costo | neto  | partic | partic-acum 
--------+-------------+-------+-------+--------+-------------
 0002   | lápiz       | 3.000 | 5.000 |     28 |          28
 0005   | arroz       | 1.000 | 5.000 |     28 |          56
 0001   | sandía      | 2.000 | 3.000 |     17 |          72
 0004   | naranja     | 2.000 | 3.000 |     17 |          89
 0003   | manzana     | 1.000 | 2.000 |     11 |         100
(5 rows)

Then we run the whole thing in a virtual table to generate the rankings.

This may look like a more complex query, but it's all done in a single table pass -- it's the fastest solution I think you can get out of this.

Related Question