Tweet
Tweet

Slide 1

Slide 1 text

SQL nຊϊοΫ 1($POG"TJB νϡʔτϦΞϧ࿮

Slide 2

Slide 2 text

What is it? ࠓ೔Έͳ͞Μʹ࣋ͬͯؼͬͯ΄͍͜͠ͱ

Slide 3

Slide 3 text

What is it? ࠓ೔͔Β࢖͑ΔSQLͷςΫχοΫ

Slide 4

Slide 4 text

What is it? ૝ఆडߨऀ

Slide 5

Slide 5 text

What is it? ૝ఆडߨऀ ↓ SELECTɾINSERTɾUPDATEɾDELETE Λॻ͍ͨ͜ͱ͕͋ΔʢԿͱͳ͘Θ͔Δ

Slide 6

Slide 6 text

What is it? ͦΜͳSQLͷϫϯϥϯΫΞοϓʂʂ

Slide 7

Slide 7 text

What is it? ର৅ͷσʔλϕʔειϑτ΢ΣΞ

Slide 8

Slide 8 text

What is it? PostgreSQL 9.6 ଞͷRDBͷ࿩͸͠·ͤΜ

Slide 9

Slide 9 text

What is it? ࠓ೔ͷσϞͰ࢖͏αϯϓϧ Githubʹ͓͍ͯ·͢

Slide 10

Slide 10 text

What is it? ޕલதʹडߨͨ͠ਓ͸ खݩͰࢼ͠ͳ͕Β

Slide 11

Slide 11 text

What is it? ޕલத͸डߨͯ͠ͳ͍ਓ͸ ͋ͱ͔Β෮श͢ΔલఏͰ

Slide 12

Slide 12 text

What is it? ͊͞ϫϯϥϯΫΞοϓ͠·͠ΐ͏ʂʂ

Slide 13

Slide 13 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ

Slide 14

Slide 14 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ

Slide 15

Slide 15 text

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿWebΤϯδχΞ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

Slide 16

Slide 16 text

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿWebΤϯδχΞ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

Slide 17

Slide 17 text

தࠃ஍ํDBษڧձ https://dbstudychugoku.github.io/

Slide 18

Slide 18 text

தࠃ஍ํDBษڧձ https://dbstudychugoku.github.io/ աڈͷࢿྉ͕߇͑Ίʹݴͬͯ΋ ࠷ߴͰ࠷ڧͳͷͰੋඇνΣοΫʂ

Slide 19

Slide 19 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ

Slide 20

Slide 20 text

SQLͷجຊతͳߏจ 42-ͷجຊ͸4&-&$5

Slide 21

Slide 21 text

SELECT SELECT * FROM table_name WHERE id = 1

Slide 22

Slide 22 text

SELECT SELECT * FROM table_name WHERE id = 1 OR id = 2

Slide 23

Slide 23 text

SELECT SELECT * FROM table_name WHERE id = 1 OR id = 2 JE͕ͱͷσʔλ͕ݕࡧ͞ΕΔ

Slide 24

Slide 24 text

SELECT SELECT * FROM table_name WHERE id IN (1, 2)

Slide 25

Slide 25 text

SELECT SELECT * FROM table_name WHERE id IN (1, 2) JE͕ͱͷσʔλ͕ݕࡧ͞ΕΔ

Slide 26

Slide 26 text

SQLͷجຊతͳߏจ 03͸*/۟ͱ౳Ձ

Slide 27

Slide 27 text

SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛ࠶ར༻͢Δ

Slide 28

Slide 28 text

SELECT SELECT * FROM table_name WHERE id IN ( SELECT id FROM table_name) )

Slide 29

Slide 29 text

SELECT SELECT * FROM table_name WHERE id IN ( SELECT id FROM table_name) ) αϒΫΤϦͷݕࡧ݁ՌΛར༻ͯ͠8)&3&͢Δ

Slide 30

Slide 30 text

SELECT SELECT * FROM table_name WHERE id IN ( SELECT unnest(ARRAY[1,2])) )

Slide 31

Slide 31 text

SELECT SELECT * FROM table_name WHERE id IN ( SELECT unnest(ARRAY[1,2])) ) "33":͕ٖࣅςʔϒϧ

Slide 32

Slide 32 text

SELECT SELECT * FROM table_name WHERE id IN ( SELECT unnest(ARRAY[1,2])) ) "33":͕ٖࣅςʔϒϧ JE͕ͱͷσʔλ͕ݕࡧ͞ΕΔ

Slide 33

Slide 33 text

VTFS@JE OBNF BSFB@JE TPOF TPVEBJ UBLFUPNP TPVEBJ BSFB@JE BSFB@OBNF ޿ౡ Ԭࢁ ౦ژ େࡕ

Slide 34

Slide 34 text

VTFS@JE OBNF BSFB@JE TPOF TPVEBJ UBLFUPNP TPVEBJ BSFB@JE BSFB@OBNF ޿ౡ Ԭࢁ ౦ژ େࡕ

Slide 35

Slide 35 text

VTFS@JE OBNF BSFB@JE TPOF TPVEBJ UBLFUPNP TPVEBJ BSFB@JE BSFB@OBNF ޿ౡ Ԭࢁ ౦ژ େࡕ ·ͣݕࡧͷݩΛߜΓࠐΉ

Slide 36

Slide 36 text

VTFS@JE OBNF BSFB@JE TPOF TPVEBJ UBLFUPNP TPVEBJ BSFB@JE BSFB@OBNF ޿ౡ Ԭࢁ ౦ژ େࡕ ݕࡧͨ݁͠ՌͰ*/۟ʹ౤͛Δ ͕͜͜ઌఔͷαϒΫΤϦ෦෼

Slide 37

Slide 37 text

VTFS@JE OBNF BSFB@JE TPOF TPVEBJ UBLFUPNP TPVEBJ BSFB@JE BSFB@OBNF ޿ౡ Ԭࢁ ౦ژ େࡕ

Slide 38

Slide 38 text

VTFS@JE OBNF BSFB@JE TPOF TPVEBJ UBLFUPNP TPVEBJ BSFB@JE BSFB@OBNF ޿ౡ Ԭࢁ ౦ژ େࡕ ݕࡧ݁Ռ

Slide 39

Slide 39 text

SELECT SELECT * FROM users WHERE area_id IN ( SELECT area_id FROM area WHERE name = '޿ౡ' )

Slide 40

Slide 40 text

SELECT SELECT * FROM users WHERE area_id IN ( SELECT area_id FROM area WHERE name = '޿ౡ' )

Slide 41

Slide 41 text

SQLͷجຊతͳߏจ 8)&3&۟͸ߋ৽΍࡟আͰ΋͋Δ

Slide 42

Slide 42 text

SQLͷجຊతͳߏจ 8)&3&۟͸ߋ৽΍࡟আͰ΋͋Δ ˣ αϒΫΤϦͱ*/۟͸Ԡ༻Ͱ͖Δ

Slide 43

Slide 43 text

UPDATE UPDATE table_name SET column_name = value, column_name = value,... WHERE ৚݅

Slide 44

Slide 44 text

UPDATE UPDATE table_name SET column_name = value, column_name = value,... WHERE ৚݅ 4&-&$5ͱಉ͡

Slide 45

Slide 45 text

DELETE DELETE FROM users WHERE area_id IN ( SELECT area_id FROM area WHERE name = '޿ౡ' )

Slide 46

Slide 46 text

DELETE DELETE FROM users WHERE area_id IN ( SELECT area_id FROM area WHERE name = '޿ౡ' ) 4&-&$5ͷ݁Ռͱಉ͡ର৅Λ࡟আ͢Δ

Slide 47

Slide 47 text

SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛ࠶ར༻͢Δ ͦͷ̎

Slide 48

Slide 48 text

VTFS@JE OBNF CJSUIEBZ BHF ૖େ ኽָ r ᗅָ r ࡣָ r

Slide 49

Slide 49 text

VTFS@JE OBNF CJSUIEBZ BHF ૖େ ኽָ r ᗅָ r ࡣָ r ஀ੜ೔͕དྷͨΒߋ৽͕ඞཁ

Slide 50

Slide 50 text

UPDATE UPDATE users SET age = new_users.age FROM (SELECT id, EXTRACT(YEAR FROM age(now(), birthday)) AS age FROM users) AS new_users WHERE users.id = new_users.id

Slide 51

Slide 51 text

UPDATE UPDATE users SET age = new_users.age FROM (SELECT id, EXTRACT(YEAR FROM age(now(), birthday)) AS age FROM users) AS new_users WHERE users.id = new_users.id

Slide 52

Slide 52 text

UPDATE UPDATE users SET age = new_users.age FROM (SELECT id, EXTRACT(YEAR FROM age(now(), birthday)) AS age FROM users) AS new_users WHERE users.id = new_users.id ஀ੜ೔͔Βݱࡏͷ೥ྸΛܭࢉ

Slide 53

Slide 53 text

UPDATE UPDATE users SET age = new_users.age FROM (SELECT id, EXTRACT(YEAR FROM age(now(), birthday)) AS age FROM users) AS new_users WHERE users.id = new_users.id

Slide 54

Slide 54 text

UPDATE UPDATE users SET age = new_users.age FROM (SELECT id, EXTRACT(YEAR FROM age(now(), birthday)) AS age FROM users) AS new_users WHERE users.id = new_users.id ߋ৽ͷର৅Λ8)&3&ͰϚονϯά

Slide 55

Slide 55 text

VTFS@JE BHF VTFS@JE OBNF CJSUIEBZ BHF ૖େ ኽָ r ᗅָ r ࡣָ r

Slide 56

Slide 56 text

VTFS@JE BHF VTFS@JE OBNF CJSUIEBZ BHF ૖େ ኽָ r ᗅָ r ࡣָ r ஀ੜ೔͔Β೥ྸΛࢉग़

Slide 57

Slide 57 text

VTFS@JE BHF VTFS@JE OBNF CJSUIEBZ BHF ૖େ ኽָ r ᗅָ r ࡣָ r

Slide 58

Slide 58 text

VTFS@JE BHF VTFS@JE OBNF CJSUIEBZ BHF ૖େ ኽָ r ᗅָ r ࡣָ r ೥ྸͰ61%"5& ߋ৽ͷର৅Λ8)&3&ͰϚονϯά

Slide 59

Slide 59 text

SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛ࠶ར༻͢Δ ͦͷ

Slide 60

Slide 60 text

INSERT INSERT INTO table_name (column_name,column_name…) VALUES (value,value…) , (value,value…) , (value,value…) , (value,value…)

Slide 61

Slide 61 text

INSERT INSERT INTO table_name (column_name,column_name…) VALUES (value,value…) , (value,value…) , (value,value…) , (value,value…)

Slide 62

Slide 62 text

INSERT INSERT INTO table_name (column_name,column_name…) SELECT column_name , column_name… FROM table_name WHERE …

Slide 63

Slide 63 text

SQLͷجຊతͳߏจ ച্ϩά͔Βച্ूܭΛग़͢

Slide 64

Slide 64 text

ച্೔࣌ ച্ֹۚ ộ ộ ച্೔ ച্ֹۚ ộ ộ

Slide 65

Slide 65 text

ച্೔࣌ ച্ֹۚ ộ ộ ച্೔ ച্ֹۚ ộ ộ

Slide 66

Slide 66 text

ച্೔࣌ ച্ֹۚ ộ ộ ച্೔ ച্ֹۚ ộ ộ

Slide 67

Slide 67 text

ച্೔࣌ ച্ֹۚ ộ ộ ച্೔ ച্ֹۚ ộ ộ ೔෇͝ͱʹूܭ͢Δඞཁ͕͋Δ

Slide 68

Slide 68 text

ച্೔࣌ ച্ֹۚ ộ ộ ച্೔ ച্ֹۚ ộ ộ ूܭ݁ՌΛผUBCMFʹ*/4&35

Slide 69

Slide 69 text

INSERT INSERT INTO ച্_2016_12 SELECT to_char(ച্೔࣌,'YYYY/MM/DD') AS ച্೔ , sum(ച্ֹۚ) AS ച্ FROM ച্log WHERE ച্೔࣌ BETWEEN date_trunc('month',now()) AND date_trunc(‘month',now()) + interval '1 month' - interval '1 days' GROUP BY ച্೔

Slide 70

Slide 70 text

INSERT INSERT INTO ച্_2016_12 SELECT to_char(ച্೔࣌,'YYYY/MM/DD') AS ച্೔ , sum(ച্ֹۚ) AS ച্ FROM ച্log WHERE ച্೔࣌ BETWEEN date_trunc('month',now()) AND date_trunc(‘month',now()) + interval '1 month' - interval '1 days' GROUP BY ച্೔

Slide 71

Slide 71 text

INSERT INSERT INTO ച্_2016_12 SELECT to_char(ച্೔࣌,'YYYY/MM/DD') AS ച্೔ , sum(ച্ֹۚ) AS ച্ FROM ച্log WHERE ച্೔࣌ BETWEEN date_trunc('month',now()) AND date_trunc(‘month',now()) + interval '1 month' - interval '1 days' GROUP BY ച্೔ ɹച্MPHͷ݄ॳ͔Β݄຤ͷσʔλΛநग़

Slide 72

Slide 72 text

INSERT INSERT INTO ച্_2016_12 SELECT to_char(ച্೔࣌,'YYYY/MM/DD') AS ച্೔ , sum(ച্ֹۚ) AS ച্ FROM ച্log WHERE ച্೔࣌ BETWEEN date_trunc('month',now()) AND date_trunc(‘month',now()) + interval '1 month' - interval '1 days' GROUP BY ച্೔ ೔࣌Λ೔෇ʹม׵

Slide 73

Slide 73 text

INSERT INSERT INTO ച্_2016_12 SELECT to_char(ച্೔࣌,'YYYY/MM/DD') AS ച্೔ , sum(ച্ֹۚ) AS ച্ FROM ച্log WHERE ച্೔࣌ BETWEEN date_trunc('month',now()) AND date_trunc(‘month',now()) + interval '1 month' - interval '1 days' GROUP BY ച্೔ ೔෇ʹม׵ͨ͠ച্೔ͰάϧʔϓԽ

Slide 74

Slide 74 text

INSERT INSERT INTO ച্_2016_12 SELECT to_char(ച্೔࣌,'YYYY/MM/DD') AS ച্೔ , sum(ച্ֹۚ) AS ച্ FROM ച্log WHERE ച্೔࣌ BETWEEN date_trunc('month',now()) AND date_trunc(‘month',now()) + interval '1 month' - interval '1 days' GROUP BY ച্೔ ूܭ݁ՌΛ*/4&35

Slide 75

Slide 75 text

SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛར༻͢Δ

Slide 76

Slide 76 text

SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛར༻͢Δ ˣ ෳ਺ճͷ42-͕ҰճͰ࣮ߦग़དྷΔ

Slide 77

Slide 77 text

SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛར༻͢Δ ˣ ෳ਺ճͷ42-͕ҰճͰ࣮ߦग़དྷΔ ύϑΥʔϚϯε্͕͕Δʂ

Slide 78

Slide 78 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ

Slide 79

Slide 79 text

CASEΛ࢖͍͜ͳͦ͏ 42-ͷۤखͳࣄ

Slide 80

Slide 80 text

CASEΛ࢖͍͜ͳͦ͏ 42-ͷۤखͳࣄ ˣ ੍ޚߏจͱϧʔϓ

Slide 81

Slide 81 text

CASEΛ࢖͍͜ͳͦ͏ ͔͠͠$"4&Λ࢖͏ࣄͰ ଟ͘ͷ୅ସҊʹͳΔ

Slide 82

Slide 82 text

CASEͷجຊతͳߏจ SELECT users.name, CASE area.name WHEN ‘޿ౡ' THEN 'தࠃ஍ํ' WHEN ‘Ԭࢁ' THEN 'தࠃ஍ํ' WHEN ‘େࡕ' THEN 'ؔ੢஍ํ' ELSE 'ͦͷଞ' END AS area_group FROM users INNER JOIN area USING (area_id)

Slide 83

Slide 83 text

OBNF BSFB@HSPVQ TPOF தࠃ஍ํ TPVEBJ தࠃ஍ํ UBLFUPNP தࠃ஍ํ TPVEBJ தࠃ஍ํ IPHF ͦͷଞ GVHB ؔ੢஍ํ GPP ؔ੢஍ํ

Slide 84

Slide 84 text

CASEͷجຊతͳߏจ SELECT users.name, CASE WHEN area.name = '޿ౡ' OR area.name = ‘Ԭࢁ' THEN 'தࠃ஍ํ' WHEN area.name ='େࡕ' THEN 'ؔ੢஍ํ' ELSE 'ͦͷଞ' END AS area_group FROM users INNER JOIN area USING (area_id)

Slide 85

Slide 85 text

CASEΛ࢖͍͜ͳͦ͏ $"4&͸ࣜ

Slide 86

Slide 86 text

CASEΛ࢖͍͜ͳͦ͏ $"4&͸ࣜ ˣ Ͳ͜ʹͰ΋ॻ͚Δ

Slide 87

Slide 87 text

CASEͷجຊతͳߏจ SELECT users.name, area.name, CASE area.name WHEN ‘޿ౡ' THEN 'தࠃ஍ํ' WHEN ‘Ԭࢁ' THEN 'தࠃ஍ํ' WHEN ‘େࡕ' THEN 'ؔ੢஍ํ' ELSE 'ͦͷଞ' END AS area_group FROM users INNER JOIN area USING (area_id) ORDER BY CASE area.name WHEN '޿ౡ' THEN 1 ELSE 2 END, area_group

Slide 88

Slide 88 text

OBNF BSFB BSFB@HSPVQ TPVEBJ ޿ౡ தࠃ஍ํ TPOF ޿ౡ தࠃ஍ํ UBLFUPNP Ԭࢁ தࠃ஍ํ TPVEBJ Ԭࢁ தࠃ஍ํ IPHF ౦ژ ͦͷଞ GPP େࡕ ؔ੢஍ํ GVHB େࡕ ؔ੢஍ํ

Slide 89

Slide 89 text

CASEͷجຊతͳߏจ SELECT users.name, area.name, CASE area.name WHEN ‘޿ౡ' THEN 'தࠃ஍ํ' WHEN ‘Ԭࢁ' THEN 'தࠃ஍ํ' WHEN ‘େࡕ' THEN 'ؔ੢஍ํ' ELSE 'ͦͷଞ' END AS area_group FROM users INNER JOIN area USING (area_id) ORDER BY CASE area.name WHEN 'Ԭࢁ' THEN 1 ELSE 2 END, area_group ԬࢁΛ্ʹ͍ͨ࣌͠

Slide 90

Slide 90 text

OBNF BSFB BSFB@HSPVQ TPVEBJ Ԭࢁ தࠃ஍ํ TPOF Ԭࢁ தࠃ஍ํ UBLFUPNP ޿ౡ தࠃ஍ํ TPVEBJ ޿ౡ தࠃ஍ํ IPHF ౦ژ ͦͷଞ GPP େࡕ ؔ੢஍ํ GVHB େࡕ ؔ੢஍ํ

Slide 91

Slide 91 text

CASEͷجຊతͳߏจ SELECT users.name, area.name, CASE area.name WHEN ‘޿ౡ' THEN 'தࠃ஍ํ' WHEN ‘Ԭࢁ' THEN 'தࠃ஍ํ' WHEN ‘େࡕ' THEN 'ؔ੢஍ํ' ELSE 'ͦͷଞ' END AS area_group FROM users INNER JOIN area USING (area_id) ORDER BY CASE area.name WHEN '޿ౡ' THEN 1 ELSE 2 END, area_group

Slide 92

Slide 92 text

CASEͷجຊతͳߏจ /໰୊

Slide 93

Slide 93 text

CASEͷجຊతͳߏจ ϧʔϓͷ਺͚ͩRVFSZ౤͛Δ

Slide 94

Slide 94 text

CASEͷجຊతͳߏจ $users = $this->db->users->get(); $this->db->begin(); foreach ($users as $user_id => $user) { if ($user[“pref”] == ‘Ԭࢁ’) { $user[“pref”] = ‘େ౎ձ’ } elseif ($user[“pref”] == ‘޿ౡ’){ $user[“pref”] = ‘෱ࢁ’ } $this->db->update($user); } $this->db->commit();

Slide 95

Slide 95 text

CASEͷجຊతͳߏจ $users = $this->db->users->get(); $this->db->begin(); foreach ($users as $user_id => $user) { if ($user[“pref”] == ‘Ԭࢁ’) { $user[“pref”] = ‘େ౎ձ’ } elseif ($user[“pref”] == ‘޿ౡ’){ $user[“pref”] = ‘෱ࢁ’ } $this->db->update($user); } $this->db->commit(); ͦ΋ͦ΋8)&3&۟ͰQSFGࢦఆͯ͠ 61%"5&ͳΒճ

Slide 96

Slide 96 text

CASEͷجຊతͳߏจ ճͷ61%"5&จʹม͑Δ

Slide 97

Slide 97 text

CASEͷجຊతͳߏจ UPDATE users SET pref = CASE pref WHEN ‘Ԭࢁ’ THEN ‘େ౎ձ’ WHEN ‘޿ౡ’ THEN ‘෱ࢁ’ ELSE pref END WHERE pref IN (‘Ԭࢁ’,’޿ౡ’)

Slide 98

Slide 98 text

CASEͷجຊతͳߏจ $"4&͸ࣜͳͷͰ ৭Μͳͱ͜ΖͰ࢖͑Δ

Slide 99

Slide 99 text

CASEͷجຊతͳߏจ Ԡ༻ฤ ͭͷ%#Λൺֱͯ͠61%"5&

Slide 100

Slide 100 text

CASEͷجຊతͳߏจ JE OBNF BHF TPOF UBLFUPNP TPVEBJ VTFS@JE CJSUIEBZ QSFG ޿ౡ ෱ࢁ େ౎ձ VTFST VTFS@QSPQFSUJFT

Slide 101

Slide 101 text

CASEͷجຊతͳߏจ UPDATE users AS u, (SELECT users.id AS id, CASE WHEN user_properties.birthday > ‘1996-07-30’ THEN ‘ෆৄ’ ELSE age END AS age FROM users INNER JOIN user_properties ON user.id = user_properties.user_id AND user_properties.pref IN (‘෱ࢁ’,’ͳ͝΍’,’େ౎ձ’)) AS tmp SET u.age = tmp.age WHERE u.id = tmp.id

Slide 102

Slide 102 text

CASEͷجຊతͳߏจ JE OBNF BHF TPOF UBLFUPNP ෆৄ TPVEBJ VTFS@JE CJSUIEBZ QSFG ޿ౡ ෱ࢁ େ౎ձ

Slide 103

Slide 103 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ

Slide 104

Slide 104 text

ૉૣ͘ாථΛ࡞Δ PostgreSQLͷؔ਺

Slide 105

Slide 105 text

8JOEPXؔ਺ PostgreSQLͷؔ਺

Slide 106

Slide 106 text

঎඼໊ ച্ ച্೔ ϓϩάϥϚͷͨΊͷ42- ಺෦ߏ଄͔ΒֶͿ ιϑτ΢ΣΞσβΠϯ 42-࣮ફೖ໳ ཧ࿦͔ΒֶͿσʔλϕʔε 42-ٯҾ͖େશͷۃҙ ϓϩάϥϚͷͨΊͷ42- 42-࣮ફೖ໳ ཧ࿦͔ΒֶͿσʔλϕʔε 42-࣮ફೖ໳ Ϧʔμϒϧίʔυ Ϧʔμϒϧίʔυ σʔλϕʔεɾϦϑΝΫλ ୡਓʹֶͿ%#ઃܭ ɿ ɿ ɿ ɿ ɿ ɿ ˞࣮຿Ͱ͸ฤ͸ਖ਼نԽ͢Δ΂͖

Slide 107

Slide 107 text

Windowؔ਺ ·ͣ͸೔ผʹूܭ͢Δ

Slide 108

Slide 108 text

࣮ࡍͷSQL SELECT to_char( sum(“ച্"),'FM999,999,999' ) AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY “ച্೔" ORDER BY "ച্೔"

Slide 109

Slide 109 text

࣮ࡍͷSQL SELECT to_char( sum(“ച্"),'FM999,999,999' ) AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY “ച্೔" ORDER BY "ച্೔"

Slide 110

Slide 110 text

࣮ࡍͷSQL SELECT to_char( sum(“ച্"),'FM999,999,999' ) AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY “ച্೔" ORDER BY "ച্೔" ܻ۠੾ΓͰΧϯϚΛೖΕΔ

Slide 111

Slide 111 text

೔ผച্ ച্೔ ɿ ɿ ɿ ɿ

Slide 112

Slide 112 text

Windowؔ਺ લ೔ͱͷࠩ෼Λग़͍ͨ͠

Slide 113

Slide 113 text

Windowؔ਺ લ೔ͱͷࠩ෼Λग़͍ͨ͠ ˣ MBH Λ࢖ͬͯूܭ͢Δ

Slide 114

Slide 114 text

࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ"

Slide 115

Slide 115 text

࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ"

Slide 116

Slide 116 text

࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ

Slide 117

Slide 117 text

࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" 8JOEPXؔ਺ͷର৅Λࢦఆ ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ

Slide 118

Slide 118 text

࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" 8JOEPXؔ਺ͷର৅Λࢦఆ ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ લߦͷऔಘ͢ΔΧϥϜΛࢦఆ

Slide 119

Slide 119 text

೔ผച্ ച্೔ લ೔ച্ /6-- ɿ ɿ ɿ ɿ ɿ ɿ

Slide 120

Slide 120 text

೔ผച্ ച্೔ લ೔ച্ /6-- ɿ ɿ ɿ ɿ ɿ ɿ લߦ͕ແ͍ͷͰ/6--

Slide 121

Slide 121 text

Windowؔ਺ ཌ೔ͷ৔߹͸ʁ

Slide 122

Slide 122 text

Windowؔ਺ ཌ೔ͷ৔߹͸ʁ ˣ MFBE Λ࢖ͬͯूܭग़དྷΔ

Slide 123

Slide 123 text

ؔ਺ આ໌ SPX@OVNCFS ߦ൪߸ SBOL ϥϯΩϯά ಉ཰Ͱ൪߸Λඈ͹͢ EFOTF@SBOL ϥϯΩϯά ಉ཰Ͱ൪߸Λඈ͹͞ͳ͍ QFSDFOU@SBOL ϥϯΩϯά Ͱදࣔ SBOL શߦ਺ DVNF@EJTU QFSDFOU@SBOLʹྨࣅ ݱࡏͷߦͷҐஔ શߦ਺ OUJMF / ϥϯΩϯά /ʹ෼ׂ MBH WBMVF P⒎TFU EFGBVMU ιʔτঢ়ଶͰͷલͷߦͷ஋ MFBE WBMVF P⒎TFU EFGBVMU ιʔτঢ়ଶͰͷޙͷߦͷ஋ pSTU@WBMVF WBMVF ࠷ॳͷ஋ MBTU@WBMVF WBMVF ࠷ޙͷ஋ OUI@WBMVF WBMVF / /൪໨ͷ஋ ͔Β਺͑Δ

Slide 124

Slide 124 text

Windowؔ਺ ศརͳྫ w ݄ผͷாථ࡞੒ w ϥϯΩϯάදࣔ w άϧʔϓผͷूܭ

Slide 125

Slide 125 text

ศརͳܕΛར༻͢Δ 1PTUHSF42-ͷػೳΛ׆༻͢Δ

Slide 126

Slide 126 text

ศརͳܕΛར༻͢Δ 1PTUHSF42-͸+40/͕ಘҙ

Slide 127

Slide 127 text

ศརͳܕΛར༻͢Δ 1PTUHSF42-͸+40/͕ಘҙ ˣ +40/ܕɾ+40/#ܕ͕͋Δ

Slide 128

Slide 128 text

ศརͳܕΛར༻͢Δ 1PTUHSF42-͸ൣғΛѻ͏ͷ͕ಘҙ

Slide 129

Slide 129 text

ศརͳܕΛར༻͢Δ 1PTUHSF42-͸ൣғΛѻ͏ͷ͕ಘҙ ˣ ൣғܕΛ࢖͑͹ΧϨϯμʔ΋؆୯

Slide 130

Slide 130 text

λεΫ໊ ৄࡉ ରԠظؒ Πϯετʔϧ \ॴଐΞγετ ߨࢣتా^ < جຊฤ \ॴଐΞγετ ߨࢣتా^ < 42-Oຊ \ॴଐΦϛΧϨ ߨࢣીࠜ^ < σʔλ෼ੳ \ॴଐΈ͔͔ ߨࢣେࢁ^ <

Slide 131

Slide 131 text

࣮ࡍͷSQL SELECT * FROM "λεΫҰཡ" WHERE "ৄࡉ" ->> 'ߨࢣ' = 'تా'

Slide 132

Slide 132 text

λεΫ໊ ৄࡉ ରԠظؒ Πϯετʔϧ \ॴଐΞγετ ߨࢣتా^ < جຊฤ \ॴଐΞγετ ߨࢣتా^ < 42-Oຊ \ॴଐΦϛΧϨ ߨࢣીࠜ^ < σʔλ෼ੳ \ॴଐΈ͔͔ ߨࢣେࢁ^ <

Slide 133

Slide 133 text

࣮ࡍͷSQL SELECT * FROM "λεΫҰཡ" WHERE '2016-12-01'::DATE <@ "ରԠظؒ"

Slide 134

Slide 134 text

λεΫ໊ ৄࡉ ରԠظؒ Πϯετʔϧ \ॴଐΞγετ ߨࢣتా^ < جຊฤ \ॴଐΞγετ ߨࢣتా^ < 42-Oຊ \ॴଐΦϛΧϨ ߨࢣીࠜ^ < σʔλ෼ੳ \ॴଐΈ͔͔ ߨࢣେࢁ^ <

Slide 135

Slide 135 text

࣮ࡍͷSQL SELECT * FROM "λεΫҰཡ" WHERE daterange( '2016-12-03', '2016-12-04', ‘[]' ) <@ "ରԠظؒ";

Slide 136

Slide 136 text

λεΫ໊ ৄࡉ ରԠظؒ Πϯετʔϧ \ॴଐΞγετ ߨࢣتా^ < جຊฤ \ॴଐΞγετ ߨࢣتా^ < 42-Oຊ \ॴଐΦϛΧϨ ߨࢣીࠜ^ < σʔλ෼ੳ \ॴଐΈ͔͔ ߨࢣେࢁ^ <

Slide 137

Slide 137 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ

Slide 138

Slide 138 text

·ͱΊ 42-Λ֮͑Δͱදݱྗ্͕͕Δ

Slide 139

Slide 139 text

·ͱΊ ૉૣ͘දݱग़དྷΔ͜ͱͷՁ஋

Slide 140

Slide 140 text

·ͱΊ දݱྗ্͕͕Ε͹ઃܭྗ্͕͕Δ

Slide 141

Slide 141 text

·ͱΊ

Slide 142

Slide 142 text

·ͱΊ ɾQPTUHSFTRMKQ4MBDL νϟοτϧʔϜ IUUQTQPTUHSFTRMIBDLFSTKQIFSPLVBQQDPN ɾNZTRMDBTVBM4MBDL νϟοτϧʔϜ IUUQTNZTRMDBTVBMTMBDLJOIFSPLVBQQDPN

Slide 143

Slide 143 text

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ