【SQL】ウィンドウ関数のサンプル

ウィンドウ関数の簡単なサンプルを紹介していきます。
サンプルでは以下のテーブルに対してクエリを投げていきます。
商品とその価格を持っており、価格の推移を履歴として持っています。

一覧表示するSQL

-- 一覧表示
select item_id, hst_no, item_name, item_price
from p3_item
order by item_id, hst_no;

商品IDごとに平均価格を求める

商品IDごとに平均価格を求めるSQL

-- item_id毎に平均価格を求める
select distinct
  item_id,
  item_name,
  avg(item_price) over(partition by item_id)::numeric::integer as avg_price
from p3_item
order by item_id;

パーティションにitem_idを指定しているので、item_id毎の平均価格を求めることができます。
そのままだと、全てのレコードが出力されていしまうため、distinctで重複を削除しています。

現在と一つ前の履歴の平均価格を求める

現在と一つ前の履歴の平均価格を求めるSQL

-- 現在と一つ前の履歴の平均価格を求める
select
  item_id,
  item_name,
  avg(item_price)
    over(
      partition by item_id
      order by hst_no
      rows between 1 preceding
      and current row)::numeric::integer as avg_price
from p3_item;

パーティションにitem_idを指定し、履歴番号順にソートします。
カレントの1つ前をグループ化し、それらの平均価格を算出しています。
最新の履歴を出したい場合は、max(hst_no)を出力して、where条件で絞ってやればいいです。

全体の平均価格を出すSQL

-- 全体の平均価格を出す場合
select 
  avg(item_price) over()::numeric::integer as all_avg_price
from p3_item
limit 1;

partitionを指定しないとテーブル全体がグループ化されるため、全体の平均を出すことができます。