SQLのウィンドウ関数を使うと、グループ化しつつ、グループ内でランキングを作成するのが簡単と聞いていたので、PostgreSQLで試していた。
PostgreSQLのバージョンは、9.4.4
対象のデータ
ウィンドウ関数を試すにあたって、商品、カテゴリ、売上という3つのテーブルを作成し、データを投入した。
ER図
A5:SQL Mk-2を使って作成。
DDL
ER図から出力したDDLを使う。
-- 売上 drop table if exists sales cascade; create table sales ( id serial not null , item_id integer not null , category_id integer not null , price integer not null , constraint sales_PKC primary key (id) ) ; -- 商品 drop table if exists item cascade; create table item ( id serial not null , name character varying(50) not null , price integer not null , category_id integer not null , constraint item_PKC primary key (id) ) ; -- カテゴリ drop table if exists category cascade; create table category ( id serial , name character varying(50) not null , constraint category_PKC primary key (id) ) ;
データの投入
各テーブルに、集計できる最低限ぐらいのデータを入れておいた。
insert into category(id,name) values (1,'野菜'); insert into category(id,name) values (2,'果物'); insert into item(id,name,price,category_id) values (1,'じゃがいも',30,1); insert into item(id,name,price,category_id) values (2,'にんじん',80,1); insert into item(id,name,price,category_id) values (3,'たまねぎ',50,1); insert into item(id,name,price,category_id) values (4,'りんご',70,2); insert into item(id,name,price,category_id) values (5,'みかん',30,2); insert into sales(id,item_id,category_id,price) values (1,1,1,30); insert into sales(id,item_id,category_id,price) values (2,1,1,30); insert into sales(id,item_id,category_id,price) values (3,1,1,30); insert into sales(id,item_id,category_id,price) values (4,1,1,30); insert into sales(id,item_id,category_id,price) values (5,2,1,80); insert into sales(id,item_id,category_id,price) values (6,2,1,80); insert into sales(id,item_id,category_id,price) values (7,3,1,50); insert into sales(id,item_id,category_id,price) values (8,4,2,70); insert into sales(id,item_id,category_id,price) values (9,4,2,70); insert into sales(id,item_id,category_id,price) values (10,5,2,30); insert into sales(id,item_id,category_id,price) values (11,5,2,30); insert into sales(id,item_id,category_id,price) values (12,5,2,30);
ウィンドウ関数のrankを使ったクエリ
集計結果にランクをつけるクエリを試した。
with sales_report as ( select I.name , I.category_id , count(I.id) cnt , sum(I.price) price from sales S inner join item I on S.item_id = I.id inner join category C on S.category_id = C.id group by I.id ), item_rank as ( select sales_report.* , rank() over (partition by sales_report.category_id order by cnt) count_rank , rank() over (partition by sales_report.category_id order by price) price_rank from sales_report ) select * from item_rank where count_rank < 3;
1つ目のsales_reportは、売上テーブルのデータを商品IDでグループ化して、売上の合計金額と個数を出す。
2つ目のitem_rankは、rank関数を使って個数と合計金額をカテゴリID毎にランク付けする。
3つ目は、個数のランクが3未満のものだけに絞り込む。
実行結果
