Início / dba / Perguntas / 154586
Accepted
Stranger6667
Asked: 2016-11-09 04:33:24 +0800 CST

SOMA em linhas distintas com várias junções

  • 772

Esquema :

CREATE TABLE "items" (
  "id"            SERIAL                   NOT NULL PRIMARY KEY,
  "country"       VARCHAR(2)               NOT NULL,
  "created"       TIMESTAMP WITH TIME ZONE NOT NULL,
  "price"         NUMERIC(11, 2)           NOT NULL
);
CREATE TABLE "payments" (
  "id"      SERIAL                   NOT NULL PRIMARY KEY,
  "created" TIMESTAMP WITH TIME ZONE NOT NULL,
  "amount"  NUMERIC(11, 2)           NOT NULL,
  "item_id" INTEGER                  NULL
);
CREATE TABLE "extras" (
  "id"      SERIAL                   NOT NULL PRIMARY KEY,
  "created" TIMESTAMP WITH TIME ZONE NOT NULL,
  "amount"  NUMERIC(11, 2)           NOT NULL,
  "item_id" INTEGER                  NULL
);

Dados :

INSERT INTO items VALUES
  (1, 'CZ', '2016-11-01', 100),
  (2, 'CZ', '2016-11-02', 100),
  (3, 'PL', '2016-11-03', 20),
  (4, 'CZ', '2016-11-04', 150)
;
INSERT INTO payments VALUES
  (1, '2016-11-01', 60, 1),
  (2, '2016-11-01', 60, 1),
  (3, '2016-11-02', 100, 2),
  (4, '2016-11-03', 25, 3),
  (5, '2016-11-04', 150, 4)
;
INSERT INTO extras VALUES
  (1, '2016-11-01', 5, 1),
  (2, '2016-11-02', 1, 2),
  (3, '2016-11-03', 2, 3),
  (4, '2016-11-03', 3, 3),
  (5, '2016-11-04', 5, 4)
;

Então nós temos:

  • 3 itens em CZ em 1 em PL
  • 370 ganhos em CZ e 25 em PL
  • Custo de 350 em CZ e 20 em PL
  • 11 extras ganhos em CZ e 5 extras ganhos em PL

Agora quero obter respostas para as seguintes perguntas:

  1. Quantos itens tivemos no mês passado em cada país?
  2. Qual foi o valor total ganho (soma de pagamentos.quantias) em cada país?
  3. Qual foi o custo total (soma de itens.preço) em cada país?
  4. Qual foi o total de ganhos extras (soma de extras.amount) em cada país?

Com a seguinte consulta ( SQLFiddle ):

SELECT
  country                  AS "group_by",
  COUNT(DISTINCT items.id) AS "item_count",
  SUM(items.price)         AS "cost",
  SUM(payments.amount)     AS "earned",
  SUM(extras.amount)       AS "extra_earned"
FROM items
  LEFT OUTER JOIN payments ON (items.id = payments.item_id)
  LEFT OUTER JOIN extras ON (items.id = extras.item_id)
GROUP BY 1;

Os resultados estão errados:

 group_by | item_count |  cost  | earned | extra_earned
----------+------------+--------+--------+--------------
 CZ       |          3 | 450.00 | 370.00 |        16.00
 PL       |          1 |  40.00 |  50.00 |         5.00

Custo e ganho extra para CZ são inválidos - 450 em vez de 350 e 16 em vez de 11. Custo e ganho para PL também são inválidos - são duplicados.

Eu entendo, que no caso LEFT OUTER JOINhaverá 2 linhas para item com items.id = 1 (e assim por diante para outras correspondências), mas não sei como construir uma consulta adequada.

Perguntas :

  1. Como evitar resultados errados na agregação em consultas em várias tabelas?
  2. Qual é a melhor maneira de calcular a soma sobre valores distintos (items.id nesse caso)?

Versão do PostgreSQL : 9.6.1

postgresql join

1 respostas

  1. Best Answer

    Como pode haver vários paymentse vários extraspor item, você encontra uma "junção cruzada de proxy" entre essas duas tabelas. Agregue linhas por item_id antes de ingressar iteme tudo deve estar correto:

    SELECT i.country         AS group_by
     , COUNT(*)          AS item_count
     , SUM(i.price)      AS cost
     , SUM(p.sum_amount) AS earned
     , SUM(e.sum_amount) AS extra_earned
FROM  items i
LEFT  JOIN (
   SELECT item_id, SUM(amount) AS sum_amount
   FROM   payments
   GROUP  BY 1
   ) p ON p.item_id = i.id
LEFT  JOIN (
   SELECT item_id, SUM(amount) AS sum_amount
   FROM   extras
   GROUP  BY 1
   ) e ON e.item_id = i.id
GROUP BY 1;

    Considere o exemplo do "mercado de peixes":

    Para ser preciso, SUM(i.price)seria incorreto depois de se juntar a uma única tabela n, que multiplica cada preço pelo número de linhas relacionadas. Fazer isso duas vezes só piora - e também potencialmente caro computacionalmente.

    Ah, e como não multiplicamos as linhas itemsagora, podemos usar o mais barato count(*)em vez de count(DISTINCT i.id). ( idser NOT NULL PRIMARY KEY.)

    db<>fiddle aqui
    Velho sqlfiddle

    Mas se eu quiser filtrar por items.created?

    Endereçando seu comentário.

    Depende. Podemos aplicar o mesmo filtro para payments.createde extras.created?

    Se sim, basta adicionar os filtros nas subconsultas também. (Não parece provável neste caso.)

    Se não, mas ainda estamos selecionando a maioria dos itens , a consulta acima ainda seria mais eficiente. Algumas das agregações nas subconsultas são eliminadas nas junções, mas isso ainda é mais barato do que consultas mais complexas.

    Se não, e estamos selecionando uma pequena fração de itens, sugiro subconsultas ou LATERALjunções correlacionadas. Exemplos:

    • 15

relate perguntas