skillup

技術ブログ

Database

SQL 集計関数の利用

投稿日:

先日同様、ある程度複雑な問題の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

データベース設計のアンチパターン 重すぎるOLTP+Date型不統一+データ量想定が甘い

Contents1 重すぎるOLTP1.1 デメリット1.2 対策2 DATE型の型の不統一2.1 デメリット2.2 対策3 データ量の想定が甘い3.1 デメリット3.2 対策 重すぎるOLTP ※O …

no image

MySQLのメモリ設定

実務で結構サーバーをヘビーに使われるお客さんがいて、SQLが遅くなったりとしています。 なんとか対策しないといけないので、いろいろと調査をいます。 それで今回はMySQLのメモリ問題など。 MySQL …

no image

netbeansのJPQL補助機能

JPAでは基本的に生のSQLではなく、JPQLを使って書きます。 SQLでもタイプミスにイライラさせられることは多いのですが、JPQLはもっとですね・・・爆 そこで使えるのがNetbeansの入力補助 …

no image

CakePHPでの数字カンマ区切り&PHP&MySQL曜日の出力

今回は主に時間やお金の表示など、出力に関するネタです。 Contents1 Cakeでのカンマ区切り1.1 単純なカンマ区切り 例1,0001.2 \をつけるケース 例 \1,0001.3 円をつける …

no image

正規化について&EXTRACT

Contents1 正規化とは?2 第一正規化3 SQLネタ EXTRACT 正規化とは? 正確な定義は難しいですが、8割ぐらいあっている定義としては「適切なテーブルに分割すること」です。※ただし正確 …