I have some data : sales amount for each day, but sometimes I have missing data so no record (for example on the weekend, but not only). For these dates, I want to replace the null value with the last known value. I create a reference table with all calendar dates and a boolean to tell me if I have data for this day.

For example with this reference table :

I want this outcome :

I tried things with LEAD but I don't know how to add a condition like 'where is_data_present = 1'

CodePudding user response：

Basically, you don't need a window function for this.

The coalsesce is for the case that the first row is 0, and so has no value that is prior to it

SELECT
    "Date", "is_data_present",
    COALESCE((SELECT "Date" FROM table1 WHERE "Date" <= Tab1."Date" AND "is_data_present" = 1 ORDER BY "Date" DESC LIMIT 1 ),"Date") date_to_use
   FROM table1 tab1

CodePudding user response：

I tried things with LEAD but I don't know how to add a condition like 'where is_data_present = 1'

In addtion to @nbk's approach, you might consider FIRST_VALUE or LAST_VALUE if you want to use a window function since LEAD or LAG doesn't support IGNORE NULLS in it.

WITH sample_table AS (
  SELECT '27/10/2022' date, 1 is_data_present UNION ALL
  SELECT '28/10/2022' date, 1 is_data_present UNION ALL
  SELECT '29/10/2022' date, 0 is_data_present UNION ALL
  SELECT '30/10/2022' date, 0 is_data_present
)
SELECT *,
       LAST_VALUE(IF(is_data_present = 1, date, NULL) IGNORE NULLS) OVER (ORDER BY date) date_to_use,
  FROM sample_table;

 ------------ ----------------- ------------- 
|    date    | is_data_present | date_to_use |
 ------------ ----------------- ------------- 
| 27/10/2022 |               1 | 27/10/2022  |
| 28/10/2022 |               1 | 28/10/2022  |
| 29/10/2022 |               0 | 28/10/2022  |
| 30/10/2022 |               0 | 28/10/2022  |
 ------------ ----------------- -------------