Loading...
Loading...
Write ggsql queries — a grammar of graphics for SQL. Use when the user wants to create, modify, or understand a ggsql visualization query.
npx skill4agent add posit-dev/skills ggsqlVISUALISEVISUALIZEWITH...SELECTFROMSELECT 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]WITH summary AS (SELECT category, COUNT(*) AS n FROM 'dataset.csv' GROUP BY category)
VISUALISE category AS x, n AS y FROM summary
DRAW barVISUALISE ... FROMDRAW ... MAPPING ... FROMFROM salesFROM 'data.csv'FROM ggsql:penguinsFROM ggsql:airqualityVISUALISE <mapping>, ... FROM <data-source>column AS aestheticrevenue AS ycolumn*'red' AS fillVISUALISE bill_len AS x, bill_dep AS y, species AS fill FROM ggsql:penguins
VISUALISE * FROM my_tableDRAW <layer-type>
MAPPING <mapping>, ... FROM <data-source>
REMAPPING <stat-property> AS <aesthetic>, ...
SETTING <param> => <value>, ...
FILTER <condition>
PARTITION BY <column>, ...
ORDER BY <column>, ...FROMnullMAPPING null AS colorhistogramdensityboxplotviolinsmoothbarREMAPPING density AS ySETTING size => 5, stroke => 'red''identity''stack''dodge''jitter'FILTER sex = 'female' AND body_mass > 4000PARTITION BY MonthpathORDER BY timestampPARTITION BYpoint line path bar area ribbon range segment rule text tileSETTING aggregate => '<spec>' -- single
SETTING aggregate => ('<spec>', '<spec>', …) -- list'<func>''<aes>:<func>'count sum prod min max range mid mean median geomean harmean rms sdev var iqr se p05–p95ORDER BYfirst last diff<offset>±[<mult>]<expansion>'mean+1.96sdev'mean median geomean harmean rms sum prod min max mid p05–p95sdev se var iqr rangeaggregateREMAPPING aggregate AS <aes>-- 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 YearSCALE BINNEDSCALE BINNEDSCALE <aes> FROM (lo, hi)PLACE point SETTING x => 5, y => 10, color => 'red'
PLACE text SETTING x => (34, 44), y => (66, 49), label => ('Mean = 34', 'Mean = 44')aestheticSCALE <type> <aesthetic> FROM <input-range> TO <output-range> VIA <transform>
SETTING <param> => <value>, ...
RENAMING <value> => <label>, ...CONTINUOUSDISCRETEBINNEDORDINALIDENTITYx y fill stroke color(=fill+stroke) opacity size linewidth linetype shape panel row columnSCALE x ...(min, max)null(0, null)('A','B','C')('red','blue')(1, 6)viridisdark2tableau10linear log log2 ln exp10 exp2 exp sqrt square asinh pseudo_log pseudo_log2 pseudo_ln integerdate datetime timestring boolexpand(mult,add)oob'keep''censor''squish'breaks'2 months'prettyreverseminor_breaksclosed'left''right'reverseRENAMING 'Adelie' => 'Pygoscelis adeliae', 'adelie' => nullRENAMING * => '{:Title}'Title UPPER lower%B %Y%.1fSCALE x VIA date SETTING breaks => '2 months'
SCALE y FROM (0, 100) SETTING oob => 'squish'
SCALE BINNED x SETTING breaks => 10, pretty => falseFACET regionpanelFACET region BY categoryrowcolumnfreenull'x''y'('x','y')missing'repeat''null'ncolnrowFACET region
SCALE panel RENAMING 'N' => 'North', 'S' => 'South'
FACET island
SCALE panel FROM ('Biscoe', 'Dream') -- filters panels shownPROJECT <aesthetic>, ... TO <coord-type> SETTING <param> => <value>, ...cartesianclipratiopolarradiusangleclipstartendinnerPROJECT y, x TO cartesianPROJECT TO polar SETTING inner => 0.5 -- donut charttitlesubtitlecaptionnullLABEL title => 'Sales by Region', x => 'Date', y => 'Revenue (USD)', fill => nullpositionorientation'aligned''transposed'countproportionweightposition'stack'widthcountdensitycount AS <secondary>position'stack'binsbinwidthcloseddensityintensityposition'identity'bandwidthadjustkernel'gaussian'epanechnikov triangular rectangular biweight cosinetypevalueposition'dodge'outlierscoefwidthhingedensityintensitydensity AS offsetposition'dodge'bandwidthadjustkernelwidthside'both' 'left' 'bottom' 'right' 'top'tailsintensitymethod'nw''ols''tls'bandwidthadjustkernelposition'stack'orientationtotalcenterrangeslopey = a + slope*xoffset(h,v)formatparse**bold***italic*~~strike~~`code`{.red span}hjust'left' 'right' 'centre'vjust'top' 'bottom' 'middle'hingepositionggsql10tableau10 category10 set1 set2 set3 dark2 paired pastel1 pastel2 accent kelly22sequentialviridis plasma magma inferno cividis blues greens oranges reds purples greys ylgnbu ylorbr ylorrd batlow hawaii lajolla turkuvikdivergingrdbu rdylbu rdylgn spectral brbg prgn piyg puor berlin romaromaocyclicbamo broco corko viko-- Pie chart: bar layer in polar coords
VISUALISE species AS fill FROM ggsql:penguins
DRAW bar
PROJECT TO polar
-- 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 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
-- 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 Monthggsqlexec <QUERY>run <FILE>validate <QUERY>parse <QUERY>view <QUERY>--reader <URI>duckdb://memory--writer <FORMAT>vegalite--output <PATH>-D key=value-vvegalite svg pdf heppng jpeg tiff webpggsql view--outputsvgpdfggsql exec --helpggsql exec "VISUALISE bill_len AS x, bill_dep AS y FROM ggsql:penguins DRAW point" -v
ggsql exec "VISUALISE species AS fill FROM ggsql:penguins DRAW bar" -o chart.svgggsql:penguinsggsql:airqualityggsql validate "<query>"ggsql exec "<query>" -v