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

user5gTK 0
Viewed 31

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
function windows sql postgresql
2 Answers

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.

userGpjR0

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;
Saya0
Related