How to get the first value from an undetermined group

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?

Answer

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