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

データベースの権限設定

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

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

no image

HAVING句について

本日はHAVINGについて。 かろうじて用法はしっており、たまに使うこともありますが、あまりしっかり理解しているとはいえない状況ですので、掘り下げてみようと思います。 WHEREとは違い、抽出した結果 …

no image

アンチパターン トランザクションスコープ+大量データのリアルタイム集計+接続が詰まる

本日は主にインフラの設計的なことに関して。 Contents1 トランザクションスコープの設定1.1 デメリット1.2 対策2 大量データのリアルタイム集計2.1 デメリット2.2 対策3 詰まると接 …

no image

オブジェクト指向 データベース層

本日も引き続き「現場で役立つシステム設計の原則」を読み進めてます。 本日は主にデータベース層の考え方について。 Contents1 データべース層1.1 要点1.1.1 典型的なダメテーブル設計1.1 …

no image

大規模Webサービス技術入門 DBの分散

前回に引き続き、大規模サービスを運用するときに必要になるMySQLの知識についてのまとめ Contents1 テーブル・SQL設計2 レプリケーション機能3 パーティショニング テーブル・SQL設計 …