ggsql

Files (1)

• SKILL.md

SKILL.md (first lines)

# 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`.
  ```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
  ```

**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`.
```ggsql
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).
```ggsql
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.
```ggsql
-- 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.
```ggsql
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`).

```ggsql
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:
```ggsql
FACET region
SCALE panel RENAMING 'N' => 'North', 'S' => 'South'
FACET island
SCALE panel FROM ('Biscoe', 'Dream')   -- filters panels shown
```

## PROJECT clause

Coordinate system.
```

From posit-dev/skills, MIT.

More from posit-dev/skills