[SQL]SQL使用グループ関数

40473 ワード

sql 文法の整理資格テキストリンク

この投稿では、ファストパスキャンパスの「データベースとSQL合格チャネルオンライン」コースをまとめました.
グループ関数

組の関数を用いて,特定の集合の小計,中継,合計,総和を求めることができる.

グループ関数のタイプ

タイプ説明ROLLUP-グループ間の小計を計算します.ROLLUP関数では、SUBTOTALを生成するためにパラメータとして指定されたGROUPING色が使用されます.-GROUPING色数がNの場合、N+1のSUBTOTALが生成されるROLLUP関数ではパラメータの順序が変わり、結果も変わります.(ROLLUPは階層構造)CUBE-多次元小計を計算する機能.すべての連結可能な値の多次元統計を作成します.CUBE関数にN個の列がある場合、2のN乗のSUBTOTALが生成される.-これはシステムに大きな負担をもたらすため、使用状況に注意する必要があります.GROUPING SETS:特定項目の小計を計算する機能

グループ関数-GroupBY

SQL文

1722019年標準部門コード、部門名、従業員数、年俸合計、平均年俸を部門コード別に集計し、部門コード別にソートします.

SELECT A.DEPT_CD "부서코드"
   , (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS "부서명"
   , COUNT(*) AS "직원수"
   , TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
   , TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"  
FROM TB_EMP A 
   , (
      SELECT B.EMP_NO
           , SUM(B.PAY_AMT) AS "연봉"               
        FROM TB_SAL_HIS B 
       WHERE B.PAY_DE  BETWEEN '20190101' AND '20191231'
       GROUP BY B.EMP_NO 
       ORDER BY B.EMP_NO
     ) B 
WHERE A.EMP_NO = B.EMP_NO 
GROUP BY A.DEPT_CD
ORDER BY A.DEPT_CD
;

グループ関数-ROLUP

ROLLUP機能により、クエリーする合計だけでなく、ROLLUP関数GROUPINGパラメータであるDEPT CDのSUBTOTALもクエリーできます.

SELECT A.DEPT_CD "부서코드"
   , (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS "부서명"
   , COUNT(*) AS "직원수"
   , TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
   , TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"  
FROM TB_EMP A 
   , (
      SELECT B.EMP_NO
           , SUM(B.PAY_AMT) AS "연봉"               
        FROM TB_SAL_HIS B 
       WHERE B.PAY_DE  BETWEEN '20190101' AND '20191231'
       GROUP BY B.EMP_NO 
       ORDER BY B.EMP_NO
     ) B 
WHERE A.EMP_NO = B.EMP_NO 
GROUP BY ROLLUP(A.DEPT_CD)
ORDER BY A.DEPT_CD ;

グループ関数-ROLLUPパラメータ値の追加

ROLLUPパラメータ値にSEX CD(性別コード)を追加します.

ROLLUPは、パラメータ値がNの場合にN+1個のSUBTOTALを生成する.

の場合、部門のSUBTOTAL、性別のSUBTOTAL、全体のSUBTOTALの計3つのSUBTOTAL値を問い合わせることができます.

SELECT A.DEPT_CD "부서코드"
   , (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS "부서명"
   , A.SEX_CD AS "성별코드"
   , COUNT(*) AS "직원수"
   , TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
   , TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"  
FROM TB_EMP A 
   , (
      SELECT B.EMP_NO
           , SUM(B.PAY_AMT) AS "연봉"               
        FROM TB_SAL_HIS B 
       WHERE B.PAY_DE  BETWEEN '20190101' AND '20191231'
       GROUP BY B.EMP_NO 
       ORDER BY B.EMP_NO
     ) B 
WHERE A.EMP_NO = B.EMP_NO 
GROUP BY ROLLUP(A.DEPT_CD,A.SEX_CD)
ORDER BY A.DEPT_CD, A.SEX_CD
;

グループ関数-GROUPING関数

カラムが統計に使用される場合は0を返し、そうでない場合は1を返します.

GROUPING(A.SEX CD)WHEN 1 THEN「すべての性別」:つまりSEX CD列が統計に用いられる場合は「すべての性別」

と表記する.

SELECT CASE GROUPING(A.DEPT_CD) WHEN 1 THEN '모든부서' ELSE A.DEPT_CD END AS "부서코드"
   , (SELECT L.DEPT_NM FROM TB_DEPT L WHERE L.DEPT_CD = A.DEPT_CD) AS "부서명"
   , CASE GROUPING(A.SEX_CD) WHEN 1 THEN '모든성별' ELSE A.SEX_CD END AS "성별코드"
   , COUNT(*) AS "직원수"
   , TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
   , TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"  
FROM TB_EMP A 
   , (
      SELECT B.EMP_NO
           , SUM(B.PAY_AMT) AS "연봉"               
        FROM TB_SAL_HIS B 
       WHERE B.PAY_DE  BETWEEN '20190101' AND '20191231'
       GROUP BY B.EMP_NO 
       ORDER BY B.EMP_NO
     ) B 
WHERE A.EMP_NO = B.EMP_NO 
GROUP BY ROLLUP(A.DEPT_CD,A.SEX_CD)
ORDER BY A.DEPT_CD, A.SEX_CD
;

グループ関数-CUBE関数

CUBE関数を用いることで多次元統計を求めることができる.

合計

SELECT A.DEPT_CD "부서코드"
   , (SELECT L.DEPT_NM 
        FROM TB_DEPT L 
        WHERE L.DEPT_CD = A.DEPT_CD) AS "부서명"
   , A.SEX_CD AS "성별코드"
   , COUNT(*) AS "직원수"
   , TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
   , TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"  
FROM TB_EMP A 
   , (
      SELECT B.EMP_NO
           , SUM(B.PAY_AMT) AS "연봉"               
        FROM TB_SAL_HIS B 
       WHERE B.PAY_DE  BETWEEN '20190101' AND '20191231'
       GROUP BY B.EMP_NO 
       ORDER BY B.EMP_NO
     ) B 
WHERE A.EMP_NO = B.EMP_NO 
GROUP BY CUBE(A.DEPT_CD, A.SEX_CD)
ORDER BY A.DEPT_CD ;

グループ関数-UNIOALLとGROUP BY

SQL文

は、

ユニオンALLおよびGROUP BY出力部門、性別、人員および年俸の合計、および平均年俸

を通過する.

SELECT A.DEPT_CD "부서코드", '모든성별' AS "성별코드"   
   , COUNT(*) AS "직원수"
   , TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
   , TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"  
FROM TB_EMP A 
   , (
      SELECT B.EMP_NO
           , SUM(B.PAY_AMT) AS "연봉"               
        FROM TB_SAL_HIS B 
       WHERE B.PAY_DE  BETWEEN '20190101' AND '20191231'
       GROUP BY B.EMP_NO 
       ORDER BY B.EMP_NO) B 
WHERE A.EMP_NO = B.EMP_NO 
GROUP BY A.DEPT_CD
UNION ALL 
SELECT '모든부서' AS "부서코드", A.SEX_CD AS "성별코드"   
   , COUNT(*) AS "부서별직원수"
   , TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "부서별연봉합계"
   , TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "부서별평균연봉"  
FROM TB_EMP A 
   , (
      SELECT B.EMP_NO
           , SUM(B.PAY_AMT) AS "연봉"               
        FROM TB_SAL_HIS B 
       WHERE B.PAY_DE  BETWEEN '20190101' AND '20191231'
       GROUP BY B.EMP_NO 
       ORDER BY B.EMP_NO) B 
WHERE A.EMP_NO = B.EMP_NO 
GROUP BY A.SEX_CD
ORDER BY "부서코드", "성별코드";

グループ関数-GoROUPING SET

GROUPING SETを使用する場合、入力GROUPING SETのパラメータに従って小計値を出力できます.

DEPT CD(部門コード)とSEX CD(性別)から小計値

を出力.

GROUPING SETのパラメータ順序を変更しても出力結果には影響しません.

SELECT DECODE(GROUPING(A.DEPT_CD), 1, '모든부서', A.DEPT_CD) AS "부서코드" 
   , DECODE(GROUPING(A.SEX_CD), 1, '모든성별', A.SEX_CD) AS "성별코드" 
   , COUNT(*) AS "직원수"
   , TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
   , TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"  
FROM TB_EMP A 
   , (
      SELECT B.EMP_NO
           , SUM(B.PAY_AMT) AS "연봉"               
        FROM TB_SAL_HIS B 
       WHERE B.PAY_DE  BETWEEN '20190101' AND '20191231'
       GROUP BY B.EMP_NO 
       ORDER BY B.EMP_NO
     ) B 
WHERE A.EMP_NO = B.EMP_NO 
GROUP BY GROUPING SETS(A.DEPT_CD, A.SEX_CD)
ORDER BY "부서코드", "성별코드"
;


--GROUPING SET 인자 순서 바꾼 쿼리 

SELECT DECODE(GROUPING(A.DEPT_CD), 1, '모든부서', A.DEPT_CD) AS "부서코드" 
   , DECODE(GROUPING(A.SEX_CD), 1, '모든성별', A.SEX_CD) AS "성별코드" 
   , COUNT(*) AS "직원수"
   , TO_CHAR(TRUNC(SUM(B."연봉")), 'L999,999,999,999') AS "연봉합계"
   , TO_CHAR(TRUNC(AVG(B."연봉")), 'L999,999,999,999') AS "평균연봉"  
FROM TB_EMP A 
   , (
      SELECT B.EMP_NO
           , SUM(B.PAY_AMT) AS "연봉"               
        FROM TB_SAL_HIS B 
       WHERE B.PAY_DE  BETWEEN '20190101' AND '20191231'
       GROUP BY B.EMP_NO 
       ORDER BY B.EMP_NO
     ) B 
WHERE A.EMP_NO = B.EMP_NO 
GROUP BY GROUPING SETS(A.SEX_CD, A.DEPT_CD)
ORDER BY "부서코드", "성별코드"
;

Reference

この問題について([SQL]SQL使用グループ関数), 我々は、より多くの情報をここで見つけました https://velog.io/@fancypenguin/SQL-SQL활용-그룹함수

テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。

Collection and Share based on the CC Protocol

RECT JSX文法

Trueデータ構造