PHPerに知ってほしいRDBな事

by soudai sone

Slide 1

Slide 1 text

PHPerʹ஌ͬͯ΄͍͠RDBͷࣄ 1)1ΧϯϑΝϨϯεˏ๺ւಓ

Slide 2

Slide 2 text

What is it? σʔλϕʔεͷण໋͸ ΞϓϦέʔγϣϯΑΓ΋௕͍

Slide 3

Slide 3 text

What is it? ͦΜͳ௕͍෇͖߹͍ʹͳΔσʔλϕʔε ͷେ੾ͳࣄΛ͓఻͑͠·͢

Slide 4

Slide 4 text

What is it? σʔλϕʔεͰ େ੾ͳ͜ͱ ΞϓϦέʔγϣϯͰ େ੾ͳ͜ͱ

Slide 5

Slide 5 text

What is it? ΞϓϦέʔγϣϯͰ େ੾ͳ͜ͱ σʔλϕʔεͰ େ੾ͳ͜ͱ ͜͜ͷ࿩Λ͠·͢

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

What is it? PostgreSQL 9.5ͱMySQL 5.6(InnoDB)ʹݶΔ ଞͷRDBͷ࿩͸͠·ͤΜ

Slide 8

Slide 8 text

What is it? େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ

Slide 9

Slide 9 text

What is it? ηΩϡϦςΟͷ࿩ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ

Slide 10

Slide 10 text

What is it? ηΩϡϦςΟͷ࿩ ↓ ಙؙઌੜͰָ͓͠Έ͍ͩ͘͞ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ

Slide 11

Slide 11 text

What is it? ৽ػೳͷ࿩ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ

Slide 12

Slide 12 text

What is it? ৽ػೳͷ࿩ ↓ ֤σʔλϕʔεͷίϛϡχςΟʹࢀՃ͠Α͏ https://mysql-casual-slackin.herokuapp.com/ https://postgresql-hackers-jp.herokuapp.com/ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ

Slide 13

Slide 13 text

What is it? ӡ༻ͱઃܭͱཧ࿦ͷ࿩ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ

Slide 14

Slide 14 text

What is it? ӡ༻ͱઃܭͱཧ࿦ͷ࿩ ↓ SQLΞϯνύλʔϯͱDB࣮ફೖ໳Λಡ΋͏ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ

Slide 15

Slide 15 text

What is it?

Slide 16

Slide 16 text

What is it? PostgreSQLͱMySQLͷҧ͍ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ

Slide 17

Slide 17 text

What is it? PostgreSQLͱMySQLͷҧ͍ ↓ Ͱ΋ͪΐͬͱ͚ͩग़͖ͯ·͢ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ

Slide 18

Slide 18 text

What is it? ࠓ೔ͷ࿩͢Δ͜ͱ

Slide 19

Slide 19 text

What is it? RDBΛ࢖͏্Ͱ͓͍֮͑ͯͯཉ͍͜͠ͱ

Slide 20

Slide 20 text

What is it? RDBΛ࢖͏্Ͱ͓͍֮͑ͯͯཉ͍͜͠ͱ ↓ جૅతͳ͜ͱͰ͙࣮͢ફͰ͖Δ͜ͱ

Slide 21

Slide 21 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹਖ਼نԽ ̐ɹWindowؔ਺ ̑ɹ·ͱΊ

Slide 22

Slide 22 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹਖ਼نԽ ̐ɹWindowؔ਺ ̑ɹ·ͱΊ

Slide 23

Slide 23 text

ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ31ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢

Slide 24

Slide 24 text

What is it? d

Slide 25

Slide 25 text

Slide 26

Slide 26 text

͕ͬͪ͜ࢲͰ͢

Slide 27

Slide 27 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹਖ਼نԽͱJOIN ̐ɹWindowؔ਺ ̑ɹ·ͱΊ

Slide 28

Slide 28 text

ݕࡧͱINDEX #5SFF*/%&9

Slide 29

Slide 29 text

ݕࡧͱINDEX #5SFF*/%&9 3%#ͷ*/%&9͸جຊతʹ͜Ε ʢ(*/΍35SFFʹ͍ͭͯ͸ࠓճ৮Εͳ͍ʣ

Slide 30

Slide 30 text

ݕࡧͱINDEX Ҿ༻ݩɿIUUQTKBXJLJQFEJBPSHXJLJ##&$" ΢ΟΩϖσΟΞʮ#໦ʯ

Slide 31

Slide 31 text

ݕࡧͱINDEX w 8)&3&۟Ͱ࢖͏ w (3061#:۟Ͱ΋࢖͏ w 03%&3#:۟Ͱ΋࢖͏

Slide 32

Slide 32 text

ݕࡧͱINDEX w 8)&3&۟Ͱ࢖͏ w (3061#:۟Ͱ΋࢖͏ w 03%&3#:۟Ͱ΋࢖͏ ݕࡧશൠͰ࢖͏

Slide 33

Slide 33 text

ݕࡧͱINDEX w 8)&3&۟Ͱ࢖͏ w (3061#:۟Ͱ΋࢖͏ w 03%&3#:۟Ͱ΋࢖͏ ݕࡧશൠͰ࢖͏ ͔͠͠.Z42-͸ͭͷ5"#-&Ͱ ͔ͭ͠*/%&9Λ࢖Θͳ͍

Slide 34

Slide 34 text

ݕࡧͱINDEX */%&9ΛޮՌతʹ࢖͏ʹ͸ w ॏෳ͕গͳ͍΄͏͕ྑ͍ w ݁Ռ͕গͳ͍΄͏͕ྑ͍

Slide 35

Slide 35 text

ݕࡧͱINDEX */%&9ΛޮՌతʹ࢖͏ʹ͸ w ॏෳ͕গͳ͍΄͏͕ྑ͍ w ݁Ռ͕গͳ͍΄͏͕ྑ͍ 6OJRVF͕ཧ૝ ॏෳ͕ଟ͍৔߹ɺ*/%&9ͷҙຯ͕ബΕΔ Α͋͘Δͷ͸%&-&5&@'-"(

Slide 36

Slide 36 text

ݕࡧͱINDEX */%&9ΛޮՌతʹ࢖͏ʹ͸ w ॏෳ͕গͳ͍΄͏͕ྑ͍ w ݁Ռ͕গͳ͍΄͏͕ྑ͍ ࢀরͷ5"#-&ͷશͯΛදࣔ͢ΔͳΒݩ͔Βશ ෦ݟͨ΄͏͕ྑ͍ શମͷʙ·Ͱ͕*/%&9ར༻ͷ໨҆

Slide 37

Slide 37 text

ݕࡧͱINDEX */%&9Λ੍͢Δऀ͸ݕࡧΛ੍͢Δ

Slide 38

Slide 38 text

ݕࡧͱINDEX ࣮ߦܭըΛݟΔ

Slide 39

Slide 39 text

ݕࡧͱINDEX &91-"*/Ͱॏཁͳ͜ͱ w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ w ౷ܭ৘ใΛ࠷৽ʹ͢Δ w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ

Slide 40

Slide 40 text

ݕࡧͱINDEX &91-"*/Ͱॏཁͳ͜ͱ w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ w ౷ܭ৘ใΛ࠷৽ʹ͢Δ w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ σʔλྔʹΑ࣮ͬͯߦܭը͕มΘΔ͜ͱ΋ଟ͍ ࣮ࡍΑΓগͳ͍σʔλͰ֬ೝͯ͠΋ҙຯ͕ແ͍

Slide 41

Slide 41 text

ݕࡧͱINDEX &91-"*/Ͱॏཁͳ͜ͱ w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ w ౷ܭ৘ใΛ࠷৽ʹ͢Δ w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ σʔλʹେྔͷ௥Ճɾ࡟আɾมߋ͕͋Δͱ౷ܭ৘ใ͕ෆਖ਼֬ʹͳΔ "/"-:;&5"#-&Ͱ౷ܭ৘ใΛߋ৽͔ͯ͠Β&91-"*/ʂʂ

Slide 42

Slide 42 text

ݕࡧͱINDEX &91-"*/Ͱॏཁͳ͜ͱ w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ w ౷ܭ৘ใΛ࠷৽ʹ͢Δ w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ ࣮ߦܭըͰߴ଎Խͯ͠΋࣮ߦ݁Ռ͕มΘΔͱҙຯ͕ແ͍ ࠷ऴతʹຊ൪Ͱ֬ೝ͔ͯ͠ΒϦϦʔε͢Δ͜ͱ

Slide 43

Slide 43 text

ݕࡧͱINDEX &91-"*/ͷ·ͱΊ w ެࣜυΩϡϝϯτΛಡ΋͏ UZQFͳͲ͸छྨ͕୔ࢁ͋Δ   IUUQTEFWNZTRMDPNEPDSFGNBOKBFYQMBJOPVUQVUIUNM  IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMFYQMBJOIUNM w ૉৼΓେࣄ

Slide 44

Slide 44 text

ݕࡧͱINDEX .Z42-8PSLCFODI QHBENJO

Slide 45

Slide 45 text

࣮ߦܭը CREATE TABLE `demo`.ùsers` ( ìd` INT NOT NULL AUTO_INCREMENT COMMENT '', `name` VARCHAR(45) NOT NULL COMMENT '', àge` INT NOT NULL COMMENT '', `created` DATETIME NOT NULL DEFAULT NOW () COMMENT '', PRIMARY KEY (ìd`) COMMENT '' ); ——શ݅Λબ୒͢ΔͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users; ——PRIMARY KEYʢINDEXʣΛར༻ͨ͠ྫ SELECT * FROM demo.users WHERE id > 100; ——INDEX͕ແ͍ͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users WHERE age > 20;

Slide 46

Slide 46 text

MySQL

Slide 47

Slide 47 text

MySQL

Slide 48

Slide 48 text

MySQL αϒΫΤϦͱ૬ؔαϒΫΤϦ

Slide 49

Slide 49 text

αϒΫΤϦ —— INDEXͱͯ͠PRIMARY KEY͕ޮ͍͍ͯΔ SELECT * FROM users WHERE id < 1000 AND id > 800 —— 1ճ͔࣮͠ߦ͞Εͳ͍ SELECT * FROM (SELECT * FROM users WHERE id < 1000 AND id > 800) AS dummy

Slide 50

Slide 50 text

MySQL

Slide 51

Slide 51 text

૬ؔαϒΫΤϦ ——஗͍૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age FROM users WHERE id BETWEEN 10 AND 100000)

Slide 52

Slide 52 text

MySQL

Slide 53

Slide 53 text

MySQL .Z42-͔Β."5&3*"-*;&%ʹ มΘΓɺ+0*/ͷΑ͏ͳڍಈʹͳͬͨ ੲΑΓૣ͘ͳͬͨ

Slide 54

Slide 54 text

૬ؔαϒΫΤϦ ——INDEX͕ར༻Ͱ͖Ε͹૬ؔαϒΫΤϦͰ΋଎͍ SELECT * FROM users WHERE created = '2016-02-27 04:31:32' AND id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)

Slide 55

Slide 55 text

MySQL

Slide 56

Slide 56 text

MySQL ૬ؔαϒΫΤϦ͸஗͍ ৔߹͕ଟ͍

Slide 57

Slide 57 text

MySQL ૬ؔαϒΫΤϦ͸஗͍ ˣ +0*/ʹॻ͖׵͑Δ

Slide 58

Slide 58 text

JOIN SELECT * FROM users INNER JOIN users AS tmp ON tmp.id = users.id AND tmp.id BETWEEN 10 AND 100000 WHERE users.created = '2016-02-27 04:31:32'

Slide 59

Slide 59 text

MySQL

Slide 60

Slide 60 text

MySQL +0*/͸ֻ͚ࢉ

Slide 61

Slide 61 text

MySQL +0*/͸ֻ͚ࢉ ˣ ߦ☓ߦ͸ߦ

Slide 62

Slide 62 text

MySQL .Z42-ʹ͸ +0*/ͷΞϧΰϦζϜ͸Ұ͔ͭ͠ͳ͍

Slide 63

Slide 63 text

MySQL .Z42-ʹ͸ +0*/ͷΞϧΰϦζϜ͸Ұ͔ͭ͠ͳ͍ ܾͯ͠ಘҙͳΘ͚Ͱ͸ແ͍ +0*/͸ϋΠίετͳΫΤϦ

Slide 64

Slide 64 text

MySQL w ग़དྷΔ͚ͩখ͔ͯ͘͞͠Β+0*/ w ෆཁͳ+0*/͸ආ͚Δ w */%&9Λར༻ͨ͠+0*/Λ͢Δ

Slide 65

Slide 65 text

MySQL γϯϓϧɾߴ଎

Slide 66

Slide 66 text

MySQL γϯϓϧɾߴ଎ ˣ ೉͍͠ࣄΛ͠ͳ͍ ઃܭྗ͕ॏཁ

Slide 67

Slide 67 text

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ

Slide 68

Slide 68 text

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ ͭͷΫΤϦ಺Ͱෳ਺࢖͑Δ

Slide 69

Slide 69 text

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ #USFFɺ(*/ɺࣜ*/%&9ͳͲγʔϯʹ߹Θͤͯ ෳ਺ͷ*/%&9Λར༻Ͱ͖Δ

Slide 70

Slide 70 text

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ ࢖͑Δ42-ͷߏจ΋ଟ͍ ྫ͑͹΢Πϯυ΢ؔ਺ͳͲ

Slide 71

Slide 71 text

PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ w૬ؔαϒΫΤϦ΋଎͍ w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ /FTUFE-PPQ+PJO 4PSU.FSHF+PJO )BTI+PJO

Slide 72

Slide 72 text

PostgreSQL

Slide 73

Slide 73 text

PostgreSQL ͜ͷຊ͕͘͢͝ྑ͍ ͔͠͠ͷ࿩ IUUQXXXBNB[PODPKQ಺෦ߏ଄͔ΒֶͿ1PTUHSF42-ઃܭɾӡ༻ܭըͷమଇ4PGUXBSF%FTJHOQMVTEQ

Slide 74

Slide 74 text

ෳ਺ར༻ͨ͠INDEX CREATE TABLE public.users ( id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass), name text NOT NULL, age integer NOT NULL, created timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT users_pkey PRIMARY KEY (id)); CREATE INDEX users_created_idx ON public.users USING tree (created); ——idͱcreatedͷINDEXΛར༻͢Δ SELECT * FROM users WHERE id < 100 AND created < '2016-02-27 05:41:28';

Slide 75

Slide 75 text

PostgreSQL

Slide 76

Slide 76 text

PostgreSQL

Slide 77

Slide 77 text

ࣜINDEX ——ࣜINDEXΛ࡞੒ CREATE INDEX test_idx ON users (substr(name,10,12)); ——ࣜͷ݁ՌͰINDEX͕ޮ͘ SELECT name FROM users WHERE substr(name,10,12) = '10'

Slide 78

Slide 78 text

PostgreSQL

Slide 79

Slide 79 text

PostgreSQL

Slide 80

Slide 80 text

૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age FROM users WHERE id BETWEEN 10 AND 100000)

Slide 81

Slide 81 text

PostgreSQL

Slide 82

Slide 82 text

PostgreSQL

Slide 83

Slide 83 text

PostgreSQL ΦϓςΟϚΠβ͕ αϒΫΤϦΛ+0*/ʹॻ͖׵͑ͯΔ

Slide 84

Slide 84 text

૬ؔαϒΫΤϦ ——INDEXΛར༻ͨ͠αϒΫΤϦ SELECT * FROM users WHERE id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)

Slide 85

Slide 85 text

PostgreSQL ΦϓςΟϚΠβ͕+0*/ʹʢ͈́

Slide 86

Slide 86 text

૬ؔαϒΫΤϦ ——σϞͰINDEXΛ࢖ͬͯ͘Εͳ͍ͷͰINDEXΛ༏ઌతʹར༻͢ΔΑ͏ʹࢦఆ SET ENABLE_SEQSCAN=OFF; SELECT * FROM users WHERE id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)

Slide 87

Slide 87 text

PostgreSQL

Slide 88

Slide 88 text

PostgreSQL

Slide 89

Slide 89 text

PostgreSQL 1PTUHSF42-ͷ૬ؔαϒΫΤϦ͸ ൺֱత଎͍

Slide 90

Slide 90 text

PostgreSQL ෳࡶͳूܭ͸ಘҙ ˣ Ͱ΋3%#ͷجຊ͸.Z42-ͱҰॹ

Slide 91

Slide 91 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹਖ਼نԽ ̐ɹWindowؔ਺ ̑ɹ·ͱΊ

Slide 92

Slide 92 text

ਖ਼نԽ w σʔλ͸ফͤͳ͍ w σʔλΛมߋͰ͖ͳ͍ w σʔλͷ௥ՃͰରԠ

Slide 93

Slide 93 text

ਖ਼نԽ σʔλͷෆ੔߹Ͱ೰·ͳ͍ͨΊʹ

Slide 94

Slide 94 text

ਖ਼نԽ σʔλͷෆ੔߹Ͱ೰·ͳ͍ͨΊʹ ˣ ਖ਼نԽ

Slide 95

Slide 95 text

ਖ਼نԽ ΞϯέʔτϑΥʔϜ

Slide 96

Slide 96 text

JE ໊લ ճ౴ TPOF IPHF 40/& GVHB TPVEBJ GPP UBLFUPNP CBS

Slide 97

Slide 97 text

ਖ਼نԽ ΫϥΠΞϯτʮ͜͜ʹ࢓༷͕͋Δ͡ΌΖʁʯ ʈТʈ 㱭ʮ୒Ұճ౴ʯ㱬

Slide 98

Slide 98 text

ਖ਼نԽ ΫϥΠΞϯτʮ͜͜ʹ࢓༷͕͋Δ͡ΌΖʁʯ ͜ΕΛ ʈТʈ 㲇㱭㱬㲇

Slide 99

Slide 99 text

ਖ਼نԽ ΫϥΠΞϯτʮ͜͜ʹ࢓༷͕͋Δ͡ΌΖʁʯ ʈТʈ 㱭ʮෳ਺ճ౴ʯ㱬

Slide 100

Slide 100 text

ਖ਼نԽ ରԠྫ w $47ʹͯ͠ೖΕΔ w ΧϥϜΛ૿΍ͯ͠ೖΕΔ w +40/ΛUFYUͰೖΕΔˡ/&8ʂ

Slide 101

Slide 101 text

ਖ਼نԽ ରԠྫ w $47ʹͯ͠ೖΕΔ w ΧϥϜΛ૿΍ͯ͠ೖΕΔ w +40/ΛUFYUͰೖΕΔˡ/&8ʂ

Slide 102

Slide 102 text

ਖ਼نԽ ରԠྫ w $47ʹͯ͠ೖΕΔ w ΧϥϜΛ૿΍ͯ͠ೖΕΔ w +40/ΛUFYUͰೖΕΔˡ/&8ʂ

Slide 103

Slide 103 text

ਖ਼نԽ ରԠྫ w $47ʹͯ͠ೖΕΔ w ΧϥϜΛ૿΍ͯ͠ೖΕΔ w +40/ΛUFYUͰೖΕΔˡ/&8ʂ શ෦42-Ξϯνύλʔϯʂ

Slide 104

Slide 104 text

JE OBNF TPOF 40/& TPVEBJ UBLFUPNP JE ࣭໰ ࣭໰̍ ࣭໰̎ ࣭໰̏ ࣭໰̐ ճ౴ऀ@JE ࣭໰@JE ճ౴ ճ౴೔ IPHF GVHB GPP CBS ճ౴ऀ ࣭໰ ճ౴

Slide 105

Slide 105 text

ਖ਼نԽ ਖ਼نԽ͢Δ͜ͱͰ σʔλͷߏ଄͕ڧ͘ͳΔ

Slide 106

Slide 106 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹਖ਼نԽ ̐ɹWindowؔ਺ ̑ɹ·ͱΊ

Slide 107

Slide 107 text

Windowؔ਺ ૉૣ͘ாථΛ࡞Δ

Slide 108

Slide 108 text

Windowؔ਺ ͳ͓ɺ.Z42-ʹ͸ແ͍

Slide 109

Slide 109 text

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

Slide 110

Slide 110 text

·ͣ͸೔ผʹूܭ͢Δ Windowؔ਺

Slide 111

Slide 111 text

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

Slide 112

Slide 112 text

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

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

೔ผച্ ച্೔ ɿ ɿ ɿ ɿ

Slide 115

Slide 115 text

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

Slide 116

Slide 116 text

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

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 "ूܭ"

Slide 118

Slide 118 text

Slide 119

Slide 119 text

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

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

Slide 122

Slide 122 text

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

Slide 123

Slide 123 text

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

Slide 124

Slide 124 text

ཌ೔ͷ৔߹͸ʁ Windowؔ਺

Slide 125

Slide 125 text

ཌ೔ͷ৔߹͸ʁ ˣ MFBE Λ࢖ͬͯूܭग़དྷΔ Windowؔ਺

Slide 126

Slide 126 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 127

Slide 127 text

·ͱΊ ̍ɹػೳΛ֮͑Δͱ࣮૷ָ͕ʹ ̎ɹ֦ுΛݟਾ͑ͨઃܭ͕େࣄ ̏ɹཁ݅ʹ߹Θͤͯબ୒ࢶΛબͿ Windowؔ਺

Slide 128

Slide 128 text

͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ

Slide 129

Slide 129 text

·ͱΊ wυΩϡϝϯτΛݟΔ w࣮ߦܭըΛݟΔ wਪଌΑΓ΋ܭଌ

Slide 130

Slide 130 text

·ͱΊ σʔλͷࢮ͸αʔϏεͷࢮ

Slide 131

Slide 131 text

·ͱΊ ͋Δ೔ಥવ஗͘ͳΔ

Slide 132

Slide 132 text

·ͱΊ ͋Δ೔ಥવ஗͘ͳΔ ˣ σʔλྔ͕ϝϞϦʹ৐Βͳ͘ͳΔ

Slide 133

Slide 133 text

·ͱΊ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦ ϝϞϦ Ϩίʔυ Ϩίʔυ Ϩίʔυ σʔλ૿

Slide 134

Slide 134 text

·ͱΊ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦ ϝϞϦ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦʹ৐Βͳ͍ͷͰ%JTL*0͕ൃੜ͢Δ σʔλ૿

Slide 135

Slide 135 text

࣮ફͰϋϚΔ᠘ w ूܭର৅͕େ͖͘ͳͬͯूܭ࣌ʹϝϞϦʹ৐Βͳ͍  ˠ5FNQPSBSZΛ%JTLʹు͘ͷͰॏ͘ͳΔ w */%&9͕ແ͘5"#-&εΩϟϯΛ͍ͯͨ͠  ˠςετ࣌͸σʔλ͕খ͔ͬͨ͞ͷͰ໰୊ʹؾ͔ͮͳ͔ͬͨ w ϓϩάϥϜ͕ϧʔϓͷ਺͚ͩΫΤϦΛ౤͍͛ͯͨ  ˠߦ਺͕૿͑Δͱϧʔϓճ਺͕૿͑ͯॏ͘ͳΔ w ߋ৽͕ܹ͘͠*/%&9ͷஅยԽ͕ൃੜͨ͠  ˠ61%"5&΍%&-&5&ʹΑͬͯ*/%&9͕அยԽͯ͠஗͘ͳΔ