Pivot Tables & Window Functions

Reshape data with pivot tables for wide-format aggregation, and compute rolling or windowed calculations over ordered data.

Pivot

The pivot function reshapes long-format data into wide format. It groups by an index column, spreads a pivot column's distinct values into new columns, and fills each cell with an aggregated value.

Signature

(pivot table index pivot_col value_col agg_fn)

Pivot a table to wide format.
table — source table
index — row index column (symbol or vector of symbols for multi-index)
pivot_col — column whose distinct values become new column headers
value_col — column to aggregate
agg_fn — aggregation function (sum, avg, count, min, max, first, last, med, or a custom lambda)

Basic Pivot

(set trades (table [Symbol Side Price Quantity]
    (list [AAPL AAPL GOOG GOOG AAPL GOOG]
          [Buy Sell Buy Sell Buy Buy]
          [150 152 280 282 149 278]
          [100 200 50 75 300 60])))

; Total Quantity by Symbol and Side
(pivot trades 'Symbol 'Side 'Quantity sum)
; Symbol  Buy  Sell
; ------  ---  ----
; AAPL    400   200
; GOOG    110    75

Different Aggregations

; Average Price by Symbol and Side
(pivot trades 'Symbol 'Side 'Price avg)
; Symbol     Buy   Sell
; ------  ------  -----
; AAPL     149.5    152
; GOOG       279    282

; Trade Count by Symbol and Side
(pivot trades 'Symbol 'Side 'Quantity count)
; Symbol  Buy  Sell
; ------  ---  ----
; AAPL      2     1
; GOOG      2     1

; Min/Max Price
(pivot trades 'Symbol 'Side 'Price min)
(pivot trades 'Symbol 'Side 'Price max)

; First/Last Price
(pivot trades 'Symbol 'Side 'Price first)
(pivot trades 'Symbol 'Side 'Price last)

; Median Price
(pivot trades 'Symbol 'Side 'Price med)

Custom Aggregation

Pass a lambda for custom aggregation logic:

; Sum of squares
(pivot trades 'Symbol 'Side 'Quantity (fn [x] (sum (* x x))))

Multi-index Pivot

Pass a vector of column symbols as the index to create a multi-level row grouping:

(set trades (table [Date Symbol Sector Side Price Quantity]
    (list
        (+ 2024.01.01 (take (til 30) n))
        (take [AAPL GOOG MSFT AMZN] n)
        (take [Tech Tech Tech Retail] n)
        (take [Buy Sell Short] n)
        (+ 150 (til n))
        (* 100 (+ 1 (til n))))))

; Multi-index: Quantity by [Date Symbol] and Side
(pivot trades [Date Symbol] 'Side 'Quantity sum)

; Multi-index: Avg Price by [Sector Symbol] and Side
(pivot trades [Sector Symbol] 'Side 'Price avg)

OP_PIVOT DAG Implementation

Pivot compiles to a single OP_PIVOT node in the execution DAG. The implementation uses a single-pass parallel hash aggregation:

Phase 1: Discover pivot values — Scan the pivot column to collect its distinct values. These become the output column names.
Phase 2: Hash aggregation — For each morsel, hash the (index, pivot_value) composite key and route the value to the corresponding accumulator. Each output column has its own aggregation state array.
Phase 3: Materialize — Build the result table with the index column(s) as rows and one column per distinct pivot value, filled with the aggregated results.

Single-pass design. Unlike the naive approach of grouping then reshaping, OP_PIVOT performs aggregation and reshaping in one pass. This avoids materializing the grouped intermediate table and reduces memory pressure for large datasets.

Supported Aggregations

Function	Description
`sum`	Sum of values
`avg`	Arithmetic mean
`count`	Number of values
`min`	Minimum value
`max`	Maximum value
`first`	First value in group
`last`	Last value in group
`med`	Median value
`dev`	Standard deviation
`(fn [x] ...)`	Custom lambda aggregation

Window Functions

Window functions compute values across a set of related rows without collapsing the result. In Rayfall, windowed computations are expressed through select with rolling or cumulative expressions.

Rolling Computations

(set trades (table [Sym Time Price]
    (list [x x x]
          [12:00:01 12:00:04 12:00:06]
          [89.17 70.5 80.54])))

; Cumulative sum
(select {from: trades
         cols: {Sym: Sym Time: Time Price: Price
                cum_price: (scan + Price)}})

Moving Averages

; Moving average with scan and division
(select {from: trades
         cols: {Sym: Sym Time: Time Price: Price
                running_avg: (/ (scan + Price) (+ 1 (til (count Price))))}})

Rank and Order

; Rank prices within the table
(select {from: trades
         cols: {Sym: Sym Price: Price
                rank: (iasc (iasc Price))}})

Grouped Window

Combine by: with windowed expressions to compute per-group running totals:

(select {from: trades
         by: {Sym: Sym}
         cols: {total_price: (sum Price)
                min_price: (min Price)
                max_price: (max Price)}})