Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PHPerに知ってほしいRDBな事

 PHPerに知ってほしいRDBな事

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

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

88f4e84b94fe07cddbd9e6479d689192?s=128

soudai sone

April 16, 2016
Tweet

More Decks by soudai sone

Other Decks in Technology

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. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ