Window (Aggregation) Expressions

View as Markdown

Summary

Window Expressions order a set of rows (via OVER) and then look up a value from another row in that ordering
They use the LAG, LEAD, FIRST, LAST and RANK Expression Functions

Window Expressions order the rows in a set and then reference a value from another row in that ordering.

Window Expressions return per-row results (unlike regular Aggregations which reduce many rows to one value).

This can be the value of a neighbouring row, the first or last row in the series, or the row's rank.

Hint

This is particularly useful for row-relative comparisons
e.g. "the value in the previous row", "the value in the next row" or "this row's rank"

Window Expressions are, like Cumulative and Quantile Expressions, a special case of Aggregation Expressions.

Why use Window Expressions?

Compare with another row: previous close, next fixing, first trade of the day - LAG{[Close],1,OVER([Date])}
1 Line Moves: day-on-day % change: PERCENT_DIFF([Close],LAG{[Close],1,GROUP_BY([Ticker]), OVER([Date])})
Rank anything: rank trades by PnL, or Tickers by Vol each day - RANK{GROUP_BY([Date]),OVER([Volume],'DESC')}
Order comes from the data: OVER sets the order, so the values stay correct however the Grid is sorted
Per group: add GROUP_BY and each Ticker, Book or Desk gets its own series
Always live: edit or add a row and every neighbouring value updates

Note

By contrast AG Grid's formulas can only reach another row by its fixed row number
There is no way to request something like "the previous row for this Ticker"

AdapTableQL: Price Moves with Window Functions

Fork Fork

This demo shows 8 days of closing prices for 3 Tickers
It contains 4 Calculated Columns:
- Prev Close - the previous day's Close for the same Ticker: LAG{[close], 1, GROUP_BY([ticker]), OVER([date])}
- Day Change % - PERCENT_DIFF([close], LAG{[close], 1, GROUP_BY([ticker]), OVER([date])})
- Since Start % - PERCENT_DIFF([close], FIRST{[close], GROUP_BY([ticker]), OVER([date])})
- Volume Rank (Day) - each Ticker's Volume rank on that day (highest is 1): RANK{GROUP_BY([date]), OVER([volume], 'DESC')}
Format Columns colour Day Change % green or red, and highlight any move of more than 3%
The first day for each Ticker has no previous row, so Prev Close and Day Change % are blank

Try It Out

Change a Close value - its own Day Change % and the next day's both update
Sort the Grid by Close - the values do not change, because OVER([date]) sets the order, not the Grid sort
Change a Volume and watch Volume Rank (Day) re-rank the 3 Tickers on that day

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {DailyPrice} from './rowData';

export const adaptableOptions: AdaptableOptions<DailyPrice> = {
  primaryKey: 'id',
  adaptableId: 'Window Price Moves',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn', 'FormatColumn'],
    },
    Theme: {CurrentTheme: 'dark'},
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'prevClose',
          FriendlyName: 'Prev Close',
          Expression: 'LAG{[close], 1, GROUP_BY([ticker]), OVER([date])}',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'dayChange',
          FriendlyName: 'Day Change %',
          Expression:
            'PERCENT_DIFF([close], LAG{[close], 1, GROUP_BY([ticker]), OVER([date])})',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'sinceStart',
          FriendlyName: 'Since Start %',
          Expression: 'PERCENT_DIFF([close], FIRST{[close], GROUP_BY([ticker]), OVER([date])})',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'volumeRank',
          FriendlyName: 'Volume Rank (Day)',
          Expression: "RANK{GROUP_BY([date]), OVER([volume], 'DESC')}",
          CalculatedColumnSettings: {DataType: 'number'},
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'big-move',
          Scope: {ColumnIds: ['dayChange']},
          Rule: 'ABS([dayChange]) > 3',
          Style: {BackColor: '#f9a825', ForeColor: 'var(--ab-color-palette-13)', FontWeight: 'Bold'},
        },
        {
          Name: 'up-move',
          Scope: {ColumnIds: ['dayChange']},
          Rule: '[dayChange] > 0',
          Style: {ForeColor: '#66bb6a'},
        },
        {
          Name: 'down-move',
          Scope: {ColumnIds: ['dayChange']},
          Rule: '[dayChange] < 0',
          Style: {ForeColor: '#ef5350'},
        },
        {
          Name: 'two-decimals',
          Scope: {ColumnIds: ['close', 'prevClose', 'dayChange', 'sinceStart']},
          DisplayFormat: {Formatter: 'NumberFormatter', Options: {FractionDigits: 2}},
        },
        {
          Name: 'trade-date',
          Scope: {ColumnIds: ['date']},
          DisplayFormat: {Formatter: 'DateFormatter', Options: {Pattern: 'dd MMM yyyy'}},
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Daily Prices',
      Layouts: [
        {
          Name: 'Daily Prices',
          TableColumns: [
            'date',
            'ticker',
            'close',
            'prevClose',
            'dayChange',
            'sinceStart',
            'volume',
            'volumeRank',
          ],
          ColumnSorts: [
            {ColumnId: 'ticker', SortOrder: 'Asc'},
            {ColumnId: 'date', SortOrder: 'Asc'},
          ],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

Read-only

Loading demo…

Window Functions

There are 5 Window Functions available:

Function	Returns
`LAG`	The value from a previous row (SQL `LAG`)
`LEAD`	The value from a later row (SQL `LEAD`)
`FIRST`	The value from the first row in the ordering
`LAST`	The value from the last row in the ordering
`RANK`	The 1-based rank of the row in the ordering (ties share a rank)

Using `OVER`

Every Window Function needs to know in what order the rows should be considered.

That order is specified by the OVER clause, which takes the name of an Adaptable Column as its first parameter.

Note

The OVER clause only works with Columns of type date or number

LAG and LEAD also take an offset (a positive integer) that specifies how many rows back or forward to look:

// Value of PnL from the previous row, ordered by TradeDate
 LAG{[PnL], 1, OVER([TradeDate])};

// Value of PnL from 2 rows later, ordered by TradeDate
 LEAD{[PnL], 2, OVER([TradeDate])};

FIRST and LAST return the value at the start or end of the ordered series:

// Value of PnL in the earliest row, ordered by TradeDate
 FIRST{[PnL], OVER([TradeDate])};

RANK does not take a value column - it ranks each row by the OVER Column itself (ties share a rank, and the next distinct value skips ranks):

// Rank each row by its PnL
 RANK{OVER([PnL])};

Hint

There is no need physically to sort the Grid using the Column specified by OVER
Just like Cumulative Expressions, OVER ignores the current Grid Sort and uses the order in the given Column

Sort Direction

By default OVER orders rows in ascending order (smallest or earliest first).

To reverse the order, pass 'DESC' as an optional second parameter to OVER ('ASC' is the default and can be omitted).

This is most useful with RANK, where rank 1 is usually wanted for the largest value:

// Rank each row by its PnL - largest PnL is ranked 1
 RANK{OVER([PnL], 'DESC')};

It works with every Window Function, e.g. FIRST with a descending order returns the value from the latest row:

// Value of PnL in the most recent row, ordered by TradeDate
 FIRST{[PnL], OVER([TradeDate], 'DESC')};

Note

Rows with a blank value in the OVER Column are always placed last - in both ascending and descending order

AdapTableQL: Window Functions

Fork Fork

This demo contains 3 Calculated Columns that use Window Expressions:
- Previous Stars - uses LAG to show the Stars count of the repo created immediately before this one (ordered by Created)
- Next Stars - uses LEAD to show the Stars count of the repo created immediately after this one (ordered by Created)
- Stars Rank - uses RANK with 'DESC' to rank every repo by its Stars count (the repo with the most Stars is ranked 1)
The Grid is sorted by Created (ascending) so the LAG / LEAD lookups line up with the rows above and below

Expand to see the Window Expressions

The 3 Calculated Columns are defined as follows:

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'prev-stars',
      FriendlyName: 'Previous Stars',
      Expression: 'LAG{[github_stars], 1, OVER([created_at])}',
      CalculatedColumnSettings: {
        DataType: 'number'
      },
    },
    {
      ColumnId: 'next-stars',
      FriendlyName: 'Next Stars',
      Expression: 'LEAD{[github_stars], 1, OVER([created_at])}',
      CalculatedColumnSettings: {
        DataType: 'number'
      },
    },
    {
      ColumnId: 'stars-rank',
      FriendlyName: 'Stars Rank',
      Expression: "RANK{OVER([github_stars], 'DESC')}",
      CalculatedColumnSettings: {
        DataType: 'number'
      },
    },
  ]
}

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Window Expression',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'language',
            'created_at',
            'github_stars',
            'prev-stars',
            'next-stars',
            'stars-rank',
          ],
          ColumnSorts: [{ColumnId: 'created_at', SortOrder: 'Asc'}],
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'prev-stars',
          FriendlyName: 'Previous Stars',
          Expression: 'LAG{[github_stars], 1, OVER([created_at])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'next-stars',
          FriendlyName: 'Next Stars',
          Expression: 'LEAD{[github_stars], 1, OVER([created_at])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'stars-rank',
          FriendlyName: 'Stars Rank',
          Expression: "RANK{OVER([github_stars], 'DESC')}",
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-created_at',
          Scope: {
            ColumnIds: ['created_at'],
          },
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyy/MM/dd',
            },
          },
        },
      ],
    },
  },
};

Read-only

Loading demo…

Grouping

As with other Aggregation Expressions, you can use the GROUP_BY clause.

This will compute the Window Function within each partition rather than across the whole Grid.

Note

With GROUP_BY the ordering (OVER) and the lookup (or rank) are applied independently within each distinct group

// First PnL (ordered by TradeDate) within each Currency
 FIRST{[PnL], OVER([TradeDate]), GROUP_BY([currency])};

// Rank each row by PnL within each Currency
 RANK{OVER([PnL]), GROUP_BY([currency])};

AdapTableQL: Grouped Window Functions

Fork Fork

This demo contains 3 Calculated Columns that use Window Expressions, each partitioned by Language using GROUP_BY:
- First{per Language} - uses FIRST to show the Stars count of the earliest-created repo in each Language
- Last{per Language} - uses LAST to show the Stars count of the latest-created repo in each Language
- Rank within Language - uses RANK with 'DESC' to rank each repo by its Stars count within its Language (most Stars is ranked 1)
The Grid is sorted by Language then Created to make the partitions easy to see

Expand to see the Grouped Window Expressions

The 3 Calculated Columns are defined as follows:

CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'first-lang-stars',
      FriendlyName: 'First{per Language}',
      Expression: 'FIRST{[github_stars], GROUP_BY([language]), OVER([created_at])}',
      CalculatedColumnSettings: {
        DataType: 'number'
      },
    },
    {
      ColumnId: 'last-lang-stars',
      FriendlyName: 'Last{per Language}',
      Expression: 'LAST{[github_stars], GROUP_BY([language]), OVER([created_at])}',
      CalculatedColumnSettings: {
        DataType: 'number'
      },
    },
    {
      ColumnId: 'lang-rank',
      FriendlyName: 'Rank within Language',
      Expression: "RANK{GROUP_BY([language]), OVER([github_stars], 'DESC')}",
      CalculatedColumnSettings: {
        DataType: 'number'
      },
    },
  ]
}

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Window Grouped Expression',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'language',
            'created_at',
            'github_stars',
            'first-lang-stars',
            'last-lang-stars',
            'lang-rank',
          ],
          ColumnSorts: [
            {ColumnId: 'language', SortOrder: 'Asc'},
            {ColumnId: 'created_at', SortOrder: 'Asc'},
          ],
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'first-lang-stars',
          FriendlyName: 'First{per Language}',
          Expression: 'FIRST{[github_stars], GROUP_BY([language]), OVER([created_at])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'last-lang-stars',
          FriendlyName: 'Last{per Language}',
          Expression: 'LAST{[github_stars], GROUP_BY([language]), OVER([created_at])}',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'lang-rank',
          FriendlyName: 'Rank within Language',
          Expression: "RANK{GROUP_BY([language]), OVER([github_stars], 'DESC')}",
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-created_at',
          Scope: {
            ColumnIds: ['created_at'],
          },
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyy/MM/dd',
            },
          },
        },
      ],
    },
  },
};

Read-only

Loading demo…

Filtering Rows

A WHERE clause restricts which rows take part in the ordering - see WHERE Clause for full details.

Place it after the value column:

// Price of the previous Buy trade (Sell trades are ignored)
 LAG{[Price] WHERE [Side] = 'Buy', 1, OVER([TradeDate])};

RANK has no value column, so add WHERE as a separate item (after a comma):

// Rank Open trades by PnL (largest first)
 RANK{OVER([PnL], 'DESC'), WHERE [Status] = 'Open'};

Note

Rows excluded by WHERE return a blank value

Comparing with Another Row

A Window Function returns an ordinary value, so it can be compared with the current row.

The PERCENT_DIFF and ABSOLUTE_DIFF functions do this directly:

// Day-on-day % move of each Ticker's Close
 PERCENT_DIFF([Close], LAG{[Close], 1, GROUP_BY([Ticker]), OVER([Date])});

// % move of each Ticker's Close since its first day
 PERCENT_DIFF([Close], FIRST{[Close], GROUP_BY([Ticker]), OVER([Date])});

Hint

Put GROUP_BY on the inner Window Function (not on PERCENT_DIFF)
The demo at the top of this page uses both of these Expressions

Find Out More

See Window Calculated Columns for how to use these Expressions in a Calculated Column

PreviousAggregation Expressions NextCumulative Expressions

Window (Aggregation) Expressions

Summary

Hint

Why use Window Expressions?

Note

Window Functions

Using OVER

Note

Hint

Sort Direction

Note

Expand to see the Window Expressions

Grouping

Note

Expand to see the Grouped Window Expressions

Filtering Rows

Note

Comparing with Another Row

Hint

Find Out More

Using `OVER`