Ggsql

ggsql Query Writer

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

Optional SQL part + required VISUALISE/VISUALIZE part.

• SELECT → VISUALISE: last statement is a SELECT (bare, WITH...SELECT, set ops); its result feeds VISUALISE, which has no FROM.

  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.

  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 (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

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 (name = aesthetic name); wildcard * (all matching columns); constant 'red' AS fill.

VISUALISE bill_len AS x, bill_dep AS y, species AS fill FROM ggsql:penguins
VISUALISE * FROM my_table

DRAW clause

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>, ...

• 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.

Aggregate (a SETTING)

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).

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).

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).

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.

-- 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

Annotation layer, literal values only, no data mapping; tuples for multiple annotations.

PLACE point SETTING x => 5, y => 10, color => 'red'
PLACE text SETTING x => (34, 44), y => (66, 49), label => ('Mean = 34', 'Mean = 44')

SCALE clause

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>, ...

• 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).

SCALE x VIA date SETTING breaks => '2 months'
SCALE y FROM (0, 100) SETTING oob => 'squish'
SCALE BINNED x SETTING breaks => 10, pretty => false

FACET clause

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:

FACET region
SCALE panel RENAMING 'N' => 'North', 'S' => 'South'
FACET island
SCALE panel FROM ('Biscoe', 'Dream')   -- filters panels shown

PROJECT clause

Coordinate system.

PROJECT <aesthetic>, ... TO <coord-type> SETTING <param> => <value>, ...

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.

PROJECT TO polar SETTING inner => 0.5   -- donut chart