Question 114681 in PostgreSQL Question posted in
The official documentation can be found here.

Is there an SQL (postgresql) command to access to the previous value that does not equal the current one?

I have table that looks like the following, with many emails as well. The column product can contain NULL or No values.

email date product
email1 2020-12-15 20:31:18 Product1
email1 2020-12-15 20:32:28 Product1
email1 2020-12-15 20:33:48 Product1
email1 2020-12-15 20:34:23 NULL
email1 2020-12-15 20:35:10 Product2
email1 2020-12-15 20:35:48 Product2
email1 2020-12-15 20:36:09 No
email1 2020-12-15 20:37:45 No
email1 2020-12-15 20:38:10 No
email1 2020-12-15 20:39:28 Product3

My intent is to assign to the NULL or No products the previous values of the product column that are not NULL or No. Like:

email date product
email1 2020-12-15 20:31:18 Product1
email1 2020-12-15 20:32:28 Product1
email1 2020-12-15 20:33:48 Product1
email1 2020-12-15 20:34:23 Product1
email1 2020-12-15 20:35:10 Product2
email1 2020-12-15 20:35:48 Product2
email1 2020-12-15 20:36:09 Product2
email1 2020-12-15 20:37:45 Product2
email1 2020-12-15 20:38:10 Product2
email1 2020-12-15 20:39:28 Product3

I have tried using WINDOWS functions like

SELECT email,
    date,
    product,
    CASE    
        WHEN product='No' THEN lag(product) OVER(PARTITION BY email ORDER BY date)
        WHEN product IS NULL THEN lag(product) OVER(PARTITION BY email ORDER BY date)
    END AS product2
    FROM table;

Obtaining

email date product product2
email1 2020-12-15 20:31:18 Product1
email1 2020-12-15 20:32:28 Product1
email1 2020-12-15 20:33:48 Product1
email1 2020-12-15 20:34:23 NULL Product1
email1 2020-12-15 20:35:10 Product2
email1 2020-12-15 20:35:48 Product2
email1 2020-12-15 20:36:09 No Product2
email1 2020-12-15 20:37:45 No No
email1 2020-12-15 20:38:10 No No
email1 2020-12-15 20:39:28 Product3
Tags:

2

Answers


  1. 0

    You may use a subquery as the following:

    SELECT T.email, T.date, 
   (SELECT D.product FROM table_name D 
    WHERE D.email=T.email AND D.date<=T.date AND D.product IS NOT NULL AND D.product<>'No' 
    ORDER BY D.date DESC 
    LIMIT 1) product
FROM table_name T
ORDER BY T.email, T.date

    See a demo.

    Login or Signup to reply.
  2. 0

    What you did is actually correct. I’d change it slightly though:

    SELECT
    email,
    date,
    CASE WHEN product = 'No'
        OR product IS NULL THEN
        lag(product) OVER (PARTITION BY email ORDER BY date)
    ELSE
        product
    END AS product
FROM
    table_name;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search