skillup

技術ブログ

Database

SQL 集計関数の利用

投稿日:2016年10月21日 更新日:

先日同様、ある程度複雑な問題のSQLに関して。出典はSQL実践入門。

問題 下記のようなテーブル(テーブル名)で

より古い年のデータが存在しない場合 NULL
直近の年のデータより売り上げが伸びた場合:+
直近の年のデータより売り上げが下がった場合:-
直近の年のデータと売り上げが同じ場合:=

company year sale
A 2002 52
A 2004 54
A 2005 84
B 2006 15
B 2001 95
B 2003 23
B 2008 15
C 2001 18
C 2009 32
C 2010 12

難しいのは前回との差分の取り方でしょう・・・サブクエリでゴリゴリやればいけるかもしれませんがかなり難しいでしょう。

こんな時にウィンドウ関数を使えば比較的楽に計算結果を出すことができます。

手順としては

PARTITION BYを使って会社ごとにグルーピングし、年度で並び替え、をするのですが、下記関数で前列との比較ができます。

select列に上記の関数を入れてあげると、去年の売上を出すことができます。

の部分ですが、1行前から1行前の範囲という意味です。(要は直前の1行だけを対象に最大値を求めています。)

これを応用し、下記のように書いてあげれば前年度との変化を書くことができます。

CASE式の中がやや複雑ですが、「今の売上-前期の売上」を書いているだけです。

上記のようなものでも構いませんが、さらにSIGNという関数を使い下記のようにスマートに書けます。

いまさらですがCASE式に関して

一つの値に対してパラメーターだけで分岐させる場合は 下記のように書く。

一般的なプログラミングに近いイメージ。

ただしWHENの中には式も書ける

http://skill-up-engineering.com/?p=1963
http://skill-up-engineering.com/?p=1999

のような例。

-Database

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

joinとeager loading

フレームワークでデータをORMがらみでjoinするときのネタ。 自分の場合はLaravel。他のフレームワークでも考え方は通じるものあるかと・・ Contents1 通常のjoin2 ループの中で取得 …

no image

JPAを使用する  JavaSE編

現在の開発ではデータベースへの接続ではJPAを使っています。このJPAがなかなか曲者でいろいろと詰まることがありましたので解説させていただきます。 Contents1 JPAとは?1.1 メリット1. …

no image

jQuery modalダイアログについて&重複時間処理

Contents1 jqueryモーダルダイアログ1.1 あらかじめ読み込むライブラリ1.2 ソース本体1.2.1 Html側1.2.2 Javascript側1.2.3 参考リンク2 重複時間につい …

no image

データベースのインデックスについて

今回はデータベースのインデックスに関して。 検索条件を早くする場合、何よりも速度が速くなるのはインデックスを張ることでしょう。 インデックスを張ることは単語を索引順に並べることですので、劇的に速度が向 …

no image

テストのダミーデータ作成

データベースに大量のデータを作りたいときにいつもあああやhoge,aaaですとデータという感じがしないですし、抽出や集計ができません。 なるべく自然に近いデータが欲しいのですが、簡単に作れる方法があり …