2-3-21 条件付き平均(重複あり)

SQLパズル

ValListテーブル
ID  Val
--  ---
 1   10
 1   30
 1   60
 1   90
 1   90
 2   10
 2   10
 2   60
 2   90
 3   20
 3   20
 3   20
 3   90
 3   90
 4   50
 5   10
 5   10
 5   20
 5   20
 5   30
 6   10
 6   20
 6   30
 6   30

IDごとのValの平均を求める。
ただし、IDごとの重複を排除したValの数が3件以上だったら
最大値と最小値は対象外とする。

ValListには重複したレコードが存在する。

出力結果
ID  平均
--  ----
 1    45  (30+60)/2
 2    60
 3    48  (20*3+90*2)/5
 4    50
 5    20
 6    20

データ作成スクリプト

create table ValList(
ID  number(1),
Val number(2));

insert into ValList values(1,10);
insert into ValList values(1,30);
insert into ValList values(1,60);
insert into ValList values(1,90);
insert into ValList values(1,90);
insert into ValList values(2,10);
insert into ValList values(2,10);
insert into ValList values(2,60);
insert into ValList values(2,90);
insert into ValList values(3,20);
insert into ValList values(3,20);
insert into ValList values(3,20);
insert into ValList values(3,90);
insert into ValList values(3,90);
insert into ValList values(4,50);
insert into ValList values(5,10);
insert into ValList values(5,10);
insert into ValList values(5,20);
insert into ValList values(5,20);
insert into ValList values(5,30);
insert into ValList values(6,10);
insert into ValList values(6,20);
insert into ValList values(6,30);
insert into ValList values(6,30);
commit;

SQL

select ID,Avg(Val) as 平均
from (select ID,Val,
      count(distinct Val) over(partition by ID) as ValCount,
      max(Val) over(partition by ID) as MaxVal,
      min(Val) over(partition by ID) as MinVal
        from ValList)
where ValCount >= 3 and Val not in(MaxVal,MinVal)
   or ValCount <= 2
group by ID
order by ID;

解説

インラインビューで、
重複を排除したValの数と、最大値と最小値を取得してます。

---