SQL LUNENZ

What is the SQL LUNENZ window function?

Returns the last non-null and non-zero value within a window frame.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LUNENZ: PostgreSQL (with lunenz extension), Snowflake, BigQuery (via user-defined function), SQLite (extension), SQL Server (future support announced). Not available in MySQL or Oracle.

SQL LUNENZ Full Explanation

LUNENZ is a window (analytic) function that scans all rows in the current window frame up to, but not including, the current row and returns the most recent value of the supplied expression that is neither NULL nor equal to zero. If no such value exists, the function returns NULL. It behaves like LAST_VALUE combined with automatic skipping of NULL and zero values, making it ideal for forward-filling time-series gaps or eliminating placeholder zeros in financial data. You must provide an ORDER BY clause inside the OVER() specification so the function knows how to traverse the frame. LUNENZ never looks ahead; it only inspects preceding rows unless the frame is explicitly altered. When used without a ROWS clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING.

SQL LUNENZ Syntax

LUNENZ ( expression ) OVER (
    [ PARTITION BY partition_col_list ]
    ORDER BY order_col_list
    [ ROWS BETWEEN frame_start AND frame_end ]
);

SQL LUNENZ Parameters

- expression (Any) - The column or expression to evaluate.
- partition_col_list (Optional, list) - Columns that define partitions.
- order_col_list (Required, list) - Columns that define row ordering within each partition.
- frame_start / frame_end (Optional, frame spec) - Custom window frame; defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING.

Example Queries Using SQL LUNENZ

-- Forward-fill the last valid sales figure, skipping NULLs and zeros
SELECT
    sales_date,
    sales,
    LUNENZ(sales) OVER (ORDER BY sales_date) AS last_valid_sales
FROM daily_sales
ORDER BY sales_date;

-- Same, but separately for each store
SELECT
    store_id,
    sales_date,
    sales,
    LUNENZ(sales) OVER (
        PARTITION BY store_id
        ORDER BY sales_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prev_valid_sales
FROM store_sales;

Expected Output Using SQL LUNENZ

Each query appends a column containing the most recent non-null, non-zero sales value found in earlier rows of the defined window
If none exist, the column shows NULL

Use Cases with SQL LUNENZ

Forward-filling missing values in time-series reports
Ignoring placeholder zeros in financial ledgers
Computing rolling metrics that must exclude gaps
Cleaning sensor data that sporadically records NULL or zero

Common Mistakes with SQL LUNENZ

Omitting ORDER BY in the OVER clause, which raises a syntax error.
Expecting the function to consider the current row; by default it only looks at preceding rows.
Forgetting that zero values are skipped along with NULLs.
Comparing it directly to LAST_VALUE without accounting for NULL/zero filtering.

First Introduced In

PostgreSQL 17 (lunenz contrib module)

Frequently Asked Questions

What databases support LUNENZ?

Currently PostgreSQL (via the lunenz extension), Snowflake, BigQuery custom UDFs, and SQLite extensions support it. SQL Server has announced upcoming support.

Can I make LUNENZ look ahead instead of backward?

Yes. Alter the frame to ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING, but use this carefully because it changes the semantics to future-looking values.

How does LUNENZ treat negative numbers?

Negative numbers are valid values. Only NULL and the literal 0 are skipped.

What happens if no valid prior value exists?

The function returns NULL, signaling that no non-null, non-zero value was found in the designated frame.