2016-12-20

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をかいたりしたいです（どれもまだできない）

2016-12-18

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で引っ張ってくるのが今日中にしたい作業だ。

2016-12-18

ローカルで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

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