7-2 クロス集計

SQLパズル

Loginテーブル
UserID  LoginTime
------  ----------
aaaa    2005-08-01
bbbb    2005-08-01
cccc    2005-08-01
dddd    2005-08-03
eeee    2005-08-06
ffff    2005-08-07
gggg    2005-08-09
hhhh    2005-08-21

Loginテーブルから、
8月の日ごとのログイン数と、
8月のログイン数の総合計を求める。

出力結果
集計対象   ログイン数
--------   ---------
20050801          3
20050803          1
20050806          1
20050807          1
20050809          1
20050821          1
総合計            8

データ作成スクリプト

create Table Login(
UserID    char(4),
LoginTime date);

insert into Login values('aaaa',to_date('20050801','yyyymmdd'));
insert into Login values('bbbb',to_date('20050801','yyyymmdd'));
insert into Login values('cccc',to_date('20050801','yyyymmdd'));
insert into Login values('dddd',to_date('20050803','yyyymmdd'));
insert into Login values('eeee',to_date('20050806','yyyymmdd'));
insert into Login values('ffff',to_date('20050807','yyyymmdd'));
insert into Login values('gggg',to_date('20050809','yyyymmdd'));
insert into Login values('hhhh',to_date('20050821','yyyymmdd'));
commit;

SQL

--■■■クロス集計を使用■■■
select case when grouping(LoginTime) = 1 then '総合計'
       else to_char(LoginTime,'yyyymmdd') end as 集計対象,
count(UserID) as ログイン数
from Login
group by rollup(LoginTime)
order by 集計対象;

--■■■分析関数とunionを使用■■■
select distinct to_char(LoginTime,'yyyymmdd') as 集計対象,
count(*) over (partition by LoginTime) as ログイン数
from Login
union all select '総合計',count(UserID) as ログイン数 from Login
order by 集計対象;

解説

rollupやcubeやgrouping関数を使うと、
用途に応じたクロス集計を行うことができます。

3-29 grouping setsで総合計を取得

---

＠ITハイブックス クロス集計
新しい業界標準「SQL99」詳細解説

マニュアル(group by)

---