More SQL in MySQL 8.0

下载 1

潇潇

发布于

1565

人观看

#信息技术

MySQL8.0使编写更多查询成为可能。MySQL现在可以遍历层次结构，以新的方式分析数据，并将JSON和空间数据与传统类型结合起来——所有这些都在同一个查询中。
在本文中，我们将介绍公共表表达式（CTE）、窗口函数、地理支持和JSON功能，以及如何使用这些功能来完成MySQL查询以前从未做过的事情。
如果你对这些特性是什么或者你能用它们做些什么感到好奇，那么这篇演讲就是为你准备的。

展开查看详情

1 .

5 .Common Table Expressions (CTEs) ● Alternatve to derived tables or views SELECT … FROM (subquery) AS derived, t1 … CREATE VIEW derived AS (subquery); SELECT … FROM derived, t1 … WITH derived AS (subquery) SELECT … FROM derived, t1 … ● Can refer to other CTEs WITH cte1 AS (subquery), cte2 AS (SELECT … FROM cte1 …) SELECT … FROM cte2 … ● Can refer to itself, creatng a recursive CTE Copyright © 2018 Oracle and/or its afliates. All rights reserved. 5

6 .Recursive CTEs WITH RECURSIVE cte AS (SELECT … FROM table_name /* "seed" SELECT */ UNION [DISTINCT|ALL] SELECT … FROM cte, table_name) /* "recursive" SELECT */ SELECT … FROM cte; ● A recursive CTE refers to itself in a subquery ● The “seed” SELECT is executed once to create the inital data subset ● The “recursive” SELECT is executed repeatedly – Stops when iteraton doesn’t generate any new rows – Set cte_max_recursion_depth to limit recursion (default 1000) ● Useful to traverse hierarchies (parent/child, part/subpart) Copyright © 2018 Oracle and/or its afliates. All rights reserved. 6

7 .Recursive CTE Example CREATE TABLE employee (number INTEGER PRIMARY KEY, name VARCHAR(100), manager INTEGER REFERENCES employee (number)); INSERT INTO employee VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Bety', 1), (4, 'Charlie', 2), (5, 'Cherise', 2), (6, 'Chandler', 2), (7, 'Chris', 3), (8, 'Camilla', 3), (9, 'Dave', 8), (10, 'Denise', 8); number name manager reportng_chain WITH RECURSIVE emps AS ( 1 Alice NULL Alice SELECT *, name AS reportng_chain 2 Bob 1 Bob, Alice FROM employee WHERE manager IS NULL 3 Bety 1 Bety, Alice UNION ALL SELECT e.number, e.name, e.manager, 4 Charlie 2 Charlie, Bob, Alice CONCAT(e.name, ', ', m.reportng_chain) 5 Cherise 2 Cherise, Bob, Alice FROM employee AS e JOIN emps AS m 6 Chandler 2 Chandler, Bob, Alice ON e.manager = m.number 7 Chris 3 Chris, Bety, Alice ) 8 Camilla 3 Camilla, Bety, Alice SELECT * FROM emps; 9 Dave 8 Dave, Camilla, Bety, Alice 10 Denise 8 Denise, Camilla, Bety, Alice Copyright © 2018 Oracle and/or its afliates. All rights reserved. 7

8 .Recursive CTE Example CREATE TABLE employee (number INTEGER PRIMARY KEY, name VARCHAR(100), manager INTEGER REFERENCES employee (number)); INSERT INTO employee VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Bety', 1), (4, 'Charlie', 2), (5, 'Cherise', 2), (6, 'Chandler', 2), (7, 'Chris', 3), (8, 'Camilla', 3), (9, 'Dave', 8), (10, 'Denise', 8); number name manager reportng_chain WITH RECURSIVE emps AS ( 1 Alice NULL Alice SELECT *, name AS reportng_chain 2 Bob 1 Bob, Alice FROM employee WHERE manager IS NULL 3 Bety 1 Bety, Alice UNION ALL SELECT e.number, e.name, e.manager, 4 Charlie 2 Charlie, Bob, Alice CONCAT(e.name, ', ', m.reportng_chain) 5 Cherise 2 Cherise, Bob, Alice FROM employee AS e JOIN emps AS m 6 Chandler 2 Chandler, Bob, Alice ON e.manager = m.number 7 Chris 3 Chris, Bety, Alice ) 8 Camilla 3 Camilla, Bety, Alice SELECT * FROM emps; 9 Dave 8 Dave, Camilla, Bety, Alice 10 Denise 8 Denise, Camilla, Bety, Alice Copyright © 2018 Oracle and/or its afliates. All rights reserved. 8

9 .Window Functons ● Similar to aggregaton functons – Computes one value based on multple rows – But does not group Aggregaton functon Window functon Copyright © 2018 Oracle and/or its afliates. All rights reserved. 9

10 .Window Functon Example Sum up total salary per department SELECT name, dept_id, salary, SUM(salary) OVER (PARTITION BY name dept_id salary dept_total dept_id) AS dept_total Newt NULL 75000 75000 FROM employee Dag 10 NULL 370000 ORDER BY dept_id, name; Ed 10 100000 370000 Fred 10 60000 370000 The OVER Jon 10 60000 370000 keyword signals a Michael 10 70000 370000 window functon Newt 10 80000 370000 PARTITION ⇒ Lebedev 20 65000 130000 disjoint set of Pete 20 65000 130000 Jef 30 300000 370000 rows in result set Will 30 70000 370000 Copyright © 2018 Oracle and/or its afliates. All rights reserved. 10

11 .Window Functon Example Sum up total salary per department SELECT name, dept_id, salary, SUM(salary) OVER (PARTITION BY name dept_id salary dept_total dept_id) AS dept_total Newt NULL 75000 75000 FROM employee Dag 10 NULL 370000 ORDER BY dept_id, name; Ed 10 100000 370000 Fred 10 60000 370000 The OVER Jon 10 60000 370000 keyword signals a Michael 10 70000 370000 window functon Newt 10 80000 370000 PARTITION ⇒ Lebedev 20 65000 130000 disjoint set of Pete 20 65000 130000 Jef 30 300000 370000 rows in result set Will 30 70000 370000 Copyright © 2018 Oracle and/or its afliates. All rights reserved. 11

12 .JSON_TABLE INSERT INTO t1 (json_col) VALUES ( '{ "people": [ { "name":"John Smith", "address":"780 Mission St, San Francisco, CA 94103"}, { "name":"Sally Brown", "address":"75 37th Ave S, St Cloud, MN 94103"}, { "name":"John Johnson", "address":"1262 Roosevelt Trail, Raymond, ME 04071"} ] }' ); SELECT people.* FROM t1, JSON_TABLE(json_col, '$.people[*]' COLUMNS ( name VARCHAR(40) PATH '$.name', address VARCHAR(100) PATH '$.address')) AS people; name address John Smith 780 Mission St, San Francisco, CA 94103 Sally Brown 75 37th Ave S, St Cloud, MN 9410 John Johnson 1262 Roosevelt Trail, Raymond, ME 04071 Copyright © 2018 Oracle and/or its afliates. All rights reserved. 12

13 .JSON_TABLE Nested Arrays [ { "father":"John", "mother":"Mary", id father married child_id child age "marriage_date":"2003-12-05", "children": [ { "name":"Eric", "age":10 }, 1 John 1 1 Eric 10 { "name":"Beth", "age":12 } ] 1 John 1 2 Beth 12 }, { "father":"Paul", "mother":"Laura", 2 Paul 0 1 Sarah 9 "children": [ { "name":"Sarah", "age":9}, 2 Paul 0 2 Noah 3 { "name":"Noah", "age":3} , 2 Paul 0 3 Peter 10 { "name":"Peter", "age":10} ] } ] Copyright © 2018 Oracle and/or its afliates. All rights reserved. 13

14 .JSON_TABLE Nested Arrays JSON_TABLE (families, '$[*]' COLUMNS ( id father married child_id child age id FOR ORDINALITY, father VARCHAR(30) PATH '$.father', 1 John 1 1 Eric 10 married INTEGER EXISTS PATH 1 John 1 2 Beth 12 '$.marriage_date', NESTED PATH '$.children[*]' COLUMNS ( 2 Paul 0 1 Sarah 9 child_id FOR ORDINALITY, child VARCHAR(30) PATH '$.name', 2 Paul 0 2 Noah 3 age INTEGER PATH '$.age' ) 2 Paul 0 3 Peter 10 )) Copyright © 2018 Oracle and/or its afliates. All rights reserved. 14

16 .Demo Warning! ● The following queries are intended to demonstrate the power of the features ● This is not necessarily the best way of doing things ● This is not necessarily the easiest way of doing things ● This is not necessarily smart … … but it's fun! :-) Copyright © 2018 Oracle and/or its afliates. All rights reserved. 16

18 .Birth Order # First child in a family = 1, second child = 2, etc. SELECT fam.*, RANK() OVER w AS nth_child FROM families, JSON_TABLE (families, '$[*]' COLUMNS ( id FOR ORDINALITY, father VARCHAR(30) PATH '$.father', mother VARCHAR(30) PATH '$.mother', married INTEGER EXISTS PATH '$.marriage_date', marriage_date DATE PATH '$.marriage_date', NESTED PATH '$.children[*]' COLUMNS ( child_id FOR ORDINALITY, child VARCHAR(30) PATH '$.name', age INTEGER PATH '$.age' ) ) ) AS fam WINDOW w AS (PARTITION BY id ORDER BY age DESC) ORDER BY id, child_id; Copyright © 2018 Oracle and/or its afliates. All rights reserved. 18

19 .Families as Table # Use JSON_TABLE to convert JSON document to a table representng the same info CREATE TABLE families_tab SELECT fam.* FROM families, JSON_TABLE (families, '$[*]' COLUMNS ( id FOR ORDINALITY, father VARCHAR(30) PATH '$.father', mother VARCHAR(30) PATH '$.mother', marriage_date DATE PATH '$.marriage_date', NESTED PATH '$.children[*]' COLUMNS ( child_id FOR ORDINALITY, child VARCHAR(30) PATH '$.name', age INTEGER PATH '$.age' ) ) ) AS fam; Copyright © 2018 Oracle and/or its afliates. All rights reserved. 19

20 .Families from Table to JSON # Use JSON_ARRAYAGG to produce JSON SELECT JSON_PRETTY( JSON_OBJECT( 'father',ANY_VALUE(father), 'mother',ANY_VALUE(mother), 'marriage_date',ANY_VALUE(marriage_date), 'children',JSON_ARRAYAGG(JSON_OBJECT('name',child,'age',age)) ) ) FROM families_tab GROUP BY id; # Getng rid of "marriage_date" : null is lef as an exercise to the reader … Copyright © 2018 Oracle and/or its afliates. All rights reserved. 20

22 .Timeline CREATE TABLE tmeline ( ts TIMESTAMP, INSERT INTO tmeline VALUES pos POINT SRID 4326 ('2018-11-03 20:47:00', ST_SRID(POINT(8.627309818915933, 50.11758702076614), 4326)), ); ('2018-11-03 20:47:05', ST_SRID(POINT(8.627089877776712, 50.11764549632028), 4326)), ('2018-11-03 20:47:10', ST_SRID(POINT(8.626848478965371, 50.117734929382614), 4326)), INSERT INTO tmeline VALUES ('2018-11-03 20:47:15', ST_SRID(POINT(8.626660724334329, 50.11781404310618), 4326)), ('2018-11-03 20:30:00', ST_SRID(POINT(8.627229352645486, 50.11755950283356), 4326)), ('2018-11-03 20:47:20', ST_SRID(POINT(8.626655359916299, 50.11797227016113), 4326)), ('2018-11-03 20:30:05', ST_SRID(POINT(8.627438564948648, 50.11767989367171), 4326)), ('2018-11-03 20:47:25', ST_SRID(POINT(8.626515885047525, 50.11805138349252), 4326)), ('2018-11-03 20:30:10', ST_SRID(POINT(8.627234717063516, 50.11775212802929), 4326)), ('2018-11-03 20:47:30', ST_SRID(POINT(8.62637641017875, 50.11807202173137), 4326)), ('2018-11-03 20:30:15', ST_SRID(POINT(8.627057691268533, 50.11782436227788), 4326)), ('2018-11-03 20:47:35', ST_SRID(POINT(8.626215477637857, 50.11800322756724), 4326)), ('2018-11-03 20:30:20', ST_SRID(POINT(8.62689139430961, 50.117879397822705), 4326)), ('2018-11-03 20:47:40', ST_SRID(POINT(8.626076002769082, 50.11791035528887), 4326)), ('2018-11-03 20:30:25', ST_SRID(POINT(8.626735826186746, 50.117941312735), 4326)), ('2018-11-03 20:47:45', ST_SRID(POINT(8.625904341392129, 50.11781404310618), 4326)), ('2018-11-03 20:30:30', ST_SRID(POINT(8.62662853782615, 50.11786219922173), 4326)), ('2018-11-03 20:47:50', ST_SRID(POINT(8.625738044433206, 50.117721170460825), 4326)) ('2018-11-03 20:30:35', ST_SRID(POINT(8.626714368514627, 50.11778996503024), 4326)), ; ('2018-11-03 20:30:40', ST_SRID(POINT(8.626837750129312, 50.11774180884201), 4326)), ('2018-11-03 20:30:45', ST_SRID(POINT(8.626977224998086, 50.11770053207072), 4326)), ('2018-11-03 20:30:50', ST_SRID(POINT(8.62712206428489, 50.11764893605653), 4326)), ('2018-11-03 20:30:55', ST_SRID(POINT(8.627218623809426, 50.11761453868286), 4326)) ; INSERT INTO tmeline VALUES ('2018-11-03 20:41:00', ST_SRID(POINT(8.627277632407754, 50.117590460506584), 4326)), ('2018-11-03 20:41:05', ST_SRID(POINT(8.627492209128945, 50.11751134641354), 4326)), ('2018-11-03 20:41:10', ST_SRID(POINT(8.62763704841575, 50.11746318994507), 4326)), ('2018-11-03 20:41:15', ST_SRID(POINT(8.627755065612405, 50.117425352685885), 4326)), ('2018-11-03 20:41:20', ST_SRID(POINT(8.62788917606315, 50.117518225905094), 4326)), ('2018-11-03 20:41:25', ST_SRID(POINT(8.627733607940286, 50.117573261801816), 4326)), ('2018-11-03 20:41:30', ST_SRID(POINT(8.627578039817422, 50.11762485789756), 4326)), ('2018-11-03 20:41:35', ST_SRID(POINT(8.627417107276528, 50.11768677313905), 4326)), ('2018-11-03 20:41:40', ST_SRID(POINT(8.627277632407754, 50.117590460506584), 4326)) ; Copyright © 2018 Oracle and/or its afliates. All rights reserved. 22

23 .Window Functon to Add Rank # Order by tmestamp. First point = 1, second point = 2, etc. CREATE VIEW ranked_tmeline AS SELECT *, RANK() OVER w AS num, TIME_TO_SEC(TIMEDIFF(ts, LAG(ts) OVER w)) AS dif FROM tmeline WINDOW w AS ( PARTITION BY DATE(ts) ORDER BY ts ) ; Copyright © 2018 Oracle and/or its afliates. All rights reserved. 23

24 .Generate GeoJSON Path # 5 minutes (300 seconds) or more between points -> new trip. WITH RECURSIVE cte AS ( SELECT num AS trip, ts AS start, ts AS stop, num, JSON_ARRAY(JSON_ARRAY(ST_Longitude(pos), ST_Lattude(pos))) AS path FROM ranked_tmeline WHERE dif IS NULL OR dif >= 300 UNION ALL SELECT cte.trip, cte.start, ranked_tmeline.ts, ranked_tmeline.num, JSON_ARRAY_APPEND(cte.path, '$', JSON_ARRAY(ST_Longitude(ranked_tmeline.pos), ST_Lattude(ranked_tmeline.pos))) FROM ranked_tmeline, cte WHERE ranked_tmeline.num - 1 = cte.num AND ranked_tmeline.dif < 300 ) SELECT DISTINCT trip, start, FIRST_VALUE(stop) OVER w AS stop, JSON_OBJECT('type', 'LineString', 'coordinates', FIRST_VALUE(path) OVER w) AS path_json FROM cte WINDOW w AS (PARTITION BY trip ORDER BY num DESC); Copyright © 2018 Oracle and/or its afliates. All rights reserved. 24

28 .Safe Harbor Statement The preceding is intended to outline our general product directon. It is intended for informaton purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functonality, and should not be relied upon in making purchasing decisions. The development, release, and tming of any features or functonality described for Oracle’s products remains at the sole discreton of Oracle. Copyright © 2018 Oracle and/or its afliates. All rights reserved. 28

29 .

0点赞

0收藏

1下载