PostgreSQLのSQLｎ本ノック

PGConf.Asiaでの登壇資料です

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

Transcript

1. SQL nຊϊοΫ
   1($POG"TJB
   νϡʔτϦΞϧ࿮
   

   View Slide

2. What is it?
   ࠓ೔Έͳ͞Μʹ࣋ͬͯؼͬͯ΄͍͜͠ͱ
   

   View Slide

3. What is it?
   ࠓ೔͔Β࢖͑ΔSQLͷςΫχοΫ
   

   View Slide

4. What is it?
   ૝ఆडߨऀ
   

   View Slide

5. What is it?
   ૝ఆडߨऀ
   ↓
   SELECTɾINSERTɾUPDATEɾDELETE
   Λॻ͍ͨ͜ͱ͕͋ΔʢԿͱͳ͘Θ͔Δ
   

   View Slide

6. What is it?
   ͦΜͳSQLͷϫϯϥϯΫΞοϓʂʂ
   

   View Slide

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

   View Slide

8. What is it?
   PostgreSQL 9.6
   ଞͷRDBͷ࿩͸͠·ͤΜ
   

   View Slide

9. What is it?
   ࠓ೔ͷσϞͰ࢖͏αϯϓϧ
   Githubʹ͓͍ͯ·͢
   

   View Slide

10. What is it?
    ޕલதʹडߨͨ͠ਓ͸
    खݩͰࢼ͠ͳ͕Β
    

    View Slide

11. What is it?
    ޕલத͸डߨͯ͠ͳ͍ਓ͸
    ͋ͱ͔Β෮श͢ΔલఏͰ
    

    View Slide

12. What is it?
    ͊͞ϫϯϥϯΫΞοϓ͠·͠ΐ͏ʂʂ
    

    View Slide

13. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹSQLͷجຊతͳߏจͱԠ༻
    ̏ɹCASEΛ࢖͍͜ͳͦ͏
    ̐ɹPostgreSQLͷؔ਺
    ̑ɹ·ͱΊ
    

    View Slide

14. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹSQLͷجຊతͳߏจͱԠ༻
    ̏ɹCASEΛ࢖͍͜ͳͦ͏
    ̐ɹPostgreSQLͷؔ਺
    ̑ɹ·ͱΊ
    

    View Slide

15. ࣗݾ঺հ
    ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ
    ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ
    ৬ۀɿWebΤϯδχΞ
    ॴଐɿ೔ຊPostgreSQLϢʔβձ
    ɹɹɹதࠃࢧ෦ ࢧ෦௕
    ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
    

    View Slide

16. ࣗݾ঺հ
    ໊લɿીࠜɹ૖େʢͦͶɹ͚ͨͱ΋ʣ
    ೥ྸɿ32ࡀʢࡾਓͷࢠڙ͕͍·͢ʣ
    ৬ۀɿWebΤϯδχΞ
    ॴଐɿ೔ຊPostgreSQLϢʔβձ
    ɹɹɹதࠃࢧ෦ ࢧ෦௕
    ɹɹٕज़తʹ͸LLܥݴޠͱ͔RDB͕޷͖Ͱ͢
    

    View Slide

17. தࠃ஍ํDBษڧձ
    https://dbstudychugoku.github.io/
    

    View Slide

18. தࠃ஍ํDBษڧձ
    https://dbstudychugoku.github.io/
    աڈͷࢿྉ͕߇͑Ίʹݴͬͯ΋
    ࠷ߴͰ࠷ڧͳͷͰੋඇνΣοΫʂ
    

    View Slide

19. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹSQLͷجຊతͳߏจͱԠ༻
    ̏ɹCASEΛ࢖͍͜ͳͦ͏
    ̐ɹPostgreSQLͷؔ਺
    ̑ɹ·ͱΊ
    

    View Slide

20. SQLͷجຊతͳߏจ
    42-ͷجຊ͸4&-&$5
    

    View Slide

21. SELECT
    SELECT
    *
    FROM
    table_name
    WHERE
    id = 1
    

    View Slide

22. SELECT
    SELECT
    *
    FROM
    table_name
    WHERE
    id = 1
    OR
    id = 2
    

    View Slide

23. SELECT
    SELECT
    *
    FROM
    table_name
    WHERE
    id = 1
    OR
    id = 2
    JE͕ͱͷσʔλ͕ݕࡧ͞ΕΔ
    

    View Slide

24. SELECT
    SELECT
    *
    FROM
    table_name
    WHERE
    id IN (1, 2)
    

    View Slide

25. SELECT
    SELECT
    *
    FROM
    table_name
    WHERE
    id IN (1, 2)
    JE͕ͱͷσʔλ͕ݕࡧ͞ΕΔ
    

    View Slide

26. SQLͷجຊతͳߏจ
    03͸*/۟ͱ౳Ձ
    

    View Slide

27. SQLͷجຊతͳߏจ
    4&-&$5ͷ݁ՌΛ࠶ར༻͢Δ
    

    View Slide

28. SELECT
    SELECT
    *
    FROM
    table_name
    WHERE
    id IN (
    SELECT id FROM table_name)
    )
    

    View Slide

29. SELECT
    SELECT
    *
    FROM
    table_name
    WHERE
    id IN (
    SELECT id FROM table_name)
    )
    αϒΫΤϦͷݕࡧ݁ՌΛར༻ͯ͠8)&3&͢Δ
    

    View Slide

30. SELECT
    SELECT
    *
    FROM
    table_name
    WHERE
    id IN (
    SELECT unnest(ARRAY[1,2]))
    )
    

    View Slide

31. SELECT
    SELECT
    *
    FROM
    table_name
    WHERE
    id IN (
    SELECT unnest(ARRAY[1,2]))
    )
    "33":͕ٖࣅςʔϒϧ
    

    View Slide

32. SELECT
    SELECT
    *
    FROM
    table_name
    WHERE
    id IN (
    SELECT unnest(ARRAY[1,2]))
    )
    "33":͕ٖࣅςʔϒϧ
    JE͕ͱͷσʔλ͕ݕࡧ͞ΕΔ
    

    View Slide

33. VTFS@JE OBNF BSFB@JE
    TPOF
    TPVEBJ
    UBLFUPNP
    TPVEBJ
    BSFB@JE BSFB@OBNF
    ޿ౡ
    Ԭࢁ
    ౦ژ
    େࡕ
    

    View Slide

34. VTFS@JE OBNF BSFB@JE
    TPOF
    TPVEBJ
    UBLFUPNP
    TPVEBJ
    BSFB@JE BSFB@OBNF
    ޿ౡ
    Ԭࢁ
    ౦ژ
    େࡕ
    

    View Slide

35. VTFS@JE OBNF BSFB@JE
    TPOF
    TPVEBJ
    UBLFUPNP
    TPVEBJ
    BSFB@JE BSFB@OBNF
    ޿ౡ
    Ԭࢁ
    ౦ژ
    େࡕ
    ·ͣݕࡧͷݩΛߜΓࠐΉ
    

    View Slide

36. VTFS@JE OBNF BSFB@JE
    TPOF
    TPVEBJ
    UBLFUPNP
    TPVEBJ
    BSFB@JE BSFB@OBNF
    ޿ౡ
    Ԭࢁ
    ౦ژ
    େࡕ
    ݕࡧͨ݁͠ՌͰ*/۟ʹ౤͛Δ
    ͕͜͜ઌఔͷαϒΫΤϦ෦෼
    

    View Slide

37. VTFS@JE OBNF BSFB@JE
    TPOF
    TPVEBJ
    UBLFUPNP
    TPVEBJ
    BSFB@JE BSFB@OBNF
    ޿ౡ
    Ԭࢁ
    ౦ژ
    େࡕ
    

    View Slide

38. VTFS@JE OBNF BSFB@JE
    TPOF
    TPVEBJ
    UBLFUPNP
    TPVEBJ
    BSFB@JE BSFB@OBNF
    ޿ౡ
    Ԭࢁ
    ౦ژ
    େࡕ
    ݕࡧ݁Ռ
    

    View Slide

39. SELECT
    SELECT *
    FROM
    users
    WHERE
    area_id IN (
    SELECT area_id
    FROM area
    WHERE name = '޿ౡ'
    )
    

    View Slide

40. SELECT
    SELECT *
    FROM
    users
    WHERE
    area_id IN (
    SELECT area_id
    FROM area
    WHERE name = '޿ౡ'
    )
    

    View Slide

41. SQLͷجຊతͳߏจ
    8)&3&۟͸ߋ৽΍࡟আͰ΋͋Δ
    

    View Slide

42. SQLͷجຊతͳߏจ
    8)&3&۟͸ߋ৽΍࡟আͰ΋͋Δ
    ˣ
    αϒΫΤϦͱ*/۟͸Ԡ༻Ͱ͖Δ
    

    View Slide

43. UPDATE
    UPDATE
    table_name
    SET
    column_name = value,
    column_name = value,...
    WHERE
    ৚݅
    

    View Slide

44. UPDATE
    UPDATE
    table_name
    SET
    column_name = value,
    column_name = value,...
    WHERE
    ৚݅
    4&-&$5ͱಉ͡
    

    View Slide

45. DELETE
    DELETE FROM
    users
    WHERE
    area_id IN (
    SELECT area_id
    FROM area
    WHERE name = '޿ౡ'
    )
    

    View Slide

46. DELETE
    DELETE FROM
    users
    WHERE
    area_id IN (
    SELECT area_id
    FROM area
    WHERE name = '޿ౡ'
    )
    4&-&$5ͷ݁Ռͱಉ͡ର৅Λ࡟আ͢Δ
    

    View Slide

47. SQLͷجຊతͳߏจ
    4&-&$5ͷ݁ՌΛ࠶ར༻͢Δ
    ͦͷ̎
    

    View Slide

48. VTFS@JE OBNF CJSUIEBZ BHF
    ૖େ
    ኽָ r
    ᗅָ r
    ࡣָ r
    

    View Slide

49. VTFS@JE OBNF CJSUIEBZ BHF
    ૖େ
    ኽָ r
    ᗅָ r
    ࡣָ r
    ஀ੜ೔͕དྷͨΒߋ৽͕ඞཁ
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    ஀ੜ೔͔Βݱࡏͷ೥ྸΛܭࢉ
    

    View Slide

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
    

    View Slide

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&ͰϚονϯά
    

    View Slide

55. VTFS@JE BHF
    
    VTFS@JE OBNF CJSUIEBZ BHF
    ૖େ
    ኽָ r
    ᗅָ r
    ࡣָ r
    

    View Slide

56. VTFS@JE BHF
    
    VTFS@JE OBNF CJSUIEBZ BHF
    ૖େ
    ኽָ r
    ᗅָ r
    ࡣָ r
    ஀ੜ೔͔Β೥ྸΛࢉग़
    

    View Slide

57. VTFS@JE BHF
    
    VTFS@JE OBNF CJSUIEBZ BHF
    ૖େ
    ኽָ r
    ᗅָ r
    ࡣָ r
    

    View Slide

58. VTFS@JE BHF
    
    VTFS@JE OBNF CJSUIEBZ BHF
    ૖େ
    ኽָ r
    ᗅָ r
    ࡣָ r
    ೥ྸͰ61%"5&
    ߋ৽ͷର৅Λ8)&3&ͰϚονϯά
    

    View Slide

59. SQLͷجຊతͳߏจ
    4&-&$5ͷ݁ՌΛ࠶ར༻͢Δ
    ͦͷ
    

    View Slide

60. INSERT
    INSERT INTO table_name
    (column_name,column_name…)
    VALUES
    (value,value…)
    , (value,value…)
    , (value,value…)
    , (value,value…)
    

    View Slide

61. INSERT
    INSERT INTO table_name
    (column_name,column_name…)
    VALUES
    (value,value…)
    , (value,value…)
    , (value,value…)
    , (value,value…)
    

    View Slide

62. INSERT
    INSERT INTO table_name
    (column_name,column_name…)
    SELECT
    column_name
    , column_name…
    FROM table_name
    WHERE …
    

    View Slide

63. SQLͷجຊతͳߏจ
    ച্ϩά͔Βച্ूܭΛग़͢
    

    View Slide

64. ച্೔࣌ ച্ֹۚ
    
    
    
    
    ộ ộ
    ച্೔ ച্ֹۚ
    
    
    
    
    ộ ộ
    

    View Slide

65. ച্೔࣌ ച্ֹۚ
    
    
    
    
    ộ ộ
    ച্೔ ച্ֹۚ
    
    
    
    
    ộ ộ
    

    View Slide

66. ച্೔࣌ ച্ֹۚ
    
    
    
    
    ộ ộ
    ച্೔ ച্ֹۚ
    
    
    
    
    ộ ộ
    

    View Slide

67. ച্೔࣌ ച্ֹۚ
    
    
    
    
    ộ ộ
    ച্೔ ച্ֹۚ
    
    
    
    
    ộ ộ
    ೔෇͝ͱʹूܭ͢Δඞཁ͕͋Δ
    

    View Slide

68. ച্೔࣌ ച্ֹۚ
    
    
    
    
    ộ ộ
    ച্೔ ച্ֹۚ
    
    
    
    
    ộ ộ
    ूܭ݁ՌΛผUBCMFʹ*/4&35
    

    View Slide

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 ച্೔
    

    View Slide

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 ച্೔
    

    View Slide

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ͷ݄ॳ͔Β݄຤ͷσʔλΛநग़
    

    View Slide

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 ച্೔
    ೔࣌Λ೔෇ʹม׵
    

    View Slide

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 ച্೔
    ೔෇ʹม׵ͨ͠ച্೔ͰάϧʔϓԽ
    

    View Slide

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
    

    View Slide

75. SQLͷجຊతͳߏจ
    4&-&$5ͷ݁ՌΛར༻͢Δ
    

    View Slide

76. SQLͷجຊతͳߏจ
    4&-&$5ͷ݁ՌΛར༻͢Δ
    ˣ
    ෳ਺ճͷ42-͕ҰճͰ࣮ߦग़དྷΔ
    

    View Slide

77. SQLͷجຊతͳߏจ
    4&-&$5ͷ݁ՌΛར༻͢Δ
    ˣ
    ෳ਺ճͷ42-͕ҰճͰ࣮ߦग़དྷΔ
    ύϑΥʔϚϯε্͕͕Δʂ
    

    View Slide

78. ͋͐͡Μͩ
    ̍ɹࣗݾ঺հ
    ̎ɹSQLͷجຊతͳߏจͱԠ༻
    ̏ɹCASEΛ࢖͍͜ͳͦ͏
    ̐ɹPostgreSQLͷؔ਺
    ̑ɹ·ͱΊ
    

    View Slide

79. CASEΛ࢖͍͜ͳͦ͏
    42-ͷۤखͳࣄ
    

    View Slide

80. CASEΛ࢖͍͜ͳͦ͏
    42-ͷۤखͳࣄ
    ˣ
    ੍ޚߏจͱϧʔϓ
    

    View Slide

81. CASEΛ࢖͍͜ͳͦ͏
    ͔͠͠$"4&Λ࢖͏ࣄͰ
    ଟ͘ͷ୅ସҊʹͳΔ
    

    View Slide

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)
    

    View Slide

83. OBNF BSFB@HSPVQ
    TPOF தࠃ஍ํ
    TPVEBJ தࠃ஍ํ
    UBLFUPNP தࠃ஍ํ
    TPVEBJ தࠃ஍ํ
    IPHF ͦͷଞ
    GVHB ؔ੢஍ํ
    GPP ؔ੢஍ํ
    

    View Slide

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)
    

    View Slide

85. CASEΛ࢖͍͜ͳͦ͏
    $"4&͸ࣜ
    

    View Slide

86. CASEΛ࢖͍͜ͳͦ͏
    $"4&͸ࣜ
    ˣ
    Ͳ͜ʹͰ΋ॻ͚Δ
    

    View Slide

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
    

    View Slide

88. OBNF BSFB BSFB@HSPVQ
    TPVEBJ ޿ౡ தࠃ஍ํ
    TPOF ޿ౡ தࠃ஍ํ
    UBLFUPNP Ԭࢁ தࠃ஍ํ
    TPVEBJ Ԭࢁ தࠃ஍ํ
    IPHF ౦ژ ͦͷଞ
    GPP େࡕ ؔ੢஍ํ
    GVHB େࡕ ؔ੢஍ํ
    

    View Slide

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
    ԬࢁΛ্ʹ͍ͨ࣌͠
    

    View Slide

90. OBNF BSFB BSFB@HSPVQ
    TPVEBJ Ԭࢁ தࠃ஍ํ
    TPOF Ԭࢁ தࠃ஍ํ
    UBLFUPNP ޿ౡ தࠃ஍ํ
    TPVEBJ ޿ౡ தࠃ஍ํ
    IPHF ౦ژ ͦͷଞ
    GPP େࡕ ؔ੢஍ํ
    GVHB େࡕ ؔ੢஍ํ
    

    View Slide

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
    

    View Slide

92. CASEͷجຊతͳߏจ
    /໰୊
    

    View Slide

93. CASEͷجຊతͳߏจ
    ϧʔϓͷ਺͚ͩRVFSZ౤͛Δ
    

    View Slide

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();
    

    View Slide

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&ͳΒճ
    

    View Slide

96. CASEͷجຊతͳߏจ
    ճͷ61%"5&จʹม͑Δ
    

    View Slide

97. CASEͷجຊతͳߏจ
    UPDATE users SET
    pref = CASE pref
    WHEN ‘Ԭࢁ’ THEN ‘େ౎ձ’
    WHEN ‘޿ౡ’ THEN ‘෱ࢁ’
    ELSE pref
    END
    WHERE pref IN (‘Ԭࢁ’,’޿ౡ’)
    

    View Slide

98. CASEͷجຊతͳߏจ
    $"4&͸ࣜͳͷͰ
    ৭Μͳͱ͜ΖͰ࢖͑Δ
    

    View Slide

99. CASEͷجຊతͳߏจ
    Ԡ༻ฤ
    ͭͷ%#Λൺֱͯ͠61%"5&
    

    View Slide

100. CASEͷجຊతͳߏจ
     JE OBNF BHF
     TPOF
     UBLFUPNP
     TPVEBJ
     VTFS@JE CJSUIEBZ QSFG
     ޿ౡ
     ෱ࢁ
     େ౎ձ
     VTFST VTFS@QSPQFSUJFT
     
     

     View Slide

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
     

     View Slide

102. CASEͷجຊతͳߏจ
     JE OBNF BHF
     TPOF
     UBLFUPNP ෆৄ
     TPVEBJ
     VTFS@JE CJSUIEBZ QSFG
     ޿ౡ
     ෱ࢁ
     େ౎ձ
     

     View Slide

103. ͋͐͡Μͩ
     ̍ɹࣗݾ঺հ
     ̎ɹSQLͷجຊతͳߏจͱԠ༻
     ̏ɹCASEΛ࢖͍͜ͳͦ͏
     ̐ɹPostgreSQLͷؔ਺
     ̑ɹ·ͱΊ
     

     View Slide

104. ૉૣ͘ாථΛ࡞Δ
     PostgreSQLͷؔ਺
     

     View Slide

105. 8JOEPXؔ਺
     PostgreSQLͷؔ਺
     

     View Slide

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

     View Slide

107. Windowؔ਺
     ·ͣ͸೔ผʹूܭ͢Δ
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

111. ೔ผച্ ച্೔
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     ɿ
     ɿ
     ɿ
     ɿ
     

     View Slide

112. Windowؔ਺
     લ೔ͱͷࠩ෼Λग़͍ͨ͠
     

     View Slide

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

     View Slide

114. ࣮ࡍͷSQL
     SELECT
     *
     , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্
     FROM
     (SELECT
     to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্"
     , "ച্೔"
     FROM "ച্ද"
     GROUP BY "ച্೔") AS "ूܭ"
     

     View Slide

115. ࣮ࡍͷSQL
     SELECT
     *
     , lag("೔ผച্") OVER (ORDER BY "ച্೔")AS લ೔ച্
     FROM
     (SELECT
     to_char(sum("ച্"),'FM999,999,999') AS "೔ผച্"
     , "ച্೔"
     FROM "ച্ද"
     GROUP BY "ച্೔") AS "ूܭ"
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

121. Windowؔ਺
     ཌ೔ͷ৔߹͸ʁ
     

     View Slide

122. Windowؔ਺
     ཌ೔ͷ৔߹͸ʁ
     ˣ
     MFBE
     Λ࢖ͬͯूܭग़དྷΔ
     

     View Slide

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 /
     /൪໨ͷ஋ ͔Β਺͑Δ
     
     

     View Slide

124. Windowؔ਺
     ศརͳྫ
     w ݄ผͷாථ࡞੒
     w ϥϯΩϯάදࣔ
     w άϧʔϓผͷूܭ
     

     View Slide

125. ศརͳܕΛར༻͢Δ
     1PTUHSF42-ͷػೳΛ׆༻͢Δ
     

     View Slide

126. ศརͳܕΛར༻͢Δ
     1PTUHSF42-͸+40/͕ಘҙ
     

     View Slide

127. ศརͳܕΛར༻͢Δ
     1PTUHSF42-͸+40/͕ಘҙ
     ˣ
     +40/ܕɾ+40/#ܕ͕͋Δ
     

     View Slide

128. ศརͳܕΛར༻͢Δ
     1PTUHSF42-͸ൣғΛѻ͏ͷ͕ಘҙ
     

     View Slide

129. ศརͳܕΛར༻͢Δ
     1PTUHSF42-͸ൣғΛѻ͏ͷ͕ಘҙ
     ˣ
     ൣғܕΛ࢖͑͹ΧϨϯμʔ΋؆୯
     

     View Slide

130. λεΫ໊ ৄࡉ ରԠظؒ
     Πϯετʔϧ \ॴଐΞγετ ߨࢣتా^ <
     
     جຊฤ \ॴଐΞγετ ߨࢣتా^ <
     
     42-Oຊ \ॴଐΦϛΧϨ ߨࢣીࠜ^ <
     
     σʔλ෼ੳ \ॴଐΈ͔͔ ߨࢣେࢁ^ <
     
     

     View Slide

131. ࣮ࡍͷSQL
     SELECT
     *
     FROM
     "λεΫҰཡ"
     WHERE
     "ৄࡉ" ->> 'ߨࢣ' = 'تా'
     

     View Slide

132. λεΫ໊ ৄࡉ ରԠظؒ
     Πϯετʔϧ \ॴଐΞγετ ߨࢣتా^ <
     
     جຊฤ \ॴଐΞγετ ߨࢣتా^ <
     
     42-Oຊ \ॴଐΦϛΧϨ ߨࢣીࠜ^ <
     
     σʔλ෼ੳ \ॴଐΈ͔͔ ߨࢣେࢁ^ <
     
     

     View Slide

133. ࣮ࡍͷSQL
     SELECT
     *
     FROM
     "λεΫҰཡ"
     WHERE
     '2016-12-01'::DATE <@ "ରԠظؒ"
     

     View Slide

134. λεΫ໊ ৄࡉ ରԠظؒ
     Πϯετʔϧ \ॴଐΞγετ ߨࢣتా^ <
     
     جຊฤ \ॴଐΞγετ ߨࢣتా^ <
     
     42-Oຊ \ॴଐΦϛΧϨ ߨࢣીࠜ^ <
     
     σʔλ෼ੳ \ॴଐΈ͔͔ ߨࢣେࢁ^ <
     
     

     View Slide

135. ࣮ࡍͷSQL
     SELECT
     *
     FROM
     "λεΫҰཡ"
     WHERE
     daterange(
     '2016-12-03', '2016-12-04', ‘[]'
     ) <@ "ରԠظؒ";
     

     View Slide

136. λεΫ໊ ৄࡉ ରԠظؒ
     Πϯετʔϧ \ॴଐΞγετ ߨࢣتా^ <
     
     جຊฤ \ॴଐΞγετ ߨࢣتా^ <
     
     42-Oຊ \ॴଐΦϛΧϨ ߨࢣીࠜ^ <
     
     σʔλ෼ੳ \ॴଐΈ͔͔ ߨࢣେࢁ^ <
     
     

     View Slide

137. ͋͐͡Μͩ
     ̍ɹࣗݾ঺հ
     ̎ɹSQLͷجຊతͳߏจͱԠ༻
     ̏ɹCASEΛ࢖͍͜ͳͦ͏
     ̐ɹPostgreSQLͷؔ਺
     ̑ɹ·ͱΊ
     

     View Slide

138. ·ͱΊ
     42-Λ֮͑Δͱදݱྗ্͕͕Δ
     

     View Slide

139. ·ͱΊ
     ૉૣ͘දݱग़དྷΔ͜ͱͷՁ஋
     

     View Slide

140. ·ͱΊ
     දݱྗ্͕͕Ε͹ઃܭྗ্͕͕Δ
     

     View Slide

141. ·ͱΊ
     

     View Slide

142. ·ͱΊ
     ɾQPTUHSFTRMKQ4MBDL νϟοτϧʔϜ
     
     IUUQTQPTUHSFTRMIBDLFSTKQIFSPLVBQQDPN
     ɾNZTRMDBTVBM4MBDL νϟοτϧʔϜ
     
     IUUQTNZTRMDBTVBMTMBDLJOIFSPLVBQQDPN
     

     View Slide

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

     View Slide