Share
Embed
Transcript
20 slides
-
ςʔϒϧఆٛ 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 -
ΫΤϦղઆ ˞࣮ߦ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) -
˞࣮ߦ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) ·ͨ·ͨ"ʹݶͬͨέʔεͰߟ͑Δͱʜ -
˞࣮ߦ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ͷ͕ શͯҰக͢ΔߦΛͨ͢Ίͷ૬ؔαϒΫΤϦ ddؒશͯ"ͳͷͰΔ dؒͰɺʹ#ɺʹ$͕ࠞ͡ΔͷͰ "--ड़ޠ͕Γཱͨͳ͍ɻʹফ͑Δ -
ΫΤϦղઆ ˞࣮ߦ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) -
ΫΤϦղઆ ˞࣮ߦ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ʹ֨ೲ͍ͯ͠Δ -
ΫΤϦղઆ ˞࣮ߦ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Λ͚͚ͨͩ͠ɻ -
ΫΤϦղઆ ˞࣮ߦ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ɻ ɾ࠷খ ಉ͡ͷγʔέϯγϟϧͳάϧʔϓͷ։࢝Ґஔ ɾฏۉ ΛूܭؔͰܭࢉͯ͠දࣔ -
ରσʔλ 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) -
͓͞Β͍ ˞࣮ߦ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) -
͓͞Β͍ ˞࣮ߦ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) -
͓͞Β͍ ˞࣮ߦ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Λ͚͚ͨͩ͠ɻ -
͓͞Β͍ ˞࣮ߦ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ɻ ɾ࠷খ ಉ͡ͷγʔέϯγϟϧͳάϧʔϓͷ։࢝Ґஔ ɾฏۉ ΛूܭؔͰܭࢉͯ͠දࣔ