PHPerに知ってほしいRDBな事

PHPカンファレンス2016＠北海道の資料です

http://phpcon.sapporo-php.net/2016/

Transcript

1. PHPerʹ஌ͬͯ΄͍͠RDBͷࣄ 1)1ΧϯϑΝϨϯεˏ๺ւಓ
2. What is it? σʔλϕʔεͷण໋͸ ΞϓϦέʔγϣϯΑΓ΋௕͍
3. What is it? ͦΜͳ௕͍෇͖߹͍ʹͳΔσʔλϕʔε ͷେ੾ͳࣄΛ͓఻͑͠·͢
4. What is it? σʔλϕʔεͰ
   େ੾ͳ͜ͱ ΞϓϦέʔγϣϯͰ
   େ੾ͳ͜ͱ
5. What is it? ΞϓϦέʔγϣϯͰ
   େ੾ͳ͜ͱ σʔλϕʔεͰ
   େ੾ͳ͜ͱ ͜͜ͷ࿩Λ͠·͢
6. What is it? ର৅ͷσʔλϕʔειϑτ΢ΣΞ
7. What is it? PostgreSQL 9.5ͱMySQL 5.6(InnoDB)ʹݶΔ ଞͷRDBͷ࿩͸͠·ͤΜ
8. What is it? େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ
9. What is it? ηΩϡϦςΟͷ࿩ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ
10. What is it? ηΩϡϦςΟͷ࿩ ↓ ಙؙઌੜͰָ͓͠Έ͍ͩ͘͞ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ
11. What is it? ৽ػೳͷ࿩ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ
12. What is it? ৽ػೳͷ࿩ ↓ ֤σʔλϕʔεͷίϛϡχςΟʹࢀՃ͠Α͏ https://mysql-casual-slackin.herokuapp.com/ https://postgresql-hackers-jp.herokuapp.com/ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ
13. What is it? ӡ༻ͱઃܭͱཧ࿦ͷ࿩ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ
14. What is it? ӡ༻ͱઃܭͱཧ࿦ͷ࿩ ↓ SQLΞϯνύλʔϯͱDB࣮ફೖ໳Λಡ΋͏ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ
15. What is it?
16. What is it? PostgreSQLͱMySQLͷҧ͍ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ
17. What is it? PostgreSQLͱMySQLͷҧ͍ ↓ Ͱ΋ͪΐͬͱ͚ͩग़͖ͯ·͢ େ੾ͳ࿩͚ͩͲࠓ೔͸͠ͳ͍͜ͱ
18. What is it? ࠓ೔ͷ࿩͢Δ͜ͱ
19. What is it? RDBΛ࢖͏্Ͱ͓͍֮͑ͯͯཉ͍͜͠ͱ
20. What is it? RDBΛ࢖͏্Ͱ͓͍֮͑ͯͯཉ͍͜͠ͱ ↓ جૅతͳ͜ͱͰ͙࣮͢ફͰ͖Δ͜ͱ
21. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹਖ਼نԽ ̐ɹWindowؔ਺ ̑ɹ·ͱΊ
22. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹਖ਼نԽ ̐ɹWindowؔ਺ ̑ɹ·ͱΊ
23. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ31ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
24. What is it? d
25. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ31ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿגࣜձࣾɹΦϛΧϨʢCTOʣ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
26. ͕ͬͪ͜ࢲͰ͢
27. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹਖ਼نԽͱJOIN ̐ɹWindowؔ਺ ̑ɹ·ͱΊ
28. ݕࡧͱINDEX #5SFF
    */%&9
29. ݕࡧͱINDEX #5SFF
    */%&9 3%#ͷ*/%&9͸جຊతʹ͜Ε
    ʢ(*/΍35SFFʹ͍ͭͯ͸ࠓճ৮Εͳ͍ʣ
30. ݕࡧͱINDEX Ҿ༻ݩɿIUUQTKBXJLJQFEJBPSHXJLJ##&$"
    ΢ΟΩϖσΟΞʮ# ໦ʯ
31. ݕࡧͱINDEX w 8)&3&۟Ͱ࢖͏
    w (3061
    #:۟Ͱ΋࢖͏
    w 03%&3
    #:۟Ͱ΋࢖͏
32. ݕࡧͱINDEX w 8)&3&۟Ͱ࢖͏
    w (3061
    #:۟Ͱ΋࢖͏
    w 03%&3
    #:۟Ͱ΋࢖͏ ݕࡧશൠͰ࢖͏
33. ݕࡧͱINDEX w 8)&3&۟Ͱ࢖͏
    w (3061
    #:۟Ͱ΋࢖͏
    w 03%&3
    #:۟Ͱ΋࢖͏ ݕࡧશൠͰ࢖͏ ͔͠͠.Z42-͸ͭͷ5"#-&Ͱ
    ͔ͭ͠*/%&9Λ࢖Θͳ͍
34. ݕࡧͱINDEX */%&9ΛޮՌతʹ࢖͏ʹ͸
    w ॏෳ͕গͳ͍΄͏͕ྑ͍
    w ݁Ռ͕গͳ͍΄͏͕ྑ͍
35. ݕࡧͱINDEX */%&9ΛޮՌతʹ࢖͏ʹ͸
    w ॏෳ͕গͳ͍΄͏͕ྑ͍
    w ݁Ռ͕গͳ͍΄͏͕ྑ͍ 6OJRVF͕ཧ૝
    ॏෳ͕ଟ͍৔߹ɺ*/%&9ͷҙຯ͕ബΕΔ
    Α͋͘Δͷ͸%&-&5&@'-"(
36. ݕࡧͱINDEX */%&9ΛޮՌతʹ࢖͏ʹ͸
    w ॏෳ͕গͳ͍΄͏͕ྑ͍
    w ݁Ռ͕গͳ͍΄͏͕ྑ͍ ࢀরͷ5"#-&ͷશͯΛදࣔ͢ΔͳΒݩ͔Βશ ෦ݟͨ΄͏͕ྑ͍
    શମͷʙ·Ͱ͕*/%&9ར༻ͷ໨҆
37. ݕࡧͱINDEX */%&9Λ੍͢Δऀ͸ݕࡧΛ੍͢Δ
38. ݕࡧͱINDEX ࣮ߦܭըΛݟΔ
39. ݕࡧͱINDEX &91-"*/Ͱॏཁͳ͜ͱ
    w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ
    w ౷ܭ৘ใΛ࠷৽ʹ͢Δ
    w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ
40. ݕࡧͱINDEX &91-"*/Ͱॏཁͳ͜ͱ
    w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ
    w ౷ܭ৘ใΛ࠷৽ʹ͢Δ
    w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ σʔλྔʹΑ࣮ͬͯߦܭը͕มΘΔ͜ͱ΋ଟ͍
    ࣮ࡍΑΓগͳ͍σʔλͰ֬ೝͯ͠΋ҙຯ͕ແ͍
41. ݕࡧͱINDEX &91-"*/Ͱॏཁͳ͜ͱ
    w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ
    w ౷ܭ৘ใΛ࠷৽ʹ͢Δ
    w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ σʔλʹେྔͷ௥Ճɾ࡟আɾมߋ͕͋Δͱ౷ܭ৘ใ͕ෆਖ਼֬ʹͳΔ
    "/"-:;&
    5"#-&Ͱ౷ܭ৘ใΛߋ৽͔ͯ͠Β&91-"*/ʂʂ
42. ݕࡧͱINDEX &91-"*/Ͱॏཁͳ͜ͱ
    w ૝ఆ͢ΔϨίʔυʹ࣮σʔλΛ͋ΘͤΔ
    w ౷ܭ৘ใΛ࠷৽ʹ͢Δ
    w ࣮ߦ݁Ռ͕ਖ਼͍͜͠ͱΛ֬ೝ͢Δ ࣮ߦܭըͰߴ଎Խͯ͠΋࣮ߦ݁Ռ͕มΘΔͱҙຯ͕ແ͍
    ࠷ऴతʹຊ൪Ͱ֬ೝ͔ͯ͠ΒϦϦʔε͢Δ͜ͱ
43. ݕࡧͱINDEX &91-"*/ͷ·ͱΊ
    w ެࣜυΩϡϝϯτΛಡ΋͏ UZQFͳͲ͸छྨ͕୔ࢁ͋Δ 
 IUUQTEFWNZTRMDPNEPDSFGNBOKBFYQMBJOPVUQVUIUNM
 IUUQTXXXQPTUHSFTRMKQEPDVNFOUIUNMTRMFYQMBJOIUNM
    w ૉৼΓେࣄ
44. ݕࡧͱINDEX .Z42-
    8PSLCFODI
    QHBENJO
    
45. ࣮ߦܭը CREATE TABLE `demo`.`users` ( `id` INT NOT NULL AUTO_INCREMENT COMMENT '', `name` VARCHAR(45) NOT NULL COMMENT '', `age` INT NOT NULL COMMENT '', `created` DATETIME NOT NULL DEFAULT NOW () COMMENT '', PRIMARY KEY (`id`) COMMENT '' ); ——શ݅Λબ୒͢ΔͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users; ——PRIMARY KEYʢINDEXʣΛར༻ͨ͠ྫ SELECT * FROM demo.users WHERE id > 100; ——INDEX͕ແ͍ͷͰςʔϒϧεΩϟϯ SELECT * FROM demo.users WHERE age > 20;
46. MySQL
47. MySQL
48. MySQL αϒΫΤϦͱ૬ؔαϒΫΤϦ
49. αϒΫΤϦ —— 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
50. MySQL
51. ૬ؔαϒΫΤϦ ——஗͍૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age FROM users WHERE id BETWEEN 10 AND 100000)
52. MySQL
53. MySQL .Z42-
    ͔Β."5&3*"-*;&%ʹ มΘΓɺ+0*/ͷΑ͏ͳڍಈʹͳͬͨ
    ੲΑΓૣ͘ͳͬͨ
54. ૬ؔαϒΫΤϦ ——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)
55. MySQL
56. MySQL ૬ؔαϒΫΤϦ͸஗͍
    ৔߹͕ଟ͍
57. MySQL ૬ؔαϒΫΤϦ͸஗͍
    ˣ
    +0*/ʹॻ͖׵͑Δ
58. 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'
59. MySQL
60. MySQL +0*/͸ֻ͚ࢉ
61. MySQL +0*/͸ֻ͚ࢉ
    ˣ
    ߦ☓ߦ͸ߦ
62. MySQL .Z42-ʹ͸
    +0*/ͷΞϧΰϦζϜ͸Ұ͔ͭ͠ͳ͍
63. MySQL .Z42-ʹ͸
    +0*/ͷΞϧΰϦζϜ͸Ұ͔ͭ͠ͳ͍ ܾͯ͠ಘҙͳΘ͚Ͱ͸ແ͍
    +0*/͸ϋΠίετͳΫΤϦ
64. MySQL w ग़དྷΔ͚ͩখ͔ͯ͘͞͠Β+0*/
    w ෆཁͳ+0*/͸ආ͚Δ
    w */%&9Λར༻ͨ͠+0*/Λ͢Δ
65. MySQL γϯϓϧɾߴ଎
66. MySQL γϯϓϧɾߴ଎
    ˣ
    ೉͍͠ࣄΛ͠ͳ͍
    ઃܭྗ͕ॏཁ
67. PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ
    wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ
    w૬ؔαϒΫΤϦ΋଎͍
    w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ
68. PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ
    wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ
    w૬ؔαϒΫΤϦ΋଎͍
    w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ ͭͷΫΤϦ಺Ͱෳ਺࢖͑Δ
69. PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ
    wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ
    w૬ؔαϒΫΤϦ΋଎͍
    w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ #USFFɺ(*/ɺࣜ*/%&9ͳͲγʔϯʹ߹Θͤͯ
    ෳ਺ͷ*/%&9Λར༻Ͱ͖Δ
70. PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ
    wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ
    w૬ؔαϒΫΤϦ΋଎͍
    w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ ࢖͑Δ42-ͷߏจ΋ଟ͍
    ྫ͑͹΢Πϯυ΢ؔ਺ͳͲ
71. PostgreSQL w*/%&9͸ෳ਺ݸ࢖͑Δ
    wͦ΋ͦ΋*/%&9ͷछྨ͕୔ࢁ͋Δ
    w૬ؔαϒΫΤϦ΋଎͍
    w+0*/ͷΞϧΰϦζϜ΋ෳ਺͋Δ /FTUFE
    -PPQ
    +PJO
    4PSU
    .FSHF
    +PJO
    )BTI
    +PJO
72. PostgreSQL
73. PostgreSQL ͜ͷຊ͕͘͢͝ྑ͍
    ͔͠͠ͷ࿩
    IUUQXXXBNB[PODPKQ಺෦ߏ଄͔ΒֶͿ1PTUHSF42-ઃܭɾӡ༻ܭըͷమଇ4PGUXBSF%FTJHOQMVTEQ
    
74. ෳ਺ར༻ͨ͠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';
75. PostgreSQL
76. PostgreSQL
77. ࣜINDEX ——ࣜINDEXΛ࡞੒ CREATE INDEX test_idx ON users (substr(name,10,12)); ——ࣜͷ݁ՌͰINDEX͕ޮ͘ SELECT name FROM users WHERE substr(name,10,12) = '10'
78. PostgreSQL
79. PostgreSQL
80. ૬ؔαϒΫΤϦ SELECT * FROM users WHERE age IN (SELECT age FROM users WHERE id BETWEEN 10 AND 100000)
81. PostgreSQL
82. PostgreSQL
83. PostgreSQL ΦϓςΟϚΠβ͕
    αϒΫΤϦΛ+0*/ʹॻ͖׵͑ͯΔ
84. ૬ؔαϒΫΤϦ ——INDEXΛར༻ͨ͠αϒΫΤϦ SELECT * FROM users WHERE id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)
85. PostgreSQL ΦϓςΟϚΠβ͕+0*/ʹʢ͈́
86. ૬ؔαϒΫΤϦ ——σϞͰINDEXΛ࢖ͬͯ͘Εͳ͍ͷͰINDEXΛ༏ઌతʹར༻͢ΔΑ͏ʹࢦఆ SET ENABLE_SEQSCAN=OFF; SELECT * FROM users WHERE id IN (SELECT id FROM users WHERE id BETWEEN 10 AND 100000)
87. PostgreSQL
88. PostgreSQL
89. PostgreSQL 1PTUHSF42-ͷ૬ؔαϒΫΤϦ͸
    ൺֱత଎͍
90. PostgreSQL ෳࡶͳूܭ͸ಘҙ
    ˣ
    Ͱ΋3%#ͷجຊ͸.Z42-ͱҰॹ
91. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹਖ਼نԽ ̐ɹWindowؔ਺ ̑ɹ·ͱΊ
92. ਖ਼نԽ w σʔλ͸ফͤͳ͍
    w σʔλΛมߋͰ͖ͳ͍
    w σʔλͷ௥ՃͰରԠ
93. ਖ਼نԽ σʔλͷෆ੔߹Ͱ೰·ͳ͍ͨΊʹ
    
94. ਖ਼نԽ σʔλͷෆ੔߹Ͱ೰·ͳ͍ͨΊʹ
    ˣ
    ਖ਼نԽ
95. ਖ਼نԽ ΞϯέʔτϑΥʔϜ
96. JE ໊લ ճ౴  TPOF IPHF  40/& GVHB  TPVEBJ GPP  UBLFUPNP CBS
97. ਖ਼نԽ ΫϥΠΞϯτʮ͜͜ʹ࢓༷͕͋Δ͡ΌΖʁʯ
    
    ʈТʈ
    
    㱭ʮ୒Ұճ౴ʯ㱬
    
    
98. ਖ਼نԽ ΫϥΠΞϯτʮ͜͜ʹ࢓༷͕͋Δ͡ΌΖʁʯ
    ͜ΕΛ
    
    ʈТʈ
    
    㲇㱭㱬㲇
    
99. ਖ਼نԽ ΫϥΠΞϯτʮ͜͜ʹ࢓༷͕͋Δ͡ΌΖʁʯ
    
    ʈТʈ
    
    㱭ʮෳ਺ճ౴ʯ㱬
    
    
100. ਖ਼نԽ ରԠྫ
     w $47ʹͯ͠ೖΕΔ
     w ΧϥϜΛ૿΍ͯ͠ೖΕΔ
     w +40/ΛUFYUͰೖΕΔˡ/&8ʂ
101. ਖ਼نԽ ରԠྫ
     w $47ʹͯ͠ೖΕΔ
     w ΧϥϜΛ૿΍ͯ͠ೖΕΔ
     w +40/ΛUFYUͰೖΕΔˡ/&8ʂ
102. ਖ਼نԽ ରԠྫ
     w $47ʹͯ͠ೖΕΔ
     w ΧϥϜΛ૿΍ͯ͠ೖΕΔ
     w +40/ΛUFYUͰೖΕΔˡ/&8ʂ
103. ਖ਼نԽ ରԠྫ
     w $47ʹͯ͠ೖΕΔ
     w ΧϥϜΛ૿΍ͯ͠ೖΕΔ
     w +40/ΛUFYUͰೖΕΔˡ/&8ʂ શ෦42-Ξϯνύλʔϯʂ
104. JE OBNF
      TPOF  40/&  TPVEBJ  UBLFUPNP JE ࣭໰  ࣭໰̍  ࣭໰̎  ࣭໰̏  ࣭໰̐ ճ౴ऀ@JE ࣭໰@JE ճ౴ ճ౴೔   IPHF    GVHB    GPP    CBS  ճ౴ऀ ࣭໰ ճ౴
105. ਖ਼نԽ ਖ਼نԽ͢Δ͜ͱͰ
     σʔλͷߏ଄͕ڧ͘ͳΔ
106. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹݕࡧͱINDEX ̏ɹਖ਼نԽ ̐ɹWindowؔ਺ ̑ɹ·ͱΊ
107. Windowؔ਺ ૉૣ͘ாථΛ࡞Δ
108. Windowؔ਺ ͳ͓ɺ.Z42-ʹ͸ແ͍
109. ঎඼໊ ച্ ച্೔ ϓϩάϥϚͷͨΊͷ42-   ಺෦ߏ଄͔ΒֶͿ   ιϑτ΢ΣΞσβΠϯ   42-࣮ફೖ໳   ཧ࿦͔ΒֶͿσʔλϕʔε   42-ٯҾ͖େશͷۃҙ   ϓϩάϥϚͷͨΊͷ42-   42-࣮ફೖ໳   ཧ࿦͔ΒֶͿσʔλϕʔε   42-࣮ફೖ໳   Ϧʔμϒϧίʔυ   Ϧʔμϒϧίʔυ   σʔλϕʔεɾϦϑΝΫλ   ୡਓʹֶͿ%#ઃܭ   ɿ
     ɿ ɿ
     ɿ ɿ
     ɿ ˞࣮຿Ͱ͸ฤ͸ਖ਼نԽ͢Δ΂͖
110. ·ͣ͸೔ผʹूܭ͢Δ Windowؔ਺
111. ࣮ࡍͷSQL SELECT to_char( sum(“ച্"),'FM999,999,999' ) AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY “ച্೔" ORDER BY "ച্೔"
112. ࣮ࡍͷSQL SELECT to_char( sum(“ച্"),'FM999,999,999' ) AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY “ച্೔" ORDER BY "ച্೔"
113. ࣮ࡍͷSQL SELECT to_char( sum(“ച্"),'FM999,999,999' ) AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY “ച্೔" ORDER BY "ച্೔" ܻ۠੾ΓͰΧϯϚΛೖΕΔ
114. ೔ผച্ ച্೔                                                         ɿ
     ɿ ɿ
     ɿ
115. લ೔ͱͷࠩ෼Λग़͍ͨ͠ Windowؔ਺
116. લ೔ͱͷࠩ෼Λग़͍ͨ͠
     ˣ
     MBH Λ࢖ͬͯूܭ͢Δ Windowؔ਺
117. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ"
118. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ"
119. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ
120. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" 8JOEPXؔ਺ͷର৅Λࢦఆ ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ
121. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" 8JOEPXؔ਺ͷର৅Λࢦఆ ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ લߦͷऔಘ͢ΔΧϥϜΛࢦఆ
122. ೔ผച্ ച্೔ લ೔ച্     /6--                                                                                            ɿ
     ɿ ɿ
     ɿ ɿ
     ɿ
123. ೔ผച্ ച্೔ લ೔ച্     /6--                                                                                            ɿ
     ɿ ɿ
     ɿ ɿ
     ɿ લߦ͕ແ͍ͷͰ/6--
124. ཌ೔ͷ৔߹͸ʁ
     Windowؔ਺
125. ཌ೔ͷ৔߹͸ʁ
     ˣ
     MFBE Λ࢖ͬͯूܭग़དྷΔ Windowؔ਺
126. ؔ਺ આ໌ 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
     / /൪໨ͷ஋
     ͔Β਺͑Δ
127. ·ͱΊ ̍ɹػೳΛ֮͑Δͱ࣮૷ָ͕ʹ
     ̎ɹ֦ுΛݟਾ͑ͨઃܭ͕େࣄ
     ̏ɹཁ݅ʹ߹Θͤͯબ୒ࢶΛબͿ Windowؔ਺
128. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹMySQL ̏ɹPostgreSQL ̐ɹ࣮ફͰϋϚΔ᠘ ̑ɹ·ͱΊ
129. ·ͱΊ wυΩϡϝϯτΛݟΔ
     w࣮ߦܭըΛݟΔ
     wਪଌΑΓ΋ܭଌ
130. ·ͱΊ σʔλͷࢮ͸αʔϏεͷࢮ
131. ·ͱΊ ͋Δ೔ಥવ஗͘ͳΔ
132. ·ͱΊ ͋Δ೔ಥવ஗͘ͳΔ
     ˣ
     σʔλྔ͕ϝϞϦʹ৐Βͳ͘ͳΔ
133. ·ͱΊ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦ ϝϞϦ Ϩίʔυ Ϩίʔυ Ϩίʔυ σʔλ૿
134. ·ͱΊ Ϩίʔυ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦ ϝϞϦ Ϩίʔυ Ϩίʔυ Ϩίʔυ ϝϞϦʹ৐Βͳ͍ͷͰ%JTL
     *0͕ൃੜ͢Δ σʔλ૿
135. ࣮ફͰϋϚΔ᠘ w ूܭର৅͕େ͖͘ͳͬͯूܭ࣌ʹϝϞϦʹ৐Βͳ͍
 ˠ5FNQPSBSZΛ%JTLʹు͘ͷͰॏ͘ͳΔ
     w */%&9͕ແ͘5"#-&εΩϟϯΛ͍ͯͨ͠
 ˠςετ࣌͸σʔλ͕খ͔ͬͨ͞ͷͰ໰୊ʹؾ͔ͮͳ͔ͬͨ
     w ϓϩάϥϜ͕ϧʔϓͷ਺͚ͩΫΤϦΛ౤͍͛ͯͨ
 ˠߦ਺͕૿͑Δͱϧʔϓճ਺͕૿͑ͯॏ͘ͳΔ
     w ߋ৽͕ܹ͘͠*/%&9ͷஅยԽ͕ൃੜͨ͠
 ˠ61%"5&΍%&-&5&ʹΑͬͯ*/%&9͕அยԽͯ͠஗͘ͳΔ
136. ࣮ફͰϋϚΔ᠘ w ूܭର৅͕େ͖͘ͳͬͯूܭ࣌ʹϝϞϦʹ৐Βͳ͍
 ˠ5FNQPSBSZΛ%JTLʹు͘ͷͰॏ͘ͳΔ
     w */%&9͕ແ͘5"#-&εΩϟϯΛ͍ͯͨ͠
 ˠςετ࣌͸σʔλ͕খ͔ͬͨ͞ͷͰ໰୊ʹؾ͔ͮͳ͔ͬͨ
     w ϓϩάϥϜ͕ϧʔϓͷ਺͚ͩΫΤϦΛ౤͍͛ͯͨ
 ˠߦ਺͕૿͑Δͱϧʔϓճ਺͕૿͑ͯॏ͘ͳΔ
     w ߋ৽͕ܹ͘͠*/%&9ͷஅยԽ͕ൃੜͨ͠
 ˠ61%"5&΍%&-&5&ʹΑͬͯ*/%&9͕அยԽͯ͠஗͘ͳΔ ࣮ߦܭըΛ
     ݟΕΔΑ͏ʹͳΖ͏
137. ·ͱΊ %#ͷ໰୊͸๨Εͨࠒʹ΍ͬͯ͘Δ
138. ·ͱΊ 3%#ͷ஌ࣝ͸ण໋͕௕͍
139. ·ͱΊ 3%#ͷ஌ࣝ͸ण໋͕௕͍
     ˣ
     ֮͑Ε͹࢓ࣄͰ௕͍ؒ໾ʹཱͭ
140. ·ͱΊ %#ͷ໰୊͸ΫϦςΟΧϧ
     
141. ·ͱΊ %#ͷ໰୊͸ΫϦςΟΧϧ
     ˣ
     ͭ·Γղܾ͢Ε͹ӳ༤
142. ·ͱΊ ۪ऀ͸ܦݧʹֶͿ
     ݡऀ͸աڈʹֶͿ
143. ·ͱΊ 3%#͸ྺ࢙͕௕͍
     
144. ·ͱΊ 3%#͸ྺ࢙͕௕͍
     ˣ
     ৭Μͳܦݧஊ͔Βֶ΂Δ
145. ·ͱΊ 3%#͸͍͍ͧɻ
146. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ