The chart component

Example 1

An area chart representing a time series, using the top-level property time. Ticks on the x axis are adjusted automatically, and ISO datetimes are parsed and displayed in a readable format.

select 
    'chart'             as component,
    'Quarterly Revenue' as title,
    'area'              as type,
    'blue-lt'           as color,
    5                   as marker,
    TRUE                as time;
select 
    '2022-01-01T00:00:00Z' as x,
    15                     as y;
select 
    '2022-04-01T00:00:00Z' as x,
    46                     as y;
select 
    '2022-07-01T00:00:00Z' as x,
    23                     as y;
select 
    '2022-10-01T00:00:00Z' as x,
    70                     as y;
select 
    '2023-01-01T00:00:00Z' as x,
    35                     as y;
select 
    '2023-04-01T00:00:00Z' as x,
    106                    as y;
select 
    '2023-07-01T00:00:00Z' as x,
    53                     as y;

Example 2

A pie chart.

select 
    'chart'   as component,
    'Answers' as title,
    'pie'     as type,
    TRUE      as labels;
select 
    'Yes' as label,
    65    as value;
select 
    'No' as label,
    35   as value;

Example 3

A basic bar chart

select 
    'chart'             as component,
    'bar'               as type,
    'Quarterly Results' as title,
    TRUE                as horizontal,
    TRUE                as labels;
select 
    'Tom' as label,
    35    as value;
select 
    'Olive' as label,
    15      as value;

Example 4

A TreeMap Chart allows you to display hierarchical data in a nested layout. This is useful for visualizing the proportion of each part to the whole.

select 
    'chart'   as component,
    'treemap' as type,
    'Quarterly Results By Region (in k$)' as title,
    TRUE      as labels;
select 
    'North America' as series,
    'United States' as label,
    35              as value;
select 
    'North America' as series,
    'Canada'        as label,
    15              as value;
select 
    'Europe' as series,
    'France' as label,
    30       as value;
select 
    'Europe'  as series,
    'Germany' as label,
    55        as value;
select 
    'Asia'  as series,
    'China' as label,
    20      as value;
select 
    'Asia'  as series,
    'Japan' as label,
    10      as value;

Example 5

A bar chart with multiple series.

select 
    'chart'    as component,
    'Expenses' as title,
    'bar'      as type,
    TRUE       as stacked,
    TRUE       as toolbar,
    FALSE      as show_legend,
    10         as ystep;
select 
    'Marketing' as series,
    2021        as x,
    35          as value;
select 
    'Marketing' as series,
    2022        as x,
    15          as value;
select 
    'Human resources' as series,
    2021              as x,
    30                as value;
select 
    'Human resources' as series,
    2022              as x,
    55                as value;

Example 6

A stacked area chart, showing how each series contributes to a total. The stacked property also works with the line and bar chart types.

Series are aligned on their x values, and a series that has no value for a given x counts as zero there: below, the graphics card draws no power outside of the render. If a missing value does not mean zero in your data, make all the series share the same x values, for instance by rounding timestamps to a common interval.

select 
    'chart'      as component,
    'Power draw' as title,
    'area'       as type,
    TRUE         as stacked,
    TRUE         as time,
    'watts'      as ytitle,
    'blue'       as color,
    'teal'       as color,
    4            as marker;
select 
    'CPU'                  as series,
    '2024-03-01T10:00:00Z' as x,
    45                     as value;
select 
    'CPU'                  as series,
    '2024-03-01T10:15:00Z' as x,
    52                     as value;
select 
    'CPU'                  as series,
    '2024-03-01T10:30:00Z' as x,
    48                     as value;
select 
    'CPU'                  as series,
    '2024-03-01T10:45:00Z' as x,
    44                     as value;
select 
    'GPU'                  as series,
    '2024-03-01T10:15:00Z' as x,
    120                    as value;
select 
    'GPU'                  as series,
    '2024-03-01T10:30:00Z' as x,
    140                    as value;

Example 7

A line chart with multiple series. One of the most common types of charts, often used to show trends over time. Also demonstrates the use of the toolbar attribute to allow the user to download the graph as an image or the data as a CSV file.

select 
    'chart'   as component,
    'Revenue' as title,
    0         as ymin,
    TRUE      as toolbar;
select 
    'Chicago Store' as series,
    2021            as x,
    35              as value;
select 
    'Chicago Store' as series,
    2022            as x,
    15              as value;
select 
    'Chicago Store' as series,
    2023            as x,
    45              as value;
select 
    'New York Store' as series,
    2021             as x,
    30               as value;
select 
    'New York Store' as series,
    2022             as x,
    55               as value;
select 
    'New York Store' as series,
    2023             as x,
    19               as value;

Example 8

A scatter plot with multiple custom options.

select 
    'chart'               as component,
    'Gross domestic product and its growth' as title,
    'scatter'             as type,
    'Growth Rate'         as xtitle,
    'GDP (Trillions USD)' as ytitle,
    500                   as height,
    8                     as marker,
    0                     as xmin,
    10                    as xmax,
    0                     as ymin,
    25                    as ymax,
    5                     as yticks;
select 
    'Brazil' as series,
    2.5      as x,
    2        as y;
select 
    'China' as series,
    6.5     as x,
    14      as y;
select 
    'United States' as series,
    2.3             as x,
    21              as y;
select 
    'France' as series,
    1.5      as x,
    3        as y;
select 
    'South Africa' as series,
    0.9            as x,
    0.3            as y;

Example 9

Heatmaps

You can build heatmaps using the heatmap top-level property.

The data format follows the apexcharts heatmap format, where each series is represented as a line in the chart:

• The x property of each item will be used as the x-axis value.
• The series property of each item will be used as the y-axis value.
• The y property of each item will be used as the value to display in the heatmap

The color property sets the color of each series separately, in order.

select 
    'chart'                     as component,
    'Survey Results'            as title,
    'heatmap'                   as type,
    'Database managemet system' as ytitle,
    'Year'                      as xtitle,
    'purple'                    as color,
    'purple'                    as color,
    'purple'                    as color;
select 
    'PostgreSQL' as series,
    '2000'       as x,
    48           as y;
select 
    'SQLite' as series,
    '2000'   as x,
    44       as y;
select 
    'MySQL' as series,
    '2000'  as x,
    78      as y;
select 
    'PostgreSQL' as series,
    '2010'       as x,
    65           as y;
select 
    'SQLite' as series,
    '2010'   as x,
    62       as y;
select 
    'MySQL' as series,
    '2010'  as x,
    83      as y;
select 
    'PostgreSQL' as series,
    '2020'       as x,
    73           as y;
select 
    'SQLite' as series,
    '2020'   as x,
    38       as y;
select 
    'MySQL' as series,
    '2020'  as x,
    87      as y;

Example 10

A timeline displaying events with a start and an end date

select 
    'chart'            as component,
    'Project Timeline' as title,
    'rangeBar'         as type,
    TRUE               as time,
    'teal'             as color,
    'cyan'             as color,
    TRUE               as labels,
    '2021-12-28'       as xmin,
    '2022-01-04'       as xmax;
select 
    'Phase 1'    as series,
    'Operations' as label,
    '2021-12-29' as value,
    '2022-01-02' as value;
select 
    'Phase 2'    as series,
    'Operations' as label,
    '2022-01-03' as value,
    '2022-01-04' as value;
select 
    'Yearly maintenance' as series,
    'Maintenance'        as label,
    '2022-01-01'         as value,
    '2022-01-03'         as value;

Example 11

Coloring a single value

A data row can carry its own color, to paint the one bar, slice or point it plots. Use it when the color says something the axes do not: a threshold crossed, a status, the one category the reader should look at first.

select 'chart' as component, 'bar' as type, true as horizontal,
    true as labels, false as show_legend;
select
    window_label as label,
    accounts as value,
    case when days <= 30 then 'red' when days <= 60 then 'orange' else 'green' end as color
from expiring_accounts order by days;

A row color takes precedence over the color of its series. On a line or an area chart it paints the marker of the point, so set marker for it to show. A heatmap shades its cells from their own value and ignores it.

select 
    'chart'                  as component,
    'Accounts expiring soon' as title,
    'bar'                    as type,
    TRUE                     as horizontal,
    TRUE                     as labels,
    FALSE                    as show_legend;
select 
    '30 days' as label,
    100       as value,
    'red'     as color;
select 
    '60 days' as label,
    200       as value,
    'orange'  as color;
select 
    '90 days' as label,
    300       as value,
    'green'   as color;

Example 12

A pie chart whose rows choose their own slice colors.

select 
    'chart'           as component,
    'Support tickets' as title,
    'pie'             as type,
    TRUE              as labels;
select 
    'Resolved' as label,
    72         as value,
    'green'    as color;
select 
    'In progress' as label,
    21            as value,
    'yellow'      as color;
select 
    'Overdue' as label,
    7         as value,
    'red'     as color;

Example 13

Reference lines

A row with a yline is not plotted as a data point, but drawn as a line across the whole chart, at that value of the y axis. Use it for the limit that the data should be read against: a disk quota, an alarm threshold, a service level objective. Add yline_end to make it a band instead of a line.

Reference lines are rows, so they come from a query like everything else, and a chart can have as many of them as the query returns:

select 'chart' as component, 'CPU temperature' as title, true as time, 100 as ymax;
select celsius as yline, name as label, color as color from thresholds;
select measured_at as x, celsius as y from readings order by measured_at;

They are drawn as annotations rather than as an extra series, so they are not added to the total of a stacked chart, and are not filled in an area chart.

A line outside of the y axis is not drawn, and does not stretch the axis to fit, so set ymax when the limit is above the data.

select 
    'chart'           as component,
    'CPU temperature' as title,
    'line'            as type,
    TRUE              as time,
    '°C'              as ytitle,
    100               as ymax,
    'azure'           as color,
    4                 as marker;
select 
    70       as yline,
    'target' as label,
    'green'  as color;
select 
    90           as yline,
    100          as yline_end,
    'throttling' as label,
    'red'        as color;
select 
    '2024-05-01T08:00:00Z' as x,
    52                     as y;
select 
    '2024-05-01T09:00:00Z' as x,
    58                     as y;
select 
    '2024-05-01T10:00:00Z' as x,
    71                     as y;
select 
    '2024-05-01T11:00:00Z' as x,
    83                     as y;
select 
    '2024-05-01T12:00:00Z' as x,
    94                     as y;
select 
    '2024-05-01T13:00:00Z' as x,
    76                     as y;
select 
    '2024-05-01T14:00:00Z' as x,
    63                     as y;

Example 14

Marking events

xline is the counterpart of yline: it marks a position on the x axis instead of a value on the y axis. On its own it marks a moment, like a deployment. With xline_end, it covers everything in between, like an incident or a maintenance window. A single query can draw a whole log of them:

select started_at as xline, ended_at as xline_end, summary as label,
    case severity when 'outage' then 'red' else 'orange' end as color
from incidents where started_at > $since;

When time is set, an xline is a date or a timestamp, written like the x of a data point. On a chart with text labels on the x axis, it is one of those labels.

select 
    'chart'           as component,
    'Request latency' as title,
    'area'            as type,
    TRUE              as time,
    'ms'              as ytitle,
    'blue-lt'         as color,
    3                 as marker;
select 
    '2024-05-01T10:00:00Z' as xline,
    'deploy'               as label,
    'green'                as color;
select 
    '2024-05-01T11:30:00Z' as xline,
    '2024-05-01T13:00:00Z' as xline_end,
    'incident'             as label,
    'red'                  as color;
select 
    '2024-05-01T08:00:00Z' as x,
    120                    as y;
select 
    '2024-05-01T09:00:00Z' as x,
    134                    as y;
select 
    '2024-05-01T10:00:00Z' as x,
    128                    as y;
select 
    '2024-05-01T11:00:00Z' as x,
    141                    as y;
select 
    '2024-05-01T12:00:00Z' as x,
    512                    as y;
select 
    '2024-05-01T13:00:00Z' as x,
    470                    as y;
select 
    '2024-05-01T14:00:00Z' as x,
    156                    as y;
select 
    '2024-05-01T15:00:00Z' as x,
    133                    as y;

Example 15

Reference lines follow their axis

A reference belongs to the column it is written in, not to a direction on the screen. yline always marks a value of y, and xline a position on x, whichever way round the chart is drawn. A horizontal bar chart runs its y axis from left to right, so a yline is drawn down the chart and an xline picks out one of the bars.

select 'chart' as component, 'bar' as type, true as horizontal, 100 as ymax;
select 90 as yline, 'full' as label, 'red' as color;
select 'db-1' as xline, 'watched' as label, 'purple' as color;
select host as x, percent_used as y from disks order by percent_used;

A pie has no axes and ignores reference lines, and on a heatmap, whose y axis holds the names of the series, only xline has a meaning.

select 
    'chart'      as component,
    'Disk usage' as title,
    'bar'        as type,
    TRUE         as horizontal,
    100          as ymax,
    'azure'      as color,
    TRUE         as labels;
select 
    90     as yline,
    'full' as label,
    'red'  as color;
select 
    'db-1'    as xline,
    'watched' as label,
    'purple'  as color;
select 
    'backup-1' as x,
    41         as y;
select 
    'web-2' as x,
    63      as y;
select 
    'db-1' as x,
    88     as y;
select 
    'web-1' as x,
    96      as y;

Example 16

Multiple charts on the same line

You can create information-dense dashboards by using the card component to put multiple charts on the same line.

For this, create one sql file per visualization you want to show, and set the embed attribute of the card component to the path of the file you want to include, followed by ?_sqlpage_embed.

select 
    'card' as component,
    'A dashboard with multiple graphs on the same line' as title,
    2      as columns;
select 
    '/examples/chart.sql?color=green&n=42&_sqlpage_embed' as embed;
select 
    '/examples/chart.sql?_sqlpage_embed' as embed;