SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

分析関数の衝撃

分析関数の衝撃(中編)

CodeZineに掲載されたSQLを分析関数で記述する 2


  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード ソースコード (4.2 KB)

2. 前年と年商が同じ年度を求める

 次に前年と年商が同じ年度を求めるSQLです。「相関サブクエリで行と行を比較する」では、以下のSQLが提示されています。

前年と年商が同じ年度を求める その1:相関サブクエリの利用
SELECT year,sale
  FROM Sales S1
 WHERE sale = (SELECT sale
                 FROM Sales S2
                WHERE S2.year = S1.year - 1)
 ORDER BY year;

 これを分析関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。

 手続き型の言語であれば、

  1. yearの昇順にソート
  2. yearの最小値からループ開始
  3. saleの値を変数に保存
  4. 前のsaleの値と等しかったら出力

 といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

分析関数で書き換えたSQL
select year,sale
  from (select year,sale,
        Lag(sale) over(order by year) as LagSale
          from Sales)
 where sale= LagSale
order by year;

 Lag関数で、「year」の昇順にソートした1行前のレコードの値を取得してます。「year」が連続している(歯抜けがない)ので、「year」の昇順にソートした1行前のレコードは、1年前のレコードとなります。

SQLのイメージ
SQLのイメージ

 後は「sale」と「1年前のsale」が等しいことを、外側のselect文のwhere句で条件としてます。

 DB2では、Lag関数が使えないので、下記のSQLとなります。

分析関数で書き換えたSQL(DB2)
select year,sale
  from (select year,sale,
               max(sale) over(order by year
               Rows between 1 Preceding and 1 Preceding) as LagSale
          from Sales) dummy
 where sale= LagSale
order by year;

3. 累積差を求める

 次に累積差を求めるSQLです。「相関サブクエリで行と行を比較する」では、以下のSQLが提示されています。

累積差を求める その2:自己外部結合の利用。開始時点も結果に含まれる
SELECT S2.year AS pre_year,
       S1.year AS now_year,
       S2.sale AS pre_sale,
       S1.sale AS now_sale,
       S1.sale - S2.sale AS diff
 FROM Sales2 S1 LEFT OUTER JOIN Sales2 S2
   ON S2.year = (SELECT MAX(year)
                   FROM Sales2 S3
                  WHERE S1.year > S3.year)
 ORDER BY now_year;

 これを分析関数で書き換えてみます。まずは、テーブルのデータと出力結果を考えます。

 手続き型の言語であれば、

  1. yearの昇順にソート
  2. yearの最小値からループ開始
  3. 当年のyearとsaleを出力
  4. 変数に保存した前年のyearとsaleがあれば出力
  5. yearとsaleの値を変数に保存

 といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

分析関数で書き換えたSQL
select Lag(year) over(order by year) as pre_year,
year as now_year,
Lag(sale) over(order by year) as pre_sale,
sale as now_sale,
sale - Lag(sale) over(order by year) as diff
  from Sales2
order by now_year;

 前問と同様に、Lag関数で、「year」の昇順にソートした1行前のレコードの値を取得してます。

SQLのイメージ
SQLのイメージ

 DB2では、Lag関数が使えないので、下記のSQLとなります。

分析関数で書き換えたSQL(DB2)
select
max(year) over(order by year
Rows between 1 Preceding and 1 Preceding) as pre_year,
year as now_year,
max(sale) over(order by year
Rows between 1 Preceding and 1 Preceding) as pre_sale,
sale as now_sale,
sale - max(sale) over(order by year
       Rows between 1 Preceding and 1 Preceding) as diff
from Sales2
order by now_year;

次のページ
4. 累計を求める

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
分析関数の衝撃連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/1298 2007/09/04 12:39

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング