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

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

ςʔϒϧఆٛ 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);

໰୊ͷΫΤϦ 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;

Կ͕͍ͨ͠ΫΤϦͳͷ͔ʁ ੡඼ͷϦετͱ඼࣭είΞͷσʔλΛ࣋ͭ1SPEVDU5FTUTͱ͍͏ςʔϒϧΛߟ ͑Α͏ɻ͜ͷςʔϒϧ͔Βɺಉ͡੡඼ͷγʔέϯγϟϧͳάϧʔϓʹର͢Δ඼ ࣭είΞͷฏۉΛٻΊ͍ͨɻͨͱ͑͹ɺ੡඼"ͷόον൪߸ɺɺʹର͢Δ ฏۉ඼࣭είΞ͸ɺ ʜ ͱ͍ͬͨ۩߹ͩɻ͜͜Ͱ஫ҙ͕ඞཁͳͷ͸ɺόον൪߸൪ͷ੡඼"ͷΑ͏ʹɺ
ඈͼੴʹͳͬͨ৔߹͸ผάϧʔϓͱͯ͠ѻ͏͜ͱͩɻ +PF$FMLPϓϩάϥϚͷͨΊͷ42-ୈ൛͢΂ͯΛ஌Γਚ͍ͨ͋͘͠ͳͨʹ

ର৅σʔλͱ݁Ռ ର৅σʔλ 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)

ΫΤϦղઆ ˞࣮ߦ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)

˞࣮ߦ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

"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)

'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) ·ͨ·ͨ"ʹݶͬͨέʔεͰߟ͑Δͱʜ

'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ؒͰ͸ɺʹ#ɺʹ$͕ࠞ͡ΔͷͰ "--ड़ޠ͕੒Γཱͨͳ͍ɻʹফ͑Δ

"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)

"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ʹ֨ೲ͍ͯ͠Δ

"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Λ෇͚଍͚ͨͩ͠ɻ

"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ɻ ɾ࠷খ஋ ಉ͡੡඼ͷγʔέϯγϟϧͳάϧʔϓͷ։࢝Ґஔ ɾฏۉ஋ Λूܭؔ਺Ͱܭࢉͯ͠දࣔ

׬੒ ର৅σʔλ 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)

͓͞Β͍ ˞࣮ߦ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)

"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)

"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)

"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Λ෇͚଍͚ͨͩ͠ɻ

"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ɻ ɾ࠷খ஋ ಉ͡੡඼ͷγʔέϯγϟϧͳάϧʔϓͷ։࢝Ґஔ ɾฏۉ஋ Λूܭؔ਺Ͱܭࢉͯ͠දࣔ

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

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

yuyabu

More Decks by yuyabu

Other Decks in Technology

Featured

Transcript

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

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

໰୊ͷΫΤϦ SELECT X.prod_code, MIN(X.batch_nbr) AS start_batch_nbr, end_batch_nbr, AVG(B4.prod_quality) AS avg_prod_quality

ର৅σʔλͱ݁Ռ ର৅σʔλ select * from ProductTests; batch_nbr | prod_code |

ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ

˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ "4BWH@QSPE@RVBMJUZ

ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ

˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ "4BWH@QSPE@RVBMJUZ

˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ "4BWH@QSPE@RVBMJUZ

ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ

ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ

ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ

ΫΤϦղઆ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ

׬੒ ର৅σʔλ select * from ProductTests; batch_nbr | prod_code |

͓͞Β͍ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ

͓͞Β͍ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ

͓͞Β͍ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ

͓͞Β͍ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ

͓͞Β͍ ˞࣮ߦ42-͸੺ࣈͰࣔ͠·͢ 4&-&$59QSPE@DPEF .*/ 9CBUDI@OCS "4TUBSU@CBUDI@OCS FOE@CBUDI@OCS "7( #QSPE@RVBMJUZ