ggsql · diff
v1.0 to v1.1
95 added, 366 removed. Audit A to A.
---
name: ggsql
description: Write ggsql queries — a grammar of graphics for SQL. Use when the user wants to create, modify, or understand a ggsql visualization query.
allowed-tools: Bash(ggsql:*)
- argument-hint: "[description of desired visualization]"
metadata:
author: George Stagg (@georgestagg)
- version: "1.0"
+ version: "1.1"
license: MIT
---
# ggsql Query Writer
- ggsql is a SQL extension for declarative data visualization based on Grammar of Graphics principles. It lets users combine SQL data queries with visualization specifications in a single, composable syntax.
-
- When the user describes a visualization they want, write a valid ggsql query. Use ONLY syntax documented below. NEVER invent clauses, settings, aesthetics, or layer types.
+ ggsql combines a SQL query with a Grammar-of-Graphics visualization spec in one syntax. Write queries using ONLY the syntax below — NEVER invent clauses, settings, aesthetics, layer types, or palette names; say so if unsure whether something exists.
## Query structure
- A ggsql query has two parts:
-
- 1. **SQL part** (optional): Standard SQL executed on the backend. Any tables, CTEs, or SELECT results are available to the visualization.
- 2. **VISUALISE part** (required): Begins with `VISUALISE` (or `VISUALIZE`). Everything after this is the visualization query.
-
- There are two patterns for combining SQL with VISUALISE:
-
- ### Pattern A: SELECT → VISUALISE
-
- The last SQL statement is a SELECT. Data flows from its result set into VISUALISE, which has no `FROM` clause.
-
- ```ggsql
- SELECT name, score_a, score_b FROM 'dataset.csv' WHERE value > 50
- VISUALISE score_a AS x, score_b AS y
- [DRAW / PLACE / SCALE / FACET / PROJECT / LABEL clauses]
- ```
-
- Works with any SQL that ends in a SELECT: bare SELECT, WITH...SELECT, UNION/INTERSECT/EXCEPT.
-
- ### Pattern B: VISUALISE FROM
-
- VISUALISE provides its own data source via `FROM`. Use when referencing a table, file, CTE, or built-in dataset directly without a trailing SELECT.
-
- ```ggsql
- VISUALISE score_a AS x, score_b AS y FROM 'dataset.csv'
- DRAW point
- ```
-
- ```ggsql
- WITH summary AS (SELECT category, COUNT(*) AS n FROM 'dataset.csv' GROUP BY category)
- VISUALISE category AS x, n AS y FROM summary
- DRAW bar
- ```
-
- ## Data sources
+ Optional SQL part + required `VISUALISE`/`VISUALIZE` part.
- Data sources can appear in `VISUALISE ... FROM` or `DRAW ... MAPPING ... FROM`:
+ - **SELECT → VISUALISE**: last statement is a SELECT (bare, `WITH...SELECT`, set ops); its result feeds VISUALISE, which has no `FROM`.
+ ```ggsql
+ SELECT name, score_a, score_b FROM 'dataset.csv' WHERE value > 50
+ VISUALISE score_a AS x, score_b AS y
+ [DRAW / PLACE / SCALE / FACET / PROJECT / LABEL clauses]
+ ```
+ - **VISUALISE FROM**: VISUALISE supplies its own source (table/file/CTE/built-in), no trailing SELECT.
+ ```ggsql
+ WITH summary AS (SELECT category, COUNT(*) AS n FROM 'dataset.csv' GROUP BY category)
+ VISUALISE category AS x, n AS y FROM summary
+ DRAW bar
+ ```
- - **Table/CTE name** (unquoted): `FROM sales`, `FROM my_cte`
- - **File path** (single-quoted string): `FROM 'data.parquet'`, `FROM 'data.csv'`
- - **Built-in datasets**: `FROM ggsql:penguins`, `FROM ggsql:airquality`
+ **Data sources** (in `VISUALISE ... FROM` or `DRAW ... MAPPING ... FROM`): unquoted table/CTE (`FROM sales`), quoted file path (`FROM 'data.csv'`), built-in dataset (`FROM ggsql:penguins`, `FROM ggsql:airquality`).
## VISUALISE clause
- Marks the start of the visualization. Optionally defines global mappings inherited by all layers.
-
+ Starts the viz; optional global mappings inherited by every layer.
```
VISUALISE <mapping>, ... FROM <data-source>
```
-
- ### Mapping forms
-
- - **Explicit**: `column AS aesthetic` — e.g. `revenue AS y`
- - **Implicit**: `column` — column name must match aesthetic name, e.g. `x` maps to `x`
- - **Wildcard**: `*` — all columns with names matching aesthetics are mapped
- - **Constants**: `'red' AS fill`, `42 AS size` — literal values mapped to aesthetic
-
+ Mapping forms: explicit `column AS aesthetic` (e.g. `revenue AS y`); implicit `column` (name = aesthetic name); wildcard `*` (all matching columns); constant `'red' AS fill`.
```ggsql
VISUALISE bill_len AS x, bill_dep AS y, species AS fill FROM ggsql:penguins
VISUALISE * FROM my_table
- VISUALISE FROM ggsql:penguins
```
## DRAW clause
- Defines a layer. Multiple DRAW clauses stack layers (first = bottom, last = top).
-
+ Defines a layer; multiple DRAW stack bottom→top. All subclauses optional given global mappings/data.
```
DRAW <layer-type>
MAPPING <mapping>, ... FROM <data-source>
REMAPPING <stat-property> AS <aesthetic>, ...
SETTING <param> => <value>, ...
FILTER <condition>
PARTITION BY <column>, ...
ORDER BY <column>, ...
```
-
- All subclauses are optional if VISUALISE provides global mappings and data.
-
- ### MAPPING
-
- Same syntax as VISUALISE mappings. Layer mappings merge with global mappings (layer takes precedence). Can include `FROM` for layer-specific data.
-
- - Use `null` to prevent inheriting a global mapping: `MAPPING null AS color`
-
- ### REMAPPING
-
- For statistical layers (histogram, density, boxplot, violin, smooth, bar without y). Maps calculated statistics to aesthetics. Each layer documents its available stats and default remapping.
-
- ```ggsql
- DRAW histogram
- MAPPING body_mass AS x
- REMAPPING density AS y -- use density instead of default count
- ```
-
- ### SETTING
-
- Set literal aesthetic values or layer parameters. Aesthetics set here bypass scales.
-
- ```ggsql
- DRAW point
- SETTING size => 5, opacity => 0.7, stroke => 'red'
- ```
-
- **Position adjustment** is a special setting:
- ```ggsql
- SETTING position => 'identity' -- no adjustment (default for most)
- SETTING position => 'stack' -- stack (default for bar, histogram, area)
- SETTING position => 'dodge' -- side by side (default for boxplot, violin)
- SETTING position => 'jitter' -- random offset
- ```
-
- ### FILTER
-
- SQL WHERE condition applied to layer data. Content is passed to the database:
- ```ggsql
- DRAW point
- FILTER sex = 'female' AND body_mass > 4000
- ```
+ - **MAPPING** — same forms as VISUALISE; merges with (layer wins over) global mappings, can add its own `FROM`. `null` blocks inheriting a global mapping: `MAPPING null AS color`.
+ - **REMAPPING** — for stat layers (`histogram`, `density`, `boxplot`, `violin`, `smooth`, `bar` w/o y): maps a computed stat to an aesthetic, e.g. `REMAPPING density AS y` instead of a layer's default stat.
+ - **SETTING** — literal aesthetic values or layer params (bypasses scales), e.g. `SETTING size => 5, stroke => 'red'`. Position adjustment: `'identity'` (default, most layers), `'stack'` (default bar/histogram/area), `'dodge'` (default boxplot/violin), `'jitter'`.
+ - **FILTER** — SQL WHERE condition on layer data: `FILTER sex = 'female' AND body_mass > 4000`.
+ - **PARTITION BY** — extra grouping columns beyond discrete mappings: `PARTITION BY Month`.
+ - **ORDER BY** — record order, matters for `path`: `ORDER BY timestamp`.
- ### PARTITION BY
+ ### Aggregate (a SETTING)
- Additional grouping columns beyond mapped discrete aesthetics:
+ Collapses each group (`PARTITION BY` cols + discrete mappings) to one row, replacing numeric mappings with aggregated values. Layers: `point line path bar area ribbon range segment rule text tile` (not stat layers, which have their own).
```ggsql
- DRAW line
- MAPPING Day AS x, Temp AS y
- PARTITION BY Month
+ SETTING aggregate => '<spec>' -- single
+ SETTING aggregate => ('<spec>', '<spec>', …) -- list
```
+ Spec is **untargeted** `'<func>'` (every unmapped-target numeric aesthetic; ≤2 untargeted defaults — 1st for lower-side aesthetics x/xmin/etc + all non-range layers, 2nd for upper-side xend/xmax) or **targeted** `'<aes>:<func>'` (overrides untargeted for that aesthetic).
- ### ORDER BY
+ Functions — reductions: `count sum prod min max range mid mean median geomean harmean rms sdev var iqr se p05–p95`; positional (need upstream `ORDER BY`): `first last diff`; band `<offset>±[<mult>]<expansion>` e.g. `'mean+1.96sdev'` (offsets: `mean median geomean harmean rms sum prod min max mid p05–p95`; expansions: `sdev se var iqr range`).
- Controls record order (important for path layers):
+ **Explosion**: targeting one aesthetic with multiple functions emits one row/function/group, tagged by a synthetic `aggregate` column — drive another aesthetic via `REMAPPING aggregate AS <aes>`. Equal-length exploded aesthetics run in lockstep; single-function targets repeat each row. Mixed lengths >1 error.
```ggsql
- DRAW path
- ORDER BY timestamp
+ -- min/max envelope as two lines per group, coloured by function
+ DRAW line MAPPING Date AS x, Temp AS y
+ REMAPPING aggregate AS color
+ SETTING aggregate => ('y:min', 'y:max')
+ PARTITION BY Year
```
+ **Scale interaction**: for a *targeted* aesthetic, `SCALE BINNED` runs after aggregation (so stats aren't cancelled within a bin); untargeted `SCALE BINNED` still bins pre-aggregate to drive grouping. Continuous censoring (`SCALE <aes> FROM (lo, hi)`) and discrete OOB filtering defer to post-aggregate whenever that aesthetic is aggregated.
## PLACE clause
- Creates annotation layers with literal values only (no data mappings). Supports tuples for multiple annotations.
-
- ```
- PLACE <layer-type>
- SETTING <aesthetic/param> => <value>, ...
- ```
-
+ Annotation layer, literal values only, no data mapping; tuples for multiple annotations.
```ggsql
PLACE point SETTING x => 5, y => 10, color => 'red'
- PLACE rule SETTING y => 70, linetype => 'dotted'
PLACE text SETTING x => (34, 44), y => (66, 49), label => ('Mean = 34', 'Mean = 44')
```
## SCALE clause
- Controls how data values are translated to aesthetic values. Sensible defaults are always provided.
-
+ Maps data → aesthetic output; sensible defaults always apply. Only `aesthetic` is required.
```
SCALE <type> <aesthetic> FROM <input-range> TO <output-range> VIA <transform>
SETTING <param> => <value>, ...
RENAMING <value> => <label>, ...
```
-
- All parts except `aesthetic` are optional.
-
- ### Scale types (optional, placed before aesthetic)
-
- - `CONTINUOUS` — continuous numeric/temporal data
- - `DISCRETE` — categorical/string data
- - `BINNED` — bin continuous data into discrete groups (never auto-selected, must be explicit)
- - `ORDINAL` — ordered discrete data (never auto-selected, must be explicit)
- - `IDENTITY` — pass data through unchanged (no legend created)
-
- If omitted, type is inferred from data.
-
- ### Aesthetic names
-
- Use the base name: `x`, `y`, `fill`, `stroke`, `color` (sets both fill and stroke), `opacity`, `size`, `linewidth`, `linetype`, `shape`, `panel` (facet), `row`, `column`.
-
- For position families (xmin/xmax/xend/ymin/ymax/yend), scale with the base name: `SCALE x ...`
-
- ### FROM (input range)
-
- - Continuous: `FROM (min, max)` — use `null` to infer from data: `FROM (0, null)`
- - Discrete: `FROM ('A', 'B', 'C')` — controls order, omitted values are nulled
- - Include null explicitly: `FROM ('Torgersen', 'Biscoe', null)`
-
- ### TO (output range)
-
- - Array of values: `TO ('red', 'blue', 'green')`, `TO (1, 6)`
- - Named palette: `TO viridis`, `TO dark2`, `TO tableau10`
-
- ### VIA (transform)
-
- Continuous transforms: `linear`, `log`, `log2`, `ln`, `exp10`, `exp2`, `exp`, `sqrt`, `square`, `asinh`, `pseudo_log`, `pseudo_log2`, `pseudo_ln`, `integer`
-
- Temporal transforms: `date`, `datetime`, `time` — automatically chosen for date/datetime/time columns.
-
- Discrete transforms: `string`, `bool`
-
- ```ggsql
- SCALE x VIA date -- treat x as temporal
- SCALE y VIA log -- log transform
- SCALE size VIA square -- scale by radius not area
- ```
-
- ### SETTING
-
- Continuous/binned scales:
- - `expand` — expansion factor, scalar or `(mult, add)`. Default `0.05`. Only for x/y.
- - `oob` — out-of-bounds: `'keep'` (default for x/y), `'censor'` (default for others), `'squish'`
- - `breaks` — integer count, array of values, or interval string for temporal (e.g. `'2 months'`, `'week'`)
- - `pretty` — boolean, default `true`. Use Wilkinson's algorithm for nice breaks.
- - `reverse` — boolean, default `false`. Reverse scale direction.
-
- Binned scales additionally:
- - `closed` — `'left'` (default) or `'right'`
-
- Discrete/ordinal scales:
- - `reverse` — boolean
+ - **Type** (before aesthetic; inferred if omitted): `CONTINUOUS`, `DISCRETE`, `BINNED` (bin continuous→discrete, never auto), `ORDINAL` (never auto), `IDENTITY` (pass through, no legend).
+ - **Aesthetic** — base name only: `x y fill stroke color(=fill+stroke) opacity size linewidth linetype shape panel row column`. Position families (xmin/xmax/xend/ymin/ymax/yend) scale via base name (`SCALE x ...`).
+ - **FROM** — continuous `(min, max)`, `null` infers (`(0, null)`); discrete `('A','B','C')` sets order & nulls the rest, or include null explicitly.
+ - **TO** — value array (`('red','blue')`, `(1, 6)`) or named palette (`viridis`, `dark2`, `tableau10`).
+ - **VIA** — continuous: `linear log log2 ln exp10 exp2 exp sqrt square asinh pseudo_log pseudo_log2 pseudo_ln integer`; temporal (auto for date/datetime/time cols): `date datetime time`; discrete: `string bool`.
+ - **SETTING** — continuous/binned: `expand` (factor or `(mult,add)`, default 0.05, x/y only), `oob` (`'keep'` default x/y, `'censor'` default others, `'squish'`), `breaks` (count/array/interval string e.g. `'2 months'`), `pretty` (bool, default true), `reverse` (bool). Continuous only: `minor_breaks` (count/array/interval string; ignored by Vega-Lite). Binned only: `closed` (`'left'`/`'right'`). Discrete/ordinal: `reverse`.
+ - **RENAMING** — direct + wildcard formatting (direct wins): `RENAMING 'Adelie' => 'Pygoscelis adeliae', 'adelie' => null` or `RENAMING * => '{:Title}'` (formatters: `Title UPPER lower`, time `%B %Y`, num `%.1f`).
```ggsql
- SCALE x SETTING breaks => '2 months'
+ SCALE x VIA date SETTING breaks => '2 months'
SCALE y FROM (0, 100) SETTING oob => 'squish'
SCALE BINNED x SETTING breaks => 10, pretty => false
```
- ### RENAMING
-
- Rename break labels. Direct renaming, wildcard formatting, or both (direct takes priority):
-
- ```ggsql
- RENAMING 'Adelie' => 'Pygoscelis adeliae', 'adelie' => null -- direct / suppress
- RENAMING * => '{} mm' -- string interpolation
- RENAMING * => '{:Title}' -- formatters: Title, UPPER, lower, time %B %Y, num %.1f
- ```
-
## FACET clause
- Split data into small multiples.
-
- ```
- FACET <column> BY <column>
- SETTING <param> => <value>, ...
- ```
-
- - 1D: `FACET region` — wrap layout, aesthetic name is `panel`
- - 2D: `FACET region BY category` — grid layout, aesthetics are `row` and `column`
-
- ### Settings
-
- - `free` — `null` (default/fixed), `'x'`, `'y'`, or `('x', 'y')` for independent scales
- - `missing` — `'repeat'` (default, show layer in all panels) or `'null'` (only show in null panel)
- - `ncol`/`nrow` — layout dimensions for 1D faceting (only one allowed)
-
- ### Customizing strip labels
-
- Use SCALE on the facet aesthetic:
+ Small multiples. 1D `FACET region` (wrap, aesthetic `panel`); 2D `FACET region BY category` (grid, aesthetics `row`/`column`). Settings: `free` (`null` default/fixed, `'x'`, `'y'`, `('x','y')`), `missing` (`'repeat'` default / `'null'`), `ncol`/`nrow` (1D only, pick one). Customize/filter via SCALE on the facet aesthetic:
```ggsql
FACET region
- SCALE panel
- RENAMING 'N' => 'North', 'S' => 'South'
- ```
-
- ### Filtering panels
-
- Use SCALE FROM to select which panels to show:
- ```ggsql
+ SCALE panel RENAMING 'N' => 'North', 'S' => 'South'
FACET island
- SCALE panel FROM ('Biscoe', 'Dream')
+ SCALE panel FROM ('Biscoe', 'Dream') -- filters panels shown
```
## PROJECT clause
- Controls the coordinate system.
-
+ Coordinate system.
```
- PROJECT <aesthetic>, ... TO <coord-type>
- SETTING <param> => <value>, ...
+ PROJECT <aesthetic>, ... TO <coord-type> SETTING <param> => <value>, ...
```
-
- ### Coordinate types
-
- **cartesian** (default) — horizontal x, vertical y
- - Settings: `clip` (boolean, default true), `ratio` (aspect ratio number or null)
- - Default aesthetics: `x`, `y`
-
- **polar** — angle + radius from center
- - Settings: `clip`, `start` (degrees, default 0 = 12 o'clock), `end` (degrees, default start+360), `inner` (0-1 proportion for donut hole, default 0)
- - Default aesthetics: `radius` (primary), `angle` (secondary)
-
- Swap aesthetic order to flip axes: `PROJECT y, x TO cartesian`. If no PROJECT clause, coordinate type is inferred from mappings (x/y = cartesian, radius/angle = polar).
-
+ `cartesian` (default): aesthetics x/y; settings `clip` (bool, default true), `ratio` (number or null). `polar`: aesthetics `radius`(primary)/`angle`(secondary); settings `clip`, `start`/`end` (degrees, default 0/start+360), `inner` (0-1 donut hole, default 0). Swap order to flip axes (`PROJECT y, x TO cartesian`); without PROJECT, type is inferred from mappings.
```ggsql
- PROJECT TO polar SETTING inner => 0.5 -- donut chart
- PROJECT TO polar SETTING start => -90, end => 90 -- half-circle gauge
+ PROJECT TO polar SETTING inner => 0.5 -- donut chart
```
## LABEL clause
- Override default axis/legend labels and add titles.
-
- ```
- LABEL
- <aesthetic/title> => <string>, ...
- ```
-
- Available labels:
- - `title` — main title
- - `subtitle` — subtitle below title
- - `caption` — text below the plot
- - Any aesthetic name — axis/legend title: `x`, `y`, `fill`, `color`, etc.
- - Use `null` to suppress a label: `fill => null`
-
+ Overrides axis/legend labels & titles: `title`, `subtitle`, `caption`, or any aesthetic name; `null` suppresses.
```ggsql
- LABEL
- title => 'Sales by Region',
- subtitle => 'Q4 2024 data',
- x => 'Date',
- y => 'Revenue (USD)',
- fill => 'Region',
- caption => 'Source: internal sales database'
+ LABEL title => 'Sales by Region', x => 'Date', y => 'Revenue (USD)', fill => null
```
---
## Layer types
- ### point
- Scatterplot. Required: x, y. Optional: size, colour, stroke, fill, opacity, shape.
-
- ### line
- Line plot sorted along primary axis. Required: x, y. Optional: colour/stroke, opacity, linewidth, linetype. Settings: `position`, `orientation` (`'aligned'`/`'transposed'`).
-
- ### path
- Like line but connects points in data order (not sorted). Same aesthetics as line.
-
- ### bar
- Bar chart. Auto-counts if y not provided. Optional: x (categories), y (height), fill, colour, stroke. Stats: `count`, `proportion`. Properties: `weight`. Settings: `position` (default `'stack'`), `width` (0-1). Orientation inferred from mapping (categories on x = vertical, on y = horizontal).
-
- ```ggsql
- DRAW bar MAPPING species AS x -- auto-count
- DRAW bar MAPPING species AS x, total AS y -- pre-computed
- DRAW bar MAPPING species AS x, sex AS fill -- stacked (default)
- SETTING position => 'dodge' -- side by side
- ```
-
- ### histogram
- Bins continuous data. Required: x. Stats: `count`, `density`. Default remapping: `count AS <secondary>`. Settings: `position` (default `'stack'`), `bins` (default 30), `binwidth`, `closed` (`'left'`/`'right'`).
-
- ```ggsql
- DRAW histogram MAPPING body_mass AS x SETTING binwidth => 100
- DRAW histogram MAPPING body_mass AS x REMAPPING density AS y -- density instead of count
- ```
-
- ### density
- Kernel density estimation. Required: x. Stats: `density`, `intensity`. Settings: `position` (default `'identity'`), `bandwidth`, `adjust` (default 1), `kernel` (`'gaussian'` default, `'epanechnikov'`, `'triangular'`, `'rectangular'`, `'biweight'`, `'cosine'`).
-
- ### boxplot
- Five-number summary with outliers. Required: x (categorical), y (continuous). Stats: `type`, `value`. Settings: `position` (default `'dodge'`), `outliers` (default true), `coef` (whisker IQR multiple, default 1.5), `width` (default 0.9).
-
- ### violin
- Mirrored kernel density for groups. Required: x (categorical), y (continuous). Stats: `density`, `intensity`. Default remapping: `density AS offset`. Settings: `position` (default `'dodge'`), `bandwidth`, `adjust`, `kernel` (same as density), `width` (default 0.9), `side` (`'both'`/`'left'`/`'bottom'`/`'right'`/`'top'`), `tails` (number or null, default 3).
-
- ### smooth
- Trendline. Required: x, y. Stats: `intensity`. Settings: `method` (`'nw'` default, `'ols'`, `'tls'`), `bandwidth`, `adjust`, `kernel` (same as density, nw only).
-
- ### area
- Area chart anchored at zero. Required: x, y. Settings: `position` (default `'stack'`), `orientation`, `total` (normalize stacks), `center` (boolean, for steamgraph).
-
- ### ribbon
- Like area but with explicit ymin/ymax (unanchored). Required: x, ymin, ymax.
-
- ### segment
- Line segments between two endpoints. Required: x, y, xend, yend. For axis-aligned intervals where one coordinate is shared between start and end, use `range` instead.
-
- ### rule
- Reference lines spanning the full panel. Required: x or y. Optional: `slope` (for diagonal: `y = a + slope * x`).
-
- ### text
- Text labels. Required: x, y, label. Settings: `offset` (number or `(h, v)`), `format` (string interpolation like RENAMING). `hjust`: `'left'`/`'right'`/`'centre'` or 0-1. `vjust`: `'top'`/`'bottom'`/`'middle'` or 0-1.
-
- ### rect
- Rectangles. Required: pick 2 per axis from center (x/y), min (xmin/ymin), max (xmax/ymax), width, height. Or just center (defaults width/height to 1).
-
- ### polygon
- Closed shapes from ordered coordinates. Required: x, y. Use PARTITION BY to separate distinct polygons.
-
- ### range
- Range/interval display between two values along the secondary axis. Required: x, ymin, ymax. Settings: `width` (hinge width in points, default 10, null to hide).
-
- All layers accept common optional aesthetics (colour/stroke, fill, opacity, linewidth, linetype) and `position` setting where applicable.
+ - **point** — required x, y; optional size, colour, stroke, fill, opacity, shape.
+ - **line** — required x, y; sorted by primary axis; optional colour/stroke, opacity, linewidth, linetype; settings `position`, `orientation` (`'aligned'`/`'transposed'`).
+ - **path** — like line but data-order (unsorted); same aesthetics.
+ - **bar** — auto-counts if no y; optional x, y, fill, colour, stroke; stats `count`, `proportion`; property `weight`; settings `position` (default `'stack'`), `width`. Orientation from mapping (x=vertical, y=horizontal).
+ - **histogram** — required x; stats `count`, `density` (default remap `count AS <secondary>`); settings `position` (`'stack'`), `bins` (30), `binwidth`, `closed`.
+ - **density** — required x; stats `density`, `intensity`; settings `position` (`'identity'`), `bandwidth`, `adjust` (1), `kernel` (`'gaussian'` default, `epanechnikov triangular rectangular biweight cosine`).
+ - **boxplot** — required x (cat), y (cont); stats `type`, `value`; settings `position` (`'dodge'`), `outliers` (true), `coef` (1.5), `width` (0.9), `hinge` (points, default null/hidden).
+ - **violin** — required x (cat), y (cont); stats `density`, `intensity` (default remap `density AS offset`); settings `position` (`'dodge'`), `bandwidth`, `adjust`, `kernel`, `width` (0.9), `side` (`'both' 'left' 'bottom' 'right' 'top'`), `tails` (default 3).
+ - **smooth** — required x, y; stat `intensity`; settings `method` (`'nw'` default, `'ols'`, `'tls'`), `bandwidth`, `adjust`, `kernel` (nw only).
+ - **area** — required x, y, anchored at zero; settings `position` (`'stack'`), `orientation`, `total` (normalize), `center` (steamgraph).
+ - **ribbon** — like area but explicit ymin/ymax, unanchored.
+ - **segment** — required x, y, xend, yend; use `range` instead when one coord is shared between start/end.
+ - **rule** — required x or y (full-panel reference line); optional `slope` (diagonal: `y = a + slope*x`).
+ - **text** — required x, y, label; settings `offset` (number or `(h,v)`), `format` (RENAMING-style interpolation), `parse` (bool, default true: markdown `**bold**`/`*italic*`/`~~strike~~`/`` `code` ``/`{.red span}`; not in Vega-Lite), `hjust` (`'left' 'right' 'centre'` or 0-1), `vjust` (`'top' 'bottom' 'middle'` or 0-1).
+ - **rect** — pick 2 per axis from center/min/max/width/height, or just center (defaults size to 1).
+ - **polygon** — required x, y; ordered coords; PARTITION BY separates distinct polygons.
+ - **range** — required x, ymin, ymax; setting `hinge` (points, default 10, null hides).
- ---
+ All layers accept colour/stroke, fill, opacity, linewidth, linetype, and `position` where applicable.
## Named color palettes
- - **Discrete**: `ggsql10` (default), `tableau10`, `category10`, `set1`, `set2`, `set3`, `dark2`, `paired`, `pastel1`, `pastel2`, `accent`, `kelly22`
- - **Sequential**: `sequential` (default), `viridis`, `plasma`, `magma`, `inferno`, `cividis`, `blues`, `greens`, `oranges`, `reds`, `purples`, `greys`, `ylgnbu`, `ylorbr`, `ylorrd`, `batlow`, `hawaii`, `lajolla`, `turku`, and more
- - **Diverging**: `vik`/`diverging`, `rdbu`, `rdylbu`, `rdylgn`, `spectral`, `brbg`, `prgn`, `piyg`, `puor`, `berlin`, `roma`, and more
- - **Cyclic**: `romao`/`cyclic`, `bamo`, `broco`, `corko`, `viko`
-
- ---
+ - Discrete: `ggsql10`(default) `tableau10 category10 set1 set2 set3 dark2 paired pastel1 pastel2 accent kelly22`
+ - Sequential: `sequential`(default) `viridis plasma magma inferno cividis blues greens oranges reds purples greys ylgnbu ylorbr ylorrd batlow hawaii lajolla turku` …
+ - Diverging: `vik`/`diverging` `rdbu rdylbu rdylgn spectral brbg prgn piyg puor berlin roma` …
+ - Cyclic: `romao`/`cyclic` `bamo broco corko viko`
## Common patterns
```ggsql
- -- Pie chart
+ -- Pie chart: bar layer in polar coords
VISUALISE species AS fill FROM ggsql:penguins
DRAW bar
PROJECT TO polar
- -- Horizontal bar chart
- DRAW bar MAPPING species AS y
-
-- Multi-series line chart
VISUALISE Date AS x
DRAW line MAPPING Temp AS y, 'Temperature' AS color
DRAW line MAPPING Ozone AS y, 'Ozone' AS color
SCALE x VIA date
-- Lollipop chart
SELECT ROUND(bill_dep) AS bill_dep, COUNT(*) AS n FROM ggsql:penguins GROUP BY 1
VISUALISE bill_dep AS x
- DRAW range MAPPING 0 AS ymin, n AS ymax SETTING width => null
+ DRAW range MAPPING 0 AS ymin, n AS ymax SETTING hinge => null
DRAW point MAPPING n AS y
-- Ridgeline / joy plot
VISUALISE Temp AS x, Month AS y FROM ggsql:airquality
DRAW violin SETTING width => 4, side => 'top'
SCALE ORDINAL y
- -- Bar labels
- SELECT island, COUNT(*) AS n FROM ggsql:penguins GROUP BY island
- VISUALISE island AS x, n AS y
- DRAW bar
- DRAW text MAPPING n AS label SETTING vjust => 'top', offset => (0, -11), fill => 'white'
-
- -- CTEs with separate layer data
- WITH temps AS (SELECT Date, Temp as value FROM ggsql:airquality),
- ozone AS (SELECT Date, Ozone as value FROM ggsql:airquality WHERE Ozone IS NOT NULL)
- VISUALISE
- DRAW line MAPPING Date AS x, value AS y, 'Temperature' AS color FROM temps
- DRAW point MAPPING Date AS x, value AS y, 'Ozone' AS color FROM ozone
- SCALE x VIA date
+ -- Mean ± 1.96·sdev band per group, as a ribbon
+ VISUALISE Day AS x, Temp AS ymin, Temp AS ymax FROM ggsql:airquality
+ DRAW ribbon SETTING aggregate => ('mean-1.96sdev', 'mean+1.96sdev') PARTITION BY Month
```
- ---
-
## CLI
- The `ggsql` CLI should be on the PATH. Subcommands: `exec <QUERY>`, `run <FILE>`, `validate <QUERY>`, `parse <QUERY>`. Common options: `--reader <URI>` (default `duckdb://memory`), `--writer <FORMAT>` (default `vegalite`), `--output <PATH>`, `-v` (verbose).
+ `ggsql` subcommands: `exec <QUERY>`, `run <FILE>`, `validate <QUERY>`, `parse <QUERY>`, `view <QUERY>` (window, blocks until closed). Options: `--reader <URI>` (default `duckdb://memory`), `--writer <FORMAT>` (default `vegalite`), `--output <PATH>` (extension picks writer), `-D key=value`, `-v`. Writers: `vegalite svg pdf hep` (no GPU) and `png jpeg tiff webp` (GPU, not every build).
+ **Don't run `ggsql view`** unless a window was requested — it blocks and you can't close it; use `--output` instead. **Prefer `svg`/`pdf`** for pictures (no GPU adapter needed); `ggsql exec --help` lists available writers.
+
```bash
- ggsql validate "VISUALISE x, y FROM data DRAW point"
ggsql exec "VISUALISE bill_len AS x, bill_dep AS y FROM ggsql:penguins DRAW point" -v
- ggsql run query.sql --output chart.vl.json
+ ggsql exec "VISUALISE species AS fill FROM ggsql:penguins DRAW bar" -o chart.svg
```
- ---
-
- ## Additional References
-
- * https://ggsql.org/syntax/index.llms.md — Online documentation with the latest syntax
+ ## Reference
- ---
+ https://ggsql.org/syntax/index.llms.md — latest syntax docs.
## Instructions for responding
- 1. Write a complete, valid ggsql query matching the user's request.
- 2. Use SQL CTEs/queries before VISUALISE when data shaping is needed.
- 3. Choose the simplest layer types and settings that achieve the goal.
- 4. Include SCALE clauses when the defaults are insufficient (e.g. date formatting, custom palettes, range limits).
- 5. Include LABEL for titles when the context warrants it.
- 6. Briefly explain your choices after the query.
- 7. NEVER invent syntax, settings, aesthetics, layer types, or palette names not documented above.
- 8. If unsure whether a feature exists, say so rather than guessing.
- 9. Use `ggsql:penguins` or `ggsql:airquality` as example data when no specific data is mentioned.
- 10. When the user wants to validate a query, use `ggsql validate "<query>"`. When the user wants to see the output, use `ggsql exec "<query>" -v`.
+ 1. Write a complete, valid ggsql query for the request; use SQL/CTEs before VISUALISE for data shaping.
+ 2. Choose the simplest layer types/settings that work; add SCALE for formatting/palettes/ranges and LABEL for titles when warranted.
+ 3. Briefly explain your choices after the query. Never invent syntax — say so if unsure.
+ 4. Default to `ggsql:penguins`/`ggsql:airquality` when no data is specified.
+ 5. Use `ggsql validate "<query>"` to validate, `ggsql exec "<query>" -v` to run and show output.