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.

find    [?dept [mean ?sal ?mean]]
where   [[?e dept ?dept]
         [?e salary ?sal]]
groupBy [?dept]
orderBy [?dept]

[{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.

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]

[{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.

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]

[{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:

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.

find    [?dept [mean ?sal ?mean]]
where   [[?e dept ?dept]
         [?e salary ?sal]]
groupBy [?dept]
having  [[> ?mean 85]]
orderBy [?dept]

[{dept eng
  mean 110.0}]

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

find    [?dept [mean ?sal ?mean]]
where   [[?e dept ?dept]
         [?e salary ?sal]]
groupBy [?dept]
having  [[?e dept eng]]

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

find    [?dept [mean ?sal ?mean]]
where   [[?e dept ?dept]
         [?e salary ?sal]]
groupBy [?dept ?dept]

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.

find    [?name ?place]
where   [[?e salary ?sal]
         [?e name ?name]]
window  [[[rowNumber] ?place {orderBy [[?sal desc]]}]]
orderBy [[?sal desc]]

[{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.

window [[[rank ?sal] ?place {orderBy [[?sal desc]]}]]

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.

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]

[{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.

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]]

[{dept eng
  name Ada
  prev null}
 {dept eng
  name Bob
  prev 120}
 {dept ops
  name Eve
  prev null}
 {dept sales
  name Cy
  prev null}
 {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.

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]]

[{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.

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]

[{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.

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]]

[{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.

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]]

[{name   Ada
  first  Ada
  last   Ada
  second null}
 {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:

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.