more than a query language sql in the 21 st century
play

More Than a Query Language: SQL in the 21 st Century @MarkusWinand - PowerPoint PPT Presentation

Feb 23, 2024 •8 likes •1.46k views

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

  1. SQL:1999 — LATERAL Lateral derived tables lift both limitations and can be correlated: “Derived table” means 
 SELECT … it’s in the 
 , ldt.* FROM/JOIN clause FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 WHERE t1.x = t2.y ) AS ldt …

  2. SQL:1999 — LATERAL Lateral derived tables lift both limitations and can be correlated: “Derived table” means 
 SELECT … it’s in the 
 , ldt.* FROM/JOIN clause FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 WHERE t1.x = t2.y ) AS ldt Still … “correlated”

  3. SQL:1999 — LATERAL Lateral derived tables lift both limitations and can be correlated: “Derived table” means 
 SELECT … it’s in the 
 , ldt.* FROM/JOIN clause FROM t2 CROSS JOIN LATERAL (SELECT column_1, column_2 FROM t1 Regular join WHERE t1.x = t2.y semantics ) AS ldt Still … “correlated”

  4. SQL:1999 — LATERAL

  5. 
 SQL:1999 — LATERAL ‣ Top-N per group 
 inside a lateral derived table 
 FETCH FIRST (or LIMIT , TOP ) 
 applies per row from left tables.

  6. 
 SQL:1999 — LATERAL ‣ Top-N per group 
 FROM t JOIN LATERAL (SELECT … inside a lateral derived table 
 FROM … FETCH FIRST (or LIMIT , TOP ) 
 WHERE t.c=… applies per row from left tables. ORDER BY … LIMIT 10 ) derived_table

  7. 
 SQL:1999 — LATERAL Add proper index 
 for Top-N query ‣ Top-N per group 
 FROM t JOIN LATERAL (SELECT … inside a lateral derived table 
 FROM … FETCH FIRST (or LIMIT , TOP ) 
 WHERE t.c=… applies per row from left tables. ORDER BY … LIMIT 10 ) derived_table https://use-the-index-luke.com/sql/partial-results/top-n-queries

  8. 
 SQL:1999 — LATERAL Add proper index 
 for Top-N query ‣ Top-N per group 
 FROM t JOIN LATERAL (SELECT … inside a lateral derived table 
 FROM … FETCH FIRST (or LIMIT , TOP ) 
 WHERE t.c=… applies per row from left tables. ORDER BY … LIMIT 10 ‣ Also useful to find most recent ) derived_table news from several subscribed topics (“multi-source top-N”). https://use-the-index-luke.com/sql/partial-results/top-n-queries

  9. 
 
 SQL:1999 — LATERAL Add proper index 
 for Top-N query ‣ Top-N per group 
 FROM t JOIN LATERAL (SELECT … inside a lateral derived table 
 FROM … FETCH FIRST (or LIMIT , TOP ) 
 WHERE t.c=… applies per row from left tables. ORDER BY … LIMIT 10 ‣ Also useful to find most recent ) derived_table news from several subscribed topics (“multi-source top-N”). https://use-the-index-luke.com/sql/partial-results/top-n-queries ‣ Table function arguments 
 FROM t ( TABLE often implies LATERAL ) 
 JOIN TABLE (your_func(t.c))

  10. SQL:1999 — LATERAL 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 MariaDB 5.1 MySQL 8.0.14 PostgreSQL 9.3 SQLite 1.0 DB2 LUW 9.1 Oracle 11gR1 12cR1 [0] SQL Server 2005 [1] [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.

  11. SQL:1999 — LATERAL MDEV-6373: 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 Fix Version: none MariaDB 5.1 MySQL 8.0.14 PostgreSQL 9.3 SQLite 1.0 DB2 LUW 9.1 Oracle 11gR1 12cR1 [0] SQL Server 2005 [1] [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.

  12. 1999 2003

  13. http://www.acm.org:80/sigmod/record/issues/0206/standard.pdf (via Wayback machine)

  14. SQL:2003 — Schemaless & Analytical Schemaless ‣ Introduced XML ‣ Non-uniform 
 documents in 
 a single column

  15. SQL:2003 — Schemaless & Analytical Schemaless ‣ Introduced XML ‣ Non-uniform 
 documents in 
 a single column Later: ‣ JSON added with SQL:2016 ‣ Proprietary JSON support: ‣ 2012: PostgreSQL ‣ 2014: Oracle ‣ 2015: MySQL ‣ 2016: SQL Server

  16. SQL:2003 — Schemaless & Analytical Schemaless Analytical ‣ Introduced XML ‣ Introduced 
 ‣ Non-uniform 
 window functions ‣ Accessing other rows 
 documents in 
 a single column of the current result Later: ‣ JSON added with SQL:2016 ‣ Proprietary JSON support: ‣ 2012: PostgreSQL ‣ 2014: Oracle ‣ 2015: MySQL ‣ 2016: SQL Server

  17. SQL:2003 — Schemaless & Analytical Schemaless Analytical ‣ Introduced XML ‣ Introduced 
 ‣ Non-uniform 
 window functions ‣ Accessing other rows 
 documents in 
 a single column of the current result Later: Later: ‣ JSON added with SQL:2016 ‣ Extended in SQL:2011 ‣ Proprietary JSON support: ‣ Popular among “New SQLs” ‣ 2012: PostgreSQL ‣ 2013: BigQuery, Hive ‣ 2014: Oracle ‣ 2014: Impala ‣ 2015: MySQL ‣ 2015: Spark SQL ‣ 2016: SQL Server ‣ 2016: NuoDB, MemSQL, Cockroach DB, VoltDB

  18. SQL:2003 — Analytical id value SELECT id, value 1 +10 2 +20 3 -10 4 +50 5 -30 6 -20 FROM t

  19. SQL:2003 — Analytical id value bal SELECT id, value 1 +10 2 +20 3 -10 4 +50 5 -30 6 -20 FROM t

  20. SQL:2003 — Analytical id value bal SELECT id, value 1 +10 +10 2 +20 3 -10 4 +50 5 -30 6 -20 FROM t

  21. SQL:2003 — Analytical id value bal SELECT id, value 1 +10 +10 2 +20 +30 3 -10 +20 4 +50 +70 5 -30 +40 6 -20 +20 FROM t

  22. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 3 -10 +20 4 +50 +70 5 -30 +40 6 -20 +20 ) bal FROM t

  23. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 4 +50 +70 5 -30 +40 6 -20 +20 ) bal FROM t

  24. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 4 +50 +70 5 -30 +40 6 -20 +20 ) bal FROM t

  25. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 6 -20 +20 ) bal FROM t

  26. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 6 -20 +20 ) bal FROM t

  27. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

  28. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

  29. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

  30. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

  31. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

  32. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

  33. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

  34. SQL:2003 — Analytical id value bal SELECT id, value , SUM(value) 1 +10 +10 OVER ( 2 +20 +30 ORDER BY id 3 -10 +20 ROWS BETWEEN 4 +50 +70 UNBOUNDED PRECEDING 5 -30 +40 AND CURRENT ROW 6 -20 +20 ) bal FROM t

  35. SQL:2003 — Analytical 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 MariaDB 5.1 10.2 8.0 MySQL PostgreSQL 8.4 SQLite 1.0 3.25.0 DB2 LUW 7.0 Oracle 8i SQL Server 2005 2012 [0] [0] Without framing

  36. Inverse Distribution Functions (percentiles)

  37. SQL:2003 — Analytical (Median)

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

  39. SQL:2003 — Analytical (Median) Number rows 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)

  40. SQL:2003 — Analytical (Median) Number rows 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(*) = Pick middle one (SELECT FLOOR(COUNT(*)/2) FROM data d3)

  41. SQL:2003 — Analytical (Median) Number rows 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(*) = Pick middle one (SELECT FLOOR(COUNT(*)/2) FROM data d3)

  42. SQL:2003 — Analytical (Median) Number rows 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(*) = Pick middle one (SELECT FLOOR(COUNT(*)/2) FROM data d3)

  43. SQL:2003 — Analytical (Median) Notice Number rows SELECT d1.val FROM data d1 All employees must JOIN data d2 wash hands ON (d1.val < d2.val after using OR (d1.val=d2.val AND d1.id<d2.id)) self-joins GROUP BY d1.val HAVING count(*) = Pick middle one (SELECT FLOOR(COUNT(*)/2) FROM data d3)

  44. SQL:2003 — Analytical (Median) Grab stickers 
 Notice and coasters! Number rows SELECT d1.val FROM data d1 All employees must JOIN data d2 wash hands ON (d1.val < d2.val after using OR (d1.val=d2.val AND d1.id<d2.id)) self-joins GROUP BY d1.val HAVING count(*) = Pick middle one (SELECT FLOOR(COUNT(*)/2) FROM data d3)

  45. SQL:2003 — Analytical (Median)

  46. SQL:2003 — Analytical (Median) SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

  47. SQL:2003 — Analytical (Median) Median SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

  48. SQL:2003 — Analytical (Median) Median SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) Which value? FROM data

  49. SQL:2003 — Analytical (Median) SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data

  50. SQL:2003 — Analytical (Median) SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY val) FROM data Two variants: ‣ for discrete values 
 (categories) ‣ for continuous values 
 (linear interpolation)

Recommend

More Than a Query Language: SQL in the 21 st Century @MarkusWinand @ModernSQL

More Than a Query Language: SQL in the 21 st Century @MarkusWinand @ModernSQL

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

1.74k views • 146 slides
More Than a Query Language: SQL in the 21 st Century @MarkusWinand @ModernSQL

More Than a Query Language: SQL in the 21 st Century @MarkusWinand @ModernSQL

More Than a Query Language: SQL in the 21 st Century @MarkusWinand @ModernSQL http://www.almaden.ibm.com/cs/people/chamberlin/sequel-1974.pdf 1974 1992 SQL-92 Tied to the Relational Idea SQL-92 Tied to the Relational Idea

1.37k views • 124 slides
More Than a Query Language: SQL in the 21st Century Markus Winand, winand.at SQL Renaissance

More Than a Query Language: SQL in the 21st Century Markus Winand, winand.at SQL Renaissance

More Than a Query Language: SQL in the 21st Century Markus Winand, winand.at SQL Renaissance Ambassador 1974 1992 SQL-92 Tied to the Relational Idea SQL-92 Tied to the Relational Idea Relational Data Model Atomic types

1.55k views • 117 slides
SQL SQL SQL = Structured Query Language Standard query language for relational

SQL SQL SQL = Structured Query Language Standard query language for relational

SQL SQL SQL = Structured Query Language Standard query language for relational DBMSs History: Developed at IBM in late 70s 1 st standard: SQL-86 2 nd standard: SQL-92 3 rd standard: SQL-99 or SQL3, well over 1000 pages

643 views • 22 slides
The CQL Con*nuous Query Language: Seman*c Founda*ons and Query

The CQL Con*nuous Query Language: Seman*c Founda*ons and Query

The CQL Con*nuous Query Language: Seman*c Founda*ons and Query Execu*on Arvind Arasu, Shivnath Babu, Jennifer Widom Presented by: Young-Rae Kim

580 views • 44 slides
SQL Structured Query Language 1 Best known and most commonly used relational database query

SQL Structured Query Language 1 Best known and most commonly used relational database query

SQL Structured Query Language 1 Best known and most commonly used relational database query and manipulation language. Used in many commercial database systems. 2 SQL includes several components including: Data definition

723 views • 53 slides
Database Systems SQL Based on slides by Feifei Li, University of Utah The SQL Query Language n

Database Systems SQL Based on slides by Feifei Li, University of Utah The SQL Query Language n

Database Systems SQL Based on slides by Feifei Li, University of Utah The SQL Query Language n SQL stands for Structured Query Language n The most widely used relational query language. Current standard is SQL:2016 (actually there is a new

1.22k views • 97 slides
1 Logic-based Markup and Logic-based Markup and Query Language Query Language Eight

1 Logic-based Markup and Logic-based Markup and Query Language Query Language Eight

Overview Semantic Streams: a Framework for Composable Motivation Semantic Interpretation of Sensor What are Semantic Streams? Data Logic-based Markup and Query Language Kamin Whitehouse, Feng Zhao, and Jie Liu Query Processing

444 views • 5 slides
SQL , the Structured Query Language Overview Introduction DDL Commands DML Commands SQL

SQL , the Structured Query Language Overview Introduction DDL Commands DML Commands SQL

SQL , the Structured Query Language Overview Introduction DDL Commands DML Commands SQL Statements, Operators, Clauses Aggregate Functions Structured Query Language ( SQL SQL ) The ANSI standard language for the definition and manipulation

854 views • 30 slides
1 Language processing Query Graph Model Two stages: compilation and Vertices execution

1 Language processing Query Graph Model Two stages: compilation and Vertices execution

Outline Extensible Query Processing in Motivation Starburst Solution: Extensible DBMS Language Processing Query Graph Model Presentation: Kati Query rewrite Discussion: Andrew Summary Motivation Starburst Project

281 views • 3 slides
Query Execu:on Declara:ve Query (SQL) We start from

Query Execu:on Declara:ve Query (SQL) We start from

SQL III The Query Language R &amp; G - Chapter 5 Based on Slides from UC Berkeley and book. Query Execu:on Declara:ve Query (SQL) We start from here

1.02k views • 64 slides
Query Execu:on Declara:ve Query (SQL) We start from

Query Execu:on Declara:ve Query (SQL) We start from

SQL The Query Language R &amp; G - Chapter 5 Based on Slides from UC Berkeley and book. 2 Query Execu:on Declara:ve Query (SQL) We start from

689 views • 34 slides
Query Execu*on Declara*ve Query (SQL) We start from

Query Execu*on Declara*ve Query (SQL) We start from

SQL The Query Language R &amp; G - Chapter 5 2 Query Execu*on Declara*ve Query (SQL) We start from here Query Op*miza*on and Execu*on (Rela*onal)

808 views • 44 slides
Relational Algebra and SQL Chapter 5 1 Relational Query Languages Languages for describing

Relational Algebra and SQL Chapter 5 1 Relational Query Languages Languages for describing

Relational Algebra and SQL Chapter 5 1 Relational Query Languages Languages for describing queries on a relational database Structured Query Language Structured Query Language (SQL) Predominant application-level query language

572 views • 35 slides
SQL Structured Query Language (SQL) CS 235: The language of databases Based on

SQL Structured Query Language (SQL) CS 235: The language of databases Based on

SQL Structured Query Language (SQL) CS 235: The language of databases Based on relational algebra Introduction to Databases extended algebra operations other extensions. Svetlozar Nestorov Lecture Notes #9 SQL Queries

316 views • 4 slides
Pe Personalized Language Model fo for Query Auto-

Pe Personalized Language Model fo for Query Auto-

Pe Personalized Language Model fo for Query Auto- -Co Completion Aaron Jaech and Mari Ostendorf University of Washington Qu Query Auto- -Co

192 views • 15 slides
XPATH and XQUERY Two query language to search for features in XML documents XML Query

XPATH and XQUERY Two query language to search for features in XML documents XML Query

XPATH and XQUERY Two query language to search for features in XML documents XML Query Languages XPATH XQUERY XPATH XQUERY 1 2 XPATH XQUERY XPATH is a language for describing XQUERY is a full query language for paths in

418 views • 5 slides
LANGUAGE &amp; TOOLS WHY GOVERNMENT CANNOT MANAGE THE 21ST CENTURY 21st Century Problems

LANGUAGE &amp; TOOLS WHY GOVERNMENT CANNOT MANAGE THE 21ST CENTURY 21st Century Problems

LANGUAGE &amp; TOOLS WHY GOVERNMENT CANNOT MANAGE THE 21ST CENTURY 21st Century Problems Climate, for example, is hard The Big Threats Nukes, Biotech, Nanotech, Asteroids, Weird Physics, Environment Mostly government funded

300 views • 28 slides
Structured Query Language CS430/630 Lecture 4 Slides based on Database Management Systems 3

Structured Query Language CS430/630 Lecture 4 Slides based on Database Management Systems 3

Structured Query Language CS430/630 Lecture 4 Slides based on Database Management Systems 3 rd ed, Ramakrishnan and Gehrke Relational Query Language: SQL Supports simple, yet powerful querying of data. Precise semantics for

461 views • 23 slides
Universal Language in the 17th Century Our examination of the history of machine translation

Universal Language in the 17th Century Our examination of the history of machine translation

Universal Language in the 17th Century Our examination of the history of machine translation begins in 17th century Europe. For the scholars of that era, the search for meaning in language was intimately tied with two accounts found early in the

642 views • 13 slides
SSE 3200 Mysql lab Introduction SQL (Structured Query Language) is a standard language for

SSE 3200 Mysql lab Introduction SQL (Structured Query Language) is a standard language for

SSE 3200 Mysql lab Introduction SQL (Structured Query Language) is a standard language for creating, accessing, and manipulating databases. A database is a collection of tables . Each table consists of several rows of data called records . Records

456 views • 9 slides
Datomic A modern database Databases Perks Declarative Query Language (e.g. SQL) Indexes

Datomic A modern database Databases Perks Declarative Query Language (e.g. SQL) Indexes

Datomic A modern database Databases Perks Declarative Query Language (e.g. SQL) Indexes Transactions (ACI) Durability (D) What are the Problems? Query as Strings Query Power &quot;over there&quot; Update in Place

757 views • 21 slides
The Cypher Language 2017 Presentation to the LDBC Query Language Task Force Neo Technology Cypher

The Cypher Language 2017 Presentation to the LDBC Query Language Task Force Neo Technology Cypher

DM32.2 2018-00145 Informational Paper The Cypher Language 2017 Presentation to the LDBC Query Language Task Force Neo Technology Cypher Language Group Date of original presentation 3 July 2017 Submitted to DM32.2 13 July 2018 Neo4j Query

430 views • 39 slides
Twentieth artistic language (techniques of Century representation) versus the image and its

Twentieth artistic language (techniques of Century representation) versus the image and its

20th century part 1 F11.shw the 20th century dialectic: Twentieth artistic language (techniques of Century representation) versus the image and its source Ruptures and [or] Continuities disintegration of the real versus the hallucination

461 views • 7 slides

More recommend