Bring the most recent record with conditionals - PostgreSQL

Question

Bring the most recent record with conditionals - PostgreSQL

Navigation

#1 by (2 votes)
#2 by (0 votes)
#3 by (-1 votes)

2

I have a table with product information (product code, customer code, date of purchase, purchase value, etc.) and I need to pull the following information:

1- All customers that the last purchase was a specific product. That is, I need to analyze when the customer's last purchase was and see if that last purchase was for that particular product. If so, bring it, if not, ignore it.

Example:

Product = Soap

Bring all the customers who bought soap on your last purchase. Customer 1, 2 and 3 bought soap days X, Y and Z. It must necessarily be the customer's last purchase, if he made some purchases later and did not buy soap, he can not show up in my appointment.

Did you understand?

The following is the structure of the database:

nome_prod   cod_prod   cod_cliente   data_compra
Sabonete    1          338           30/09/2017
Pão         2          338           02/03/2018
Alho        3          338           15/12/2017
Cenoura     4          338           01/01/2018
Água        5          587           30/09/2017
Sabonete    2          587           02/03/2018
Presunto    6          587           15/12/2017
Alface      7          856           30/09/2017
Sabonete    2          856           02/03/2018
Queijo      8          856           15/12/2017

sql postgresql

asked by anonymous 02.04.2018 / 20:48

3 answers

WebPack + NodeJS import bootstrap + jquery Threaded Exceptions in Python

score 2 · Answer 1

I made a subselect to bring the last purchases of each customer independent of the product , then I made a join with the original table to bring only the results that were from the last purchase and that this purchase is that of the Soap product.

SQLFiddle - Online example:

SELECT Venda.cod_cliente
  , Venda.nome_prod
  , Venda.cod_prod
  , Venda.data_compra
FROM (
  SELECT cod_cliente
   , MAX(data_compra) AS data_compra
  FROM Venda
  GROUP BY cod_cliente
) UltimaVenda
JOIN Venda
  ON Venda.data_compra = UltimaVenda.data_compra
  AND Venda.cod_cliente = UltimaVenda.cod_cliente
WHERE Venda.nome_prod = 'Sabonete'

score 0 · Answer 2

You can combine window function ROW_NUMBER with subquery (in the example below, I'm using a CTE )

WITH CTE_Compras AS (
    SELECT
        ROW_NUMBER() OVER (PARITION BY cod_cliente ORDER BY data_compra DESC) AS ordem
        nome_prod, 
        cod_prod, 
        cod_cliente, 
        data_compra
    FROM Compras
)

SELECT 
    nome_prod, 
    cod_prod, 
    cod_cliente, 
    data_compra
FROM CTE_Compras 
WHERE ordem = 1 AND nome_prod = 'Sabonete'

score -1 · Answer 3

Assuming you have something like:

CREATE TABLE tbl_compra
(
    id BIGINT PRIMARY KEY,
    nome_prod TEXT NOT NULL,
    cod_prod BIGINT NOT NULL,
    cod_cliente BIGINT NOT NULL,
    data_compra DATE NOT NULL
);

INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  1, 'Sabonete', 1, 338, to_date('30/09/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  2, 'Pão',      2, 338, to_date('02/03/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  3, 'Alho',     3, 338, to_date('15/12/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  4, 'Cenoura',  4, 338, to_date('01/01/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  5, 'Água',     5, 587, to_date('30/09/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  6, 'Sabonete', 1, 587, to_date('02/03/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  7, 'Presunto', 6, 587, to_date('15/12/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  8, 'Alface',   7, 856, to_date('30/09/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES (  9, 'Sabonete', 1, 856, to_date('02/03/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES ( 10, 'Queijo',   8, 856, to_date('15/12/2017','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES ( 11, 'Sabonete', 1, 338, to_date('02/01/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES ( 12, 'Sabonete', 1, 879, to_date('12/02/2018','DD/MM/YYYY') );
INSERT INTO tbl_compra ( id, nome_prod, cod_prod, cod_cliente, data_compra ) VALUES ( 13, 'Sabonete', 1, 879, to_date('23/12/2017','DD/MM/YYYY') );

You can use the max() function combined with the GROUP BY clause:

SELECT
    cod_cliente,                          -- CODIGO DO CLIENTE
    count(1) AS qtd_compras,              -- QUANTIDADE TOTAL DE SABONETES COMPRADOS
    max(data_compra) AS data_ultima_compra -- DATA DA ULTIMA COMPRA DE UM SABONETE
FROM
    tbl_compra
WHERE
    nome_prod = 'Sabonete'
GROUP BY
    cod_cliente;

Output:

| cod_cliente | qtd_compras | data_ultima_compra |
|-------------|-------------|--------------------|
|         338 |           2 |         2018-01-02 |
|         587 |           1 |         2018-03-02 |
|         856 |           1 |         2018-03-02 |
|         879 |           2 |         2018-02-12 |

SQLFiddle: link