集約 (集�関数)

ã?“ã?®ç¯€ã?¯é›†ç´„(ANSI SQL-92ã?§ã?¯é›†å?ˆé–¢æ•°ã€?データベースã?®æ›¸ç±?ã?«ã‚ˆã?£ã?¦ã?¯åˆ—関数ã?¨å‘¼ã?³ã?¾ã?™ã€‚)ã?«ã?¤ã?„ã?¦èª¬æ˜Žã?—ã?¾ã?™ã€‚集約ã?¯ã€?行ã?®é›†å?ˆã?«å¯¾ã?—ã?¦å¼?を評価ã?™ã‚‹æ–¹æ³•ã‚’æ??ä¾›ã?—ã?¾ã?™ã€‚ä»–ã?®çµ„è¾¼ã?¿é–¢æ•°ã?¯å?˜å¼?ã?«å¯¾ã?—ã?¦ä½œç”¨ã?—ã?¾ã?™ã?Œã€?集約ã?¯å€¤ã?®é›†å?ˆã?«å¯¾ã?—ã?¦ä½œç”¨ã?—ã?¦ä¸€ã?¤ã?®ã‚¹ã‚«ãƒ©ãƒ¼å€¤ã?«ã?¾ã?¨ã‚?られã?¾ã?™ã€‚組込ã?¿ã?®é›†ç´„ã?«ã‚ˆã‚Šã€?値ã?®é›†å?ˆã?«å¯¾ã?—ã?¦ã€?å¼?ã?®æœ€å°?値ã€?最大値ã€?å?ˆè¨ˆã€?計数を行ã?„ã€?例ã?ˆã?°è¡Œã‚’æ•°ã?ˆã‚‹ã‚ˆã?†ã?ªäº‹ã?Œã?§ã??ã?¾ã?™ã€‚ã?¾ã?Ÿçµ„è¾¼ã?¿ã?®ã‚‚ã?®ã?¨ã?¯åˆ¥ã?«é›†ç´„を作ã?£ã?¦ã€?例ã?ˆã?°æ¨™æº–å??差を計算ã?™ã‚‹ã‚ˆã?†ã?ªã?“ã?¨ã‚‚ã?§ã??ã?¾ã?™ã€‚

組込ã?¿ã?®é›†ç´„ã?¯ã€?組込ã?¿ã?®é›†ç´„ã?«ã?¦å?¯èƒ½ã?ªãƒ‡ãƒ¼ã‚¿åž‹ã?«ã?‚るデータ型ã?«ä½œç”¨ã?•ã?›ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚
表 1. 組込��集約���能�データ型
  全データ型 組込ã?¿ã?®æ•°å€¤åž‹
COUNT X X
MIN   X
MAX   X
AVG   X
SUM   X
集約ã?¯ä»¥ä¸‹ã?®æ–‡è„ˆã?«ã?®ã?¿ç½®ã??ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚
  • é?¸æŠžå¼?ã?®é?¸æŠžé …ç›®
  • HAVING節
  • 集約ã?Œã??ã?®å•?å?ˆã?›ã?«ã?‚ã‚‹ã?ªã‚‰ã?°ã€?ORDER BY 節(別å??を使ã?†)ã?¤ã?¾ã‚Šã€?é?¸æŠžå¼?ã?®é?¸æŠžé …ç›®ã?«é›†ç´„ã?Œã?‚ã‚‹å ´å?ˆã?«é™?りã€?集約ã?¸ã?®åˆ¥å??ã‚’ORDER BY 節ã?«ç½®ã??ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚
é?¸æŠžå¼?ã?®é?¸æŠžé …ç›®ã?«ã?‚ã‚‹å…¨å¼?ã?¯ã€?集約ã?‹åˆ†é¡žåˆ—ã?§ã?‚ã‚‹å¿…è¦?ã?Œã?‚りã?¾ã?™ã€‚(GROUP BY 節をå?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。) (GROUP BY 節ã?Œã?ªã?„ã?«ã‚‚ã?‹ã?‹ã‚?らã?šã€?HAVING節ã?Œã?‚ã‚‹å ´å?ˆã‚‚å?Œæ§˜ã?§ã?™ã€‚) ã?ªã?œã?ªã‚‰ã?°ã€?é?¸æŠžå¼?ã?®ResultSetã?¯ã€?スカラー(å?˜å€¤)ã?‹ãƒ™ã‚¯ãƒˆãƒ«(副値)ã?®ã?„ã?šã‚Œã?‹ã?§ã?ªã?‘れã?°ã?ªã‚‰ã?šã€?ã??ã?®æ··å?ˆã?¯è¨±ã?•れã?¦ã?„ã?ªã?„ã?‹ã‚‰ã?§ã?™ã€‚(集約ã?¯ã‚¹ã‚«ãƒ©ãƒ¼ã?®å€¤ã?¨è©•価ã?•れã€?列ã?¸ã?®å?‚ç…§ã?¯ãƒ™ã‚¯ãƒˆãƒ«ã?®å€¤ã?¨è©•価ã?•れã?¾ã?™ã€‚)例ã?ˆã?°æ¬¡ã?®å•?å?ˆã?›ã?¯ã‚¹ã‚«ãƒ©ãƒ¼ã?¨ãƒ™ã‚¯ãƒˆãƒ«ã?®å€¤ã?Œæ··ã?–ã?£ã?¦ã?„ã‚‹ã?®ã?§ç„¡åйã?§ã?™ã€‚
-- not valid
SELECT MIN(flying_time), flight_id
FROM Flights
集約ã?«ã?¦å¤–部ã?¸ã?®å?‚ç…§(相関)ã?¯ç„¡åйã?§ã?™ã€‚ã?¤ã?¾ã‚Šã€?副å•?å?ˆã?›ã?Œé›†ç´„ã‚’å?«ã‚€ã?ªã‚‰ã?°ã€?ã??ã?®é›†ç´„ã?Œä½œç”¨ã?™ã‚‹å¼?ã?¯ã€?副å•?å?ˆã?›ã?®å¤–ã?«ã?‚ã‚‹å•?å?ˆã?›ã?®åˆ—ã‚’å?‚ç…§ã?§ã??ã?¾ã?›ã‚“。 例ã?ˆã?°ã€?SUM演算å­?ã?Œå¤–ã?®å•?å?ˆã?›ã?®åˆ—ã?«å¯¾ã?—ã?¦è¡Œã‚?れã?¦ã?„ã‚‹ã?®ã?§ã€?次ã?®å•?å?ˆã?›ã?¯æœ‰åйã?§ã?¯ã?‚りã?¾ã?›ã‚“。
SELECT c1
FROM t1
GROUP BY c1
HAVING c2 >
    (SELECT t2.x
    FROM t2
    WHERE t2.y = SUM(t1.c3)) 

集約ã?Œå¤–ã?®å•?å?ˆã?›ã?«ã?‚ã‚‹å ´å?ˆã€?ã??ã?®ResultSetã?®ã‚«ãƒ¼ã‚½ãƒ«ã?¯æ›´æ–°å?¯èƒ½ã?§ã?¯ã?‚りã?¾ã?›ã‚“。

��節��以下�集約��り��。