Neues in Open-Source-SQL-Datenbanken @MarkusWinand @ModernSQL - PowerPoint PPT Presentation

MySQL BOOLEAN Type Since SQL:2003 MariaDB 
 SQLite CREATE TABLE … ( … deleted BOOLEAN NOT NULL, … Alias for int ) INSERT … (…, deleted, …) VALUES (…, true, …) INSERT … (…, deleted, …) VALUES (…, false, …)

MySQL BOOLEAN Type Since SQL:2003 MariaDB 
 SQLite CREATE TABLE … ( … deleted BOOLEAN NOT NULL, … Alias for int ) INSERT … (…, deleted, …) VALUES (…, true, …) INSERT … (…, deleted, …) VALUES (…, false, …) INSERT … (…, deleted, …) VALUES (…, 42, …)

MySQL BOOLEAN Type Since SQL:2003 MariaDB 
 SQLite CREATE TABLE … ( … deleted BOOLEAN NOT NULL, UNIQUE , … Alias for int ) INSERT … (…, deleted, …) VALUES (…, true, …) INSERT … (…, deleted, …) VALUES (…, false, …) INSERT … (…, deleted, …) VALUES (…, 42, …)

MySQL BOOLEAN Type Since SQL:2003 MariaDB 
 SQLite CREATE TABLE … ( … deleted BOOLEAN NOT NULL, UNIQUE , … +----+ Alias for int ) | de | +----+ INSERT … (…, deleted, …) VALUES (…, true, …) | 1 | INSERT … (…, deleted, …) VALUES (…, false, …) | 0 | INSERT … (…, deleted, …) VALUES (…, 42, …) | 42 | +----+

BOOLEAN Type Since SQL:2003 Note that boolean in base tables is often questionable: 


BOOLEAN Type Since SQL:2003 Note that boolean in base tables is often questionable: 
 ‣ deleted flags are a poor mans temporal database model 


BOOLEAN Type Since SQL:2003 Note that boolean in base tables is often questionable: 
 ‣ deleted flags are a poor mans temporal database model 
 ‣ States often need more than two (or three) values 
 consider using an enum instead (can be evolved)


 BOOLEAN Type Since SQL:2003 Note that boolean in base tables is often questionable: 
 ‣ deleted flags are a poor mans temporal database model 
 ‣ States often need more than two (or three) values 
 consider using an enum instead (can be evolved) See: 3 Reasons I Hate Booleans In Databases by Je ff Potter 
 https://medium.com/@jpotts18/646d99696580

BOOLEAN Type Since SQL:2003 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 MariaDB 5.1 [0] MySQL 5.0.51a [0] PostgreSQL 8.4 SQLite 3.23.0 [0] DB2 LUW Oracle SQL Server [0] BOOLEAN, TRUE, FALSE are aliases for TINYINT(1), 1, 0 respectivley.

OVER and PARTITION BY

OVER (PARTITION BY) The Problem Two distinct concepts could not be used independently:

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

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

OVER (PARTITION BY) The Problem SELECT c1 SELECT c1 , c2 , c2 FROM t FROM t

OVER (PARTITION BY) The Problem SELECT c1 SELECT c1 Yes ⇠ Merge rows ⇢ No , c2 , c2 FROM t FROM t

OVER (PARTITION BY) The Problem SELECT c1 SELECT c1 Yes ⇠ Merge rows ⇢ No , c2 , c2 FROM t FROM t SELECT DISTINCT c1 , c2 FROM t

OVER (PARTITION BY) The Problem No ⇠ Aggregate ⇢ Yes SELECT c1 SELECT c1 Yes ⇠ Merge rows ⇢ No , c2 , c2 FROM t FROM t SELECT DISTINCT c1 , c2 FROM t

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 , 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 , c2 , c2 FROM t FROM t JOIN ( ) ta ON (t.c1=ta.c1) 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 , 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) 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 FROM t 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 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 Look here dep salary 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 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 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 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 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 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 SELECT dep, 1 1000 1000 salary, 22 1000 2000 SUM(salary) 22 1000 2000 OVER() ) 333 1000 3000 FROM emp 333 1000 3000 333 1000 3000

OVER (PARTITION BY) How it works dep salary 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

OVER (PARTITION BY) How it works dep salary 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

OVER (PARTITION BY) How it works dep salary 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

OVER and ORDER BY (Framing & Ranking)

OVER (ORDER BY) The Problem acnt id value balance SELECT id, 1 1 +10 +10 value, FROM transactions t 22 2 +20 +30 22 3 -10 +20 333 4 +50 +70 333 5 -30 +40 333 6 -20 +20

OVER (ORDER BY) The Problem acnt id value balance SELECT id, 1 1 +10 +10 value, FROM transactions t 22 2 +20 +30 22 3 -10 +20 333 4 +50 +70 333 5 -30 +40 333 6 -20 +20

OVER (ORDER BY) The Problem acnt id value balance SELECT id, 1 1 +10 +10 value, FROM transactions t 22 2 +20 +30 22 3 -10 +20 333 4 +50 +70 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 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, (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) Since SQL:2003 acnt id value balance SELECT id, 1 1 +10 +10 value, 22 2 +20 +30 22 3 -10 +20 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 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 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 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