###【概要】
下記記事を見て、どうにかしてサブクエリを使用しないで平均値以上のデータを抽出できないものかと考えました。
ちょうど分析関数を勉強していたところ、分析関数を使用して同じ結果が出せました。
###【環境】
・Windows8.1
・Oracle 11.2.0
・SQL Developer バージョン4.1.5.21
※インストール方法については下記を参照してください
[備忘録]vagrantでOracleインストール&SQL Developerで接続する
###【テストデータ作成】
CREATE TABLE BASEBALL_TEAM_HIT(team_id, team_name, hit) AS
SELECT '001', 'Red Sox', 10 FROM DUAL UNION ALL
SELECT '002', 'Cubs', 20 FROM DUAL UNION ALL
SELECT '003', 'Indians', 30 FROM DUAL UNION ALL
SELECT '004', 'Rays', 40 FROM DUAL UNION ALL
SELECT '005', 'Braves', 50 FROM DUAL;
※hitの平均値は30
###【SQL】
####①サブクエリを使わない平均値以上のデータ抽出
■SQL
MINUS演算子でhitがNULLのデータを除外しているところがミソです。
SELECT
team_id
,team_name
,CASE WHEN
hit >= AVG(hit) OVER() then hit
END hit
FROM BASEBALL_TEAM_HIT
MINUS
SELECT team_id, team_name, NULL FROM BASEBALL_TEAM_HIT;
####②サブクエリを使う平均値以上のデータ抽出
■SQL
SELECT *
FROM BASEBALL_TEAM_HIT
WHERE
hit >= (SELECT AVG(hit) FROM BASEBALL_TEAM_HIT);
###【SQLの実行結果】
TEAM_ID | TEAM_NAME | HIT |
---|---|---|
003 | Indians | 30 |
004 | Rays | 40 |
005 | Braves | 50 |
###【参考サイト】
・図でイメージするOracle DatabaseのSQL全集 第3回 分析関数