Introduction #

SQL datasets are the most widely used dataset type in SmartChart. Simply write SQL in the development interface. The system caches query results and intelligently converts them to chart-friendly format (2D array with headers).

Overview #

• The most commonly used datasets are SQL datasets
• A standard dataset can be imagined as an Excel-like 2D table with rows and columns
• Just write SQL in the dataset development interface
• See “Special Data Sources” for more dataset types
• See “Parameters & Linkage” chapter for dynamic parameter datasets

Dataset Output Format #

SmartChart datasets are uniformly passed to the chart as 2D arrays with headers:

// Single SQL query result (obtained via __dataset__)
[['field1', 'field2', ...],
 [value1, value2, ...],
 ...]

// Multiple SQL query result (obtained via __dataset__.df0, __dataset__.df1)
{"df0": [[...]], "df1": [[...]]}

Standard Chart Data Format #

Type A Data Source #

If your original database table format is as follows, table name tb_name:

Query SQL:

select City, Type, sum(Count) AS Count
from tb_name group by City, Type

Normal query result:

[['City','Type','Count'],
 ['Changsha','A',35],
 ['Shanghai','B',19]]

Since the second row data format is [string, string, number], the backend will intelligently pivot:

[['Category','A','B'],
 ['Changsha', 35, 0],
 ['Shanghai', 0, 19]]

Note: In dataset preview you may see the first format, but in the chart it’s actually the second pivoted format

Type B Data Source #

For example, if table data has expanded metrics:

SQL:

select City, sum(A) as A, sum(B) as B
from tb_name group by City

Result:

[['City','A','B'],
 ['Changsha', 19, 22],
 ['Shanghai', 11, 19]]

SQL Multi-segment Queries #

Sometimes one SQL query isn’t enough. You may need both detail data and summary data. Use semicolons to separate multiple SQL statements:

// In dataset:
select ... from xxx;
select ..... from xxxxxxx

// Passed to chart as:
{"df0":[[...]], "df1":[[......]]}
df0, df1 correspond to the first and second query respectively

Access multi-segment query data in chart:

let dataset = __dataset__;
let df0 = dataset.df0;  // First query result
let df1 = dataset.df1;  // Second query result

Multi-segment query video reference