How PostgreSQL’s SQL dialect stays ahead of its competitors @MarkusWinand
I’m sorry!
I couldn’t make it to PgConf.EU in 2014!
Don’t repeat my mistake.
Come to PgConf.EU 2018 in Lisbon! Oct 23-26.
How PostgreSQL’s SQL dialect stays ahead of its competitors @MarkusWinand
PostgreSQL 9.5 2016-01-07
GROUPING SETS
Before SQL:1999 GROUPING SETS Only one GROUP BY operation at a time: Monthly revenue Yearly revenue SELECT year SELECT year , month , sum(revenue) , sum(revenue) FROM tbl FROM tbl GROUP BY year, month GROUP BY year
Before SQL:1999 GROUPING SETS SELECT year , month , sum(revenue) FROM tbl GROUP BY year, month UNION ALL SELECT year , null , sum(revenue) FROM tbl GROUP BY year
Since SQL:1999 GROUPING SETS SELECT year SELECT year , month , month , sum(revenue) , sum(revenue) FROM tbl FROM tbl GROUP BY year, month GROUP BY UNION ALL GROUPING SETS ( SELECT year (year, month) , null , (year) , sum(revenue) ) FROM tbl GROUP BY year
In a Nutshell GROUPING SETS GROUPING SETS are multiple GROUP BY s in one go () (empty parenthesis) build a group over all rows GROUPING (function) disambiguates the meaning of NULL (was the grouped data NULL or is this column not currently grouped?) Permutations can be created using ROLLUP and CUBE ( ROLLUP(a,b,c) = GROUPING SETS ((a,b,c), (a,b),(a),())
Availability GROUPING SETS 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 MariaDB 5.1 [0] MySQL 5.0 [1] PostgreSQL 9.5 SQLite DB2 LUW 5 Oracle 9iR1 SQL Server 2008 [0] Only ROLLUP (properitery syntax). [1] Only ROLLUP (properitery syntax). GROUPING function since MySQL 8.0.
TABLESAMPLE
Since SQL:2003 TABLESAMPLE Or: bernoulli(10) better sampling, way slower. SELECT * FROM tbl TABLESAMPLE system(10) REPEATABLE (0) User provided seed value
Since SQL:2003 TABLESAMPLE Or: bernoulli(10) better sampling, way slower. SELECT * FROM tbl TABLESAMPLE system(10) REPEATABLE (0) User provided seed value
Availability TABLESAMPLE 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 MariaDB 5.1 MySQL PostgreSQL 9.5 [0] SQLite DB2 LUW 8.2 [0] Oracle 8i [0] SQL Server 2005 [0] [0] Not for derived tables
PostgreSQL 10 2017-10-05
XMLTABLE
Since SQL:2006 XMLTABLE Stored in tbl.x : SELECT id , c1 <d> XPath * expression , n to identify rows <e id="42"> FROM tbl <c1>…</c1> , XMLTABLE( </e> '/d/e' </d> PASSING x COLUMNS id INT PATH '@id' , c1 VARCHAR(255) PATH 'c1' , n FOR ORDINALITY ) r *Standard SQL allows XQuery
Since SQL:2006 XMLTABLE Stored in tbl.x : SELECT id , c1 <d> , n <e id="42"> FROM tbl <c1>…</c1> , XMLTABLE( </e> '/d/e' </d> PASSING x COLUMNS id INT PATH '@id' , c1 VARCHAR(255) PATH 'c1' , n FOR ORDINALITY ) r *Standard SQL allows XQuery
Since SQL:2006 XMLTABLE Stored in tbl.x : SELECT id , c1 <d> , n <e id="42"> FROM tbl <c1>…</c1> XPath * expressions , XMLTABLE( </e> to extract data '/d/e' </d> PASSING x COLUMNS id INT PATH '@id' , c1 VARCHAR(255) PATH 'c1' , n FOR ORDINALITY ) r *Standard SQL allows XQuery
Since SQL:2006 XMLTABLE Stored in tbl.x : SELECT id , c1 <d> , n <e id="42"> FROM tbl <c1>…</c1> , XMLTABLE( </e> '/d/e' </d> Row number PASSING x (like for unnest) COLUMNS id INT PATH '@id' , c1 VARCHAR(255) PATH 'c1' , n FOR ORDINALITY ) r *Standard SQL allows XQuery
Since SQL:2006 XMLTABLE Stored in tbl.x : SELECT id Result , c1 <d> id | c1 | n , n <e id="42"> ----+----+--- FROM tbl <c1>…</c1> 42 | … | 1 , XMLTABLE( </e> '/d/e' </d> PASSING x COLUMNS id INT PATH '@id' , c1 VARCHAR(255) PATH 'c1' , n FOR ORDINALITY ) r *Standard SQL allows XQuery
Availability XMLTABLE 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 MariaDB MySQL 10 [0] PostgreSQL SQLite DB2 LUW 9.7 Oracle 11gR1 SQL Server [0] No XQuery (only XPath). No default namespace declaration.
IDENTITY COLUMNS
IDENTITY column Since SQL:2003 Similar to serial columns, but standard and more powerful. CREATE TABLE tbl ( id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10) , PRIMARY KEY (id) )
IDENTITY column Since SQL:2003 Similar to serial columns, but standard and more powerful. CREATE TABLE tbl ( id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10) , PRIMARY KEY (id) ) More (esp. vs. serial): https://blog.2ndquadrant.com/postgresql-10-identity-columns/
IDENTITY column Availability 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 MariaDB 5.1 MySQL PostgreSQL 10 SQLite DB2 LUW 7.0 Oracle 12cR1 SQL Server
PostgreSQL 11 201?-??-??
Not new, but bear with me OVER and PARTITION BY
OVER (PARTITION BY) The Problem Two distinct concepts could not be used independently: ‣ Merge rows with the same key properties ‣ GROUP BY to specify key properties ‣ DISTINCT to use full row as key ‣ Aggregate data from related rows ‣ Requires GROUP BY to segregate the rows ‣ COUNT , SUM , AVG , MIN , MAX to aggregate grouped rows
OVER (PARTITION BY) The Problem No ⇠ Aggregate ⇢ Yes SELECT c1 SELECT c1 Yes ⇠ Merge rows ⇢ No , c2 , c2 FROM t FROM t SELECT DISTINCT SELECT c1 SELECT c1 c1 , SUM (c2) tot , SUM (c2) tot , c2 FROM t FROM t FROM t GROUP BY c1 GROUP BY c1
OVER (PARTITION BY) The Problem No ⇠ Aggregate ⇢ Yes SELECT c1 SELECT c1 Yes ⇠ Merge rows ⇢ No , tot , c2 , c2 FROM t FROM t SELECT c1 JOIN ( ) ta , SUM (c2) tot FROM t GROUP BY c1 ON (t.c1=ta.c1) SELECT DISTINCT SELECT c1 c1 , SUM (c2) tot , c2 FROM t FROM t GROUP BY c1
OVER (PARTITION BY) The Problem No ⇠ Aggregate ⇢ Yes SELECT c1 SELECT c1 Yes ⇠ Merge rows ⇢ No , tot , c2 , c2 FROM t FROM t SELECT c1 JOIN ( ) ta , SUM (c2) tot FROM t GROUP BY c1 ON (t.c1=ta.c1) SELECT DISTINCT SELECT c1 c1 , SUM (c2) tot , c2 FROM t FROM t GROUP BY c1
OVER (PARTITION BY) Since SQL:2003 No ⇠ Aggregate ⇢ Yes SELECT c1 SELECT c1 Yes ⇠ Merge rows ⇢ No , c2 , c2 FROM t FROM t , SUM(c2) OVER (PARTITION BY c1) FROM t SELECT DISTINCT SELECT c1 c1 , SUM (c2) tot , c2 FROM t FROM t GROUP BY c1
OVER (PARTITION BY) How it works dep salary ts SELECT dep, 1 1000 6000 salary, 22 1000 6000 SUM(salary) 22 1000 6000 OVER() 333 1000 6000 FROM emp 333 1000 6000 333 1000 6000
OVER (PARTITION BY) How it works dep salary ts SELECT dep, 1 1000 6000 salary, 22 1000 6000 SUM(salary) 22 1000 6000 OVER() 333 1000 6000 FROM emp 333 1000 6000 333 1000 6000
OVER (PARTITION BY) How it works dep salary ts SELECT dep, 1 1000 1000 salary, 22 1000 2000 SUM(salary) 22 1000 2000 OVER() PARTITION BY dep ) 333 1000 3000 FROM emp 333 1000 3000 333 1000 3000
Still not new, but bear with me OVER and ORDER BY (Framing & Ranking)
OVER (ORDER BY) The Problem acnt id value balance SELECT id, 1 1 +10 +10 value, (SELECT SUM(value) 22 2 +20 +30 FROM transactions t2 22 3 -10 +20 WHERE t2.id <= t.id) 333 4 +50 +70 FROM transactions t 333 5 -30 +40 333 6 -20 +20
OVER (ORDER BY) The Problem acnt id value balance SELECT id, 1 1 +10 +10 value, (SELECT SUM(value) 22 2 +20 +30 FROM transactions t2 22 3 -10 +20 WHERE t2.id <= t.id) 333 4 +50 +70 FROM transactions t 333 5 -30 +40 Range segregation (<=) not possible with 333 6 -20 +20 GROUP BY or PARTITION BY
OVER (ORDER BY) Since SQL:2003 acnt id value balance SELECT id, 1 1 +10 +10 value, SUM(value) 22 2 +20 +30 OVER ( 22 3 -10 +20 ORDER BY id 333 4 +50 +70 333 5 -30 +40 333 6 -20 +20 ) FROM transactions t
OVER (ORDER BY) Since SQL:2003 acnt id value balance SELECT id, 1 1 +10 +10 value, SUM(value) 22 2 +20 +30 OVER ( 22 3 -10 +20 ORDER BY id 333 4 +50 +70 ROWS BETWEEN UNBOUNDED PRECEDING 333 5 -30 +40 AND CURRENT ROW 333 6 -20 +20 ) FROM transactions t
OVER (ORDER BY) Since SQL:2003 acnt id value balance SELECT id, 1 1 +10 +10 value, SUM(value) 22 2 +20 +30 OVER ( 22 3 -10 +20 ORDER BY id 333 4 +50 +70 ROWS BETWEEN UNBOUNDED PRECEDING 333 5 -30 +40 AND CURRENT ROW 333 6 -20 +20 ) FROM transactions t
OVER (ORDER BY) Since SQL:2003 acnt id value balance SELECT id, 1 1 +10 +10 value, SUM(value) 22 2 +20 +30 OVER ( 22 3 -10 +20 ORDER BY id 333 4 +50 +70 ROWS BETWEEN UNBOUNDED PRECEDING 333 5 -30 +40 AND CURRENT ROW 333 6 -20 +20 ) FROM transactions t
Recommend
More recommend
