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

PostgreSQLのSQLn本ノック

88f4e84b94fe07cddbd9e6479d689192?s=47 soudai sone
December 02, 2016

 PostgreSQLのSQLn本ノック

PGConf.Asiaでの登壇資料です

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

88f4e84b94fe07cddbd9e6479d689192?s=128

soudai sone

December 02, 2016
Tweet

More Decks by soudai sone

Other Decks in Technology

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. ·ͱΊ ɾQPTUHSFTRMKQ4MBDL νϟοτϧʔϜ  IUUQTQPTUHSFTRMIBDLFSTKQIFSPLVBQQDPN ɾNZTRMDBTVBM4MBDL νϟοτϧʔϜ  IUUQTNZTRMDBTVBMTMBDLJOIFSPLVBQQDPN

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