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

herokuでMySQL

昨日に続き、heroku+MySQLのメモです。 herokuはディフォルトではPostgreSQLですが、アドオンを使うとMySQLも使えるようになります。 使い方ですが、herokuの管理画面でク …

no image

JPAを使用する  JavaSE編

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

no image

persistence.xmlのプロパティについて

JavaEEではデータベースとの設定情報はpersistence.xmlに記述します。 (ユーザー名、パスワード、ポート、driver名、データベース名などの情報はglassfish-resource …

no image

データベースの権限設定

データベースを作成するときに

と入力していますが、ほぼ機械的にこれを売っているのでこれを機にどんな使い方があるのかを調べてみました。 …

no image

サロゲートキーに関して

テーブル設計に関してのメモ。 テーブルを作る時にid int not null auto_increment primary keyを自動的に作ることが多いと思いますが、サロゲートキーといい、グレーノ …