CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformpartition
John Thomas
John Thomas

Reputation: 1105

How to combine data from different variables together in SQL?

Let's say I have data like this:

USER_ID                 TIMESTAMP   data  data2
   0001   2021-05-09 12:13:03.445            44
   0001   2021-05-09 13:13:03.445    rob    
   0001   2021-05-09 11:13:03.445       
   0002   2021-05-09 09:13:03.445  perry    333
   0002   2021-05-09 12:13:03.445   carl    333
   0003   2021-05-09 16:13:03.445  mitch      1
   0003   2021-05-09 17:13:03.445
   0002   2021-05-09 16:13:03.445  mitch      5

All I want to do is collect the most recent non-null value from each column and condense them into a table with each row being an entry.

Final result:

USER_ID   data  data2
   0001    rob     44 
   0003  mitch      1
   0002  mitch      5

Here's what I have but it's not complete:

WITH form AS (
    select b.*,
        rank() over (
            partition by user_id
            order by timestamp DESC
        ) as num
    FROM b
SELECT *
FROM b
WHERE num = 1

Upvotes: 1

Views: 77

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

Related: Equivalent for Keep in Snowflake:

It could be achieved with:

WITH cte(user_id, timestamp, "data", data2) AS (
  SELECT *
  FROM (VALUES ('0001','2021-05-09 12:13:03.445'::timestamp,NULL,44),
   ('0001','2021-05-09 13:13:03.445'::timestamp,'rob',NULL),
   ('0001','2021-05-09 11:13:03.445'::timestamp,NULL,NULL),
   ('0002','2021-05-09 09:13:03.445'::timestamp,'perry',333),
   ('0002','2021-05-09 12:13:03.445'::timestamp,'carl',333),
   ('0003','2021-05-09 16:13:03.445'::timestamp,'mitch',1),
   ('0003','2021-05-09 17:13:03.445'::timestamp,NULL,NULL),
   ('0002','2021-05-09 16:13:03.445'::timestamp,'mitch',5)
  ) 
)
SELECT user_id,
  (ARRAY_AGG("data") WITHIN GROUP (ORDER BY timestamp DESC))[0]::STRING AS "data",
  (ARRAY_AGG(data2)  WITHIN GROUP (ORDER BY timestamp DESC))[0] AS data2
FROM cte
GROUP BY user_id
ORDER BY user_id;

Output:

+---------+----------+-------+
| USER_ID |   data   | data2 |
+---------+----------+-------+
|    0001 | rob      |    44 |
|    0002 | mitch    |     5 |
|    0003 | mitch    |     1 |
+---------+----------+-------+

ARRAY_AGG by default omits NULLs and it is sorted by timestamp descending. Once array per user_id is created it is a matter of accesing first element(element with index [0]).

Upvotes: 1

Mike Walton
Mike Walton

Reputation: 7339

You can use IGNORE NULL using the LAST_VALUE or FIRST_VALUE function. For your dataset:

WITH x AS (
SELECT *
FROM (VALUES ('0001','2021-05-09 12:13:03.445'::timestamp,NULL,44),
   ('0001','2021-05-09 13:13:03.445'::timestamp,'rob',NULL),
   ('0001','2021-05-09 11:13:03.445'::timestamp,NULL,NULL),
   ('0002','2021-05-09 09:13:03.445'::timestamp,'perry',333),
   ('0002','2021-05-09 12:13:03.445'::timestamp,'carl',333),
   ('0003','2021-05-09 16:13:03.445'::timestamp,'mitch',1),
   ('0003','2021-05-09 17:13:03.445'::timestamp,NULL,NULL),
   ('0002','2021-05-09 16:13:03.445'::timestamp,'mitch',5)
  ) x (id, ts, data, data2)
)

You'd do something like this:

SELECT id,
       LAST_VALUE(data) IGNORE NULLS OVER (PARTITION BY ID ORDER BY ts) as data_last,
       LAST_VALUE(data2) IGNORE NULLS OVER (PARTITION BY ID ORDER BY ts) as data2_last
FROM x
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts) = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Hmmm . . . this is where ignore nulls is really useful -- but Postgres doesn't support that (yet??).

Instead, you can use arrays ordering the non-NULL values first and then by timestamp:

select user_id,
       (array_agg(data order by (data is not null) desc, timestamp desc))[1],
       (array_agg(data2 order by (data2 is not null) desc, timestamp desc))[1]
from t
group by user_id;

Here is a db<>fiddle.

Upvotes: 1

Related Questions