Skip to content

Window Functions

Jump to bottom
Konstantin Triger edited this page Aug 20, 2019 · 5 revisions

From PostgreSQL documentation:

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

Another great article explaining window functions by example.

Let's examine window functions usage with FluentJPA by example:

FluentQuery query = FluentJPA.SQL((Observation o) -> {

    WindowDef w = PARTITION(BY(o.getSubject())).ORDER(BY(o.getTime()))
            .ROWS(FrameBounds.UNBOUNDED_PRECEDING);

    WindowDef w1 = PARTITION(BY(o.getSubject())).ORDER(BY(o.getTime()))
            .ROWS()
            .BETWEEN(FrameBounds.UNBOUNDED_PRECEDING)
            .AND(4, FrameBounds.FOLLOWING)
            .EXCLUDE_CURRENT_ROW();

    WindowDef w2 = ORDER(BY(o.getTime()));

    WindowDef w3 = windowFrame().GROUPS(FrameBounds.CURRENT_ROW);

    Integer first = alias(aggregateBy(FIRST_VALUE(o.getVal())).OVER(w), Stats::getFirst);
    Integer last = alias(aggregateBy(LAST_VALUE(o.getVal())).OVER(w1), Stats::getLast);
    Integer nth = alias(aggregateBy(NTH_VALUE(o.getVal(), 2)).FILTER(WHERE(o.getVal() == 7))
                                   .OVER(w2), Stats::getNth);
    Integer nth1 = alias(aggregateBy(NTH_VALUE(o.getVal(), 4)).OVER(w3), Stats::getNth4);

    SELECT(o.getTime(), o.getSubject(), o.getVal(), first, last, nth, nth1);
    FROM(o);
});

This example summarizes all the syntactical scenarios:

  • Start with aggregateBy() and pass it an expression.
  • Optionally FILTER()/WITHIN_GROUP() (supported by PostgreSQL only).
  • Then call OVER() and pass it a window definition, which is created either:
    • by PARTITION() function.
    • by ORDER() function.
    • by windowFrame() directive.

Getting Started

Examples

Basic SQL DML Statements

Advanced SQL DML Statements

Advanced Topics

Clone this wiki locally