集約関数

57343 ワード
テキストリンク

💫 集約関数

  • 所与のデータを特定の列ごとにグループ化し、各グループの「合計」(SUM)、「平均」(AVG)、「最大値」(MAX)、「最小値」(MIN)、「カウント」(COUNT)の関数
    • を返す.
  • SELECTセクションで集約関数と通常(または式)を同時に使用する場合は、GROUPセクション:
    • を記述する必要があります.
  • SELECT節で集約関数のみを使用する場合、GROUP BY節
    • をスキップする.
    (사용형식)
SELECT 컬럼list
FROM 테이블명
[WHERE 조건]
[GROUP BY 컬럼[,컬럼,..]]
[HAVING 조건]
[ORDER BY 컬럼|컬럼인덱스 [ASC|DESC],...];

- ORDER BY 절에 기술하는 컬럼은 SELECT절에 사용된 컬럼 
	또는 해당 테이블에서 그룹으로 나누기 위해 필요한 컬럼
- 집계함수가 사용되면 다중행결과 반환
- HAVING 조건 : 집계함수에 조건이 부여된 경우 기술

    EX 1)従業員テーブルに格納されている従業員数の表示

    SELECT COUNT(*) FROM HR.EMPLOYEES

    EX 2）「従業員」テーブルで各部門の従業員数を表示する

      SELECT DEPARTMENT_ID,
         COUNT(*)
    FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;

    EX 3）社員表において各部門の社員数が10人を超える部門を表示する

    	SELECT DEPARTMENT_ID,
         COUNT(*)
    FROM HR.EMPLOYEES
GROUP BY  DEPARTMENT_ID
  HAVING COUNT(*)>10
ORDER BY 1;

    HAVINGではなくWHEREを書くと間違います!

    💫 1) SUM

  • 各グループで特定の列の総和を求めて戻る.
    • (사용형식)
SUM(expr)

    (EX 1)社員表で各部門の給与合計を求めてください。

    SELECT DEPARTMENT_ID AS 부서,
SUM(SALARY) AS "급여 합계"
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;

    (EX 2)購入票にて、2005年2月の各商品の購入数量合計と購入金額合計を求める

    SELECT BUY_PROD AS 상품코드,
SUM(BUY_QTY) AS "매입수량 합계",
SUM(BUY_QTY*BUY_COST) AS "매입금액 합계"
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050201') AND LAST_DAY('20050201')
GROUP BY BUY_PROD
ORDER BY 1;

    (EX 3)毎月の商品の購入数量合計と購入金額合計を2005年度の購入資料で調べてください。

    SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월,
BUY_PROD AS 상품코드,
SUM(BUY_QTY) AS "매입수량 합계",
SUM(BUY_QTY*BUY_COST) AS "매입금액 합계"
FROM BUYPROD
WHERE EXTRACT(YEAR FROM BUY_DATE) = 2005
GROUP BY EXTRACT(MONTH FROM BUY_DATE), BUY_PROD
ORDER BY 1;

    (EX 4)メンバーズシートで性別ポイント合計を探してください。

    SELECT CASE WHEN SUBSTR(MEM_REGNO2,1,1)='1' OR
SUBSTR(MEM_REGNO2,1,1)='3' THEN '남성' ELSE '여성' END AS 구분,
SUM(MEM_MILEAGE) AS "마일리지 합계"
FROM MEMBER
GROUP BY CASE WHEN SUBSTR(MEM_REGNO2,1,1)='1' OR
SUBSTR(MEM_REGNO2,1,1)='3' THEN '남성' ELSE '여성' END

    (EX 5)2005年4月の各会員の購入数合計を販売表で求めてください。

    SELECT CART_MEMBER AS 회원,
SUM(CART_QTY) AS 구매수량합계
FROM CART
WHERE SUBSTR(CART_NO,1,6)='200504'
GROUP BY CART_MEMBER

    (EX 6)販売フォームに2005年4月の会員様にて購入数合計を行い、購入数に準じて上位5名までの情報を印刷してください


    サブクエリを使用します。
    SELECT A.CART_MEMBER AS 회원번호,
A.SAMT AS 구매수량합계
FROM (SELECT CART_MEMBER,
SUM(CART_QTY) AS SAMT
FROM CART
WHERE CART_NO LIKE '200504%'
GROUP BY CART_MEMBER
ORDER BY 2 DESC) A
WHERE ROWNUM<=5;

    (EX 7)2005年1~3月の各商品の購入金額合計で購入金額が500万ウォン以上の商品のみ検索します。

    SELECT BUY_PROD AS 상품코드,
SUM(BUY_QTY*BUY_COST) AS 매입금액합계
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND TO_DATE('20050331')
GROUP BY BUY_PROD
HAVING SUM(BUY_QTY*BUY_COST) >= 5000000
ORDER BY 1;

    💫 等量(WINDOWS関数)-SELECTセクションでのみ使用

  • RANK()OVER、DENSE RANK()OVER、ROW NUMBER()OVER提供
  • RANK()OVER関数のPARTION BYセクションを使用して、グループ別ピアツーピアライセンス
    • を実装する.
    各
  • 組の最小最大値はKEEP()関数とDENSE RANK()関数を用いて
    • を実現する.

    💫 1) RANK() OVER

  • の一般的な等計数出力(同じ値は同じ等計数を有し、次の等計数は同じ値の個計数のみ)
    スキップされた値を指定します(ex:1,2,2,5,6...)
    • (사용형식)
RANK() OVER(ORDER BY 컬럼명 [ASC|DESC][,컬럼명 [ASC|DESC],...])

    (EX 1)従業員表で給与順に並べ替えてください


    Aliasには、従業員番号、従業員名、部門コード、報酬、ランキングが含まれています.
    SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
SALARY AS 급여,
RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM HR.EMPLOYEES;

    (EX 2)社員表に給与順に並べて、同じ給与であれば入社日


    迅速な従業員ランキング
    Alias는 사원번호, 사원명, 부서코드, 급여, 순위
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
HIRE_DATE AS 입사일,
SALARY AS 급여,
RANK() OVER(ORDER BY SALARY DESC) AS 순위1,
RANK() OVER(ORDER BY SALARY DESC, HIRE_DATE ASC) AS 순위2
FROM HR.EMPLOYEES;

    (EX 3)2005年商品購入数順


    Aliasは商品番号、商品名、購入数、ランキングです
    SELECT A.BUY_PROD AS 상품번호,
B.PROD_NAME AS 상품명,
SUM(BUY_QTY) AS 매입수량,
RANK() OVER(ORDER BY SUM(BUY_QTY) DESC) AS 순위
FROM BUYPROD A, PROD B
WHERE A.BUY_PROD = B.PROD_ID --조인조건
AND EXTRACT(YEAR FROM BUY_DATE) = 2005
GROUP BY A.BUY_PROD, B.PROD_NAME;
    グループ別ランキング:PARTIONBYセクション<<1つのグループに複数の非使用を含める

    💫 2) PARTITION BY

  • 「PARTIONBYコラム名」は「GROUP BYコラム名」
    (사용형식)
RANK() OVER (PARTITION BY 컬럼명[,컬럼명,...] ORDER BY 컬럼명 [ASC|DESC][,컬럼명 [ASC|DESC],...])

    (EX 1)従業員表において、部門別従業員の給与順


    Aliasには、従業員番号、従業員名、部門コード、報酬、ランキングが含まれています.
    SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
SALARY AS 급여,
RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS 순위
FROM HR.EMPLOYEES;

    💫 3）グループごとに最小値、最大値を求める

  • KEEP()関数およびFIRST、LAST識別子
    • を使用する.
  • DENSE RANK関数のみ
    • を使用できます.
    (사용형식)
MIN(컬럼명)|MAX(컬럼명) KEEP(DENSE_RANK FIRST|LAST ORDER BY 컬럼명 [ASC|DESC])
OVER(PARTITION BY 컬럼명)

    (EX 1)社員テーブルで社員の給与を照会するが、社員の所属部署の


    最高賃金と最低賃金を調べてください.
    Aliasは、従業員番号、従業員名、部門コード、給与、最低給与、最高給与を提供します.
    SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
DEPARTMENT_ID AS 부서코드,
SALARY AS 급여,
MIN(SALARY) KEEP(DENSE_RANK FIRST ORDER BY SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS 부서최소급여,
MAX(SALARY) KEEP(DENSE_RANK LAST ORDER BY SALARY) OVER(PARTITION BY DEPARTMENT_ID) AS 부서최대급여
FROM HR.EMPLOYEES
ORDER BY 3, 4;

    💫 DENSE_RANK() OVER

  • 並べ替え方法はRANK()OVER関数と異なり、その他の特徴は同じ
    • である.
  • 同じ値に同じソートを与えますが、順序は順序でソートされます(ex:1,2,2,3,4,5...)

    • 💫 ROW_NUMBER() OVER


    順序付け
  • の方法はRANK()ONER関数またはDENSE RANK()とは異なり、他の特徴は同じ
    • である.
  • 同じ値も順次増加します(たとえば、値:10、20、20、20、30、40、50...)
    ランキング:1、2、3、4、5、6、7、...)

    • 💫 2) AVG


    は、
  • グループの平均値
    • を返します.
    (사용형식)
AVG(expr)

    (EX 1)商品表でカテゴリ別に平均購入価格を調べてください

      SELECT PROD_LGU AS 분류코드, 
         AVG(PROD_COST) AS "평균 매입가"
    FROM PROD
GROUP BY PROD_LGU
ORDER BY 1;

    小数点が切り捨てられたり、四捨五入されたりします.
    ROUND( AVG(PROD_COST)) AS "평균 매입가"

    （EX 2）各部署の平均勤続年数を社員表に照会してください

    SELECT DEPARTMENT_ID AS 부서코드, 
        AVG(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE)) AS "평균 근속년수"
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;

    小数第一位四捨五入
    ROUND(AVG(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM HIRE_DATE)),1) AS "평균 근속년수"

    (EX 3)各部署の平均給与を社員表で照会してください

    SELECT  DEPARTMENT_ID AS 부서, 
        ROUND(AVG(SALARY)) AS 평균급여
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;

    (EX 4)会員表で年齢別平均ポイントを求めてください

    SELECT TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)||'대' AS 연령대, 
        ROUND(AVG(MEM_MILEAGE))  AS "평균 마일리지"
FROM MEMBER
GROUP BY TRUNC(EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR),-1)
ORDER BY 1;

    💫 3) COUNT

  • グループのデータ(行数)
    • を返します.
    COUNT(컬럼명|*)
- '컬럼명|*' : 외부조인(OUTER JOIN)을 제외하고 '컬럼명'이나 '*'는 차이가 없음
- 외부조인인 경우 '*'을 사용하면 자료가 없어도 '1'을 출력함(NULL값을 갖는 행이 존재하기 때문)
따라서 외부조인에서 COUNT함수 사용시 '컬럼명'을 기본키 컬럼으로 기술하는것이 안전하다

    (EX 1)各部署の人数を従業員表で照会してください

    SELECT DEPARTMENT_ID AS 부서코드, 
        COUNT(*) AS 인원수1,
        COUNT(EMP_NAME) AS 인원수2,
        COUNT(DEPARTMENT_ID) AS 인원수3
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 1;

    3万人
    部門コードがNULLなのは1人だけなので数字が違います.

    金伯利湾NULL

    同様にCOMMISSION PCTもNULL値が多いので、これを基準にするともっと悪いです

    (EX 2)HRアカウント会社の全部門の人員数を調べてください

  • 社員表で使用する部門
    • SELECT DISTINCT DEPARTMENT_ID
FROM HR.EMPLOYEES
ORDER BY 1;
  • 部門表の部門
    • SELECT DISTINCT DEPARTMENT_ID
FROM HR.DEPARTMENTS
ORDER BY 1;

    部門表のDEPARTMENT IDはプライマリ・キーであるため、重複値またはNULL値を持つことはできません
    お寺のテーブルに外来キーNULLがついているので出来ます!
    照会
  • HRアカウント会社の全部門人員数
    • SELECT DISTINCT DEPARTMENT_ID AS 부서코드,
        COUNT (*) AS 인원수1,
        COUNT (A.EMP_NAME) AS 인원수2
FROM HR.EMPLOYEES A, HR.DEPARTMENTS B
WHERE A.DEPARTMENT_ID(+)=B.DEPARTMENT_ID
GROUP BY B.DEPARTMENT_ID
ORDER BY 1;
    ターゲットとなるテーブルの基本キーを書き出します外部結合...
    でもどうして帰らないの.

    (EX 3)各部署の人数が5より多い部署を社員表に問い合わせる

    		SELECT DEPARTMENT_ID AS 부서코드,
					 COUNT(*) AS 인원수
			FROM HR.EMPLOYEES
	GROUP BY DEPARTMENT_ID
		HAVING COUNT(*)>=5
	ORDER BY 1;

    グループ関数に条件がある場合はHAVINGセクションを使用します

    (EX 4)商品表でカテゴリ別に商品数を調べてください

    SELECT PROD_LGU AS 분류코드, 
        COUNT(*) AS 상품수
FROM PROD
GROUP BY PROD_LGU
ORDER BY 1;

    (EX 5)毎月の購入回数は購入フォームにてお問い合わせください

    SELECT EXTRACT(MONTH FROM BUY_DATE) AS 월,
        COUNT(BUY_QTY) AS 매입건수
FROM BUYPROD
GROUP BY EXTRACT(MONTH FROM BUY_DATE)
ORDER BY 1;

    （EX 6）毎月の販売件数が10以上の販売情報のみを販売表に照会してください

    SELECT  SUBSTR(CART_NO,5,2) AS 월, 
        COUNT(CART_QTY) AS 매출건수
FROM CART
WHERE CART_NO LIKE '2005%'
GROUP BY SUBSTR(CART_NO,5,2)
HAVING COUNT(CART_QTY)>=10
ORDER BY 1;
    SUBSTR(CART NO,5,2):5文字目から2文字を出す
    🚨 これは間違いの原因です:同じ日、一人でたくさんのものを買って、一つとして処理したいと思っていましたが、複数として処理されました>>DISTINCTで繰り返し解決しましょう.
    SELECT  SUBSTR(A.CNO,5,2)||'월' AS 월, 
        COUNT(*) AS 매출건수
FROM (SELECT DISTINCT CART_NO AS CNO 
                FROM CART) A
WHERE SUBSTR(A.CNO,1,4) = '2005'
GROUP BY SUBSTR(A.CNO,5,2)
HAVING COUNT(*)>=10
ORDER BY 1;

    💫 4) MAX, MIN

  • は、最大(MAX)、最小(MIN)の値
    • を返す.
    (사용형식)
MAX(expr) / MIN(expr)
    🚨 集約関数は繰り返し使用できないので、注意して解いてみてください!

    EX 1)各部門の最高賃金と最低賃金を従業員表に照会してください。

    SELECT DEPARTMENT_ID AS 부서코드, 
EMP_NAME AS 사원명,
MAX(SALARY) AS 최대급여, 
MAX(SALARY) AS 최소급여
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID, EMP_NAME
ORDER 1;
    どうして帰らないの
    これは私たちが探しているテーブルではありません.部署別に名前を書くべきで、自分の給料が高いか低いか書いてあるはずですが・・・
    SELECT B.EMPLOYEE_ID AS 사원번호,
        B.EMP_NAME AS 사원명,
        A.DEPARTMENT_ID AS 부서코드,
        A.MSAL AS 급여
FROM (SELECT DEPARTMENT_ID,
MAX(SALARY) AS MSAL 
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID) A,
HR.EMPLOYEES B
WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
AND B.SALARY = A.MSAL
ORDER BY 3;

    EX 2)各部署の最高給与と最低給与の従業員番号、従業員名、部門コード、給与を従業員表に表示してください

    	SELECT  B.EMPLOYEE_ID AS 사원번호,
	        B.EMP_NAME AS 사원명,
	        A.DEPARTMENT_ID AS 부서코드,
	        A.MSAL AS 급여
	  FROM (SELECT DEPARTMENT_ID,
	         MAX(SALARY) AS MSAL 
	  FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID) A,
					HR.EMPLOYEES B
   WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
					AND B.SALARY = A.MSAL
ORDER BY 3;

    EX 3)2005年5月に最大購入(金額基準)した会員の会員番号、会員名、購入金額を照会

  • 72005年5月メンバー別購入合計
    • SELECT A.CART_MEMBER,
        SUM(A.CART_QTY*B.PROD_PRICE) AS ASUM
    FROM CART A, PROD B
   WHERE A.CART_PROD = B.PROD_ID
  AND CART_NO LIKE '200505%'
  GROUP BY A.CART_MEMBER
    クエリ結果
  • "1"のASUM値の最大値
    • SELECT F.MEM_ID AS 회원번호,
  F.MEM_NAME AS 회원명,
  D.MCASUM AS 구매금액
  
  FROM (SELECT MAX(C.ASUM) AS MCASUM
     FROM(SELECT A.CART_MEMBER AS ACID,
              SUM(A.CART_QTY*B.PROD_PRICE) AS ASUM
            FROM CART A, PROD B
            WHERE A.CART_PROD = B.PROD_ID
             AND CART_NO LIKE '200505%'
            GROUP BY A.CART_MEMBER) C) D,
  (SELECT A.CART_MEMBER AS ACID,
        SUM(A.CART_QTY*B.PROD_PRICE) ASUM
  FROM CART A, PROD B
  WHERE A.CART_PROD = B.PROD_ID
  AND CART_NO LIKE '200505%'
  GROUP BY A.CART_MEMBER) E,
  MEMBER F
  WHERE D.MCASUM = E.ASUM
  AND F.MEM_ID = E.ACID;
    REDIS-コマンド
    asp.Netmvcアップロード画像