PostgreSQLのSQLｎ本ノック

PGConf.Asiaでの登壇資料です

http://www.pgconf.asia/JP/

Transcript

1. SQL nຊϊοΫ 1($POG"TJB
   
   νϡʔτϦΞϧ࿮
2. What is it? ࠓ೔Έͳ͞Μʹ࣋ͬͯؼͬͯ΄͍͜͠ͱ
3. What is it? ࠓ೔͔Β࢖͑ΔSQLͷςΫχοΫ
4. What is it? ૝ఆडߨऀ
5. What is it? ૝ఆडߨऀ ↓ SELECTɾINSERTɾUPDATEɾDELETE Λॻ͍ͨ͜ͱ͕͋ΔʢԿͱͳ͘Θ͔Δ
6. What is it? ͦΜͳSQLͷϫϯϥϯΫΞοϓʂʂ
7. What is it? ର৅ͷσʔλϕʔειϑτ΢ΣΞ
8. What is it? PostgreSQL 9.6 ଞͷRDBͷ࿩͸͠·ͤΜ
9. What is it? ࠓ೔ͷσϞͰ࢖͏αϯϓϧ Githubʹ͓͍ͯ·͢
10. What is it? ޕલதʹडߨͨ͠ਓ͸ खݩͰࢼ͠ͳ͕Β
11. What is it? ޕલத͸डߨͯ͠ͳ͍ਓ͸ ͋ͱ͔Β෮श͢ΔલఏͰ
12. What is it? ͊͞ϫϯϥϯΫΞοϓ͠·͠ΐ͏ʂʂ
13. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ
14. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ
15. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿWebΤϯδχΞ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
16. ࣗݾ঺հ ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ ৬ۀɿWebΤϯδχΞ ॴଐɿ೔ຊPostgreSQLϢʔβձ ɹɹɹதࠃࢧ෦ ࢧ෦௕ ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
17. தࠃ஍ํDBษڧձ https://dbstudychugoku.github.io/
18. தࠃ஍ํDBษڧձ https://dbstudychugoku.github.io/ աڈͷࢿྉ͕߇͑Ίʹݴͬͯ΋
    ࠷ߴͰ࠷ڧͳͷͰੋඇνΣοΫʂ
19. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ
20. SQLͷجຊతͳߏจ 42-ͷجຊ͸4&-&$5
21. SELECT SELECT * FROM table_name WHERE id = 1
22. SELECT SELECT * FROM table_name WHERE id = 1 OR id = 2
23. SELECT SELECT * FROM table_name WHERE id = 1 OR id = 2 JE͕ͱͷσʔλ͕ݕࡧ͞ΕΔ
24. SELECT SELECT * FROM table_name WHERE id IN (1, 2)
25. SELECT SELECT * FROM table_name WHERE id IN (1, 2) JE͕ͱͷσʔλ͕ݕࡧ͞ΕΔ
26. SQLͷجຊతͳߏจ 03͸*/۟ͱ౳Ձ
27. SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛ࠶ར༻͢Δ
28. SELECT SELECT * FROM table_name WHERE id IN ( SELECT id FROM table_name) )
29. SELECT SELECT * FROM table_name WHERE id IN ( SELECT id FROM table_name) ) αϒΫΤϦͷݕࡧ݁ՌΛར༻ͯ͠8)&3&͢Δ
30. SELECT SELECT * FROM table_name WHERE id IN ( SELECT unnest(ARRAY[1,2])) )
31. SELECT SELECT * FROM table_name WHERE id IN ( SELECT unnest(ARRAY[1,2])) ) "33":͕ٖࣅςʔϒϧ
32. SELECT SELECT * FROM table_name WHERE id IN ( SELECT unnest(ARRAY[1,2])) ) "33":͕ٖࣅςʔϒϧ JE͕ͱͷσʔλ͕ݕࡧ͞ΕΔ
33. VTFS@JE OBNF BSFB@JE  TPOF   TPVEBJ   UBLFUPNP   TPVEBJ  BSFB@JE BSFB@OBNF  ޿ౡ  Ԭࢁ  ౦ژ  େࡕ
34. VTFS@JE OBNF BSFB@JE  TPOF   TPVEBJ   UBLFUPNP   TPVEBJ  BSFB@JE BSFB@OBNF  ޿ౡ  Ԭࢁ  ౦ژ  େࡕ
35. VTFS@JE OBNF BSFB@JE  TPOF   TPVEBJ   UBLFUPNP   TPVEBJ  BSFB@JE BSFB@OBNF  ޿ౡ  Ԭࢁ  ౦ژ  େࡕ ·ͣݕࡧͷݩΛߜΓࠐΉ
36. VTFS@JE OBNF BSFB@JE  TPOF   TPVEBJ   UBLFUPNP   TPVEBJ  BSFB@JE BSFB@OBNF  ޿ౡ  Ԭࢁ  ౦ژ  େࡕ ݕࡧͨ݁͠ՌͰ*/۟ʹ౤͛Δ
    ͕͜͜ઌఔͷαϒΫΤϦ෦෼
37. VTFS@JE OBNF BSFB@JE  TPOF   TPVEBJ   UBLFUPNP   TPVEBJ  BSFB@JE BSFB@OBNF  ޿ౡ  Ԭࢁ  ౦ژ  େࡕ
38. VTFS@JE OBNF BSFB@JE  TPOF   TPVEBJ   UBLFUPNP   TPVEBJ  BSFB@JE BSFB@OBNF  ޿ౡ  Ԭࢁ  ౦ژ  େࡕ ݕࡧ݁Ռ
39. SELECT SELECT * FROM users WHERE area_id IN ( SELECT area_id FROM area WHERE name = '޿ౡ' )
40. SELECT SELECT * FROM users WHERE area_id IN ( SELECT area_id FROM area WHERE name = '޿ౡ' )
41. SQLͷجຊతͳߏจ 8)&3&۟͸ߋ৽΍࡟আͰ΋͋Δ
42. SQLͷجຊతͳߏจ 8)&3&۟͸ߋ৽΍࡟আͰ΋͋Δ
    ˣ
    αϒΫΤϦͱ*/۟͸Ԡ༻Ͱ͖Δ
43. UPDATE UPDATE table_name SET column_name = value, column_name = value,... WHERE ৚݅
44. UPDATE UPDATE table_name SET column_name = value, column_name = value,... WHERE ৚݅ 4&-&$5ͱಉ͡
45. DELETE DELETE FROM users WHERE area_id IN ( SELECT area_id FROM area WHERE name = '޿ౡ' )
46. DELETE DELETE FROM users WHERE area_id IN ( SELECT area_id FROM area WHERE name = '޿ౡ' ) 4&-&$5ͷ݁Ռͱಉ͡ର৅Λ࡟আ͢Δ
47. SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛ࠶ར༻͢Δ
    ͦͷ̎
48. VTFS@JE OBNF CJSUIEBZ BHF  ૖େ    ኽָ r   ᗅָ r   ࡣָ r 
49. VTFS@JE OBNF CJSUIEBZ BHF  ૖େ    ኽָ r   ᗅָ r   ࡣָ r  ஀ੜ೔͕དྷͨΒߋ৽͕ඞཁ
50. 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
51. 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
52. 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 ஀ੜ೔͔Βݱࡏͷ೥ྸΛܭࢉ
53. 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
54. 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&ͰϚονϯά
55. VTFS@JE BHF   VTFS@JE OBNF CJSUIEBZ BHF  ૖େ    ኽָ r   ᗅָ r   ࡣָ r 
56. VTFS@JE BHF   VTFS@JE OBNF CJSUIEBZ BHF  ૖େ    ኽָ r   ᗅָ r   ࡣָ r  ஀ੜ೔͔Β೥ྸΛࢉग़
57. VTFS@JE BHF   VTFS@JE OBNF CJSUIEBZ BHF  ૖େ    ኽָ r   ᗅָ r   ࡣָ r 
58. VTFS@JE BHF   VTFS@JE OBNF CJSUIEBZ BHF  ૖େ    ኽָ r   ᗅָ r   ࡣָ r  ೥ྸͰ61%"5& ߋ৽ͷର৅Λ8)&3&ͰϚονϯά
59. SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛ࠶ར༻͢Δ
    ͦͷ
60. INSERT INSERT INTO table_name (column_name,column_name…) VALUES (value,value…) , (value,value…) , (value,value…) , (value,value…)
61. INSERT INSERT INTO table_name (column_name,column_name…) VALUES (value,value…) , (value,value…) , (value,value…) , (value,value…)
62. INSERT INSERT INTO table_name (column_name,column_name…) SELECT column_name , column_name… FROM table_name WHERE …
63. SQLͷجຊతͳߏจ ച্ϩά͔Βച্ूܭΛग़͢
64. ച্೔࣌ ച্ֹۚ 
      
      
      
      ộ ộ ച্೔ ച্ֹۚ         ộ ộ
65. ച্೔࣌ ച্ֹۚ 
      
      
      
      ộ ộ ച্೔ ച্ֹۚ         ộ ộ
66. ച্೔࣌ ച্ֹۚ 
      
      
      
      ộ ộ ച্೔ ച্ֹۚ         ộ ộ
67. ച্೔࣌ ച্ֹۚ 
      
      
      
      ộ ộ ച্೔ ച্ֹۚ         ộ ộ ೔෇͝ͱʹूܭ͢Δඞཁ͕͋Δ
68. ച্೔࣌ ച্ֹۚ 
      
      
      
      ộ ộ ച্೔ ച্ֹۚ         ộ ộ ूܭ݁ՌΛผUBCMFʹ*/4&35
69. 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 ച্೔
70. 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 ച্೔
71. 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ͷ݄ॳ͔Β݄຤ͷσʔλΛநग़
72. 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 ച্೔ ೔࣌Λ೔෇ʹม׵
73. 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 ച্೔ ೔෇ʹม׵ͨ͠ച্೔ͰάϧʔϓԽ
74. 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
75. SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛར༻͢Δ
76. SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛར༻͢Δ
    ˣ
    ෳ਺ճͷ42-͕ҰճͰ࣮ߦग़དྷΔ
77. SQLͷجຊతͳߏจ 4&-&$5ͷ݁ՌΛར༻͢Δ
    ˣ
    ෳ਺ճͷ42-͕ҰճͰ࣮ߦग़དྷΔ ύϑΥʔϚϯε্͕͕Δʂ
78. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ
79. CASEΛ࢖͍͜ͳͦ͏ 42-ͷۤखͳࣄ
80. CASEΛ࢖͍͜ͳͦ͏ 42-ͷۤखͳࣄ
    ˣ
    ੍ޚߏจͱϧʔϓ
81. CASEΛ࢖͍͜ͳͦ͏ ͔͠͠$"4&Λ࢖͏ࣄͰ
    ଟ͘ͷ୅ସҊʹͳΔ
82. 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)
83. OBNF BSFB@HSPVQ TPOF தࠃ஍ํ TPVEBJ தࠃ஍ํ UBLFUPNP தࠃ஍ํ TPVEBJ தࠃ஍ํ IPHF ͦͷଞ GVHB ؔ੢஍ํ GPP ؔ੢஍ํ
84. 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)
85. CASEΛ࢖͍͜ͳͦ͏ $"4&͸ࣜ
    
86. CASEΛ࢖͍͜ͳͦ͏ $"4&͸ࣜ
    ˣ
    Ͳ͜ʹͰ΋ॻ͚Δ
87. 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
88. OBNF BSFB BSFB@HSPVQ TPVEBJ ޿ౡ தࠃ஍ํ TPOF ޿ౡ தࠃ஍ํ UBLFUPNP Ԭࢁ தࠃ஍ํ TPVEBJ Ԭࢁ தࠃ஍ํ IPHF ౦ژ ͦͷଞ GPP େࡕ ؔ੢஍ํ GVHB େࡕ ؔ੢஍ํ
89. 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 ԬࢁΛ্ʹ͍ͨ࣌͠
90. OBNF BSFB BSFB@HSPVQ TPVEBJ Ԭࢁ தࠃ஍ํ TPOF Ԭࢁ தࠃ஍ํ UBLFUPNP ޿ౡ தࠃ஍ํ TPVEBJ ޿ౡ தࠃ஍ํ IPHF ౦ژ ͦͷଞ GPP େࡕ ؔ੢஍ํ GVHB େࡕ ؔ੢஍ํ
91. 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
92. CASEͷجຊతͳߏจ / ໰୊
93. CASEͷجຊతͳߏจ ϧʔϓͷ਺͚ͩRVFSZ౤͛Δ
94. 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();
95. 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&ͳΒճ
96. CASEͷجຊతͳߏจ ճͷ61%"5&จʹม͑Δ
97. CASEͷجຊతͳߏจ UPDATE users SET pref = CASE pref WHEN ‘Ԭࢁ’ THEN ‘େ౎ձ’ WHEN ‘޿ౡ’ THEN ‘෱ࢁ’ ELSE pref END WHERE pref IN (‘Ԭࢁ’,’޿ౡ’)
98. CASEͷجຊతͳߏจ $"4&͸ࣜͳͷͰ
    ৭Μͳͱ͜ΖͰ࢖͑Δ
99. CASEͷجຊతͳߏจ Ԡ༻ฤ
    ͭͷ%#Λൺֱͯ͠61%"5&
100. CASEͷجຊతͳߏจ JE OBNF BHF  TPOF   UBLFUPNP   TPVEBJ  VTFS@JE CJSUIEBZ QSFG   ޿ౡ   ෱ࢁ   େ౎ձ VTFST VTFS@QSPQFSUJFT 
101. 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
102. CASEͷجຊతͳߏจ JE OBNF BHF  TPOF   UBLFUPNP ෆৄ  TPVEBJ  VTFS@JE CJSUIEBZ QSFG   ޿ౡ   ෱ࢁ   େ౎ձ
103. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ
104. ૉૣ͘ாථΛ࡞Δ PostgreSQLͷؔ਺
105. 8JOEPXؔ਺ PostgreSQLͷؔ਺
106. ঎඼໊ ച্ ച্೔ ϓϩάϥϚͷͨΊͷ42-   ಺෦ߏ଄͔ΒֶͿ   ιϑτ΢ΣΞσβΠϯ   42-࣮ફೖ໳   ཧ࿦͔ΒֶͿσʔλϕʔε   42-ٯҾ͖େશͷۃҙ   ϓϩάϥϚͷͨΊͷ42-   42-࣮ફೖ໳   ཧ࿦͔ΒֶͿσʔλϕʔε   42-࣮ફೖ໳   Ϧʔμϒϧίʔυ   Ϧʔμϒϧίʔυ   σʔλϕʔεɾϦϑΝΫλ   ୡਓʹֶͿ%#ઃܭ   ɿ
     ɿ ɿ
     ɿ ɿ
     ɿ ˞࣮຿Ͱ͸ฤ͸ਖ਼نԽ͢Δ΂͖
107. Windowؔ਺ ·ͣ͸೔ผʹूܭ͢Δ
108. ࣮ࡍͷSQL SELECT to_char( sum(“ച্"),'FM999,999,999' ) AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY “ച্೔" ORDER BY "ച্೔"
109. ࣮ࡍͷSQL SELECT to_char( sum(“ച্"),'FM999,999,999' ) AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY “ച্೔" ORDER BY "ച্೔"
110. ࣮ࡍͷSQL SELECT to_char( sum(“ച্"),'FM999,999,999' ) AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY “ച্೔" ORDER BY "ച্೔" ܻ۠੾ΓͰΧϯϚΛೖΕΔ
111. ೔ผച্ ച্೔                                                         ɿ
     ɿ ɿ
     ɿ
112. Windowؔ਺ લ೔ͱͷࠩ෼Λग़͍ͨ͠
113. Windowؔ਺ લ೔ͱͷࠩ෼Λग़͍ͨ͠
     ˣ
     MBH Λ࢖ͬͯूܭ͢Δ
114. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ"
115. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ"
116. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ
117. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" 8JOEPXؔ਺ͷର৅Λࢦఆ ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ
118. ࣮ࡍͷSQL SELECT * , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্ FROM (SELECT to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্" , "ച্೔" FROM "ച্ද" GROUP BY "ച্೔") AS "ूܭ" 8JOEPXؔ਺ͷର৅Λࢦఆ ઌ΄ͲͷΫΤϦΛαϒΫΤϦʹ͢Δ લߦͷऔಘ͢ΔΧϥϜΛࢦఆ
119. ೔ผച্ ച্೔ લ೔ച্     /6--                                                                                            ɿ
     ɿ ɿ
     ɿ ɿ
     ɿ
120. ೔ผച্ ച্೔ લ೔ച্     /6--                                                                                            ɿ
     ɿ ɿ
     ɿ ɿ
     ɿ લߦ͕ແ͍ͷͰ/6--
121. Windowؔ਺ ཌ೔ͷ৔߹͸ʁ
     
122. Windowؔ਺ ཌ೔ͷ৔߹͸ʁ
     ˣ
     MFBE Λ࢖ͬͯूܭग़དྷΔ
123. ؔ਺ આ໌ 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
     / /൪໨ͷ஋
     ͔Β਺͑Δ
124. Windowؔ਺ ศརͳྫ
     w ݄ผͷாථ࡞੒
     w ϥϯΩϯάදࣔ
     w άϧʔϓผͷूܭ
125. ศརͳܕΛར༻͢Δ 1PTUHSF42-ͷػೳΛ׆༻͢Δ
126. ศརͳܕΛར༻͢Δ 1PTUHSF42-͸+40/͕ಘҙ
     
127. ศརͳܕΛར༻͢Δ 1PTUHSF42-͸+40/͕ಘҙ
     ˣ
     +40/ܕɾ+40/#ܕ͕͋Δ
128. ศརͳܕΛར༻͢Δ 1PTUHSF42-͸ൣғΛѻ͏ͷ͕ಘҙ
     
129. ศརͳܕΛར༻͢Δ 1PTUHSF42-͸ൣғΛѻ͏ͷ͕ಘҙ
     ˣ
     ൣғܕΛ࢖͑͹ΧϨϯμʔ΋؆୯
130. λεΫ໊ ৄࡉ ରԠظؒ Πϯετʔϧ \ॴଐ
     Ξγετ
     ߨࢣ
     تా^ <  جຊฤ \ॴଐ
     Ξγετ
     ߨࢣ
     تా^ <  42-Oຊ \ॴଐ
     ΦϛΧϨ
     ߨࢣ
     ીࠜ^ <  σʔλ෼ੳ \ॴଐ
     Έ͔͔
     ߨࢣ
     େࢁ^ < 
131. ࣮ࡍͷSQL SELECT * FROM "λεΫҰཡ" WHERE "ৄࡉ" ->> 'ߨࢣ' = 'تా'
132. λεΫ໊ ৄࡉ ରԠظؒ Πϯετʔϧ \ॴଐ
     Ξγετ
     ߨࢣ
     تా^ <  جຊฤ \ॴଐ
     Ξγετ
     ߨࢣ
     تా^ <  42-Oຊ \ॴଐ
     ΦϛΧϨ
     ߨࢣ
     ીࠜ^ <  σʔλ෼ੳ \ॴଐ
     Έ͔͔
     ߨࢣ
     େࢁ^ < 
133. ࣮ࡍͷSQL SELECT * FROM "λεΫҰཡ" WHERE '2016-12-01'::DATE <@ "ରԠظؒ"
134. λεΫ໊ ৄࡉ ରԠظؒ Πϯετʔϧ \ॴଐ
     Ξγετ
     ߨࢣ
     تా^ <  جຊฤ \ॴଐ
     Ξγετ
     ߨࢣ
     تా^ <  42-Oຊ \ॴଐ
     ΦϛΧϨ
     ߨࢣ
     ીࠜ^ <  σʔλ෼ੳ \ॴଐ
     Έ͔͔
     ߨࢣ
     େࢁ^ < 
135. ࣮ࡍͷSQL SELECT * FROM "λεΫҰཡ" WHERE daterange( '2016-12-03', '2016-12-04', ‘[]' ) <@ "ରԠظؒ";
136. λεΫ໊ ৄࡉ ରԠظؒ Πϯετʔϧ \ॴଐ
     Ξγετ
     ߨࢣ
     تా^ <  جຊฤ \ॴଐ
     Ξγετ
     ߨࢣ
     تా^ <  42-Oຊ \ॴଐ
     ΦϛΧϨ
     ߨࢣ
     ીࠜ^ <  σʔλ෼ੳ \ॴଐ
     Έ͔͔
     ߨࢣ
     େࢁ^ < 
137. ͋͐͡Μͩ ̍ɹࣗݾ঺հ ̎ɹSQLͷجຊతͳߏจͱԠ༻ ̏ɹCASEΛ࢖͍͜ͳͦ͏ ̐ɹPostgreSQLͷؔ਺ ̑ɹ·ͱΊ
138. ·ͱΊ 42-Λ֮͑Δͱදݱྗ্͕͕Δ
139. ·ͱΊ ૉૣ͘දݱग़དྷΔ͜ͱͷՁ஋
140. ·ͱΊ දݱྗ্͕͕Ε͹ઃܭྗ্͕͕Δ
141. ·ͱΊ
142. ·ͱΊ ɾQPTUHSFTRMKQ
     4MBDL νϟοτϧʔϜ
     IUUQTQPTUHSFTRMIBDLFSTKQIFSPLVBQQDPN
     ɾNZTRMDBTVBM
     4MBDL νϟοτϧʔϜ
     IUUQTNZTRMDBTVBMTMBDLJOIFSPLVBQQDPN
     
143. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ