Custom Functions

Scrooge supports various custom functions, focusing on performing financial analysis.

FIRST_S

FIRST_S(A::{NUMERIC_VALUE}, B::{TIMESTAMPTZ})

It returns the value of column A based on the earliest timestamp in column B.

Example

SELECT FIRST_S(price, time) AS "open"  FROM finance;

LAST_S

LAST_S(A::{NUMERIC_VALUE}, B::{TIMESTAMPTZ})

It returns the value of column A based on the latest timestamp value of column B.

Example

SELECT  LAST_S(price, time) AS "close"  FROM finance;

TIMEBUCKET

TIMEBUCKET(A::{TIMESTAMPTZ}, B::{INTERVAL}) Creates timestamp buckets on column A, with ranges on the interval of value B.

Example

SELECT TIMEBUCKET(time,'1M'::INTERVAL) AS bucket FROM finance;

VOLATILITY

VOLATILITY(A::{NUMERIC})

Returns the volatility of that financial instrument during the given period of time.

Example

SELECT VOLATILITY(value) AS bucket FROM finance;

SMA

SMA(A::{NUMERIC})

Returns a financial instrument's average price during a period of time.

Example

SELECT SMA(value) AS bucket FROM finance;