Window functions — the full set

# ======================================================================
# Every PostgreSQL window function Django wraps. `RowNumber` numbers rows,
# `PercentRank`/`CumeDist` give relative position, `Ntile` buckets, `Lead` peeks
# at the next row, and `FirstValue`/`LastValue`/`NthValue` read specific rows in
# the window. `LastValue`/`NthValue` need an explicit `frame=RowRange(None,
# None)` to see the whole partition, not just up to the current row. A custom
# frame also powers the running total.
# ======================================================================

# ----------------------------------------------------------------------
# ranking & value functions
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(
    rownum=Window(RowNumber(), order_by=F("price").desc()),
    pct=Window(PercentRank(), order_by=F("price").desc()),
    cume=Window(CumeDist(), order_by=F("price").desc()),
    half=Window(Ntile(num_buckets=2), order_by=F("price").desc()),
    nxt=Window(Lead("price"), order_by=F("price").desc()),
    first=Window(FirstValue("title"), order_by=F("price").desc()),
    last=Window(LastValue("title"), order_by=F("price").desc(),
                frame=RowRange(start=None, end=None)),
    second=Window(NthValue("title", nth=2), order_by=F("price").desc(),
                  frame=RowRange(start=None, end=None)),
).order_by("-price")

# SQL:
#   SELECT "examples_book"."id", "examples_book"."title", "examples_book"."author_id", "examples_book"."price", "examples_book"."pages", "examples_book"."published", "examples_book"."is_active", "examples_book"."tags", "examples_book"."data", "examples_book"."search", "examples_book"."title_upper", ROW_NUMBER() OVER (ORDER BY "examples_book"."price" DESC) AS "rownum", PERCENT_RANK() OVER (ORDER BY "examples_book"."price" DESC) AS "pct", CUME_DIST() OVER (ORDER BY "examples_book"."price" DESC) AS "cume", NTILE(2) OVER (ORDER BY "examples_book"."price" DESC) AS "half", LEAD("examples_book"."price", 1) OVER (ORDER BY "examples_book"."price" DESC) AS "nxt", FIRST_VALUE("examples_book"."title") OVER (ORDER BY "examples_book"."price" DESC) AS "first", LAST_VALUE("examples_book"."title") OVER (ORDER BY "examples_book"."price" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "last", NTH_VALUE("examples_book"."title", 2) OVER (ORDER BY "examples_book"."price" DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "second"
#   FROM "examples_book"
#   ORDER BY "examples_book"."price" DESC

# Result:
#   #1 The Lord of the Rings: pct=0.00 cume=0.25 ntile=1 next=$19.99 first='The Lord of the Rings' last='Postgres for Authors' 2nd='Harry Potter'
#   #2 Harry Potter: pct=0.33 cume=0.50 ntile=1 next=$14.50 first='The Lord of the Rings' last='Postgres for Authors' 2nd='Harry Potter'
#   #3 The Hobbit: pct=0.67 cume=0.75 ntile=2 next=$0.00 first='The Lord of the Rings' last='Postgres for Authors' 2nd='Harry Potter'
#   #4 Postgres for Authors: pct=1.00 cume=1.00 ntile=2 next=$None first='The Lord of the Rings' last='Postgres for Authors' 2nd='Harry Potter'

# ----------------------------------------------------------------------
# running total (custom frame: UNBOUNDED PRECEDING .. CURRENT ROW)
# ----------------------------------------------------------------------

# Django:
Book.objects.annotate(
    running=Window(Sum("price"), order_by=F("published").asc(),
                   frame=RowRange(start=None, end=0)),
).order_by("published")

# SQL:
#   SELECT "examples_book"."id", "examples_book"."title", "examples_book"."author_id", "examples_book"."price", "examples_book"."pages", "examples_book"."published", "examples_book"."is_active", "examples_book"."tags", "examples_book"."data", "examples_book"."search", "examples_book"."title_upper", SUM("examples_book"."price") OVER (ORDER BY "examples_book"."published" ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "running"
#   FROM "examples_book"
#   ORDER BY "examples_book"."published" ASC

# Result:
#   The Hobbit ($14.50) -> running total $14.50
#   The Lord of the Rings ($29.00) -> running total $43.50
#   Harry Potter ($19.99) -> running total $63.49
#   Postgres for Authors ($0.00) -> running total $63.49