ggsql

What this skill does

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

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

Data sources can appear in `VISUALISE ... FROM` or `DRAW ... MAPPING ... FROM`:

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

## VISUALISE clause

Marks the start of the visualization. Optionally defines global mappings inherited by all layers.

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

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

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

### PARTITION BY

Additional grouping columns beyond mapped discrete aesthetics:
```ggsql
DRAW line
  MAPPING Day AS x, Temp AS y
  PARTITION BY Month
```

### ORDER BY

Controls record order (important for path layers):
```ggsql
DRAW path
  ORDER BY timestamp
```

## PLACE clause

Creates annotation layers with literal values only (no data mappings). Supports tuples for multiple annotations.

```
PLACE <layer-type>
  SETTING <aesthetic/param> => <value>, ...
```

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

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

```ggsql
SCALE x 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>, ...
```

-