How to assign row_number to missing values in SQL-CodePudding

I want to assign row_number() to missing values as well. I know that row_number will assign sequence to the non-missing values. How can we approach this problem?

Code Snippet

SELECT id, date,sum(purchase) as purch, 
row_number() over(partition by id order by date desc) as rown 
FROM table 
where
date in (current_date(),current_date()-1,current_date()-2)
OR
date between current_date()-364 and current_date()-365
group by id,date
order by date desc

I want to use the values of sum(purchase) for particular dates, so, I want the row_number for each dates even no purchase has happened at that particular day(value could get replace with NULL).

output should look like-

row_number       purchase       date
  1               23         current_date
  2               24         current_date-1
  3               null       current_date-2(let's say this is our missing date data)
  4               23         current_date-364
  5               null       current_date-365(let's say this date is mising in our data)

CodePudding user response：

You first generate a list of dates then left join it with your table.

WITH daterange AS (
  SELECT date
  FROM UNNEST(
      GENERATE_DATE_ARRAY(DATE('2021-01-01'), CURRENT_DATE(), INTERVAL 1 DAY)
  )   AS date
),  
yourtable AS (
  SELECT DATE'2022-09-15' AS date, 23 AS purchase UNION ALL
  SELECT DATE'2022-09-14' AS date, 24 AS purchase UNION ALL
  SELECT DATE'2021-09-16' AS date, 23 AS purchase
)

SELECT 
  ROW_NUMBER() OVER(ORDER BY t1.date DESC) AS row_number, 
  t2.purchase, 
  t1.date
FROM daterange t1
LEFT JOIN yourtable t2 ON t1.date = t2.date
WHERE t1.date IN (current_date(), current_date()-1, current_date()-2)
OR (t1.date BETWEEN current_date()-365 AND current_date()-364)

Query Results:

row_number  purchase    date
1           23          2022-09-15
2           24          2022-09-14
3           null        2022-09-13
4           23          2021-09-16
5           null        2021-09-15