Início / dba / Perguntas / 255832
Accepted
TheDemonLord
Asked: 2019-12-18 14:19:05 +0800 CST

Multiplique vários valores discretos em várias colunas e depois some

  • 772

Eu preciso multiplicar valores de duas tabelas e somar os resultados. O banco de dados é Postgres ou MSSQL (longa história).

MesaBought_Products

ID | Name | Customer_ID | Product_ID | Foo | Bar | Blarg
1  | test | 123         | 321        | NULL| 1   | NULL
2  | tast | 123         | 231        | 5   | NULL| 20
3  | tost | 456         | 321        | NULL| 3   | NULL

MesaPrice

ID | Item | Product_ID | Cost
1  | Foo  | 321        | 10
2  | Bar  | 321        | 5
3  | BASE | 321        | 100
4  | BASE | 231        | 50
5  | Blarg| 231        | 0.5
6  | Foo  | 231        | 1

Eu preciso de um resultado assim:

Customer | Products | Revenue
123      | 321, 231 | 170

Para o Cliente 123 existem 2 produtos: 321 e 231, com preço base de 100 e 50 respectivamente; mais 1 x item 'Bar' em 5; mais 5 x itens 'Foo' em 1; e 20 x itens 'Blarg' a 0,5.

Isto é o que tenho para começar:

select sum(p.cost) from price p
join Bought_Products bp on bp.product_id = p.product_ID
where p.item = 'BASE'

Eu preciso adicionar os valores relevantes 'Foo', 'Bar' ou 'Blarg' Bought_Productsmultiplicados pelo respectivo Price.Cost(correspondendo ao cabeçalho da coluna ao item).

Ainda não usei funções matemáticas básicas em SQL. Não tenho certeza de como unir as 2 tabelas para depois multiplicar os valores. Com subconsultas e junções subsequentes?

sql-server postgresql

1 respostas

  1. Best Answer

    Esta consulta produz o resultado desejado para cada cliente na tabela:

    SELECT customer_id AS customer
     , string_agg(DISTINCT product_id::text, ', ') AS products
     , sum(cost * CASE p.item
                     WHEN 'BASE'  THEN 1
                     WHEN 'Foo'   THEN b.foo
                     WHEN 'Bar'   THEN b.bar
                     WHEN 'Blarg' THEN b.blarg
                  END) AS revenue
FROM   bought_products b
LEFT   JOIN price p USING (product_id)
-- WHERE  p.item = ANY ('{BASE,Foo,Bar,Blarg}')
GROUP  BY 1;

    db<>fique aqui

    Só precisamos nos juntar uma vez. A mágica acontece na CASEexpressão para multiplicar dependendo do Item.

    Eu fiz isso LEFT [OUTER] JOINpara manter todos os produtos no resultado, mesmo sem nenhuma linha de preço correspondente. Use [INNER] JOINpara removê-los em vez disso. E/ou ativar a WHEREcláusula comentada para o mesmo efeito.

    A linha com ID 1na Pricetabela se destaca, btw, pois a linha relacionada em Bought_Productstem Foo IS NULL.

    O design relacional parece um pouco misterioso. Espero que você tenha bons motivos.

    • 2

relate perguntas