Analytical Functions

Advanced mathematical and row-wise aggregation functions for high-performance analytics.

Mathematical Functions

Modulus Operator (%)

-- Take modulus of two numbers
SELECT
    TICKER,
    DATE,
    PRICE,
    PRICE % 10 as price_mod
FROM prices;

-- Example from prices table
SELECT
    DATE,
    TICKER,
    open_mod: PRICE % 5
FROM prices;

Power Operator (^)

-- Raise to power
SELECT
    TICKER,
    DATE,
    PRICE,
    PRICE ^ 2 as price_squared,
    PRICE ^ 0.5 as price_sqrt
FROM prices;

-- Power for volatility calculations
UPDATE
    price_squared: PRICE ^ 2,
    return_squared: (PRICE / LAG(PRICE, 1) - 1) ^ 2
FROM prices
GROUP BY TICKER;

FACTORIAL Function

-- Factorial of an integer
SELECT
    TICKER,
    factorial(5) as fact_5,
    factorial(5) * PRICE as scaled_price
FROM prices;

-- Combinatorial calculations
SELECT
    n,
    k,
    factorial(n) / (factorial(k) * factorial(n - k)) as combinations
FROM parameters;

Trigonometric Functions

-- SIN, COS, TAN functions
UPDATE
    angle_rad: PRICE * 3.14159 / 180,
    sin_value: SIN(PRICE * 3.14159 / 180),
    cos_value: COS(PRICE * 3.14159 / 180),
    tan_value: TAN(PRICE * 3.14159 / 180)
FROM prices;

-- Arc functions: ASIN, ACOS, ATAN
UPDATE
    asin_val: ASIN(normalized_value),
    acos_val: ACOS(normalized_value),
    atan_val: ATAN(slope)
FROM analytics_table;

Other Mathematical Functions

-- ABS: Absolute value
UPDATE abs_return: ABS(daily_return)
FROM prices;

-- EXP: Exponential (e^x)
UPDATE exp_value: EXP(log_return)
FROM prices;

-- SIGN: Sign of number (-1, 0, 1)
UPDATE sign_return: SIGN(daily_return)
FROM prices;

-- LOG: Natural logarithm
UPDATE log_price: LOG(PRICE)
FROM prices;

-- LOG10: Base-10 logarithm
UPDATE log10_volume: LOG10(VOLUME)
FROM prices;

Moving Window Functions

Calculate statistics over a sliding window of rows.

MAVG - Moving Average

-- Simple moving average over window
UPDATE
    mavg_5: MAVG(PRICE, 5),
    mavg_20: MAVG(PRICE, 20),
    mavg_50: MAVG(PRICE, 50)
FROM prices
GROUP BY TICKER;

-- Moving average of returns
UPDATE mavg_return: MAVG(daily_return, 20)
FROM prices
GROUP BY TICKER;

MSUM - Moving Sum

-- Moving sum over window
UPDATE
    msum_volume: MSUM(VOLUME, 5),
    msum_return: MSUM(daily_return, 20)
FROM prices
GROUP BY TICKER;

-- Rolling total
UPDATE rolling_total: MSUM(PRICE, 10)
FROM prices
GROUP BY TICKER;

MSTD - Moving Standard Deviation

-- Moving standard deviation
UPDATE
    mstd_price: MSTD(PRICE, 20),
    mstd_return: MSTD(daily_return, 20),
    rolling_vol: MSTD(daily_return, 20) * SQRT(252)
FROM prices
GROUP BY TICKER;

-- Volatility bands
UPDATE
    upper_band: MAVG(PRICE, 20) + 2 * MSTD(PRICE, 20),
    lower_band: MAVG(PRICE, 20) - 2 * MSTD(PRICE, 20)
FROM prices
GROUP BY TICKER;

Row-wise Aggregation Functions

Row-wise Functions

These functions first compute an aggregate value for each group, then apply that single value to every row in the group. Useful for normalization and broadcasting group statistics.

WSUM - Row-wise Sum

-- Fill each row with group sum
UPDATE total_price: WSUM(PRICE)
FROM prices
GROUP BY TICKER;

-- De-mean using row-wise sum
UPDATE price_demean: PRICE - WSUM(PRICE) / WCOUNT(PRICE)
FROM prices
GROUP BY TICKER;

WAVG - Row-wise Average

-- Fill each row with group average
UPDATE avg_price: WAVG(PRICE)
FROM prices
GROUP BY TICKER;

-- Normalize by group mean
UPDATE
    mean_price: WAVG(PRICE),
    normalized: PRICE / WAVG(PRICE)
FROM prices
GROUP BY TICKER;

WSTD - Row-wise Standard Deviation

-- Fill each row with group standard deviation
UPDATE std_price: WSTD(PRICE)
FROM prices
GROUP BY TICKER;

-- Z-score normalization
UPDATE z_score: (PRICE - WAVG(PRICE)) / WSTD(PRICE)
FROM prices
GROUP BY TICKER;

WMIN and WMAX - Row-wise Min/Max

-- Fill with group min and max
UPDATE
    min_price: WMIN(PRICE),
    max_price: WMAX(PRICE)
FROM prices
GROUP BY TICKER;

-- Min-max normalization
UPDATE normalized: (PRICE - WMIN(PRICE)) / (WMAX(PRICE) - WMIN(PRICE))
FROM prices
GROUP BY TICKER;

WCOUNT - Row-wise Count

-- Fill each row with group count
UPDATE num_records: WCOUNT(PRICE)
FROM prices
GROUP BY TICKER;

-- Calculate percentage
UPDATE pct_of_total: PRICE / WSUM(PRICE) * 100
FROM prices
GROUP BY TICKER;

Ranking Functions

RANK - Sequential Ranking

-- Rank from lowest to highest within group
UPDATE price_rank: RANK(PRICE)
FROM prices
GROUP BY TICKER;

-- Rank by date (cross-sectional)
UPDATE
    price_rank_today: RANK(PRICE),
    volume_rank_today: RANK(VOLUME)
FROM prices
GROUP BY DATE;

-- Identify outliers
SELECT * FROM prices
WHERE RANK(PRICE) > 0.95 * WCOUNT(PRICE)
GROUP BY TICKER;

Combined Analytical Example

-- Comprehensive analytical pipeline
UPDATE
    -- Mathematical transformations
    log_price: LOG(PRICE),
    sqrt_volume: SQRT(VOLUME),

    -- Moving windows
    mavg_20: MAVG(PRICE, 20),
    mstd_20: MSTD(PRICE, 20),

    -- Row-wise normalization
    mean_price: WAVG(PRICE),
    std_price: WSTD(PRICE),
    z_score: (PRICE - WAVG(PRICE)) / WSTD(PRICE),

    -- Rankings
    price_rank: RANK(PRICE),
    volume_rank: RANK(VOLUME)
FROM prices
GROUP BY TICKER;

Practical Examples

Example 1: Bollinger Bands with Moving Functions

-- Load data
.load '/data/bt/20251208W/POLARIS1/data/prices.csv' as prices

-- Calculate Bollinger Bands
UPDATE
    mavg_20: MAVG(PRICE, 20),
    mstd_20: MSTD(PRICE, 20),
    upper_band: MAVG(PRICE, 20) + 2 * MSTD(PRICE, 20),
    lower_band: MAVG(PRICE, 20) - 2 * MSTD(PRICE, 20),
    bb_position: (PRICE - (MAVG(PRICE, 20) - 2 * MSTD(PRICE, 20))) /
                  (4 * MSTD(PRICE, 20))
FROM prices
GROUP BY TICKER;

-- Identify signals
UPDATE signal: CASE
    WHEN PRICE > upper_band THEN 'OVERBOUGHT'
    WHEN PRICE < lower_band THEN 'OVERSOLD'
    ELSE 'NEUTRAL'
END
FROM prices;

Example 2: Cross-Sectional Z-Score Normalization

-- Load price data
.load '/data/prices.csv' as prices

-- Calculate returns
UPDATE daily_return: PRICE / LAG(PRICE, 1) - 1
FROM prices
GROUP BY TICKER;

-- Cross-sectional normalization by date
UPDATE
    mean_return: WAVG(daily_return),
    std_return: WSTD(daily_return),
    z_score: (daily_return - WAVG(daily_return)) / WSTD(daily_return),
    percentile_rank: RANK(daily_return) / WCOUNT(daily_return)
FROM prices
GROUP BY DATE;

-- Identify extremes
SELECT TICKER, DATE, daily_return, z_score
FROM prices
WHERE ABS(z_score) > 2.0
ORDER BY DATE, z_score DESC;

Example 3: Multi-Timeframe Moving Averages

-- Calculate multiple moving averages
UPDATE
    -- Simple moving averages
    sma_5: MAVG(PRICE, 5),
    sma_10: MAVG(PRICE, 10),
    sma_20: MAVG(PRICE, 20),
    sma_50: MAVG(PRICE, 50),

    -- Moving standard deviations
    vol_20: MSTD(PRICE, 20),
    vol_50: MSTD(PRICE, 50),

    -- Trend indicators
    trend_short: CASE WHEN PRICE > MAVG(PRICE, 5) THEN 1 ELSE -1 END,
    trend_long: CASE WHEN MAVG(PRICE, 5) > MAVG(PRICE, 20) THEN 1 ELSE -1 END
FROM prices
GROUP BY TICKER;

-- Golden cross / death cross detection
UPDATE
    cross_signal: CASE
        WHEN MAVG(PRICE, 5) > MAVG(PRICE, 20)
         AND LAG(MAVG(PRICE, 5), 1) <= LAG(MAVG(PRICE, 20), 1)
        THEN 'GOLDEN_CROSS'
        WHEN MAVG(PRICE, 5) < MAVG(PRICE, 20)
         AND LAG(MAVG(PRICE, 5), 1) >= LAG(MAVG(PRICE, 20), 1)
        THEN 'DEATH_CROSS'
        ELSE NULL
    END
FROM prices
GROUP BY TICKER;

Example 4: Power Law and Log Transformations

-- Load data
.load '/data/prices.csv' as prices

-- Apply power law and log transformations
UPDATE
    -- Log transformations for returns
    log_price: LOG(PRICE),
    log_return: LOG(PRICE / LAG(PRICE, 1)),

    -- Power transformations
    price_sqrt: PRICE ^ 0.5,
    price_squared: PRICE ^ 2,

    -- Box-Cox-like transformation
    lambda: 0.5,
    transformed: (PRICE ^ 0.5 - 1) / 0.5,

    -- Exponential back-transform
    exp_log_return: EXP(LOG(PRICE / LAG(PRICE, 1)))
FROM prices
GROUP BY TICKER;

-- Statistical properties
SELECT
    TICKER,
    AVG(log_return) as mean_log_return,
    STD(log_return) as std_log_return,
    AVG(log_return) * 252 as annual_return,
    STD(log_return) * SQRT(252) as annual_volatility
FROM prices
GROUP BY TICKER;