分析関数でAVGを使用するサンプルSQLを紹介します。
分析関数 AVG サンプルSQL
前提条件
SELECT
EMP_ID AS 従業員ID
,DEPT_ID AS 部署ID
,SALARY AS 年収
FROM
TB_SALARY
;
従業員ID 部署ID 年収
---------- ------ -------
1 01 3000000
2 01 4000000
3 01 5000000
4 02 6000000
5 02 6500000
6 02 7000000
6行が選択されました。
従業員が所属する部署の平均年収を求める
SELECT EMP_ID AS 従業員ID ,DEPT_ID AS 部署ID ,SALARY AS 年収 ,AVG(SALARY) OVER(PARTITION BY DEPT_ID)AS 部署の平均年収 FROM TB_SALARY ; 従業員ID 部署ID 年収 部署の平均年収 ---------- -------- ---------- -------------- 1 01 3000000 4000000 2 01 4000000 4000000 3 01 5000000 4000000 4 02 6000000 6500000 5 02 6500000 6500000 6 02 7000000 6500000 6行が選択されました。
『AVG(SALARY)』で給与の平均値を求めています。そして、『OVER(PARTITION BY DEPT_ID)』を指定することで、部署ID毎の平均年収を求めるという意味になります。
部署毎の平均年収以上の年収がある従業員を抽出する
SELECT
VW.EMP_ID AS 従業員ID
,VW.DEPT_ID AS 部署ID
,VW.SALARY AS 年収
FROM
(
SELECT
EMP_ID AS EMP_ID
,DEPT_ID AS DEPT_ID
,SALARY AS SALARY
,AVG(SALARY) OVER(PARTITION BY DEPT_ID)
AS AVG_SALARY
FROM
TB_SALARY
) VW
WHERE
VW.SALARY >= VW.AVG_SALARY
;
従業員ID 部署ID 年収
---------- -------- ----------
2 01 4000000
3 01 5000000
5 02 6500000
6 02 7000000
分析関数を使ったSQLを副問合せにすることで、分析関数の抽出結果(集計結果)を抽出条件として絞り込み検索を行う事ができます。
従業員ID「1」の年収は「3000000」であり、平均年収「4000000」未満であるため抽出対象外となります。
従業員ID「4」の年収は「6000000」であり、平均年収「6500000」未満であるため抽出対象外となります。