I want to get the same first/last not null value for all the records in a same window. I’ve prepared this example data:
WITH sample_data AS (
SELECT 1 AS id, NULL AS value, CURRENT_TIMESTAMP() AS update_ts
UNION ALL
SELECT 1, "Good", TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
UNION ALL
SELECT 1, "Bad", TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY)
)
SELECT
id,
value,
FIRST_VALUE(value IGNORE NULLS) OVER (ids) AS first_valid_value,
LAST_VALUE(value IGNORE NULLS) OVER (ids) AS last_valid_value,
update_ts
FROM sample_data
WINDOW ids AS (PARTITION BY id ORDER BY update_ts DESC)
This is the output for this query:
| id | value | first_valid_value | last_valid_value | update_ts |
|---|---|---|---|---|
| 1 | 2024-12-13 12:37:05.762489 UTC | |||
| 1 | Good | Good | Good | 2024-12-12 12:37:05.762489 UTC |
| 1 | Bad | Good | Bad | 2024-12-11 12:37:05.762489 UTC |
There are two things which are confusing me:
- I added the
INGORE NULLSpart because I want to get a “valid” value, so I was expecting the first row (with NULLvaluefield), to inherit the “Good” value in thefirst_valid_valuefield, and “Bad” forlast_valid_value - In row #2, I was surprised that field
last_valid_valuedidn’t inherited the “Bad” value, as the window function is defining
This is the output I want to achieve and which I was expecting:
| id | value | first_valid_value | last_valid_value | update_ts |
|---|---|---|---|---|
| 1 | Good | Bad | 2024-12-13 12:37:05.762489 UTC | |
| 1 | Good | Good | Bad | 2024-12-12 12:37:05.762489 UTC |
| 1 | Bad | Good | Bad | 2024-12-11 12:37:05.762489 UTC |
Why is this happening, and how could I achieve the final result?
You need to specify the window frame clause:
SELECT
id,
value,
FIRST_VALUE(value IGNORE NULLS) OVER (ids) AS first_valid_value,
LAST_VALUE(value IGNORE NULLS) OVER (ids) AS last_valid_value,
update_ts
FROM sample_data
WINDOW ids AS (PARTITION BY id ORDER BY update_ts DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
Output:
From the docs:
For aggregate analytic functions, if the
ORDER BYclause is present but the window frame clause is not, the following window frame clause is used by default:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
2