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

プログラマのためのSQL読書会補助資料

Fb841400aeed8f5d6529cf2c4f2521e1?s=47 yuyabu
July 27, 2018

 プログラマのためのSQL読書会補助資料

Fb841400aeed8f5d6529cf2c4f2521e1?s=128

yuyabu

July 27, 2018
Tweet

Transcript

  1. ϓϩάϥϚͷͨΊͷ42- 1ͷ೉ղ42-

  2. ςʔϒϧఆٛ CREATE TABLE ProductTests (batch_nbr INTEGER NOT NULL PRIMARY KEY,

    prod_code CHAR(1) NOT NULL, prod_quality DECIMAL(8,4) NOT NULL); INSERT INTO ProductTests (batch_nbr, prod_code, prod_quality) VALUES (1, 'A', 80), (2, 'A', 70), (3, 'A', 80), (4, 'B', 60), (5, 'B', 90), (6, 'C', 80), (7, 'D', 80), (8, 'A', 50), (9, 'C', 70);
  3. ໰୊ͷΫΤϦ SELECT X.prod_code, MIN(X.batch_nbr) AS start_batch_nbr, end_batch_nbr, AVG(B4.prod_quality) AS avg_prod_quality

    FROM (SELECT B1.prod_code, B1.batch_nbr, MAX(B2.batch_nbr) AS end_batch_nbr FROM ProductTests AS B1, ProductTests AS B2 WHERE B1.batch_nbr <= B2.batch_nbr AND B1.prod_code = B2.prod_code AND B1.prod_code = ALL (SELECT prod_code FROM ProductTests AS B3 WHERE B3.batch_nbr BETWEEN B1.batch_nbr AND B2.batch_nbr) GROUP BY B1.prod_code, B1.batch_nbr) AS X INNER JOIN ProductTests AS B4 ON B4.prod_code = X.prod_code AND B4.batch_nbr = X.batch_nbr GROUP BY X.prod_code, X.end_batch_nbr;
  4. Կ͕͍ͨ͠ΫΤϦͳͷ͔ʁ ੡඼ͷϦετͱ඼࣭είΞͷσʔλΛ࣋ͭ1SPEVDU5FTUTͱ͍͏ςʔϒϧΛߟ ͑Α͏ɻ͜ͷςʔϒϧ͔Βɺಉ͡੡඼ͷγʔέϯγϟϧͳάϧʔϓʹର͢Δ඼ ࣭είΞͷฏۉΛٻΊ͍ͨɻͨͱ͑͹ɺ੡඼"ͷόον൪߸ɺɺʹର͢Δ ฏۉ඼࣭είΞ͸ɺ    ʜ ͱ͍ͬͨ۩߹ͩɻ͜͜Ͱ஫ҙ͕ඞཁͳͷ͸ɺόον൪߸൪ͷ੡඼"ͷΑ͏ʹɺ

    ඈͼੴʹͳͬͨ৔߹͸ผάϧʔϓͱͯ͠ѻ͏͜ͱͩɻ +PF$FMLPϓϩάϥϚͷͨΊͷ42-ୈ൛͢΂ͯΛ஌Γਚ͍ͨ͋͘͠ͳͨʹ
  5. ର৅σʔλͱ݁Ռ ର৅σʔλ select * from ProductTests; batch_nbr | prod_code |

    prod_quality -----------+-----------+-------------- 1 | A | 80.0000 2 | A | 70.0000 3 | A | 80.0000 4 | B | 60.0000 5 | B | 90.0000 6 | C | 80.0000 7 | D | 80.0000 8 | A | 50.0000 9 | C | 70.0000 (9 rows) ݅ͷSQLͷ࣮ߦ݁Ռ prod_code | start_batch_nbr | end_batch_nbr | avg_prod_quality -----------+-----------------+---------------+--------------------- A | 1 | 3 | 76.6666666666666667 B | 4 | 5 | 75.0000000000000000 C | 6 | 6 | 80.0000000000000000 D | 7 | 7 | 80.0000000000000000 A | 8 | 8 | 50.0000000000000000 C | 9 | 9 | 70.0000000000000000 (6 rows)
  6. ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS ※஫ҙ1 SELECT۟ͷྻࢦఆ͸*ͱ͠·ͨ͠ɻ ※஫ҙ2 Θ͔Γ΍͢͞ͷͨΊOrder byΛՃ͍͑ͯ·͢(ߦॱҙ֎ʹ݁Ռ͸͋Γ·ͤΜ) batch_nbr | prod_code | prod_quality | batch_nbr | prod_code | prod_quality -----------+-----------+--------------+-----------+-----------+-------------- 1 | A | 80.0000 | 1 | A | 80.0000 1 | A | 80.0000 | 2 | A | 70.0000 1 | A | 80.0000 | 3 | A | 80.0000 1 | A | 80.0000 | 8 | A | 50.0000 2 | A | 70.0000 | 2 | A | 70.0000 2 | A | 70.0000 | 3 | A | 80.0000 2 | A | 70.0000 | 8 | A | 50.0000 3 | A | 80.0000 | 3 | A | 80.0000 3 | A | 80.0000 | 8 | A | 50.0000 4 | B | 60.0000 | 4 | B | 60.0000 4 | B | 60.0000 | 5 | B | 90.0000 5 | B | 90.0000 | 5 | B | 90.0000 6 | C | 80.0000 | 6 | C | 80.0000 6 | C | 80.0000 | 9 | C | 70.0000 7 | D | 80.0000 | 7 | D | 80.0000 8 | A | 50.0000 | 8 | A | 50.0000 9 | C | 70.0000 | 9 | C | 70.0000 (17 rows)
  7. ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ "4BWH@QSPE@RVBMJUZ

    '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS ΫΤϦղઆ ݩσʔλ select * from ProductTests; batch_nbr | prod_code | prod_quality -----------+-----------+-------------- 1 | A | 80.0000 2 | A | 70.0000 3 | A | 80.0000 4 | B | 60.0000 5 | B | 90.0000 6 | C | 80.0000 7 | D | 80.0000 8 | A | 50.0000 9 | C | 70.0000 QSPE@DPEF͕ಉ͡ͰCBUDI@OCS͕େ͖͍΋ͷͱࣗݾ݁߹͠·͢ "ʹݶͬͯߟ͑Δͱɺ"ͷόον൪߸͸\   ^ͳͷͰ \         ^ͷ௨ΓͷϖΞ͕Ͱ͖·͢ ※஫ҙ1 SELECT۟ͷྻࢦఆ͸*ͱ͠·ͨ͠ɻ ※஫ҙ2 Θ͔Γ΍͢͞ͷͨΊOrder byΛՃ͍͑ͯ·͢(ߦॱҙ֎ʹ݁Ռ͸͋Γ·ͤΜ) batch_nbr | prod_code | prod_quality | batch_nbr | prod_code | prod_quality -----------+-----------+--------------+-----------+-----------+-------------- 1 | A | 80.0000 | 1 | A | 80.0000 1 | A | 80.0000 | 2 | A | 70.0000 1 | A | 80.0000 | 3 | A | 80.0000 1 | A | 80.0000 | 8 | A | 50.0000 2 | A | 70.0000 | 2 | A | 70.0000 2 | A | 70.0000 | 3 | A | 80.0000 2 | A | 70.0000 | 8 | A | 50.0000 3 | A | 80.0000 | 3 | A | 80.0000 3 | A | 80.0000 | 8 | A | 50.0000 4 | B | 60.0000 | 4 | B | 60.0000 4 | B | 60.0000 | 5 | B | 90.0000 5 | B | 90.0000 | 5 | B | 90.0000 6 | C | 80.0000 | 6 | C | 80.0000 6 | C | 80.0000 | 9 | C | 70.0000 7 | D | 80.0000 | 7 | D | 80.0000 8 | A | 50.0000 | 8 | A | 50.0000 9 | C | 70.0000 | 9 | C | 70.0000 (17 rows) 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF
  8. ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS ※஫ҙ1 SELECT۟ͷྻࢦఆ͸*ͱ͠·ͨ͠ɻ ※஫ҙ2 Θ͔Γ΍͢͞ͷͨΊOrder byΛՃ͍͑ͯ·͢(ߦॱҙ֎ʹ݁Ռ͸͋Γ·ͤΜ) batch_nbr | prod_code | prod_quality | batch_nbr | prod_code | prod_quality -----------+-----------+--------------+-----------+-----------+-------------- 1 | A | 80.0000 | 1 | A | 80.0000 1 | A | 80.0000 | 2 | A | 70.0000 1 | A | 80.0000 | 3 | A | 80.0000 2 | A | 70.0000 | 2 | A | 70.0000 2 | A | 70.0000 | 3 | A | 80.0000 3 | A | 80.0000 | 3 | A | 80.0000 4 | B | 60.0000 | 4 | B | 60.0000 4 | B | 60.0000 | 5 | B | 90.0000 5 | B | 90.0000 | 5 | B | 90.0000 6 | C | 80.0000 | 6 | C | 80.0000 7 | D | 80.0000 | 7 | D | 80.0000 8 | A | 50.0000 | 8 | A | 50.0000 9 | C | 70.0000 | 9 | C | 70.0000 (13 rows)
  9. ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ "4BWH@QSPE@RVBMJUZ

    '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS ΫΤϦղઆ ੨৭ͷߦ͕ফ͑·͢ batch_nbr | prod_code | prod_quality | batch_nbr | prod_quality -----------+-----------+--------------+-----------+-------------- 1 | A | 80.0000 | 1 | 80.0000 1 | A | 80.0000 | 2 | 70.0000 1 | A | 80.0000 | 3 | 80.0000 1 | A | 80.0000 | 8 | 50.0000 2 | A | 70.0000 | 2 | 70.0000 2 | A | 70.0000 | 3 | 80.0000 2 | A | 70.0000 | 8 | 50.0000 3 | A | 80.0000 | 3 | 80.0000 3 | A | 80.0000 | 8 | 50.0000 4 | B | 60.0000 | 4 | 60.0000 4 | B | 60.0000 | 5 | 90.0000 5 | B | 90.0000 | 5 | 90.0000 6 | C | 80.0000 | 6 | 80.0000 6 | C | 80.0000 | 9 | 70.0000 7 | D | 80.0000 | 7 | 80.0000 8 | A | 50.0000 | 8 | 50.0000 9 | C | 70.0000 | 9 | 70.0000 (17 rows) ※஫ҙ1 SELECT۟ͷྻࢦఆ͸*ͱ͠·ͨ͠ɻ ※஫ҙ2 Θ͔Γ΍͢͞ͷͨΊOrder byΛՃ͍͑ͯ·͢(ߦॱҙ֎ʹ݁Ռ͸͋Γ·ͤΜ) batch_nbr | prod_code | prod_quality | batch_nbr | prod_code | prod_quality -----------+-----------+--------------+-----------+-----------+-------------- 1 | A | 80.0000 | 1 | A | 80.0000 1 | A | 80.0000 | 2 | A | 70.0000 1 | A | 80.0000 | 3 | A | 80.0000 2 | A | 70.0000 | 2 | A | 70.0000 2 | A | 70.0000 | 3 | A | 80.0000 3 | A | 80.0000 | 3 | A | 80.0000 4 | B | 60.0000 | 4 | B | 60.0000 4 | B | 60.0000 | 5 | B | 90.0000 5 | B | 90.0000 | 5 | B | 90.0000 6 | C | 80.0000 | 6 | C | 80.0000 7 | D | 80.0000 | 7 | D | 80.0000 8 | A | 50.0000 | 8 | A | 50.0000 9 | C | 70.0000 | 9 | C | 70.0000 (13 rows) ·ͨ·ͨ"ʹݶͬͨέʔεͰߟ͑Δͱʜ
  10. ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ "4BWH@QSPE@RVBMJUZ

    '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS ΫΤϦղઆ ੨৭ͷߦ͕ফ͑·͢ batch_nbr | prod_code | prod_quality | batch_nbr | prod_quality -----------+-----------+--------------+-----------+-------------- 1 | A | 80.0000 | 1 | 80.0000 1 | A | 80.0000 | 2 | 70.0000 1 | A | 80.0000 | 3 | 80.0000 1 | A | 80.0000 | 8 | 50.0000 2 | A | 70.0000 | 2 | 70.0000 2 | A | 70.0000 | 3 | 80.0000 2 | A | 70.0000 | 8 | 50.0000 3 | A | 80.0000 | 3 | 80.0000 3 | A | 80.0000 | 8 | 50.0000 4 | B | 60.0000 | 4 | 60.0000 4 | B | 60.0000 | 5 | 90.0000 5 | B | 90.0000 | 5 | 90.0000 6 | C | 80.0000 | 6 | 80.0000 6 | C | 80.0000 | 9 | 70.0000 7 | D | 80.0000 | 7 | 80.0000 8 | A | 50.0000 | 8 | 50.0000 9 | C | 70.0000 | 9 | 70.0000 (17 rows) ※஫ҙ1 SELECT۟ͷྻࢦఆ͸*ͱ͠·ͨ͠ɻ ※஫ҙ2 Θ͔Γ΍͢͞ͷͨΊOrder byΛՃ͍͑ͯ·͢(ߦॱҙ֎ʹ݁Ռ͸͋Γ·ͤΜ) batch_nbr | prod_code | prod_quality | batch_nbr | prod_code | prod_quality -----------+-----------+--------------+-----------+-----------+-------------- 1 | A | 80.0000 | 1 | A | 80.0000 1 | A | 80.0000 | 2 | A | 70.0000 1 | A | 80.0000 | 3 | A | 80.0000 2 | A | 70.0000 | 2 | A | 70.0000 2 | A | 70.0000 | 3 | A | 80.0000 3 | A | 80.0000 | 3 | A | 80.0000 4 | B | 60.0000 | 4 | B | 60.0000 4 | B | 60.0000 | 5 | B | 90.0000 5 | B | 90.0000 | 5 | B | 90.0000 6 | C | 80.0000 | 6 | C | 80.0000 7 | D | 80.0000 | 7 | D | 80.0000 8 | A | 50.0000 | 8 | A | 50.0000 9 | C | 70.0000 | 9 | C | 70.0000 (13 rows) "--ड़ޠͷ৚݅͸एׯΘ͔Γʹ͍͕͘ɺ ࣗݾ݁߹ͨ͠ςʔϒϧ #ͱ#ʹ͓͍ͯɺ #CBUDI@OCSd#CBUDI@OCS ؒͷQSPE@DPEFͷ஋͕ શͯҰக͢ΔߦΛ࢒ͨ͢Ίͷ૬ؔαϒΫΤϦ d΍dؒ͸શͯ"ͳͷͰ࢒Δ dؒͰ͸ɺʹ#ɺʹ$͕ࠞ͡ΔͷͰ "--ड़ޠ͕੒Γཱͨͳ͍ɻʹফ͑Δ
  11. ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS prod_code | batch_nbr | end_batch_nbr -----------+-----------+--------------- A | 1 | 3 A | 2 | 3 A | 3 | 3 B | 4 | 5 B | 5 | 5 C | 6 | 6 D | 7 | 7 A | 8 | 8 C | 9 | 9 (9 rows)
  12. ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS prod_code | batch_nbr | end_batch_nbr -----------+-----------+--------------- A | 1 | 3 A | 2 | 3 A | 3 | 3 B | 4 | 5 B | 5 | 5 C | 6 | 6 D | 7 | 7 A | 8 | 8 C | 9 | 9 (9 rows) ର৅σʔλ select * from ProductTests; batch_nbr | prod_code | prod_quality -----------+-----------+-------------- 1 | A | 80.0000 2 | A | 70.0000 3 | A | 80.0000 4 | B | 60.0000 5 | B | 90.0000 6 | C | 80.0000 7 | D | 80.0000 8 | A | 50.0000 9 | C | 70.0000 (9 rows) ࿈ଓ͍ͯ͠Δ੡඼άϧʔϓ܈ͷ࠷ޙͷCBUDI@OCSΛ FOE@CBUDI@OCSʹ֨ೲ͍ͯ͠Δ
  13. ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS ˞੺ࣈͷ෦෼ͷ··࣮ߦͰ͖ͳ͍ͷͰTFMFDU GSPN ͰׅͬͯαϒΫΤϦͱ࣮ͯ͠ߦ͍ͯ͠·͢ prod_code | batch_nbr | end_batch_nbr | prod_quality -----------+-----------+---------------+-------------- A | 1 | 3 | 80.0000 A | 2 | 3 | 70.0000 A | 3 | 3 | 80.0000 B | 4 | 5 | 60.0000 B | 5 | 5 | 90.0000 C | 6 | 6 | 80.0000 D | 7 | 7 | 80.0000 A | 8 | 8 | 50.0000 C | 9 | 9 | 70.0000 (9 rows) ಺෦݁߹ͰQSPE@RVBMJUZΛ෇͚଍͚ͨͩ͠ɻ
  14. ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS prod_code | start_batch_nbr | end_batch_nbr | avg_prod_quality -----------+-----------------+---------------+--------------------- A | 1 | 3 | 76.6666666666666667 B | 4 | 5 | 75.0000000000000000 C | 6 | 6 | 80.0000000000000000 D | 7 | 7 | 80.0000000000000000 A | 8 | 8 | 50.0000000000000000 C | 9 | 9 | 70.0000000000000000 (6 rows) ੺ࣈͷྻͰHSPVQCZɻ ɾ࠷খ஋ ಉ͡੡඼ͷγʔέϯγϟϧͳάϧʔϓͷ։࢝Ґஔ  ɾฏۉ஋ Λूܭؔ਺Ͱܭࢉͯ͠දࣔ
  15. ׬੒ ର৅σʔλ select * from ProductTests; batch_nbr | prod_code |

    prod_quality -----------+-----------+-------------- 1 | A | 80.0000 2 | A | 70.0000 3 | A | 80.0000 4 | B | 60.0000 5 | B | 90.0000 6 | C | 80.0000 7 | D | 80.0000 8 | A | 50.0000 9 | C | 70.0000 (9 rows) prod_code | start_batch_nbr | end_batch_nbr | avg_prod_quality -----------+-----------------+---------------+--------------------- A | 1 | 3 | 76.6666666666666667 B | 4 | 5 | 75.0000000000000000 C | 6 | 6 | 80.0000000000000000 D | 7 | 7 | 80.0000000000000000 A | 8 | 8 | 50.0000000000000000 C | 9 | 9 | 70.0000000000000000 (6 rows)
  16. ͓͞Β͍ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS ※஫ҙ1 SELECT۟ͷྻࢦఆ͸*ͱ͠·ͨ͠ɻ ※஫ҙ2 Θ͔Γ΍͢͞ͷͨΊOrder byΛՃ͍͑ͯ·͢(ߦॱҙ֎ʹ݁Ռ͸͋Γ·ͤΜ) batch_nbr | prod_code | prod_quality | batch_nbr | prod_code | prod_quality -----------+-----------+--------------+-----------+-----------+-------------- 1 | A | 80.0000 | 1 | A | 80.0000 1 | A | 80.0000 | 2 | A | 70.0000 1 | A | 80.0000 | 3 | A | 80.0000 1 | A | 80.0000 | 8 | A | 50.0000 2 | A | 70.0000 | 2 | A | 70.0000 2 | A | 70.0000 | 3 | A | 80.0000 2 | A | 70.0000 | 8 | A | 50.0000 3 | A | 80.0000 | 3 | A | 80.0000 3 | A | 80.0000 | 8 | A | 50.0000 4 | B | 60.0000 | 4 | B | 60.0000 4 | B | 60.0000 | 5 | B | 90.0000 5 | B | 90.0000 | 5 | B | 90.0000 6 | C | 80.0000 | 6 | C | 80.0000 6 | C | 80.0000 | 9 | C | 70.0000 7 | D | 80.0000 | 7 | D | 80.0000 8 | A | 50.0000 | 8 | A | 50.0000 9 | C | 70.0000 | 9 | C | 70.0000 (17 rows)
  17. ͓͞Β͍ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS ※஫ҙ1 SELECT۟ͷྻࢦఆ͸*ͱ͠·ͨ͠ɻ ※஫ҙ2 Θ͔Γ΍͢͞ͷͨΊOrder byΛՃ͍͑ͯ·͢(ߦॱҙ֎ʹ݁Ռ͸͋Γ·ͤΜ) batch_nbr | prod_code | prod_quality | batch_nbr | prod_code | prod_quality -----------+-----------+--------------+-----------+-----------+-------------- 1 | A | 80.0000 | 1 | A | 80.0000 1 | A | 80.0000 | 2 | A | 70.0000 1 | A | 80.0000 | 3 | A | 80.0000 2 | A | 70.0000 | 2 | A | 70.0000 2 | A | 70.0000 | 3 | A | 80.0000 3 | A | 80.0000 | 3 | A | 80.0000 4 | B | 60.0000 | 4 | B | 60.0000 4 | B | 60.0000 | 5 | B | 90.0000 5 | B | 90.0000 | 5 | B | 90.0000 6 | C | 80.0000 | 6 | C | 80.0000 7 | D | 80.0000 | 7 | D | 80.0000 8 | A | 50.0000 | 8 | A | 50.0000 9 | C | 70.0000 | 9 | C | 70.0000 (13 rows)
  18. ͓͞Β͍ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS prod_code | batch_nbr | end_batch_nbr -----------+-----------+--------------- A | 1 | 3 A | 2 | 3 A | 3 | 3 B | 4 | 5 B | 5 | 5 C | 6 | 6 D | 7 | 7 A | 8 | 8 C | 9 | 9 (9 rows)
  19. ͓͞Β͍ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS ˞੺ࣈͷ෦෼ͷ··࣮ߦͰ͖ͳ͍ͷͰTFMFDU GSPN ͰׅͬͯαϒΫΤϦͱ࣮ͯ͠ߦ͍ͯ͠·͢ prod_code | batch_nbr | end_batch_nbr | prod_quality -----------+-----------+---------------+-------------- A | 1 | 3 | 80.0000 A | 2 | 3 | 70.0000 A | 3 | 3 | 80.0000 B | 4 | 5 | 60.0000 B | 5 | 5 | 90.0000 C | 6 | 6 | 80.0000 D | 7 | 7 | 80.0000 A | 8 | 8 | 50.0000 C | 9 | 9 | 70.0000 (9 rows) ಺෦݁߹ͰQSPE@RVBMJUZΛ෇͚଍͚ͨͩ͠ɻ
  20. ͓͞Β͍ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS  "7( #QSPE@RVBMJUZ

    "4BWH@QSPE@RVBMJUZ '30. 4&-&$5#QSPE@DPEF #CBUDI@OCS  ."9 #CBUDI@OCS "4FOE@CBUDI@OCS '30.1SPEVDU5FTUT"4# 1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#CBUDI@OCS "/%#QSPE@DPEF#QSPE@DPEF "/%#QSPE@DPEF "-- 4&-&$5QSPE@DPEF '30.1SPEVDU5FTUT"4# 8)&3&#CBUDI@OCS#&58&&/#CBUDI@OCS "/%#CBUDI@OCS  (3061#:#QSPE@DPEF #CBUDI@OCS "49 *//&3+0*/ 1SPEVDU5FTUT"4# 0/#QSPE@DPEF9QSPE@DPEF "/%#CBUDI@OCS9CBUDI@OCS (3061#:9QSPE@DPEF 9FOE@CBUDI@OCS prod_code | start_batch_nbr | end_batch_nbr | avg_prod_quality -----------+-----------------+---------------+--------------------- A | 1 | 3 | 76.6666666666666667 B | 4 | 5 | 75.0000000000000000 C | 6 | 6 | 80.0000000000000000 D | 7 | 7 | 80.0000000000000000 A | 8 | 8 | 50.0000000000000000 C | 9 | 9 | 70.0000000000000000 (6 rows) ੺ࣈͷྻͰHSPVQCZɻ ɾ࠷খ஋ ಉ͡੡඼ͷγʔέϯγϟϧͳάϧʔϓͷ։࢝Ґஔ  ɾฏۉ஋ Λूܭؔ਺Ͱܭࢉͯ͠දࣔ