テーブル値関数（SQL Server）

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

お久しぶりです。ここのところ、仕事のほうがなかなか忙しく、ブログをアップすることができませんでした。写真は、皇居の平川門を撮ったものです。東西線竹橋駅のすぐそばにあり、たまたま仕事で近くまで行ったので行ってみたところ、平川橋という橋を渡ると警備の方がいらっしゃいましたが、中に入れるということで、寄ってみました（皇居東御苑は、火曜日～木曜日なら年末年始以外なら基本的にはいつでも公開しているとのこと）。時間がなくてすぐに出てきてしまいましたが、テレビで見たことのあるようなお堀があって、季節によっては花とかとてもきれいではないかと感じました。外国の方も結構見にいらっしゃっていましたね。

ずっと前、学生の頃に、正月1月2日の新年の一般参賀に一度皇居に行ったことがあり、おぼろげにそのときのことを覚えていますが、それ以外は一度も行ったことがなかったです。普通の日でも入れるんですね～。今度休暇でもとって、ゆっくりと歩いてみたいな～！（嫁を誘ってみる？）

ちなみに平川門とは、江戸城の裏門で、大奥に最も近く、奥女中の通用門であったことから「お局門」とも呼ばれていたそうです。また、平川門の脇に小さな門があって、城内で罪人や死人が出るとこの門から出されたので「不浄門」ともいわれていたとのこと。
うんちくネタになるかな！

　皇居側から見た平川橋

＜本日の題材＞
テーブル値関数（SQL Server）

今回は、テーブル値関数を取り上げてみたいと思います。私も今まで使ったことはなかったのですが、今回途中から応援に入ったプロジェクトでは、SQL Serverを利用していましたが、テーブル値関数がよく使われていて、結構便利なものだと感じました。
どういうものか調べてみると、ユーザー定義テーブル値関数と呼ばれ、ストアドプロシージャのように様々なロジックを組めますが、結果としてtableデータ型の値を返すことができ、ビューに変わる強力なツールになるようです。

例１）まず、サンプルとして適当かどうか？というのはありますが、こういう使い方もあるということで、指定した日から1ヶ月（31日間）の日付と曜日をそれぞれ1行目、2行目に表示しつつ、日付を横並びに表示するためのものを作成してみます。

CREATE FUNCTION dbo.ufn_calender(
@p開始日 datetime
)
RETURNS TABLE
AS
RETURN(
SELECT
1 表示順,N'日付'表示名
,CONVERT(VARCHAR(10),@p開始日+0,111) 日付1
,CONVERT(VARCHAR(10),@p開始日+1,111) 日付2
,CONVERT(VARCHAR(10),@p開始日+2,111) 日付3
,CONVERT(VARCHAR(10),@p開始日+3,111) 日付4
,CONVERT(VARCHAR(10),@p開始日+4,111) 日付5
,CONVERT(VARCHAR(10),@p開始日+5,111) 日付6
,CONVERT(VARCHAR(10),@p開始日+6,111) 日付7
,CONVERT(VARCHAR(10),@p開始日+7,111) 日付8
,CONVERT(VARCHAR(10),@p開始日+8,111) 日付9
,CONVERT(VARCHAR(10),@p開始日+9,111) 日付10
,CONVERT(VARCHAR(10),@p開始日+10,111) 日付11
,CONVERT(VARCHAR(10),@p開始日+11,111) 日付12
,CONVERT(VARCHAR(10),@p開始日+12,111) 日付13
,CONVERT(VARCHAR(10),@p開始日+13,111) 日付14
,CONVERT(VARCHAR(10),@p開始日+14,111) 日付15
,CONVERT(VARCHAR(10),@p開始日+15,111) 日付16
,CONVERT(VARCHAR(10),@p開始日+16,111) 日付17
,CONVERT(VARCHAR(10),@p開始日+17,111) 日付18
,CONVERT(VARCHAR(10),@p開始日+18,111) 日付19
,CONVERT(VARCHAR(10),@p開始日+19,111) 日付20
,CONVERT(VARCHAR(10),@p開始日+20,111) 日付21
,CONVERT(VARCHAR(10),@p開始日+21,111) 日付22
,CONVERT(VARCHAR(10),@p開始日+22,111) 日付23
,CONVERT(VARCHAR(10),@p開始日+23,111) 日付24
,CONVERT(VARCHAR(10),@p開始日+24,111) 日付25
,CONVERT(VARCHAR(10),@p開始日+25,111) 日付26
,CONVERT(VARCHAR(10),@p開始日+26,111) 日付27
,CONVERT(VARCHAR(10),@p開始日+27,111) 日付28
,CONVERT(VARCHAR(10),@p開始日+28,111) 日付29
,CONVERT(VARCHAR(10),@p開始日+29,111) 日付30
,CONVERT(VARCHAR(10),@p開始日+30,111) 日付31
UNION ALL SELECT
2,N'曜日'
,LEFT(DATENAME(dw,@p開始日+0),1)
,LEFT(DATENAME(dw,@p開始日+1),1)
,LEFT(DATENAME(dw,@p開始日+2),1)
,LEFT(DATENAME(dw,@p開始日+3),1)
,LEFT(DATENAME(dw,@p開始日+4),1)
,LEFT(DATENAME(dw,@p開始日+5),1)
,LEFT(DATENAME(dw,@p開始日+6),1)
,LEFT(DATENAME(dw,@p開始日+7),1)
,LEFT(DATENAME(dw,@p開始日+8),1)
,LEFT(DATENAME(dw,@p開始日+9),1)
,LEFT(DATENAME(dw,@p開始日+10),1)
,LEFT(DATENAME(dw,@p開始日+11),1)
,LEFT(DATENAME(dw,@p開始日+12),1)
,LEFT(DATENAME(dw,@p開始日+13),1)
,LEFT(DATENAME(dw,@p開始日+14),1)
,LEFT(DATENAME(dw,@p開始日+15),1)
,LEFT(DATENAME(dw,@p開始日+16),1)
,LEFT(DATENAME(dw,@p開始日+17),1)
,LEFT(DATENAME(dw,@p開始日+18),1)
,LEFT(DATENAME(dw,@p開始日+19),1)
,LEFT(DATENAME(dw,@p開始日+20),1)
,LEFT(DATENAME(dw,@p開始日+21),1)
,LEFT(DATENAME(dw,@p開始日+22),1)
,LEFT(DATENAME(dw,@p開始日+23),1)
,LEFT(DATENAME(dw,@p開始日+24),1)
,LEFT(DATENAME(dw,@p開始日+25),1)
,LEFT(DATENAME(dw,@p開始日+26),1)
,LEFT(DATENAME(dw,@p開始日+27),1)
,LEFT(DATENAME(dw,@p開始日+28),1)
,LEFT(DATENAME(dw,@p開始日+29),1)
,LEFT(DATENAME(dw,@p開始日+30),1)
);

7月の日付について試したいので、パラメータとして「2016/07/01」を渡して抽出してみます。

SELECT * FROM [dbo].[ufn_calender]('2016/07/01')
ORDER BY 表示順;

結果は以下のようになります。

カレンダーのように、日付が横並びになり、2行目に曜日が表示されますね。

他の使い方として、最初にテーブルの定義を指定して、その後いろいろな処理や条件を組込みながら、その定義にあった結果をINSERTして、それを利用するようなやり方があります。

例２）以前も使用したことがある売上テーブルについて、指定した年月の商品別の売上金額の高いものからランクをつけながら抽出する処理を行うテーブル値関数を作成してみます。

CREATE FUNCTION dbo.ufn_商品CD別月次出荷
(
@p出荷年月 VARCHAR(6)
) RETURNS @月次出荷 TABLE (
出荷年月 VARCHAR(6),
ランキング DECIMAL(3,0),
商品CD NVARCHAR(20),
商品名 NVARCHAR(20),
出荷数量 INT,
売上金額 DECIMAL(10,0)
)
AS
BEGIN
INSERT @月次出荷
SELECT
FORMAT(U.受注日, 'yyyyMM')
,RANK() OVER (ORDER BY SUM(U.売上金額) DESC)
,U.商品CD
,S.商品名
,SUM(U.商品数量)
,SUM(U.売上金額)
FROM dbo.売上テーブル U
JOIN dbo.商品マスタ S ON U.商品CD = S.商品CD
WHERE FORMAT(U.受注日, 'yyyyMM') = @p出荷年月
GROUP BY FORMAT(U.受注日, 'yyyyMM'), U.商品CD, S.商品名;

RETURN;
END;

このテーブル値関数を使って、2016年05月の売上について、商品別の売上金額を高い順に表示します。

SELECT * FROM dbo.ufn_商品CD別月次出荷('201605')
ORDER BY ランキング;

ストアドプロシージャのようにいろいろな処理を組み込んで、テーブルのかたちで結果を返すことができるので、使い方によっては結構便利かと思います。今まで、ワーク用のテーブルを用意して、そこに一旦データを登録してから処理をしていたような場合でも、ワークのテーブルを作成せずに、テーブル値関数を使って対応するようなことも可能な場合もあると思います。

ちなみに、ORACLEでも、同じように使用することができるユーザー定義レコードというようなものが結構以前からあるようですね。

今日は以上まで

にほんブログ村

DB & SQL 技術ブログ

コメントを残すコメントをキャンセル

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

コメントを残す コメントをキャンセル

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

コメントを残すコメントをキャンセル