Grouping

An aggregate calculates one value for a group of rows. A window function calculates a value for each row from the rows around it. Both read the same five entities as Queries.

Aggregates

An aggregate column in find is a list of the aggregate name, its argument, and the variable that holds the result. groupBy names the variables that form each group.

edn130 B
{:find    [?dept ["mean" ?sal ?mean]]
 :where   [
   [?e "dept" ?dept]
   [?e "salary" ?sal]]
 :groupBy [?dept]
 :orderBy [?dept]}
Result / 110 B
{:value [
   {:dept "eng"
    :mean 110.0}
   {:dept "ops"
    :mean 80.0}
   {:dept "sales"
    :mean 80.0}]}

One query can calculate several aggregates over the same group.

edn180 B
{:find    [?dept ["count" ?e ?n] ["sum" ?sal ?total] ["min" ?sal ?lo] ["max" ?sal ?hi]]
 :where   [
   [?e "dept" ?dept]
   [?e "salary" ?sal]]
 :groupBy [?dept]
 :orderBy [?dept]}
Result / 236 B
{:value [
   {:dept  "eng"
    :n     2
    :total 220
    :lo    100
    :hi    120}
   {:dept  "ops"
    :n     1
    :total 80
    :lo    80
    :hi    80}
   {:dept  "sales"
    :n     2
    :total 160
    :lo    70
    :hi    90}]}

An aggregate can also collect the values of a group.

edn202 B
{:find    [?dept ["median" ?sal ?med] ["countDistinct" ?sal ?kinds] ["list" ?name ?who]]
 :where   [
   [?e "dept" ?dept]
   [?e "salary" ?sal]
   [?e "name" ?name]]
 :groupBy [?dept]
 :orderBy [?dept]}
Result / 223 B
{:value [
   {:dept  "eng"
    :med   110.0
    :kinds 2
    :who   ["Ada" "Bob"]}
   {:dept  "ops"
    :med   80.0
    :kinds 1
    :who   ["Eve"]}
   {:dept  "sales"
    :med   80.0
    :kinds 2
    :who   ["Cy" "Dan"]}]}

These are the aggregate names:

GroupNames
Countingcount, countDistinct
Numberssum, mean, min, max, median, variance, stddev
Selectionfirst, last, sample, rand
Collectionlist, uniq

Each name is an aggregate only in find. The same name in a where predicate is an ordinary operator.

Having

having filters the groups after Stardust calculates the aggregates. Each entry is a predicate over the group's values.

edn157 B
{:find    [?dept ["mean" ?sal ?mean]]
 :where   [
   [?e "dept" ?dept]
   [?e "salary" ?sal]]
 :groupBy [?dept]
 :having  [[">" ?mean 85]]
 :orderBy [?dept]}
Result / 44 B
{:value [
   {:dept "eng"
    :mean 110.0}]}

A having entry must start with an operator. A fact-shaped entry is an error.

edn142 B
{:find    [?dept ["mean" ?sal ?mean]]
 :where   [
   [?e "dept" ?dept]
   [?e "salary" ?sal]]
 :groupBy [?dept]
 :having  [[?e "dept" "eng"]]}

Error

Invalid_Having

groupBy accepts each variable once. A repeated variable is an error.

edn118 B
{:find    [?dept ["mean" ?sal ?mean]]
 :where   [
   [?e "dept" ?dept]
   [?e "salary" ?sal]]
 :groupBy [?dept ?dept]}

Error

Invalid_Group_By

Window functions

A window entry has three parts: the function with its arguments, the variable that holds the result, and an optional configuration. The configuration holds partitionBy, orderBy and frame.

rowNumber, rank, denseRank, percentRank and cumeDist have no argument. They read the window's own orderBy.

edn169 B
{:find    [?name ?place]
 :where   [
   [?e "salary" ?sal]
   [?e "name" ?name]]
 :window  [[["rowNumber"] ?place {:orderBy [[?sal "desc"]]}]]
 :orderBy [[?sal "desc"]]}
Result / 165 B
{:value [
   {:name  "Ada"
    :place 1}
   {:name  "Bob"
    :place 2}
   {:name  "Cy"
    :place 3}
   {:name  "Eve"
    :place 4}
   {:name  "Dan"
    :place 5}]}

An argument for one of these functions is an error.

edn61 B
{:window [[["rank" ?sal] ?place {:orderBy [[?sal "desc"]]}]]}

Error

Invalid_Window

Ranks and ties

The position functions differ when rows share a value. rank gives rows with the same value the same number and skips the numbers after them. denseRank does not skip. percentRank gives (rank - 1) / (rows - 1). cumeDist gives the part of the rows that have the same value or come before it. This query orders by dept, so the two eng rows and the two sales rows are equal.

edn312 B
{:find    [?name ?dept ?rank ?dense ?pct ?cume]
 :where   [
   [?e "dept" ?dept]
   [?e "name" ?name]]
 :window  [
   [["rank"] ?rank {:orderBy [?dept]}]
   [["denseRank"] ?dense {:orderBy [?dept]}]
   [["percentRank"] ?pct {:orderBy [?dept]}]
   [["cumeDist"] ?cume {:orderBy [?dept]}]]
 :orderBy [?dept ?name]}
Result / 471 B
{:value [
   {:name  "Ada"
    :dept  "eng"
    :rank  1
    :dense 1
    :pct   0.0
    :cume  0.4}
   {:name  "Bob"
    :dept  "eng"
    :rank  1
    :dense 1
    :pct   0.0
    :cume  0.4}
   {:name  "Eve"
    :dept  "ops"
    :rank  3
    :dense 2
    :pct   0.5
    :cume  0.6}
   {:name  "Cy"
    :dept  "sales"
    :rank  4
    :dense 3
    :pct   0.75
    :cume  1.0}
   {:name  "Dan"
    :dept  "sales"
    :rank  4
    :dense 3
    :pct   0.75
    :cume  1.0}]}

Previous and next rows

partitionBy restarts the window for each group. lag reads the row before the current row, and gives null at the start of each partition.

edn250 B
{:find    [?dept ?name ?prev]
 :where   [
   [?e "dept" ?dept]
   [?e "salary" ?sal]
   [?e "name" ?name]]
 :window  [
   [
     ["lag" ?sal]
     ?prev
     {:partitionBy [?dept]
      :orderBy     [[?sal "desc"]]}]]
 :orderBy [?dept [?sal "desc"]]}
Result / 248 B
{:value [
   {:dept "eng"
    :name "Ada"
    :prev nil}
   {:dept "eng"
    :name "Bob"
    :prev 120}
   {:dept "ops"
    :name "Eve"
    :prev nil}
   {:dept "sales"
    :name "Cy"
    :prev nil}
   {:dept "sales"
    :name "Dan"
    :prev 90}]}

lead reads the row after the current row. The second argument sets how many rows to move, and the third argument replaces null at the end of each partition.

edn255 B
{:find    [?dept ?name ?next]
 :where   [
   [?e "dept" ?dept]
   [?e "salary" ?sal]
   [?e "name" ?name]]
 :window  [
   [
     ["lead" ?sal 1 0]
     ?next
     {:partitionBy [?dept]
      :orderBy     [[?sal "desc"]]}]]
 :orderBy [?dept [?sal "desc"]]}
Result / 242 B
{:value [
   {:dept "eng"
    :name "Ada"
    :next 100}
   {:dept "eng"
    :name "Bob"
    :next 0}
   {:dept "ops"
    :name "Eve"
    :next 0}
   {:dept "sales"
    :name "Cy"
    :next 70}
   {:dept "sales"
    :name "Dan"
    :next 0}]}

Values for each group

A number function without orderBy reads the full partition. Each row gets the value for its group, and the rows stay separate.

edn245 B
{:find    [?dept ?name ?mean ?size]
 :where   [
   [?e "dept" ?dept]
   [?e "salary" ?sal]
   [?e "name" ?name]]
 :window  [
   [["mean" ?sal] ?mean {:partitionBy [?dept]}]
   [["count" ?e] ?size {:partitionBy [?dept]}]]
 :orderBy [?dept ?name]}
Result / 316 B
{:value [
   {:dept "eng"
    :name "Ada"
    :mean 110.0
    :size 2}
   {:dept "eng"
    :name "Bob"
    :mean 110.0
    :size 2}
   {:dept "ops"
    :name "Eve"
    :mean 80.0
    :size 1}
   {:dept "sales"
    :name "Cy"
    :mean 80.0
    :size 2}
   {:dept "sales"
    :name "Dan"
    :mean 80.0
    :size 2}]}

Frames

A frame sets the rows that the function reads. This running total adds each row to the rows above it.

edn286 B
{:find    [?name ?running]
 :where   [
   [?e "salary" ?sal]
   [?e "name" ?name]]
 :window  [
   [
     ["sum" ?sal]
     ?running
     {:orderBy [[?sal "desc"]]
      :frame   {:rows {:from "unboundedPreceding"
                       :to   "currentRow"}}}]]
 :orderBy [[?sal "desc"]]}
Result / 195 B
{:value [
   {:name    "Ada"
    :running 120}
   {:name    "Bob"
    :running 220}
   {:name    "Cy"
    :running 310}
   {:name    "Eve"
    :running 390}
   {:name    "Dan"
    :running 460}]}

Without a frame, a function reads the full partition, also when the window has an orderBy. from must be unboundedPreceding. to is currentRow or unboundedFollowing.

First, last and nth values

The readers give a value from a row in the frame. firstComponent reads the first row, lastComponent reads the last row, and nthComponent reads the row at a position. The first position is 1. A position after the end of the frame gives null. This query reads through the current row. Thus, lastComponent gives the current name. nthComponent gives null on the first row.

edn655 B
{:find    [?name ?first ?last ?second]
 :where   [
   [?e "salary" ?sal]
   [?e "name" ?name]]
 :window  [
   [
     ["firstComponent" ?name]
     ?first
     {:orderBy [[?sal "desc"]]
      :frame   {:rows {:from "unboundedPreceding"
                       :to   "currentRow"}}}]
   [
     ["lastComponent" ?name]
     ?last
     {:orderBy [[?sal "desc"]]
      :frame   {:rows {:from "unboundedPreceding"
                       :to   "currentRow"}}}]
   [
     ["nthComponent" ?name 2]
     ?second
     {:orderBy [[?sal "desc"]]
      :frame   {:rows {:from "unboundedPreceding"
                       :to   "currentRow"}}}]]
 :orderBy [[?sal "desc"]]}
Result / 372 B
{:value [
   {:name   "Ada"
    :first  "Ada"
    :last   "Ada"
    :second nil}
   {:name   "Bob"
    :first  "Ada"
    :last   "Bob"
    :second "Bob"}
   {:name   "Cy"
    :first  "Ada"
    :last   "Cy"
    :second "Bob"}
   {:name   "Eve"
    :first  "Ada"
    :last   "Eve"
    :second "Bob"}
   {:name   "Dan"
    :first  "Ada"
    :last   "Dan"
    :second "Bob"}]}

Without the frame, every row gets last Dan, because the function reads the full result.

Window function names

These are the window function names:

GroupNames
PositionrowNumber, rank, denseRank, percentRank, cumeDist
Neighborslag, lead
ReadersfirstComponent, lastComponent, nthComponent
Numberscount, sum, mean, min, max

The position names and the readers need an orderBy in the window configuration. lag and lead accept a value, an optional offset, and an optional fallback. The offset is 1 by default and must not be negative. nthComponent accepts a value and a position from 1. The other readers and the numbers accept one value.

Run it

database query · query run.