Dynamic Data Functions

Complex excel like formulas for recursive and external data

Dynamic Data Functions are currently in development. This documentation is for feedback and development purposes.

Overview

There are certain types of calculations that are difficult to perform in SQL, particularly recursive calculations that depend upon the result of prior calculations, or that depend on external data. Dynamic Data Functions (DDF) allow you to perform these operations within ChartSQL for advanced use cases.

DDFs are like advanced excel formulas. Your ChartSQL queries can return functions which are executed before the data is rendered.

DDFs are particularly useful for complex, iterative, and conditionally dependent calculations often found in financial modeling, simulations, and analytics

Quick Start

You enable Dynamic Data Functions by adding the directive @ddf: true to your script. This tells ChartSQL Studio to check each cell in your result for DDFs to be evaluated.

A DDF is any cell that returns a string like =function(){ //code }

-- @ddf: true
SELECT
	'James' as first_name,
	'Smith' as last_name,
	'=function(row){ return row.first_name & " " & row.last_name }' as full_name

DDFs vs Common Table Expressions (CTEs) and Recursive CTEs

SQL Common Table Expressions (CTE) and Recursive CTEs are the native way to perform complex hierarchical and self-referential calculations, however DDFs can do more than CTEs:

• Dynamic Execution: DDFs can execute additional runtime logic, manipulate data dynamically, and even fetch or integrate live data during execution.

• Inline Functionality: With DDFs, additional logic (like calling external APIs, conditional recursion) can be executed inline without needing additional stored procedures or scripts.

• Direct Data Manipulation: DDFs allow on-the-fly data manipulation (like adding rows based on runtime conditions), which is not possible directly within SQL or its CTEs.

Evaluation Order

DDF cells are always evaluated from top to bottom and left to right.

Limitations

Post Processing Only

Dynamic Data Functions are processed by ChartSQL after the data is returned, and so you cannot wrap the result in an outer query or process it further with SQL.

ChartSQL Studio Desktop Only

Dynamic Data Functions have full access to the ChartSQL Studio Server process, and so for security purposes, are not available on ChartSQL Studio Cloud, and cannot be published to DashSQL.

Execution Context

For each DDF invocation, your DDF can access variables about the invocation. You can use these variables to access data and perform dynamic calculations.

In the quick start example, the DDF makes use of the row context variable, which returns a structure (hashmap) of the currently executing row. It uses this data to concatenate the first name and last name into the full name.

=function(row){ return row.first_name & " " & row.last_name }

Context Variables Reference

Name	Description
rid	The current integer index of the executing row. Starting at 1.
cid	The current integer index of the executing column. Starting at 1.
field	The name of the currently executing column field.
data	A reference to the query data.
prev	The result of the previous calculation.
row	The data for the currently executing row.
func	The string function of the currently executing DDF. Use this to reappend the function onto new rows if creating dynamically generating queries.

Examples

You will typically use DDFs for the following situations:

1. Recursive calculations. Complex recursive calculations are straightforward and computationally efficient as DDFs. DDFs naturally accommodate recursion without complex SQL constructs.

2. Integration with real-time data from external APIs. DDFs allow ChartSQL to embed external functionality directly into queries, enhancing flexibility and reducing the latency you might face in a traditional ETL process that periodically updates a data warehouse.

Fibonacci Series

Consider this complex SQL query to generate a Fibonacci Series (for each row, add the previous two rows). This is a complicated query, hard to read and debug. These kinds of recursive queries are difficult to do in SQL when the business logic gets more complex.

-- MySQL 8.0 Fibonacci function
WITH RECURSIVE Fibonacci (n, fib_n, next_fib_n) AS (
  -- Initial subquery: defines the starting point of the recursion.
  SELECT 
    1 AS n, 
    0 AS fib_n,         -- F(0)
    1 AS next_fib_n     -- F(1)

  UNION ALL

  -- Recursive subquery: defines the recursive step.
  SELECT 
    n + 1, 
    next_fib_n AS fib_n, 
    fib_n + next_fib_n AS next_fib_n
  FROM Fibonacci
  WHERE n < 10         -- Specifies how many Fibonacci numbers to generate.
)

-- Final SELECT to output the result
SELECT n, fib_n FROM Fibonacci;

However with DDFs, this is more understandably accomplished:

-- @ddf: true
SELECT 0 as num
UNION ALL SELECT 1 as num
UNION ALL SELECT '=function(
	rid,
	data,
	func
){
	// Calculate the next value in the series
	var result = data["num"][rid - 1] + data["num"][rid - 2];

	// Add a new row to the end of the query using the same function
	// which achieves the recursion
	if(data.recordCount < 10){
		data.addRow({num: func});
	}

	return result;

}' as num

Compound Interest Rate

-- @ddf: true
SELECT
    1 as year,
    10000 as starting_amount,  -- Starting amount
    0.05 as interest_rate,
    '=function(rid, data, func, row){

		var result = row.starting_amount + row.starting_amount * row.interest_rate;

		if (row.year < 10) {  // Recurse for 10 years

			data.addRow({
				year: row.year + 1,
				starting_amount: result,
				interest_rate: row.interest_rate,
				final_amount: func
			});
        	}

		return result;

    }' as final_amount

The equivalent Recursive CTE (MySQL 8.0)

WITH RECURSIVE CompoundInterest AS
(
    -- Initial subquery: defines the starting point of the recursion.
    SELECT 
        1 AS year,
        10000 AS starting_amount,
        0.05 AS interest_rate,
        10500 AS final_amount  -- Initial final_amount equals the starting amount

    UNION ALL

    -- Recursive subquery: defines the recursive step.
    SELECT 
        year + 1 AS year,
        final_amount AS starting_amount,  -- Previous year's final_amount becomes this year's starting_amount
        0.05 AS interest_rate,  -- Assuming the interest rate remains constant
        final_amount + final_amount * 0.05 AS final_amount  -- Calculate the new final_amount
    FROM CompoundInterest
    WHERE year < 10  -- Continue recursion until year reaches 10
)

-- Final SELECT to output the result
SELECT * FROM CompoundInterest;