FIRST_VALUE / LAST_VALUE
Return the first or last value from a window frame. FIRST_VALUE with the default frame does what you'd expect. LAST_VALUE almost never does. The default frame stops at the current row, so without an explicit frame you'll get the current row's value back, not the partition's actual last value.
Syntax #
sql
SELECT key_col, ts_col, metric,
FIRST_VALUE(metric) OVER (PARTITION BY key_col ORDER BY ts_col) AS first_metric,
LAST_VALUE(metric) OVER (
PARTITION BY key_col
ORDER BY ts_col
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_metric
FROM table_name;Example #
Loading SQL editor...
Warning
**LAST_VALUE without an explicit frame returns the current row's value, not the partition's last value.** The default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which stops at "now." Either add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to extend the frame, or (cleaner) rewrite as FIRST_VALUE(...) OVER (... ORDER BY ts DESC).