Relational Data - Aggregate Functions - Reference - Last Value

From FojiSoft Docs
Revision as of 18:43, 28 August 2024 by Chris.Hansen (talk | contribs) (Import ClickHouse Docs: Wed Aug 28 2024 14:43:53 GMT-0400 (Eastern Daylight Time))
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Selects the last encountered value, similar to anyLast, but could accept NULL. Mostly it should be used with Window Functions. Without Window Functions the result will be random if the source stream is not ordered.

examples

CREATE TABLE test_data
(
    a Int64,
    b Nullable(Int64)
)
ENGINE = Memory;

INSERT INTO test_data (a, b) Values (1,null), (2,3), (4, 5), (6,null)

example1

The NULL value is ignored at default.

select last_value(b) from test_data
┌─last_value_ignore_nulls(b)─┐
│                          5 │
└────────────────────────────┘

example2

The NULL value is ignored.

select last_value(b) ignore nulls from test_data
┌─last_value_ignore_nulls(b)─┐
│                          5 │
└────────────────────────────┘

example3

The NULL value is accepted.

select last_value(b) respect nulls from test_data
┌─last_value_respect_nulls(b)─┐
│                        ᴺᵁᴸᴸ │
└─────────────────────────────┘

example4

Stabilized result using the sub-query with ORDER BY.

SELECT
    last_value_respect_nulls(b),
    last_value(b)
FROM
(
    SELECT *
    FROM test_data
    ORDER BY a ASC
)
┌─last_value_respect_nulls(b)─┬─last_value(b)─┐
│                        ᴺᵁᴸᴸ │             5 │
└─────────────────────────────┴───────────────┘