问题查询postgresql

Question

当我想在posgretsql中生成一个查询时，我遇到了问题，我正在尝试，我不知道如何计算以下内容

我的表有以下数据：

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

我需要计算以下信息

PARTIC= NETO /SUM(总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%

现在计算"partic-acum“命令DESC "partic”

PARTIC-ACUM= PARTIC +以前的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%

一旦生成结果，我想把它分类为A，B，C

• 如果值为0到80: a
• 如果数值为81至95: B
• 如果值为95到100: C

这是我想要的输出

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

我的查询如下

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;

Answer 1

首先，您不应该引用PostgreSQL中的表名或任何标识符。这是个糟糕的做法。

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;

输出

 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)

解释

首先，我们使用窗口函数生成partic。我们还没有周旋。

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

这输出

 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)

然后我们生成累积的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;

产生这个。注意这是我们绕过去的地方。

 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)

然后我们在一个虚拟表格中运行整个程序来生成排名。

这看起来可能是一个更复杂的查询，但所有这些都是在一次表传递中完成的--这是我认为您可以解决这个问题的最快的解决方案。

Answer 2

尝尝这个

With CTE_preselect AS (
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")

SELECT     
codigo,
descripcion,
cantidad,
costo,
neto,
PARTICIPACION,
CASE WHEN PARTICIPACION <= 80 THEN 'A'
     WHEN PARTICAPACION > 80 AND PARTICAPACION <= 95 THEN 'B'
     WHEN PARTICAPACION > 95 THEN 'C'
     END AS 'CLASIF'
FROM preselect
ORDER BY participacion DESC;