
ã2026幎çãSQLããŒã¿åæ å ¥éãã宿ŠãŸã§ïœSELECTã»JOINã»ãŠã£ã³ããŠé¢æ°ã»åæã®åïŒç·Žç¿åé¡60å
ã¯ããã«ïŒãã®ææã§ã§ããããš
SQL ã¯ãããŒã¿ããŒã¹ããããŒã¿ãåãåºããŠéèšããããã®èšèªã§ããããŒã¿ãµã€ãšã³ãã£ã¹ããããŒã¿ã¢ããªã¹ãã«ãšã£ãŠã¯ãPython ãçµ±èšãšäžŠã¶ãæ¯æ¥äœ¿ãéå ·ãã§ããå°±è·ã»è»¢è·ã®ã³ãŒãã£ã³ã°ãã¹ãã§ããSQL ã®åé¡ãåºã圢åŒã¯å°ãªããããŸãããDSæ€å®ïŒããŒã¿ãµã€ãšã³ãã£ã¹ãæ€å®ïŒãåºæ¬æ å ±æè¡è 詊éšã§ããSQL ãèªãã§çµæãçããåé¡ãåºãŸãïŒåºé¡ç¯å²ã¯ãããããã®å ¬åŒã®æ¡å ã§ç¢ºèªããŠãã ããïŒã
ã©ã®å Žé¢ã§ãå·®ãã€ãã®ã¯ã次ã®2ç¹ã§ãã
- SQL ãèªãã§ãçµæãæ£ããèšãããïŒWHERE ãš HAVING ã®éããJOIN ã§è¡ãã©ã墿žããããNULL ã®æ±ã ãªã©ïŒ
- ããããåæããåã©ããã«æžãããïŒåææ¯ãç¶ç¶çããã¡ãã«ãäžäœNä»¶ãéè€ã®é€å» ãªã©ïŒ
ãã®ææã¯ãMathesis Press ã SQL ããããŒã¿åæã«å¿ èŠãªç¯å²ã«çµã£ãŠ1æ¬ã«ãŸãšãããã®ã§ããæžãæ¹ã®æèšã§ã¯ãªããããã®ã¯ãšãªã¯ãã©ã®é çªã§åããŠãäœè¡ã®çµæã«ãªããããé ã®äžã§è¿œãåãã€ãããŸãã
ãããªäººã«åããŠæžããŸãã
- ããŒã¿ãµã€ãšã³ãã£ã¹ãã»ããŒã¿ã¢ããªã¹ããç®æããŠããŠãã³ãŒãã£ã³ã°ãã¹ãã® SQL ãäžå®ãªå°±æŽ»çã»è»¢è·åžæè
- DSæ€å®ãåºæ¬æ å ±æè¡è 詊éšã® SQL ã®åé¡ãã確å®ã«åããã人
- SQL ããªããšãªãæžããããJOIN ãéèšã®çµæãåã£ãŠããã®ãèªä¿¡ããªã人
- çªé¢æ°ïŒãŠã£ã³ããŠé¢æ°ïŒã CTE ããå®åã§äœ¿ããããã«ãªããã人
ãã®ææã®3ã€ã®ç¹åŸŽ
- ãåãé çªãã§æŽçããæ§æïŒSQL ã¯ãæžãé çªãšåãé çªãéããŸããç« ããšã«ãèŠç¹ããšãééããããåºå¥ãã眮ããçµæãèªã¿åãåãã€ãããŸã
- ããŒãã«ã¯ç®æ¡æžãã§ãçµæã¯ãã¹ãŠå®è¡ããŠç¢ºèªïŒç·Žç¿åé¡ 60 åã®ããŒãã«ã¯ãçãç®æ¡æžãã§ç€ºããŸãããçµæã¯ãã¹ãŠ SQLite ã§å®éã«å®è¡ããŠç¢ºãããçãã1ã€ã«æ±ºãŸãããšãæ€ç®ããŠãããŸã
- å šåãªãªãžãã«ã»å šå解説ã€ãïŒå ¬åŒåé¡ãåžè²©ã®åé¡éã®æç« ã¯äœ¿ããããã¹ãŠæ°ããäœããŸããã解説ã§ã¯ãã»ãã®éžæè¢ãã©ããªèª€è§£ããäœãããããæžããŠããŸã
SQLããŒã¿åæã®æŠèŠ
SQL ã®è©Šéšãéžèã®åœ¢ã¯ã宿œããå£äœãäŒæ¥ã«ãã£ãŠéããŸããããã§ã¯ããã®ææãåæã«ããŠããããšããŸãšããŸãã
- æ±ãç¯å²ïŒSELECT ãš WHEREãéçŽïŒGROUP BYã»HAVINGïŒãJOINïŒå éšã»å€éšã»èªå·±çµåïŒããµãã¯ãšãªãš CTEïŒWITHïŒãCASE åŒãæ¥ä»ãNULLããŠã£ã³ããŠé¢æ°ããããŠåæã§ããåºãå
- 詊éšã»éžèã§ã®åºæ¹ïŒã³ãŒãã£ã³ã°ãã¹ãã§ã¯ãããŒãã«ãäžããŠã次ã®çµæã«ãªã SQL ãæžãããšæ±ãã圢åŒãå€ããDSæ€å®ãåºæ¬æ å ±æè¡è 詊éšã§ã¯ãSQL ãèªãã§çµæãæ£ããæžãæ¹ãéžã¶åœ¢åŒãäžå¿ã§ãã圢åŒã»æéã»åºé¡ç¯å²ã¯å€ããããšãããã®ã§ãæ¡å ã§å¿ ã確èªããŠãã ãã
- 䜿ã£ãç°å¢ïŒãã¹ãŠã® SQL 㯠SQLite 3.49 ã§å®è¡ããŠç¢ºãããŸãããæšæºçãªæžãæ¹ã¯ PostgreSQL ã MySQL ã§ãåãæå³ã§ããæ¥ä»ã®é¢æ°ãªã©ç°å¢ããšã«éãéšåã«ã¯ãïŒSQLite ã®æžãæ¹ïŒããšæžããä»ã®ç°å¢ã®æžãæ¹ãæ·»ããŸãã
- èªåã§è©ŠãïŒSQLite ã¯ç¡æã§ãPython ã®æšæºã©ã€ãã©ãªïŒsqlite3ïŒãã䜿ããŸããåé¡ã®ããŒãã«ãèªåã§äœã£ãŠç¢ºããããšãå®çãæ©ããªããŸã
å顿ã®ããŒãã«ã¯ãã- 1, æ±äº¬, 1200ãã®ããã«ã1è¡ãã€å€ã䞊ã¹ãŠç€ºããŸããå é ã«ããŒãã«åãšååãæžããŸããå€ã NULL ã®ãšããã¯ãNULLããšæžããŸãã
ç¡æãµã³ãã«ïŒéèšã®åïŒGROUP BYã»HAVINGã»NULLïŒïŒå®å šçïŒ
SQL ã®çµæãèªãåé¡ã§ãã¡ã°ãå€ãã®ããéèšã§ããææéšåãšåã圢ã§ã1ã€ã®åãäœéšããŠãã ããã
èŠç¹
- SQL ã¯ãæžããé ã§ã¯ãªããFROMïŒçµåïŒâ WHERE â GROUP BY â HAVING â SELECT â ORDER BY â LIMIT ã®é ã«åã
- WHERE ã¯éèšã®åã«ãè¡ãçµããHAVING ã¯éèšã®ããšã«ãã°ã«ãŒããçµããéèšé¢æ°ã¯ WHERE ã«ã¯æžããªã
COUNT(*)ã¯è¡ã®æ°ãCOUNT(å)㯠NULL ãé€ããæ°ãCOUNT(DISTINCT å)ã¯éè€ãé€ããæ°SUMã»AVGã»MAXã»MINã¯ãNULL ã®è¡ãç¡èŠããŠèšç®ãããNULL ã 0 ãšããŠæ±ãããã§ã¯ãªã
è§£ãæ¹ã®å
çµæãèªã¿åããšãã¯ã次ã®é ã«ã衚ãæå ã§å€åœ¢ããŠãããŸãã
- FROM ã®ããŒãã«ãé ã«æãïŒJOIN ãããã°ãçµååŸã®è¡šãæãïŒ
- WHERE ã®æ¡ä»¶ã«åããªãè¡ãæ¶ãïŒNULL ãšã®æ¯èŒã¯äžæã«ãªããæ¶ããïŒ
- GROUP BY ã®åãåãè¡ãã1ã€ã®ã°ã«ãŒãã«ãŸãšãã
- HAVING ã®æ¡ä»¶ã«åããªãã°ã«ãŒããæ¶ã
- SELECT ã®åŒïŒSUM ã COUNT ãªã©ïŒããã°ã«ãŒãããšã«èšç®ãã
- ORDER BY ã§äžŠã¹ãLIMIT ã§å é ããåã
次ã®è¡šã¯ãäŸé¡ 1ã3 ã§äœ¿ãããŒãã« sales ã§ããamount ã NULL ã®è¡ïŒid 6ïŒã1ã€ãããŸãã
ããŒãã« salesïŒåïŒid, shop, amountïŒ
- 1, Aåº, 500
- 2, Båº, 300
- 3, Aåº, 200
- 4, Cåº, 900
- 5, Båº, 100
- 6, Cåº, NULL
äŸé¡
äŸé¡1 äžã®ããŒãã« sales ã«ã€ããŠèããã
次㮠SQL ãå®è¡ããçµæã¯ã©ããã
SELECT shop, SUM(amount) FROM sales WHERE amount >= 300 GROUP BY shop HAVING SUM(amount) >= 500 ORDER BY SUM(amount) DESC;
- 㢠Cåº, 900 / Aåº, 700
- 〠Cåº, 900 / Aåº, 500 / Båº, 300
- ㊠Cåº, 900 / Aåº, 500
- ãš Cåº, 900
çãïŒãŠ
解説ïŒå®è¡ã®é çªã©ããã«èããŸãããŸã WHERE ã§ãéé¡ã 300 以äžã®è¡ã ãã«çµããŸããæ®ãã®ã¯ Aåºã® 500ãBåºã® 300ãCåºã® 900 ã§ãïŒNULL ã®è¡ã¯æ¡ä»¶ãäžæã«ãªãèœã¡ãŸãïŒã次ã«åºããšã«åèšããAåº 500ãBåº 300ãCåº 900 ã«ãªããŸããHAVING ã§åèšã 500 以äžã®ã°ã«ãŒãã ããæ®ããšãAåºãš Cåºã§ããæåŸã«åèšã®å€§ããé ã«äžŠã¹ãŠãCåºãAåºã§ããã¢ã¯ WHERE ãé£ã°ã㊠Aåºã® 200 ãŸã§è¶³ããçµæãã€ã¯ HAVING ãé£ã°ã㊠Båºãæ®ã£ãçµæããšã¯ã500 ãã倧ãããã«ã㊠Aåºãèœãšããçµæã§ãã
äŸé¡2 äžã®ããŒãã« sales ã«ã€ããŠèããã
次㮠SQL ãå®è¡ããçµæã¯ã©ããã
SELECT COUNT(*), COUNT(amount), AVG(amount) FROM sales;
- 㢠6, 5, 400
- 〠6, 6, 333.3
- ㊠5, 5, 400
- ãš 6, 5, 333.3
çãïŒã¢
解説ïŒCOUNT(*) 㯠NULL ã®è¡ãå«ããè¡æ°ãªã®ã§6ã§ããCOUNT(amount) 㯠NULL ãé€ãã®ã§5ã§ããAVG(amount) ã NULL ã®è¡ãé€ãã(500 + 300 + 200 + 900 + 100) ÷ 5 = 400 ã§ããNULL ã 0 ç¹ã®ããã«æ±ã£ã 2,000 ÷ 6 = 333.3 ã«ã¯ãªããŸãããã€ã¯ COUNT(amount) ã6ã ãšæããAVG ã NULL ãå«ããçµæããŠã¯ COUNT(*) ã5ã ãšæã£ãçµæããšã¯ AVG ã ã NULL ãå«ããçµæã§ãã
äŸé¡3 äžã®ããŒãã« sales ã«ã€ããŠèããã
åºããšã®å£²äžã®åèšã 600 以äžã®åºãæ±ããããçµæãæ¬¡ã®ããã«ãªã SQL ã¯ã©ããïŒè¡ã®äžŠã³é ã¯åããªãïŒã
æ±ããçµæïŒ
- Aåº
- Cåº
éžæè¢ïŒ
- ã¢
SELECT shop FROM sales WHERE SUM(amount) >= 600 GROUP BY shop; - ã€
SELECT shop FROM sales WHERE amount >= 600 GROUP BY shop; - ãŠ
SELECT shop FROM sales GROUP BY shop HAVING COUNT(*) >= 600; - ãš
SELECT shop FROM sales GROUP BY shop HAVING SUM(amount) >= 600;
çãïŒãš
解説ïŒåºããšã®åèšã¯ Aåº 700ãBåº 400ãCåº 900 ãªã®ã§ã600 以äžã¯ Aåºãš Cåºã§ããåèšãžã®æ¡ä»¶ã¯ãéèšã®ããšã«åã HAVING ã«æžããŸããã¢ã¯ WHERE ã«éèšé¢æ°ãæžããã®ã§ãšã©ãŒã§ããã€ã¯1è¡ããšã®éé¡ã 600 以äžããèŠãã®ã§ãCåºã ãã«ãªããŸãããŠã¯åèšã§ã¯ãªãä»¶æ°ã 600 以äžããèŠãã®ã§ãã©ã®åºãåãã0è¡ã§ãã
ããããééã
- éèšã®çµæãžã®æ¡ä»¶ã WHERE ã«æžããŠããŸãïŒ
WHERE SUM(amount) >= 600ã¯ãšã©ãŒãHAVING ã䜿ãïŒ COUNT(*)ãšCOUNT(å)ãåããã®ã ãšæãïŒNULL ããããšæ°ãå€ããïŒAVGã NULL ã 0 ãšããŠèšç®ãããšæãïŒNULL ã®è¡ã¯åæ¯ã«ãå ¥ããªãïŒ- éèšã®åã«çµãæ¡ä»¶ïŒWHEREïŒãšãéèšã®åŸã«çµãæ¡ä»¶ïŒHAVINGïŒãåãéããŠãåèšã®å€ãå€ãã
ææéšåã§ã¯ããã®ããš8ã€ã®ç« ã§ãSELECT ããçªé¢æ°ãåæã®åãŸã§ãåã圢ã§è§£èª¬ããç·Žç¿åé¡ 60 åã§ä»äžããŸãã
ææéšåã®ç®æ¬¡
- 第1ç« SELECTã»WHEREã»äžŠã¹æ¿ãå1ãå6
- 第2ç« éçŽïŒGROUP BYã»HAVINGå7ãå14
- 第3ç« JOINïŒå éšã»å€éšã»èªå·±çµåå15ãå23
- 第4ç« ãµãã¯ãšãªã»CTEã»éåæŒç®ïŒ»å24ãå30
- 第5ç« CASE åŒãš NULL ã®èœãšã穎å31ãå38
- 第6ç« æ¥ä»ã®æ±ãå39ãå43
- 第7ç« ãŠã£ã³ããŠé¢æ°ïŒ»å44ãå52
- 第8ç« åæã®åïŒåææ¯ã»ç¶ç¶çã»ãã¡ãã«ã»äžäœNä»¶ã»éè€ã®é€å»ïŒ»å53ãå60
- ç·Žç¿åé¡ 60 åïŒå šåã«çããšè§£èª¬ïŒãçŽåãã§ãã¯ã·ãŒããåŠç¿ã¹ã±ãžã¥ãŒã«äŸãæ¬çªã®é²ãæ¹