sqlalchemy ignore nulls in window function

Question

I am using sqlalchemy to generate queries and am unsure how to insert the keyword "IGNORE NULLS" between the last_value() window function and the over() function. The sqlalchemy code I have now looks like this:

func.last_value(employee.created).over(partition_by=employee.id)

this generates the following sql in the select statement

LAST_VALUE(employee.created) OVER (PARTITION BY employee.id)

Ideally we would like to insert the keyword "IGNORE NULLS" between the last_value and over function, like this:

LAST_VALUE(employee.created) IGNORE NULLS OVER (PARTITION BY employee.id)

However, I am unsure how to do this using the sqlalchemy library. I am compiling sql for the oracle dialect.

Answer 1

I wanted to post a hacky solution in case anyone else is having trouble with this:

from sqlalchemy import func, text

func.last_value(employee.created.op('IGNORE')(text('NULLS'))).over(partition_by=employee.id)

Answer 2

A different approach is to define a new GenericFunction.

import sqlalchemy as sa

class LastValueIgnoreNulls(sa.sql.functions.GenericFunction):
    package = "yourpackage"
    identifier = "last_value_ignore_nulls"
    name = "LAST_VALUE"


@sa.ext.compiler.compiles(LastValueIgnoreNulls)
def compile_last_value(
    element: LastValueIgnoreNulls, compiler: sa.sql.compiler.SQLCompiler
) -> str:
    return f"{element.name}({compiler.process(element.clauses)}) IGNORE NULLS"

sa.func.yourpackage.last_value_ignore_nulls(employee.created).over(partition_by=employee.id)

This will output the equivalent SQL:

LAST_VALUE(employee.name) IGNORE NULLS OVER(PARTITION BY employee.id)