SQL Expressions: Window functions

Overview
Named window

Overview

contents

There are five built-in aggregate window functions corresponding to each aggregate function: COUNT(*) OVER, SUM OVER, AVG OVER, MIN OVER, MAX OVER.

// Aggregate functions
func CountOver(window Window) NumberField
func SumOver(field interface{}, window Window) NumberField
func AvgOver(field interface{}, window Window) NumberField
func MinOver(field interface{}, window Window) NumberField
func MaxOver(field interface{}, window Window) NumberField

To use a window function, you must define a window:

// (PARTITION BY tbl.column_1, tbl.column_2
// ORDER BY tbl.column_1 DESC
// RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING)
window := sq.PartitionBy(tbl.COLUMN_1, tbl.COLUMN_2).
    OrderBy(tbl.COLUMN_1.Desc()).
    Frame("RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING")

// (ORDER BY tbl.column_3 DESC NULLS FIRST)
window := sq.OrderBy(tbl.COLUMN_3.Desc().NullsFirst())

// (PARTITION BY tbl.column_4)
window := sq.PartitionBy(tbl.COLUMN_4)

You can then pass the windows as arguments to the window functions:

// SELECT COUNT(*) OVER ()
sq.Select(sq.CountOver(Window{})) // empty window

// SELECT SUM(tbl.column_1) OVER (PARTITION BY tbl.COLUMN_2)
sq.Select(sq.SumOver(tbl.COLUMN_1, sq.PartitionBy(tbl.COLUMN_2)))

// SELECT AVG(tbl.column_3) OVER (
//     PARTITION BY tbl.column_1
//     ORDER BY tbl.column_2
//     RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
// )
window := sq.PartitionBy(tbl.COLUMN_1).
    OrderBy(tbl.COLUMN_2.Desc()).
    Frame("RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING")
sq.Select(sq.AvgOver(tbl.COLUMN_3, window))

Here are the rest of the available window functions

// Window functions
func RowNumberOver(window Window) NumberField
func RankOver(window Window) NumberField
func DenseRankOver(window Window) NumberField
func PercentRankOver(window Window) NumberField
func CumeDistOver(window Window) NumberField
func LeadOver(field interface{}, offset interface{}, fallback interface{}, window Window) CustomField
func LagOver(field interface{}, offset interface{}, fallback interface{}, window Window) CustomField
func NtileOver(n int, window Window) NumberField
func FirstValueOver(field interface{}, window Window) CustomField
func LastValueOver(field interface{}, window Window) CustomField
func NthValueOver(field interface{}, window Window) CustomField

The SELECT query has a WINDOW clause, intended for named windows. There you can define a window and assign it a name, and you can then use that name in the SELECT clause anywhere in place of a window.

// Without named window
SELECT
    SUM(tbl.column_1) OVER (PARTTION BY tbl.column_2 ORDER BY tbl.column_3),
    AVG(tbl.column_4) OVER (PARTTION BY tbl.column_2 ORDER BY tbl.column_3),
    MIN(tbl.column_5) OVER (PARTTION BY tbl.column_6)
FROM
    tbl;

// With named window
SELECT
    SUM(tbl.column_1) OVER w1,
    AVG(tbl.column_4) OVER w1,
    MIN(tbl.column_5) OVER w2
WINDOW
    w1 AS (PARTTION BY tbl.column_2 ORDER BY tbl.column_3),
    w2 AS (PARTTION BY tbl.column_6)
FROM
    tbl;

You can name windows in sq with .As, and when you pass it into the window function you must call .Name() in order to render the window name, not the window definition. Remember to also pass in the window definition!

// SELECT
//     SUM(tbl.column_1) OVER w1,
//     AVG(tbl.column_4) OVER w1,
//     MIN(tbl.column_5) OVER w2
// WINDOW
//     w1 AS (PARTTION BY tbl.column_2 ORDER BY tbl.column_3),
//     w2 AS (PARTTION BY tbl.column_6)
// FROM
//     tbl
w1 := sq.PartitionBy(tbl.COLUMN_2).OrderBy(tbl.COLUMN_3).As("w1")
w2 := sq.PartitionBy(tbl.COLUMN_6).As("w2")
sq.Select(
    sq.SumOver(tbl.COLUMN_1, w1.Name()),
    sq.SumOver(tbl.COLUMN_4, w1.Name()),
    sq.SumOver(tbl.COLUMN_5, w2.Name()),
).
Window(w1, w2).
From(tbl)

< Prev: Aggregate functions

Up: SQL Expressions

Next: Subqueries >

SQL Expressions: Window functions

Contents

Overview

Named window