愚者の経験

「また今度」はほとんどこない

カテゴリーアーカイブ: Tips

[SQL Server][SQL][Tips]pivotを使ってAccessのクロス集計クエリっぽいことをする2

前回の続きというか応用

テーブル用意(前回と同じです)
pivott

Q.「いやね、金額と個数の両方合計してだしてくれない?」とか言われたらどうしますか?
A.イラッとする
見てのとおりpivotでは集計項目は1つしか選べません。なので同じデータを2回selectして
項目を変えればもちろん取得出来ます。

	select Y.*
		,(select top 1 A.売上先名称 from dbo.mtbl_売上先 as A where A.売上先ID=Y.売上先ID) as 売上先名称
	from (
		select A.売上先ID,B.商品名称,sum(A.金額) as 金額計
		from dbo.tbl_売上明細 as A
			inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
			left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
		group by A.売上先ID,C.分類名称,B.商品名称
	) as X
	pivot (
		max(X.金額計) for X.商品名称 in 
			(みかん,りんご,トマト,きゅうり)
	) as Y

	select Y.*
		,(select top 1 A.売上先名称 from dbo.mtbl_売上先 as A where A.売上先ID=Y.売上先ID) as 売上先名称
	from (
		select A.売上先ID,B.商品名称,sum(A.個数) as 個数計--変更部分
		from dbo.tbl_売上明細 as A
			inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
			left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
		group by A.売上先ID,C.分類名称,B.商品名称
	) as X
	pivot (
		max(X.個数計) for X.商品名称 in 
			(みかん,りんご,トマト,きゅうり)
	) as Y

あとは2つのビューを「inner join」なり「union all」なりすればいいのですが
「selectのコスト重すぎ…2回もできない…」という方、「inner join」を使う代わりにこんな方法もあります。

	select Y.*
	from (
                -- ※※※※※※※※※※※※
		select D.売上先ID
			,D.商品名称+E.Col as Col,E.Val
		from (
			select A.売上先ID,B.商品名称,sum(A.個数) as 個数計,sum(A.金額) as 金額計
			from dbo.tbl_売上明細 as A
				inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
				left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
			group by A.売上先ID,C.分類名称,B.商品名称
		) as D
			cross apply (
				select '個数',D.個数計 union all
				select '金額',D.金額計
			) as E (Col,Val)
                -- ※※※※※※※※※※※※
	) as X
	pivot (
		max(X.Val) for X.Col in
			(みかん個数,りんご個数,トマト個数,きゅうり個数
			,みかん金額,りんご金額,トマト金額,きゅうり金額)
	) as Y

なんじゃこりゃぁ…とか思いますが私も最初はそうでした(笑)当然(?)ミソはcross applyの部分です。
この部分で「項目名」と「値」のセットをそれぞれunionします。
今回の場合、集計項目が「個数」と「金額」ですので、
項目名に’個数’というリテラル文字、値に「個数計=sum(個数)」
項目名に’金額’というリテラル文字、値に「金額計=sum(金額)」
となりこれらをunionし、cross applyします。

この時点でどうなっているかというと※の間のselectを実行すればわかりますが
売上先ID、Col、Val
10、りんご個数、sum(個数)
10、りんご金額、sum(金額)

というような形になって一行のデータを個数と金額の2行に分けています。
あとはpivotで(みかん,りんご,トマト,きゅうり)だったのを
(みかん個数,りんご個数,トマト個数,きゅうり個数,みかん金額,りんご金額,トマト金額,きゅうり金額)に
変更して値を集めているというわけです。機会があったらぜひ利用してみてください。

参考URL:http://stackoverflow.com/questions/18662012/sql-server-pivot-on-multiple-columns

[SQL Server][SQL][Tips]pivotを使ってAccessのクロス集計クエリっぽいことをする1

Accessでいうところの「クロス集計クエリ」ですがSQL Serverだと「pivot」で実現は可能です。
書き方覚えられないので毎回ネットで調べてますが(笑)

テーブル用意
pivott

	select Y.*
		,(select top 1 A.売上先名称 from dbo.mtbl_売上先 as A where A.売上先ID=Y.売上先ID) as 売上先名称
	from (
		select A.売上先ID,B.商品名称,sum(A.金額) as 金額計
		from dbo.tbl_売上明細 as A
			inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
			left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
		group by A.売上先ID,C.分類名称,B.商品名称
	) as X
	pivot (
		max(X.金額計) for X.商品名称 in 
			(みかん,りんご,トマト,きゅうり)
	) as Y

pivotを使う際、直前のselectで必要項目を絞ってなくてもgroup byの様に怒られないです(笑)
ですがその代わりに集計もしてくれません…ちゃんと指定しましょう。

	select Y.売上先ID,Y.きゅうり,Y.みかん,Y.りんご,Y.トマト
		,(select top 1 A.売上先名称 from dbo.mtbl_売上先 as A where A.売上先ID=Y.売上先ID) as 売上先名称
	from (
		select A.売上先ID,B.商品名称,sum(A.金額) as 金額計,sum(A.個数) as 個数計
		from dbo.tbl_売上明細 as A
			inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
			left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
		group by A.売上先ID,C.分類名称,B.商品名称
	) as X
	pivot (
		max(X.金額計) for X.商品名称 in 
			(みかん,りんご,トマト,きゅうり)
	) as Y

pivotの中を『sum(金額) for 商品名称 in (みかん,りんご,トマト,きゅうり)』みたいに書いたほうが見た目
わかりやすいと思うかもしれませんが、私の頭がついていけないので行をまとめてからpivotすると決めています笑

[SQL Server][SQL][Tips]cteを使おう!としてみたが難しい。その3

これを使ってSplitできるんじゃないと思って考えました。
開始位置と終了位置を返すselect文をcteで
初期のアンカーメンバーを1と最初の区切り文字の場所として
区切り文字+1と次の区切り文字を区切り文字がなくなるまで再帰します。
そうしてできたクエリでsubstringすれば一発でSplitの出来上がり。
と思ったのですがすでにやってる人いましたのでそれを載せます。
参考URL:http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx

CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))
RETURNS TABLE
AS
RETURN
	WITH a AS(
		SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
		UNION ALL
		SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
		FROM a
		WHERE idx2>0
	)
	SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value
	FROM a

やはり目のつけどころが皆さん違いますね。
これは作業用のテーブルにinsertとかしませんので個人的には相当スッキリします笑

[SQL Server][SQL][Tips]cteを使おう!としてみたが難しい。その1

SQL Serverをお使いの方「cte」をご存知だろうか?

この「cte」自体は
「テーブルに別名をつけることができ、1ステートメント内であれば何度も参照できる」
というものです。Common Table Expression(共通テーブル式)
(例)

;with cte (nb,txt) as (
    select 0,'aaa' union all
    select 1,'bbb' union all
    select 2,'ccc' union all
    select 3,'111'
)
select count(*) as countcte
	,(select max(nb) from cte) as maxnb
	,(select min(nb) from cte) as minnb
	,(select max(txt) from cte) as maxtxt
	,(select min(txt) from cte) as mintxt
from cte

ぱっと思いつく用途としては「自己結合の簡略化」です。
複雑なクエリで累計するときなど自己結合が必要な場合にSQLが冗長にならずに済む可能性があります。
これはAccessの参照クエリ、SQL Serverのビューを一時的に作るような使い方になりますので
それほど難しい使い方ではないのですが、cteにはほかにも独自(?)の機能があります。

それが「再帰」です。
調べても「なるほど、わからん」という感じにしかならないです…。少しわかりかけてきたので
他のかたのために、主に自分のためにメモしておきます。