Replace NULL values per partition

Question

I want to fill NULL values in device column for each session_id with an associated non-NULL value. How can I achieve that?

Here is the sample data:

+------------+-------+---------+
| session_id | step  | device  |
+------------+-------+---------+
| 351acc     | step1 |         |
| 351acc     | step2 |         |
| 351acc     | step3 | mobile  |
| 351acc     | step4 | mobile  |
| 350bca     | step1 | desktop |
| 350bca     | step2 |         |
| 350bca     | step3 |         |
| 350bca     | step4 | desktop |
+------------+-------+---------+

Desired output:

+------------+-------+---------+
| session_id | step  | device  |
+------------+-------+---------+
| 351acc     | step1 | mobile  |
| 351acc     | step2 | mobile  |
| 351acc     | step3 | mobile  |
| 351acc     | step4 | mobile  |
| 350bca     | step1 | desktop |
| 350bca     | step2 | desktop |
| 350bca     | step3 | desktop |
| 350bca     | step4 | desktop |
+------------+-------+---------+

score 2 · Answer 1 · answered Apr 08 '21 at 15:48

According to your data sample, there is one device per session, so you could just add a subquery to get the value from the other rows

WITH j (session_id, step, device) AS (
  VALUES ('351acc','step1',NULL),
         ('351acc','step2',NULL),
         ('351acc','step3','mobile'),
         ('351acc','step4','mobile'),
         ('350bca','step1','desktop'),
         ('350bca','step2',NULL),
         ('350bca','step3',NULL),
         ('350bca','step4','desktop')
) 
SELECT session_id,step,
  (SELECT DISTINCT device 
   FROM j q2
   WHERE q2.session_id = q1.session_id AND q2.device IS NOT NULL) AS device
FROM j q1 ORDER BY session_id,step;

 session_id | step  | device  
------------+-------+---------
 350bca     | step1 | desktop
 350bca     | step2 | desktop
 350bca     | step3 | desktop
 350bca     | step4 | desktop
 351acc     | step1 | mobile
 351acc     | step2 | mobile
 351acc     | step3 | mobile
 351acc     | step4 | mobile
(8 Zeilen)

Demo: db<>fiddle

Erwin Brandstetter · Accepted Answer · 2021-04-08T16:08:27.177

The window function first_value() with the right ordering is probably cheapest:

SELECT session_id, step
     , COALESCE(device
              , first_value(device) OVER (PARTITION BY session_id ORDER BY device IS NULL, step)
               ) AS device
FROM   tbl
ORDER  BY session_id DESC, step;

db<>fiddle here

ORDER BY device IS NULL, step sorts NULL values last, so the earliest step with a notnull value is picked. See:

Sorting null values after all others, except special

If notnull devices per session_id are always the same, you can simplify to just ORDER BY device IS NULL. And you don't need COALESCE.

eshirvana · Answer 3 · 2021-04-08T15:32:43.510

1

select session_id, step,coalesce(device, max(device) over (partition by session_id order by step desc)) device
from table

edited Apr 08 '21 at 15:32

answered Apr 08 '21 at 15:25

eshirvana

23,227
3
22
38

Replace NULL values per partition

3 Answers3