SQL serverで擬似乱数生成を含むビューとプロシージャを作る

　せっかくのクリスマスイヴなので、乱数生成を含むビュー＆ストアドを作ってみる。
　いや、クリスマスイヴは関係ない。申し訳ない。

1からnまでの乱数を生成する

　大変ありがたいことに、T-SQLには擬似乱数を返すRAND()が存在する。
　返り値は浮動小数点数（float）だそうだ。……浮動小数点数ってなんだ？

　ぐぐった。

kaya-soft.com

decimal(p,s)
- 固定小数点型の数値。numericも同じ。
- 桁数指定ができる。pは値全体の桁数。整数部（p-s）桁、小数点以下s桁となる。
- 小数点以下は指定した桁数で丸められる。
- 整数部は指定した桁数を超えた場合は算術エラーになる。

float
- 浮動小数点型の数値。
- 整数部と小数部を合わせて、realは4桁、floatは8桁まで。値によって小数点の位置を変える。
- 桁数制限に引っかかり次第、小数点以下を丸める。

　固定小数点型の精度は一定だが容量を食う、
　浮動小数点型の精度は不定だが容量をとらない、といった感じだろうか。

　普段はデータ削減の要がなければ固定小数点型を使った方がよさそう。
　でもRAND()が返す値は基本的に整数部が一桁（0.～　か 1.000 のどちらか）。
　よって、今回はあまり関係なさそう。

　とりあえずSQLでクエリを走らせてみる。

SELECT RAND(), RAND()

f:id:Matsuzakid:20161224165824p:plain

　元気に乱数っぽいものを出してくれる。
　「擬似」乱数とわざわざ名乗るからには色々込み入った問題があるんだろうなー。
　そこには深入りしない。

　これを材料に、1からnの整数を作る。

select
    -- 1から2のランダムな整数を出力
    CONVERT(int,RAND() * 2)+1
    -- 1から12のランダムな整数を出力
    ,CONVERT(int,RAND() * 12)+1

　できた。
　次はこれをストアドにする。

1からnまでの乱数を生成するはずだった

　今度はこのへんを参考にした。
　途中までMySQLの記事を読んでいた。つらい。

www.atmarkit.co.jp

outofmem.tumblr.com

　とりあえず書いてみる。
　返り値が全然わかってない。

USE AW4me
go

ALTER PROCEDURE [dbo].[KBS_FUNC](
        --プロシージャ名のかっこの中に引数や返り値を指定する
        --引き数はこんな感じ 今回はデフォルトで3を入れる
        @KBS_i_1 int = 3
        --返り値はこんな感じ
        ,@KBS_o_1 varchar(max) = '' OUTPUT
    )
    --引き数と返り値の設定が終わったらAS
    AS
    --BEGIN～ENDでブロックを作る
    BEGIN
        --変数の宣言
        --繰り返しの変数を入れる
        DECLARE @i int;
        --乱数のための変数も入れる
        DECLARE @r int;
        --結果出力のための文字列入れを作る
        DECLARE @vartmp varchar(256);
        DECLARE @varOUT varchar(256) = '';

        --繰り返しの数を1に設定
        SELECT @i = 0;
        
        --繰り返し処理を開始
        WHILE @i < @KBS_i_1
            BEGIN
                --１から３の乱数を作ってrに入れる
                SET @r = CONVERT(int,RAND() * 3)+1;
                --@rが1なら金
                IF @r = 1
                    BEGIN
                        SET @vartmp = '金';                
                        --次の単語があるようならカンマを入れ、ないようなら！を入れる
                        IF @i = @KBS_i_1 - 1
                        --次の単語なしなら'！'
                        SET @vartmp = @vartmp + '！';
                        ELSE 
                        SET @vartmp = @vartmp + '、';
                    END
                ELSE
                    BEGIN
                        --@rが2なら暴力
                        IF @r = 2
                            BEGIN
                                SET @vartmp = '暴力';                
                                --次の単語があるようならカンマを入れ、ないようなら！を入れる
                                IF @i = @KBS_i_1 - 1
                                --次の単語なしなら'！'
                                SET @vartmp = @vartmp + '！';
                                ELSE 
                                SET @vartmp = @vartmp + '、';
                            END
                        ELSE
                            BEGIN
                                --@rが3ならSEX
                                IF @r = 3
                                    BEGIN
                                        SET @vartmp = 'SEX';                
                                        --次の単語があるようならカンマを入れ、ないようなら！を入れる
                                        IF @i = @KBS_i_1 - 1
                                        --次の単語なしなら'！'
                                        SET @vartmp = @vartmp + '！';
                                        ELSE 
                                        SET @vartmp = @vartmp + '、';
                                    END
                                ELSE
                                     SET @vartmp = @vartmp + '';
                            END
                       END
                --vartmpをvaroutにくっつける
                SET @varout = @varout + @vartmp
                -- カウンタを++する
                SET @i = @i + 1
            END
        SET @KBS_o_1 = @varout
        print @KBS_o_1
    END

f:id:Matsuzakid:20161224180744p:plain

　気づいたらKBSトリオの発言をランダムに出力するストアドを作っていた。
　僕は何をやっているんだ。

　トリオなら引き数に３を、カルテットなら引き数に４を指定すればランダムにKBS発言を行う。
　BEGIN～ENDの仕様が本当にどうしようもないのだが、もうちょっとなんとかならないのだろうか。

　結果を眺めていたが、KBS発言の成立はなかなか難しいようだ。
　もしかすると乱数に偏りがあるのかもしれない。

　本当は関数にしたかったが、FUNCTIONだとRAND()が書けなかった。
　うーん。

　以下、とりあえず当初作ろうと思っていた処理も作っておいた。
　殴り書きだ。

CREATE PROCEDURE [dbo].[RANDINT](
        --プロシージャ名のかっこの中に引数や返り値を指定する
        --引き数はこんな感じ
        @start int
        ,@end int
        ,@return int OUTPUT
    )
    --引き数と返り値の設定が終わったらAS
    AS
    --BEGIN～ENDでブロックを作る
    BEGIN
       SET @return = CONVERT(int,RAND() * ( @end - @start + 1 ) ) + @start ;
       RETURN ;
    END

　呼び出して結果を確認したい場合はこのように書く。

DECLARE @out int;
EXECUTE RANDINT 20,30,@out OUTPUT;
PRINT @out;

　ビューもついでに書いてみる。

--ビューの作成練習

--DBを指定
use AW4me
go

--クエリを書き出す
--新しく作るときはCREATE, 変えるときはALTER
CREATE VIEW V_RandomMonth_Order
as
--今回はSELECT文
SELECT
     Soh.[OrderDate] --オーダーの日付
    ,COUNT(DISTINCT Cu.[CustomerID]) as CustomerCount  --顧客の数
    ,COUNT(DISTINCT Cu.[PersonID]) as PersonCount　　　--Personの数
FROM Customer Cu --カスタマーテーブルが基盤
LEFT OUTER JOIN Person pe ON Cu.[PersonID] = Pe.[BusinessEntityID]  --パーソンテーブルを結合
INNER JOIN SalesOrderHeader Soh ON Soh.[CustomerID] = Cu.[CustomerID]  --オーダー概要テーブルを結合
INNER JOIN SalesOrderDetail Sod ON Sod.[SalesOrderID] = Soh.[SalesOrderID] --オーダー詳細テーブルを結合

where
--今日と同じ日付
 SUBSTRING(CONVERT(varchar(100),Soh.[OrderDate]),1,2) --オーダーの月
 = 
 CONVERT(int,RAND() * 12)+1 -- １から12のランダムな整数を出力
GROUP BY Soh.[OrderDate] --日ごとに集計

　仕事で使う場合は、どうもカーソルとやらを使いこなさなければいけないらしい。
　できるかな～。