RANK, DENSE_RANK

2014年9月27日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

関東最古の神社の一つと言われる鷲宮神社の『土師祭』(はじさい)が9月最初の日曜日(9/7)に行われたときの写真です。千貫神輿という大きな神輿を担いで通りを練り歩く姿は結構迫力があります。
また、鷲宮神社は人気まんが作品『らき☆すた』の舞台となった神社であり、聖地と呼ばれるだけあって、お祭りには地元の方だけでなく、『らき☆すた』ファンの皆さんや、アニメの登場人物やキャラクターに扮するコスプレ姿の若者などが大勢訪れ、賑わっていましたね。

　　
普段の鷲宮神社の鳥居と本殿です。

＜本日の題材＞
RANK、DENSE_RANK

順位付（ランキング）関数に、以前題材にあげたことのあるROW_NUMBER関数、それにRANK関数、DENSE_RANK関数などがあります。
ROW_NUMBER関数が単純な連番であるのに対し、同じ値があったときに、同じ順位を付けることができるのが、RANK、DENSE_RANK関数です。両者の違いは、同じ値があったときの次の値の順位を飛ばした値にするのがRANK、連続した値にするのがDENSE_RANKです。

例として下記のような商品マスタを作成 (SQL Server2008の環境)します。

CREATE TABLE dbo.商品マスタ(
商品CD VARCHAR(10)
, 商品名 VARCHAR(20)
, 分類 VARCHAR(20)
, 値段 DECIMAL(10)
, CONSTRAINT PK_商品マスタ PRIMARY KEY (商品CD));

データが以下のような場合：
SELECT * FROM dbo.商品マスタ
ORDER BY 商品CD;

商品マスタの商品を値段の高い順に表示したいときに、同じ値があったときの次の値の順位を飛ばした値にするRANK関数を使用した場合：

SELECT
RANK() OVER (ORDER BY 値段 DESC) RANK順位
, 商品CD, 商品名, 分類, 値段
FROM dbo.商品マスタ;

同じ値があったときの次の値の順位を連続した値にするDENSE_RANK関数を使用したとき、

SELECT
DENSE_RANK() OVER (ORDER BY 値段 DESC) DENSE_RANK順位
, 商品CD, 商品名, 分類, 値段
FROM dbo.商品マスタ;

ROW_NUMBER、RANK、DENSE_RANKを一緒に並べて使用した場合、
SELECT
ROW_NUMBER() OVER (ORDER BY 値段 DESC) ROW_NUMBER順位
, RANK() OVER (ORDER BY 値段 DESC) RANK順位
, DENSE_RANK() OVER (ORDER BY 値段 DESC) DENSE_RANK順位
, 商品CD, 商品名, 分類, 値段
FROM dbo.商品マスタ;

また、これらの関数は、PARTITION BY句と一緒に使用すると、グループ化してランキングを抽出することができます。

SELECT
ROW_NUMBER() OVER (PARTITION BY 分類 ORDER BY 値段 DESC) ROW_NUMBER順位
, RANK() OVER (PARTITION BY 分類 ORDER BY 値段 DESC) RANK順位
, DENSE_RANK() OVER (PARTITION BY 分類 ORDER BY 値段 DESC) DENSE_RANK順位
,商品CD,商品名,分類,値段
FROM dbo.商品マスタ;

上記結果のように、分類ごとにグループ化して、それぞれの関数のランキングを取得することができます。今回はSQL Serverで確認しましたが、基本的にOracleでも同様です。

今日は以上まで

にほんブログ村

MERGE

MERGE文（2）

2014年9月18日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

前回の題材に上げたMERGE文について、SQL Serverではできないと思っている方もいるのではないかという話を聞き、今回はSQL Serverでもできることを報告します。

＜本日の題材＞
MERGE文（SQL Server編）

MERGE文の説明は前回既に行なったので、今回は省きます。
前回と同じ例をSQL Server2008で試してみました。

元データを商品テーブルＡ、登録・更新したいテーブルを商品テーブルＢとします。

CREATE TABLE dbo.商品テーブルＡ(
商品CD VARCHAR(10)
, 商品名 VARCHAR(20)
, CONSTRAINT PK_商品テーブルＡ PRIMARY KEY (商品CD));

CREATE TABLE dbo.商品テーブルＢ(
商品CD VARCHAR(10)
, 商品名 VARCHAR(20)
, CONSTRAINT PK_商品テーブルＢ PRIMARY KEY (商品CD));

データがそれぞれ以下のような場合：
SELECT * FROM dbo.商品テーブルＡ;

SELECT * FROM dbo.商品テーブルＢ;

商品テーブルＡのデータを元に、商品テーブルＢに既に商品CDが存在するデータは、商品名を変更し、商品CDが存在しないものは商品テーブルＢにデータを登録する場合のMERGE文を実行します。

MERGE INTO dbo.商品テーブルＢ
USING dbo.商品テーブルＡ
ON (dbo.商品テーブルＡ.商品CD = dbo.商品テーブルＢ.商品CD)
WHEN MATCHED THEN
UPDATE SET
商品名 = dbo.商品テーブルＡ.商品名
WHEN NOT MATCHED THEN
INSERT (商品CD,商品名)
VALUES (商品テーブルＡ.商品CD,商品テーブルＡ.商品名);
-- (2 行処理されました)

上記を実行した後に、商品テーブルＢを確認すると、
SELECT * FROM 商品テーブルＢ;

商品CD：00001 については、商品名のみ更新し、はじめは存在していなかった商品CD：00002 のデータが追加されているのが、SQL Server2008でも確認できました。

※SQL Server2008からMERGE文は対応するようになっていますので、それ以降のバージョンでは問題なく動作します。

別の例）
上記のような同じ構造のテーブルからの更新・登録ではなく、ユーザが何かのアプリで入力した場合のような定数値の情報からデータを更新・登録するケースもあるので、その際のMERGE文の例を以下に追記します。

例えば、変数@商品CD、@商品名を使用し、商品CD「00001」、商品名「商品１(甘さ控えめ)」という値を代入して使用する場合：

DECLARE @商品CD VARCHAR(10);
DECLARE @商品名 VARCHAR(20);

SET @商品CD = '00001';
SET @商品名 = '商品１(甘さ控えめ)';

MERGE INTO dbo.商品テーブルＢ
USING (SELECT @商品CD AS 商品CD, @商品名 AS 商品名) AS 商品情報
ON (dbo.商品テーブルＢ.商品CD = 商品情報.商品CD)
WHEN MATCHED THEN
UPDATE SET
商品名 =商品情報.商品名
WHEN NOT MATCHED THEN
INSERT (商品CD,商品名)
VALUES (商品情報.商品CD,商品情報.商品名);
--(1 行処理されました)

上記を実行した後に、商品テーブルＢを確認すると、
SELECT * FROM 商品テーブルＢ;

商品CD：00001 については、商品名が更新されているのが確認できます。

今日は以上まで

にほんブログ村

MERGE

MERGE文

2014年9月16日ＩＴＣシュウちゃんコメントをどうぞ

ITコーディネータのシュウです。

本当に久しぶりのアップになります。
システムの納品の対応で、ここ1ヶ月、精神的にも肉体的にもかなり忙しくて大変な状態でした。受託開発は、途中で変更や内容が膨らんで、予定通りに進めるのが難しく、本当に納期に間に合うのか？と冷や汗をいつもかくことが多いと思うのは私だけでしょうか？
そこのスコープの管理を如何にお客様との間できちんと行うのかがとても重要であることはいつも思っているのですが。。。
必死になってときに残業も惜しまず、頑張るのですが、なぜそうなっているのか事情がわかりにくい分野でもあり、意外に周りの目は冷たかったりして．．．トホホ。
でも、問題を一つ一つ解決していき、納期が見えてきたときの達成感も、我々しか味わえないものかも知れませんね。といっても、最後の検収が上がるまでは何があるかわからないので、まだ緊張が解けないこの頃です。

心の余裕が出てきたところで、またブログ再開します。

＜本日の題材＞
MERGE文

ある抽出結果の情報を指定のテーブルに登録したいけれど、既にそのデータ（キー情報が同じデータ）が登録したいテーブルに存在していたら、値の違う部分を更新し、まだ存在していなければ登録する、というような要件に出くわすときが時折あります。

このとき、データが既に存在しているかどうかを確認して、IF文でUPDATE、INSERTを切り分けるということは、結構面倒ですが、これを一度のSQLで実行することができるのが、MERGE文です。

簡単な例として、同一構造のテーブルを元データとして、データを更新、なければ登録する場合は、下記のようになります。

元データを商品テーブルＡ、登録・更新したいテーブルを商品テーブルＢとします。

CREATE TABLE 商品テーブルＡ(
商品CD VARCHAR2(10)
, 商品名 VARCHAR2(20)
, CONSTRAINT PK_商品テーブルＡ PRIMARY KEY (商品CD));

CREATE TABLE 商品テーブルＢ(
商品CD VARCHAR2(10)
, 商品名 VARCHAR2(20)
, CONSTRAINT PK_商品テーブルＢ PRIMARY KEY (商品CD));

データがそれぞれ以下のような場合：

SELECT * FROM 商品テーブルＡ;

SELECT * FROM 商品テーブルＢ;

商品テーブルＡのデータを元に、商品テーブルＢに既に商品CDが存在するデータは、商品名を変更し、商品CDが存在しないものは商品テーブルＢにデータを登録する場合に以下のMERGE文を実行します。

MERGE INTO 商品テーブルＢ
USING 商品テーブルＡ
ON (商品テーブルＡ.商品CD = 商品テーブルＢ.商品CD)
WHEN MATCHED THEN
UPDATE SET
商品名 = 商品テーブルＡ.商品名
WHEN NOT MATCHED THEN
INSERT (商品CD, 商品名)
VALUES (商品テーブルＡ.商品CD, 商品テーブルＡ.商品名)
;

上記を実行した後に、商品テーブルＢを確認すると、

SELECT * FROM 商品テーブルＢ;

商品CD：00001 については、商品名のみ更新し、はじめは存在していなかった商品CD：00002 のデータが追加されているのが確認できます。（Oracleで検証）

今日は以上まで

にほんブログ村

DB & SQL 技術ブログ

月別アーカイブ: 2014年9月

RANK, DENSE_RANK

MERGE文（2）

MERGE文

主にSQLについて書いていきたいと思います。