4-4 集合関数を使うスカラー問い合わせ

SQLパズル

株価TBL
日付      時刻  始値   高値  安値  終値   出来高
--------  ----  ----  ----  ----  ----  ------
20050401  0900  1200  1230  1190  1210      56
20050401  0905  1210  1240  1150  1190      70
20050401  0910  1190  1230  1190  1210      55
20050401  0915  1210  1250  1170  1230      45
20050402  0900  1130  1150  1100  1140      30
20050402  0905  1140  1140  1100  1110      25
20050402  0910  1110  1110  1060  1060      55
20050402  0915  1060  1080  1060  1060      80

下記の出力を行う。

始値は、その日付の始値列の一番小さい時刻の値
高値は、その日付の高値列の一番高い値
安値は、その日付の安値列の一番低い値
終値は、その日付の終値列の一番大きい時刻の値
出来高は、日付単位の合計値

出力結果
日付       始値  高値  安値  終値   出来高
--------  ----  ----  ----  ----   ------
20050401  1200  1250  1150  1230      226
20050402  1130  1150  1060  1060      190

こちらを参考にさせていただきました

データ作成スクリプト

create table 株価TBL(
日付   varchar2(8),
時刻   varchar2(4),
始値   number(4),
高値   number(4),
安値   number(4),
終値   number(4),
出来高 number(4));

insert into 株価TBL values('20050401','0900',1200,1230,1190,1210,56);
insert into 株価TBL values('20050401','0905',1210,1240,1150,1190,70);
insert into 株価TBL values('20050401','0910',1190,1230,1190,1210,55);
insert into 株価TBL values('20050401','0915',1210,1250,1170,1230,45);
insert into 株価TBL values('20050402','0900',1130,1150,1100,1140,30);
insert into 株価TBL values('20050402','0905',1140,1140,1100,1110,25);
insert into 株価TBL values('20050402','0910',1110,1110,1060,1060,55);
insert into 株価TBL values('20050402','0915',1060,1080,1060,1060,80);
commit;

SQL

--■■■Keepを使う方法■■■
select 日付,
max(始値) Keep(Dense_Rank First order by 時刻) as 始値,
max(高値) as 高値,min(安値) as 安値,
max(終値) Keep(Dense_Rank Last order by 時刻) as 終値,
sum(出来高) as 出来高
  from 株価TBL
group by 日付
order by 日付;

--■■■分析関数を使用する方法■■■
select distinct 日付,
First_Value(始値) over(partition by 日付 order by 時刻) as 始値,
max(高値) over(partition by 日付) as 高値,
min(安値) over(partition by 日付) as 安値,
Last_Value(終値) over(partition by 日付 order by 時刻
Rows between Unbounded Preceding and Unbounded Following) as 終値,
sum(出来高) over(partition by 日付) as 出来高
from 株価TBL
order by 日付;

--■■■インラインビューを使用する方法■■■
select distinct 日付,
(select b.始値 from 株価TBL b where b.日付 = a.日付
                                and b.時刻 = (select min(c.時刻) from 株価TBL c
                                               where c.日付=b.日付)) as 始値,
高値,安値,
(select b.終値 from 株価TBL b where b.日付 = a.日付
                                and b.時刻 = (select max(c.時刻) from 株価TBL c
                                               where c.日付=b.日付)) as 終値,
出来高
from (select 日付,max(高値) as 高値,min(安値) as 安値,sum(出来高) as 出来高
      from 株価TBL group by 日付) a
order by 日付;

--■■■インラインビューを使用しない方法■■■
select distinct 日付,
(select b.始値 from 株価TBL b where b.日付 = a.日付
                                and b.時刻 = (select min(c.時刻) from 株価TBL c
                                               where c.日付=b.日付)) as 始値,
(select max(b.高値) from 株価TBL b where b.日付=a.日付) as 高値,
(select min(b.安値) from 株価TBL b where b.日付=a.日付) as 安値,
(select b.終値 from 株価TBL b where b.日付 = a.日付
                                and b.時刻 = (select max(c.時刻) from 株価TBL c
                                               where c.日付=b.日付)) as 終値,
(select sum(b.出来高) from 株価TBL b where b.日付=a.日付) as 出来高
from 株価TBL a
order by 日付;

解説

集合関数を使うスカラー問い合わせは、
エンティティ間が、1対1となるので
select句でスカラー問い合わせを使って、データを取得できます。

分析関数を使用してもいいでしょう。

---