Home > Blockchain >  How to get the first value from an undetermined group
How to get the first value from an undetermined group

Time:11-16

Can anyone help me with this, I'm using Athena, and I was looking for some solution, well Athena doesn't support variables, so how can I fill sessao as well? enter image description here

CodePudding user response:

Assuming empty values mean nulls - you can use last_value window function with ignore nulls option:

WITH dataset(value, ts) AS (
    values ('a', timestamp '2012-08-08 01:00'),
        (null, timestamp '2012-08-08 01:01'),
        (null, timestamp '2012-08-08 01:02'),
        ('b', timestamp '2012-08-08 02:00'),
        (null, timestamp '2012-08-08 02:01')
)


SELECT coalesce(value, last_value(value) ignore nulls over (order by ts)) value, ts
FROM dataset

Output:

value ts
a 2012-08-08 01:00:00.000
a 2012-08-08 01:01:00.000
a 2012-08-08 01:02:00.000
b 2012-08-08 02:00:00.000
b 2012-08-08 02:01:00.000
  • Related