SQL

分析関数 AVG

  • このエントリーをはてなブックマークに追加

分析関数で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」未満であるため抽出対象外となります。

  • このエントリーをはてなブックマークに追加

コメント

  1. ピンバック: 分析関数 MAX
  2. ピンバック: 分析関数 MIN

コメントを残す

*