More Than a Query Language: SQL in the 21 st Century @MarkusWinand - - PowerPoint PPT Presentation
More Than a Query Language: SQL in the 21 st Century @MarkusWinand @ModernSQL http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf More Than a Query Language: SQL in the 21 st Century @MarkusWinand @ModernSQL
More Than a Query Language: SQL in the 21st Century
@MarkusWinand • @ModernSQL
http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf
More Than a Query Language: SQL in the 21st Century
@MarkusWinand • @ModernSQL
http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf
Safe Harbour Statement …
Safe Harbour Statement … Instead of a
Safe Harbour Statement … Take this Safe the Planet Statement:
I’m traveling a lot for business and always aim for the most environmental friendly way to do so. If I cannot avoid flying, I offset its climate impact at a transparent climate protection company. (currently atmosfair.de)
Picture: https://upload.wikimedia.org/wikipedia/commons/2/2c/North_America_from_low_orbiting_satellite_Suomi_NPP .jpg
Instead of a
SQL-92 — Tied to the Relational Idea
SQL-92 — Tied to the Relational Idea
Relational Data Model
SQL-92 — Tied to the Relational Idea
Relational Data Model
Atom image: https://commons.wikimedia.org/wiki/File:Stylised_atom_with_three_Bohr_model_orbits_and_stylised_nucleus.png
SQL-92 — Tied to the Relational Idea
Relational Data Model
A B C
Atom image: https://commons.wikimedia.org/wiki/File:Stylised_atom_with_three_Bohr_model_orbits_and_stylised_nucleus.png
SQL-92 — Tied to the Relational Idea
Relational Data Model
A B C
SQL-92 — Tied to the Relational Idea
Relational Data Model
processing purposes
A B C
SQL-92 — Tied to the Relational Idea
Relational Data Model
processing purposes
A B C C D B E
SQL-92 — Tied to the Relational Idea
Relational Data Model
processing purposes
Relational Operations
each particular processing purposes
A B C C D B E A B C D E
SQL-92 — Tied to the Relational Idea
Relational Data Model
processing purposes
Relational Operations
each particular processing purposes
A B C C D B E
A B C D E
A B E
SQL-92 — Tied to the Relational Idea
Relational Data Model
processing purposes
Relational Operations
each particular processing purposes
A B C C D B E
A B C D E A B E
C D E
SQL-92 — Tied to the Relational Idea
Relational Data Model
processing purposes
Relational Operations
each particular processing purposes
A B C C D B E
A B C D E A B E C D E
https://www.wiscorp.com/DBMS_-_GreatNews-TheRelationalModelIsDead_-_paper_-_sam.pdf
SQL:1999 — Escaping the Relational Cage
To say that these SQL:1999 extensions are mere “extended interpretations” of the relational data model is like saying that an intercontinental ballistic missile is merely an “extended interpretation” of a spear.
https://www.wiscorp.com/DBMS_-_GreatNews-TheRelationalModelIsDead_-_paper_-_sam.pdf
SQL:1999 — Escaping the Relational Cage
To say that these SQL:1999 extensions are mere “extended interpretations” of the relational data model is like saying that an intercontinental ballistic missile is merely an “extended interpretation” of a spear. With SQL/99 you can get the best of both worlds and
It’s up to the database practitioners to do the right thing.
https://www.wiscorp.com/DBMS_-_GreatNews-TheRelationalModelIsDead_-_paper_-_sam.pdf
SQL:1999 — Escaping the Relational Cage
Relational Model?
SQL:1999 — Escaping the Relational Cage
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
SQL:1999 — Escaping the Relational Cage
I was as confused as anyone else
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
SQL:1999 — Escaping the Relational Cage
I was as confused as anyone else
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
SQL:1999 — Escaping the Relational Cage
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
SQL:1999 — Escaping the Relational Cage
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Domains Can Contain Anything!
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
SQL:1999 — Escaping the Relational Cage
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Domains Can Contain Anything!
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
SQL:1999 — Escaping the Relational Cage
A
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Domains Can Contain Anything!
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
SQL:1999 — Escaping the Relational Cage
A B
[ , ] [ ] []
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Domains Can Contain Anything!
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
SQL:1999 — Escaping the Relational Cage
A B
[ , ] [ ] []
C
C D C D C D
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Domains Can Contain Anything!
Relational Model?
Chris Date
Date on Database: Writings 2000-2006
SQL:1999 — Escaping the Relational Cage
A B C D
[ , ]
{x: , y: }
[ ]
{x: , y: }
[]
{x: , y: }
C D C D C D
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Domains Can Contain Anything!
Relational Model?
Non-Relational Operations
queries that process their own output
Chris Date
Date on Database: Writings 2000-2006
SQL:1999 — Escaping the Relational Cage
? I was as confused as anyone else By the early 1990s, however, I’d seen the light
Domains Can Contain Anything!
SQL:1999 — Recursion
CREATE TABLE t ( id INTEGER, parent INTEGER, )
SQL:1999 — Recursion
CREATE TABLE t ( id INTEGER, parent INTEGER, )
SQL:1999 — Recursion
CREATE TABLE t ( id INTEGER, parent INTEGER, )
SQL:1999 — Recursion
SQL:1999 — Recursion
SQL:1999 — Recursion
SELECT t.id, t.parent FROM t WHERE t.id = ?
SQL:1999 — Recursion
SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?
SQL:1999 — Recursion
SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?
SQL:1999 — Recursion
SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?
SQL:1999 — Recursion
WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev
SQL:1999 — Recursion
WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev
SQL:1999 — Recursion
WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev
SQL:1999 — Recursion
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
5.1 10.2
MariaDB
8.0 MySQL 8.4
PostgreSQL
1.0 3.8.3
SQLite
7.0
DB2 LUW
11gR2
Oracle
2005
SQL Server
SQL:1999 — LATERAL
Select-list sub-queries must be scalar[0]: SELECT … , (SELECT column_1 FROM t1 WHERE t1.x = t2.y ) AS c FROM t2 …
(return no more than one domain value)
SQL:1999 — LATERAL
Select-list sub-queries must be scalar[0]: SELECT … , (SELECT column_1 FROM t1 WHERE t1.x = t2.y ) AS c FROM t2 …
(return no more than one domain value)
, column_2 More than
⇒Syntax error
SQL:1999 — LATERAL
Select-list sub-queries must be scalar[0]: SELECT … , (SELECT column_1 FROM t1 WHERE t1.x = t2.y ) AS c FROM t2 …
(return no more than one domain value)
, column_2 More than
⇒Syntax error
More than
⇒Runtime error!
SQL:1999 — LATERAL
Lateral derived tables lift both limitations and can be correlated: SELECT … , ldt.* FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 WHERE t1.x = t2.y ) AS ldt …
SQL:1999 — LATERAL
Lateral derived tables lift both limitations and can be correlated: SELECT … , ldt.* FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 WHERE t1.x = t2.y ) AS ldt … “Derived table” means it’s in the FROM/JOIN clause
SQL:1999 — LATERAL
Lateral derived tables lift both limitations and can be correlated: SELECT … , ldt.* FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 WHERE t1.x = t2.y ) AS ldt … “Derived table” means it’s in the FROM/JOIN clause Still “correlated”
SQL:1999 — LATERAL
Lateral derived tables lift both limitations and can be correlated: SELECT … , ldt.* FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 WHERE t1.x = t2.y ) AS ldt … “Derived table” means it’s in the FROM/JOIN clause Still “correlated” Regular join semantics
SQL:1999 — LATERAL
SQL:1999 — LATERAL
inside a lateral derived table FETCH FIRST (or LIMIT, TOP) applies per row from left tables.
SQL:1999 — LATERAL
inside a lateral derived table FETCH FIRST (or LIMIT, TOP) applies per row from left tables.
FROM t JOIN LATERAL (SELECT … FROM … WHERE t.c=… ORDER BY … LIMIT 10 ) derived_table
SQL:1999 — LATERAL
inside a lateral derived table FETCH FIRST (or LIMIT, TOP) applies per row from left tables.
Add proper index for Top-N query
https://use-the-index-luke.com/sql/partial-results/top-n-queries
FROM t JOIN LATERAL (SELECT … FROM … WHERE t.c=… ORDER BY … LIMIT 10 ) derived_table
SQL:1999 — LATERAL
inside a lateral derived table FETCH FIRST (or LIMIT, TOP) applies per row from left tables.
news from several subscribed topics (“multi-source top-N”).
Add proper index for Top-N query
https://use-the-index-luke.com/sql/partial-results/top-n-queries
FROM t JOIN LATERAL (SELECT … FROM … WHERE t.c=… ORDER BY … LIMIT 10 ) derived_table
SQL:1999 — LATERAL
inside a lateral derived table FETCH FIRST (or LIMIT, TOP) applies per row from left tables.
news from several subscribed topics (“multi-source top-N”).
(TABLE often implies LATERAL)
Add proper index for Top-N query
https://use-the-index-luke.com/sql/partial-results/top-n-queries
FROM t JOIN TABLE (your_func(t.c)) FROM t JOIN LATERAL (SELECT … FROM … WHERE t.c=… ORDER BY … LIMIT 10 ) derived_table
SQL:1999 — LATERAL
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
5.1
MariaDB
8.0.14
MySQL
9.3
PostgreSQL
1.0
SQLite
9.1
DB2 LUW
11gR1[0] 12cR1
Oracle
2005[1]
SQL Server
[0]Undocumented. Requires setting trace event 22829. [1]LATERAL is not supported as of SQL Server 2016 but [CROSS|OUTER] APPLY can be used for the same effect.
http://www.acm.org:80/sigmod/record/issues/0206/standard.pdf (via Wayback machine)
SQL:2003 — Schemaless & Analytical
Schemaless
documents in a single column
SQL:2003 — Schemaless & Analytical
Schemaless
documents in a single column
Later:
SQL:2003 — Schemaless & Analytical
Analytical
window functions
Schemaless
documents in a single column
Later:
SQL:2003 — Schemaless & Analytical
Analytical
window functions
Schemaless
documents in a single column
Later:
Cockroach DB, VoltDB
Later:
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
SQL:2003 — Analytical
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
+10
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
, SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
ORDER BY id , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
ORDER BY id , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id 1 2 3 4 5 6 value +10 +20
+50
bal
SQL:2003 — Analytical
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id value bal 1 +10 2 +20 3
4 +50 5
6
SQL:2003 — Analytical
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id value bal 1 +10 2 +20 3
4 +50 5
6
SQL:2003 — Analytical
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id value bal 1 +10 2 +20 3
4 +50 5
6
SQL:2003 — Analytical
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id value bal 1 +10 2 +20 3
4 +50 5
6
SQL:2003 — Analytical
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SELECT id, value FROM t
id value bal 1 +10 2 +20 3
4 +50 5
6
SQL:2003 — Analytical
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW , SUM(value) OVER ( ) bal
+10 +30 +20 +70 +40 +20
SQL:2003 — Analytical
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
5.1 10.2
MariaDB
8.0 MySQL 8.4
PostgreSQL
1.0 3.25.0
SQLite
7.0
DB2 LUW
8i
Oracle
2005[0] 2012
SQL Server
[0]Without framing
SQL:2011 groups option
ORDER BY x <frame unit> between 1 preceding and 1 following
SQL:2011 — OVER GROUPS option
ORDER BY x <frame unit> between 1 preceding and 1 following rows, range
SQL:2011 — OVER GROUPS option
ORDER BY x <frame unit> between 1 preceding and 1 following rows, range
x 1 3 3.5 3.5 4
CURRENT ROW
SQL:2011 — OVER GROUPS option
ORDER BY x <frame unit> between 1 preceding and 1 following rows, range
rows
count(*)
x 1 3 3.5 3.5 4
CURRENT ROW
SQL:2011 — OVER GROUPS option
ORDER BY x <frame unit> between 1 preceding and 1 following rows, range
range
x between current_row.x - 1 and current_row.x + 1
rows
count(*)
x 1 3 3.5 3.5 4
CURRENT ROW
SQL:2011 — OVER GROUPS option
ORDER BY x <frame unit> between 1 preceding and 1 following rows, range
New in SQL:2011
groups
range
x between current_row.x - 1 and current_row.x + 1
rows
count(*)
x 1 3 3.5 3.5 4
CURRENT ROW
SQL:2011 — OVER GROUPS option
groups
count(distinct x)
ORDER BY x <frame unit> between 1 preceding and 1 following rows, range
New in SQL:2011
groups
range
x between current_row.x - 1 and current_row.x + 1
rows
count(*)
x 1 3 3.5 3.5 4
CURRENT ROW
SQL:2011 — OVER GROUPS option
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
5.1
MariaDB MySQL
11
PostgreSQL
3.28.0 SQLite
DB2 LUW Oracle SQL Server
SQL:2011 — OVER GROUPS option
SQL:2003 frame exclusion
OVER (ORDER BY … BETWEEN … exclude [ no others | current row | group | ties ] )
SQL:2003 — frame exclusion
OVER (ORDER BY … BETWEEN … exclude [ no others | current row | group | ties ] ) default no others
SQL:2003 — frame exclusion
x 1 2 2 2 3
OVER (ORDER BY … BETWEEN … exclude [ no others | current row | group | ties ] ) default no others
SQL:2003 — frame exclusion
current row
x 1 2 2 2 3
OVER (ORDER BY … BETWEEN … exclude [ no others | current row | group | ties ] ) default current row
SQL:2003 — frame exclusion
group
x = current_row
current row
x 1 2 2 2 3
OVER (ORDER BY … BETWEEN … exclude [ no others | current row | group | ties ] ) default group
SQL:2003 — frame exclusion
ties group
x = current_row
current row
x 1 2 2 2 3
OVER (ORDER BY … BETWEEN … exclude [ no others | current row | group | ties ] ) default ties
SQL:2003 — frame exclusion
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
5.1
MariaDB MySQL
11
PostgreSQL
3.28.0 SQLite
DB2 LUW Oracle SQL Server
SQL:2003 — frame exclusion
SQL:2003 — Analytical (Median)
SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)
SQL:2003 — Analytical (Median)
SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)
Number rows
SQL:2003 — Analytical (Median)
SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)
Number rows Pick middle one
SQL:2003 — Analytical (Median)
SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)
Number rows Pick middle one
SQL:2003 — Analytical (Median)
SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)
Number rows Pick middle one
SQL:2003 — Analytical (Median)
SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)
Number rows Pick middle one
SQL:2003 — Analytical (Median)
All employees must
wash hands
after using
self-joins
SELECT d1.val FROM data d1 JOIN data d2 ON (d1.val < d2.val OR (d1.val=d2.val AND d1.id<d2.id)) GROUP BY d1.val HAVING count(*) = (SELECT FLOOR(COUNT(*)/2) FROM data d3)
Number rows Pick middle one
SQL:2003 — Analytical (Median)
All employees must
wash hands
after using
self-joins
Grab stickers and coasters!
SQL:2003 — Analytical (Median)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
SQL:2003 — Analytical (Median)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
Median
SQL:2003 — Analytical (Median)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
Median Which value?
SQL:2003 — Analytical (Median)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
SQL:2003 — Analytical (Median)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
Two variants:
(categories)
(linear interpolation)
SQL:2003 — Analytical (Median)
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
Two variants:
(categories)
(linear interpolation)
SQL:2003 — Analytical (Median)
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
Two variants:
(categories)
(linear interpolation)
SQL:2003 — Analytical (Median)
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
Two variants:
(categories)
(linear interpolation)
SQL:2003 — Analytical (Median)
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC(0.5)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
Two variants:
(categories)
(linear interpolation)
SQL:2003 — Analytical (Median)
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC(0.5)
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_CONT PERCENTILE_DISC(0.5)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
Two variants:
(categories)
(linear interpolation)
SQL:2003 — Analytical (Median)
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_DISC(0.5)
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_CONT PERCENTILE_DISC(0.5)
0.25 0.5 0.75 1
1 2 3 4 PERCENTILE_CONT(0.5) PERCENTILE_DISC(0.5)
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data
Two variants:
(categories)
(linear interpolation)
SQL:2003 — Analytical (Median)
SQL:2003 — PERCENTILE_DISC
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
5.1 10.3.7
[0]
MariaDB MySQL
9.4
PostgreSQL
1.0
SQLite DB2 LUW
9iR1
Oracle
2012[0]
SQL Server
[0]Only as window function (OVER required).
SQL:2006 — XMLTABLE
<d> <e id="42"> <c1>foo</c1> </e> <e id="43"> <c1>bar</c1> </e> </d>
SQL:2006 — XMLTABLE
id c1 42 foo 43 bar
<d> <e id="42"> <c1>foo</c1> </e> <e id="43"> <c1>bar</c1> </e> </d>
SQL:2006 — XMLTABLE
id c1 42 foo 43 bar
<d> <e id="42"> <c1>foo</c1> </e> <e id="43"> <c1>bar</c1> </e> </d>
SQL:2006 — XMLTABLE
id c1 42 foo 43 bar
<d> <e id="42"> <c1>foo</c1> </e> <e id="43"> <c1>bar</c1> </e> </d>
SQL:2006 — XMLTABLE
SELECT * FROM XMLTABLE ( '/d/e' PASSING ? COLUMNS id INT PATH '@id' , c1 VARCHAR(…) PATH 'c1' ) r
id c1 42 foo 43 bar
<d> <e id="42"> <c1>foo</c1> </e> <e id="43"> <c1>bar</c1> </e> </d>
SQL:2006 — XMLTABLE
SELECT * FROM XMLTABLE ( '/d/e' PASSING ? COLUMNS id INT PATH '@id' , c1 VARCHAR(…) PATH 'c1' ) r
XQuery (XPath)
turned into a row
id c1 42 foo 43 bar
<d> <e id="42"> <c1>foo</c1> </e> <e id="43"> <c1>bar</c1> </e> </d>
SQL:2006 — XMLTABLE
SELECT * FROM XMLTABLE ( '/d/e' PASSING ? COLUMNS id INT PATH '@id' , c1 VARCHAR(…) PATH 'c1' ) r
XQuery (XPath)
turned into a row
In preparation for JSON_TABLE & co, PG 12 introduced SQL/JSON Path
id c1 42 foo 43 bar
<d> <e id="42"> <c1>foo</c1> </e> <e id="43"> <c1>bar</c1> </e> </d>
SQL:2006 — XMLTABLE
SELECT * FROM XMLTABLE ( '/d/e' PASSING ? COLUMNS id INT PATH '@id' , c1 VARCHAR(…) PATH 'c1' ) r
XQuery (XPath)
turned into a row
In preparation for JSON_TABLE & co, PG 12 introduced SQL/JSON Path
id c1 42 foo 43 bar
<d> <e id="42"> <c1>foo</c1> </e> <e id="43"> <c1>bar</c1> </e> </d>
SQL:2006 — XMLTABLE
SELECT * FROM XMLTABLE ( '/d/e' PASSING ? COLUMNS id INT PATH '@id' , c1 VARCHAR(…) PATH 'c1' ) r
Bind Parameter
XQuery (XPath)
turned into a row
In preparation for JSON_TABLE & co, PG 12 introduced SQL/JSON Path
id c1 42 foo 43 bar
<d> <e id="42"> <c1>foo</c1> </e> <e id="43"> <c1>bar</c1> </e> </d>
SQL:2006 — XMLTABLE
SELECT * FROM XMLTABLE ( '/d/e' PASSING ? COLUMNS id INT PATH '@id' , c1 VARCHAR(…) PATH 'c1' ) r
id c1 42 foo 43 bar
<d> <e id="42"> <c1>foo</c1> </e> <e id="43"> <c1>bar</c1> </e> </d>
SQL:2006 — XMLTABLE
SELECT * FROM XMLTABLE ( '/d/e' PASSING ? COLUMNS id INT PATH '@id' , c1 VARCHAR(…) PATH 'c1' ) r INSERT INTO target_table
SQL:2006 — XMLTABLE
1999 2001 2003 2005 2007 2009 2011 2013 2015 2017
5.1
MariaDB MySQL
10
[0] PostgreSQL
1.0
SQLite
9.7
DB2 LUW
11gR1
Oracle SQL Server
[0]No XQuery (only XPath). No default namespace declaration.
https://webstore.iec.ch/publication/59685
A lot has happened since SQL-92
https://webstore.iec.ch/publication/59685
SQL has evolved beyond the relational idea A lot has happened since SQL-92
https://webstore.iec.ch/publication/59685
SQL has evolved beyond the relational idea If you use SQL for CRUD operations only, you are doing it wrong A lot has happened since SQL-92
SQL has evolved beyond the relational idea If you use SQL for CRUD operations only, you are doing it wrong A lot has happened since SQL-92
https://modern-sql.com
@ModernSQL by @MarkusWinand
Training: https://winand.at/
SQL has evolved beyond the relational idea If you use SQL for CRUD operations only, you are doing it wrong A lot has happened since SQL-92
https://modern-sql.com
@ModernSQL by @MarkusWinand
Training: https://winand.at/ Feedback: https://2019.pgconf.eu/f