読者です 読者をやめる 読者になる 読者になる

PostgreSQLでウィンドウ関数rankを試す

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未満のものだけに絞り込む。

実行結果