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] --日ごとに集計

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

SQLのサンプルデータベースを材料にViewを作ってみる

システム日付の月と同じ月の発注延べ人数のＶＩＥＷを作る
create view tekito as

SELECT
     Soh.[OrderDate]
    ,COUNT(DISTINCT Cu.[CustomerID]) as CustomerCount
    ,COUNT(DISTINCT Cu.[PersonID]) as PersonCount
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) = SUBSTRING(CONVERT(varchar(100),SYSDATETIME()),6,2)
GROUP BY Soh.[OrderDate]

このＶＩＥＷを元に帳票系をしこしこ出力するバッチとか
ストアドを使って日次更新したテーブルをやっぱりＶＩＥＷにしてしこしこ出力するバッチとか
出力したcsvをあれこれするpythonをかいたりしたいです（どれもまだできない）

SQL server 2014用のサンプルDBを入れてみる

参考になったサイト

　下のサイトを参考にする。

SQL Serverのサンプルデータベースの移り変わり（Pubs、Northwind、AdventureWorks） - あおきのTechメモ

　DL元はこれ。

Microsoft SQL Server Product Samples: Database - Home

解凍待ちのあいだ考えたこと

　入れる前に解凍する前に不安なのは、
　データが綺麗すぎたらやだな、
　英語なのはやだな、の２点。

　RDBの構造が企業っぽいのは素晴らしいなー、というのはsummaryを見て感じた。

　Rのirisとかはキレイすぎて逆に大変よね。
　社外にはデータを持ち出せない（当たり前）から、
　家ではサンプルを使って分析用のコード書くわけだけど、就業時間中に前処理でハマる。

　むしろDirty sampleとかあったりするのかな。こういうニーズは多そう。
　あ、できた。この辺は慣れてるから割愛する。っておえー！

解凍後に思ったこと

f:id:Matsuzakid:20161218213223p:plain

　多いわ！！！！！！！
　というわけでとりあえず自分の練習に使えそうなテーブルだけピックアップする。
　id:aoki1210さんの記事にスキーマの画像があった。ありがたやありがたや。

  • SalesOrderHeader
    • [SalesOrderID],[CustomerID]
  • SalesOrderDetail
    • [SalesOrderID],[ProductID]
  • Product
    • [ProductID]
  • Customer
    • [CustomerID],[PersonID]
  • Person
    • [BusinessEntityID]

　とりあえずこの５つのテーブル（と、キー）から始める。
　CustomerとPersonの名寄せが[PersonID] <-> [BusinessEntityID]
　というのはリアルな感じでなかなかよろしいですな。
　AW4meというDBを作って５つのテーブルだけ移植。

Create database AW4me
go

USE [AdventureWorks2014]

select *
into [AW4me].[dbo].[SalesOrderHeader]
from Sales.SalesOrderHeader

select *
into [AW4me].[dbo].[SalesOrderDetail]
from Sales.SalesOrderDetail

select *
into [AW4me].[dbo].[Product]
from Production.Product

select *
into [AW4me].[dbo].[Customer]
from Sales.Customer

select [BusinessEntityID]
      ,[PersonType]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[EmailPromotion]
      ,[rowguid]
      ,[ModifiedDate]
into [AW4me].[dbo].[Person]
FROM [AdventureWorks2014].[Person].[Person]

　Personだけxmlスキーマで持って来れなかったので一部削っている。
　結合できるチェックに内部結合のクエリを書く。

USE AW4me

SELECT DISTINCT TOP 100
	Cu.[CustomerID]
	,Sod.[SalesOrderID]
	,Pe.[BusinessEntityID]
	,Pr.[ProductID]
FROM Person pe
INNER JOIN Customer Cu ON Cu.[PersonID] = Pe.[BusinessEntityID]
INNER JOIN SalesOrderHeader Soh ON Soh.[CustomerID] = Cu.[CustomerID]
INNER JOIN SalesOrderDetail Sod ON Sod.[SalesOrderID] = Soh.[SalesOrderID]
INNER JOIN Product Pr ON Pr.[ProductID] =Sod.[ProductID]

　結果はこう。

f:id:Matsuzakid:20161218222341p:plain

　IDがbigintっぽいので、不整合なID同士でくっつけても
　くっついちゃうんじゃないかとちょっと不安になる。
　ただ、ProductやPersonのデータにほどよくNULLがあるのは素晴らしい。

　ここからキューブをつくり、エクセルからPowerPivotで引っ張ってくるのが今日中にしたい作業だ。

ローカルでSQL Serverのサービスをスイッチするbatを作る

SQL Serverのサービスをスイッチするbatを作る

　下のサイトを参考にした。

【バッチ勉強】サービスの起動・停止状態を取得する方法 | Tipstour

　コードはこんな感じ。オリジナリティもなんもないでしゅ。

REM SQLの状態取得開始
sc query MSSQL$（インスタンス名） | findstr STATE | findstr RUNNING > null

REM SQLが動いてたら(0)止める(stop)
REM SQLが止まってたら(not 1)動かす(start)
IF %errorlevel% == 0 ( sc stop MSSQL$（インスタンス名） ) else ( sc start MSSQL$（インスタンス名） )

REM 何かキーを押して下さい...
pause

　以上。もう一つあるけど長くなったので分ける。

一度アンインストールしたSQL server 2014 expressの再インストールにつまった

昨日の夜から今日にかけて、タイトルの部分で盛大にハマったので書く

ハマりポイント１：サーバーエンジン他１つのインストールに失敗

原因：以前のインスタンス（２個）が半端に残っていたこと

　修復しようとしても新規なインスタンスはそもそも
　「インストールできていない」前提なのでコケていた
　きっちりアンインストールすることが何より大事だったようす

　管理者でログインしてとにかく「SQL」の名前のついているものをアンインストールし、
　セットアップ関連のフォルダもSQLEXPRWT_x64_JPN.exeから解凍し直したらうまくいった

　対策：インスタンス（特にサーバーエンジン回り）は
　　　　　　アンインストールし忘れていることを前提に行動すること

ハマりポイント２：Management studioまでいってもサーバーへの接続に失敗

原因：以前のサーバー名がManegement studioにデフォルトとして入っていた

　f:id:Matsuzakid:20161218183050p:plain

　こ↑れ↓
　サービスが動いていないのかと思って管理ツールまで見に行っても
　元気に動いていた　同様の現象を起こす原因が複数あるのが罠だった
　賢者には賢者のための罠が、アホにはアホのための罠がある
　ぼくはアホでした
　賢者のための対策しても意味が無かった

　f:id:Matsuzakid:20161218182428p:plain

　犯人は消したはずのインスタンス？だった
　サーバー名がぼくがせっていしたのとちがう

　震える手でサーバー名を設定したものに入れなおしたら動いた

　f:id:Matsuzakid:20161218183627p:plain

　ハマりの息抜きにAnacondaもインストールしたものの、
　残り時間的にPythonとキューブの勉強の両方はできない
　かなしい