愚者の経験

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

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

[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を使おう!としてみたが難しい。その2

cteに機能の再帰ですが実際何に使えるのかというとほとんど
・連続データの作成
になると思います。マスタなどに親コードみたいなみたいなものが
存在し、順番に追って最上位を取得するという場合にも再帰は役に立ちます。
が、あまりそのような状況に巡り合わないのでそれの説明は省きます。

with cte(inc) as (
	select 1
	union all
	select inc+1
	from cte
	where inc<30
)
select *
from cte

これで1~30までの連続データができます。
年間カレンダーを作るときなどにも使えますね。
注意しなければならない点としては
・100を超える再帰をする場合そのままではエラーになるのでoption (maxrecursion N)が必要

with cte(inc) as (
	select 1
	union all
	select inc+1
	from cte
	where inc<200
)
select *
from cte
option (maxrecursion 0)

・パラメータ等使って回転数を制御した場合等で0行を返すことはできない

declare @maxinc int
set @maxinc=0;
with cte(inc) as (
	select 1
	union all
	select inc+1
	from cte
	where inc<@maxinc
)
select *
from cte

こういうのですね…初期アンカーがある(select 1)ので
1行は必ず返ります。最後のselectにwhere句つけてください。

[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にはほかにも独自(?)の機能があります。

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

[SQL Server]ユーザーソース一覧を作る

バックエンドにSQLServerで開発しているときに
「ストアドとかビューとか関数とか何個あるか数えたい」
「あの列をSelectしてるストアドってどれ?」
「最近に修正したオブジェクトってなんだっけ?」
とか思うときがあります。

そうゆうときにあると非常に便利だと思うのが以下のビューです。
ストアド、ビュー、ユーザー定義関数のCreate文をデータにもってきてますので
Likeとかで検索できます。(definition列)

「それじゃテーブルも取得したいよ」と思ったのが運の尽き…
テーブルのCreate文って簡単に取得できないんですね…
後半はかなりやっつけなのであんまりあてにしないでください笑
PrimaryKey以外のIndex?
sys.indexesから頑張って取得してください…もう疲れました…

たぶんあっている
・列名
・データ型
・identity(seed,increment)
・rowguid
・計算列
・Null許可
・既定値

自信なし
・PrimaryKey

表示なし
・PrimaryKey以外のIndex
・付加列(当然です笑)

	select O.name
		,O.type_desc
		,O.create_date
		,O.modify_date
		,M.definition COLLATE Japanese_Bin as [definition]
	from sys.objects as O
		inner join sys.sql_modules as M on M.object_id=O.object_id
	where O.type_desc in 
			('SQL_STORED_PROCEDURE'
			,'SQL_SCALAR_FUNCTION'
			,'SQL_TABLE_VALUED_FUNCTION'
			,'VIEW')
	union all
	select T.name
		,T.type_desc
		,T.create_date
		,T.modify_date
		,'CREATE TABLE ['+D.TABLE_SCHEMA+'].['+T.name+']'+char(13)+char(10)+'('
			+(
				-- 列情報
				select '['+S.COLUMN_NAME+']'
					+coalesce
						((
							select ' AS '+P.definition+','
							from sys.computed_columns as P
							where P.object_id=C.object_id
								and P.column_id=C.column_id)
						,'['+S.DATA_TYPE+']'+isnull('('+cast(S.CHARACTER_MAXIMUM_LENGTH as varchar)+')','')
							+isnull((
							select ' IDENTITY('+cast(I.seed_value as varchar)
								+','+cast(I.increment_value as varchar)+')'
							from sys.identity_columns as I
							where I.object_id=C.object_id
								and I.column_id=C.column_id),'')
							+case when C.is_rowguidcol=1 then ' ROWGUIDCOL' else '' end
							+case when S.IS_NULLABLE='YES' then ' NULL,' else ' NOT NULL,' end)+char(13)+char(10)
				from information_schema.columns as S
					inner join sys.columns as C on C.name=S.COLUMN_NAME and C.object_id=T.object_id
				where TABLE_NAME=T.name
				order by ORDINAL_POSITION
				for xml path(''),type).value('.','varchar(max)')
			+isnull((
				-- PrimaryKey
				select ' CONSTRAINT ['+X.name+'] PRIMARY KEY '+X.type_desc+'('
					+replace((
						-- キー情報
						select '['+(select name from sys.columns
									where object_id=T.object_id and column_id=XC.column_id)+'] '
							+case when XC.is_descending_key=1 then 'DESC' else 'ASC,' end
						from sys.index_columns as XC
						where XC.object_id=T.object_id
						order by XC.index_column_id
						for xml path(''),type).value('.','varchar(max)')+') WITH ',',)',')')
					+'(PAD_INDEX='			+case when X.is_padded			=0 then 'OFF' else 'ON' end
					+',IGNORE_DUP_KEY='		+case when X.ignore_dup_key		=0 then 'OFF' else 'ON' end
					+',ALLOW_ROW_LOCKS='	+case when X.allow_row_locks	=0 then 'OFF' else 'ON' end
					+',ALLOW_PAGE_LOCKS='	+case when X.allow_page_locks	=0 then 'OFF' else 'ON' end
					+',STATISTICS_NORECOMPUTE=OFF) ON [PRIMARY]) ON [PRIMARY]'
				from sys.indexes as X
				where X.object_id=T.object_id
					and X.is_primary_key=1),'')
			+char(13)+char(10)+'GO'+char(13)+char(10)
			+isnull((
				-- 既定値の設定
				select 'ALTER TABLE ['+D.TABLE_SCHEMA+'].['+T.name+'] ADD CONSTRAINT '
					+'['+F.name+'] DEFAULT '+F.definition+' FOR '
					+'['+(select A.name from sys.columns as A
							where A.object_id=T.object_id and A.column_id=F.parent_column_id)+'] '
					+char(13)+char(10)+'GO'+char(13)+char(10)
				from sys.default_constraints as F
				where F.parent_object_id=T.object_id
				order by F.parent_column_id
				for xml path(''),type).value('.','varchar(max)'),'')
	from sys.tables as T
		inner join information_schema.tables as D on D.TABLE_NAME=T.name

[SQL]SQL Serverで値比較の落とし穴

かなり久しぶりにブログに記事を書く気がします。

別の人が書いたSQLを見て直していたんですが期待した結果が
返ってこないので調べてみたところ…
なんと「0=”」はTrueになることが判明。(なんかでも前も同じ事あったような…)
なので以下の様なちょっと不思議なSQLも書くことができます。

	select case when ''=0 then 1 end as are
		,case when 0='0' then 1 end as oya
		,case when '0'='' then 1 end as un

照合順序の辺りで解決できるのかもしれませんが、腑に落ちないのは私だけ?
とりあえず暗黙の型変換には気をつける。cast(” as int)は0!!

[SQL]動的にin句を変化させるような時の代替案

ふと気づいた。

        select *
        from table1
        where id in(@param)

というような形で「@paramに動的な複数値をカンマ区切りで渡したい」と
いう要望は結構ある。しかしこの状態では期待した結果にならないので
executeやsp_executesql(動的SQL実行)を使う

        execute ('select * from table1 where id in('+@param+')')

これは期待通りの結果になるのだが、いかんせんAccessと相性が悪いため
(カラム名が解決できないのでコントロールソースが手入力になるなど)SQLCLRを使って
カンマ区切りをテーブルにして返す関数(Split)を作成すると良いと最初は思いました。

苦労してSQLCLRを調べたりしたんですがそんなことしなくてもできます。
もちろん動的実行しなくてもできます。こうすればよかったんです…。

        select *
        from table1
        where ','+@param+',' like '%,'+cast(id as varchar)+',%'

なんでもっと早く気づかなかった自分…orz
多分インデックスも効くんじゃないかな。

見かけの文字列長で切り取る[SQL Server]

もうこの問題かなりめんどくさいです。なんで日本語って全部全角じゃないんですかね。
そもそもアルファベットとか数字とか記号を見かけ上半分の幅にしたか、
それ以外の文字を倍の幅にしたかしてしまった時点で大きなハンデを背負っている
と思います。人生のパソコン時間でアルファベット、数字、記号の半角全角変換の
時間を集約したら下手したら一年くらいかかってるんじゃないかって思います。

文書の校正で数字の変な全角半角混じりがないかとか…色々時間がかかります。
言いたいこと多々はありますがこいつのお陰で「文字列の終点を揃える」という行為に
多大な労力を割かれることになります。

なんだかんだ言ってサンプルです。

declare @var varchar(30)
set @var='是はtestですYO'
select @var as n0
	,'['+cast(substring(CAST(@var as varbinary(100)),1,1) as varchar(30)) +']' as n1
	,'['+cast(substring(CAST(@var as varbinary(100)),1,2) as varchar(30)) +']' as n2
	,'['+cast(substring(CAST(@var as varbinary(100)),1,3) as varchar(30)) +']' as n3
	,'['+cast(substring(CAST(@var as varbinary(100)),1,4) as varchar(30)) +']' as n4
	,'['+cast(substring(CAST(@var as varbinary(100)),1,5) as varchar(30)) +']' as n5
	,'['+cast(substring(CAST(@var as varbinary(100)),1,6) as varchar(30)) +']' as n6
	,'['+cast(substring(CAST(@var as varbinary(100)),1,7) as varchar(30)) +']' as n7
	,'['+cast(substring(CAST(@var as varbinary(100)),1,8) as varchar(30)) +']' as n8
	,'['+cast(substring(CAST(@var as varbinary(100)),1,9) as varchar(30)) +']' as n9
	,'['+cast(substring(CAST(@var as varbinary(100)),1,10) as varchar(30)) +']' as n10
	,'['+cast(substring(CAST(@var as varbinary(100)),1,11) as varchar(30)) +']' as n11
	,'['+cast(substring(CAST(@var as varbinary(100)),1,12) as varchar(30)) +']' as n12
	,'['+cast(substring(CAST(@var as varbinary(100)),1,13) as varchar(30)) +']' as n13
	,'['+cast(substring(CAST(@var as varbinary(100)),1,14) as varchar(30)) +']' as n14
	,'['+cast(substring(CAST(@var as varbinary(100)),1,15) as varchar(30)) +']' as n15
	,'['+cast(substring(CAST(@var as varbinary(100)),1,16) as varchar(30)) +']' as n16

重要なのは一旦
varchar型varbinary型にcastする」ことです。
元の文字列をnvarchar型の場合は前準備としてvarchar型にcastする必要があります。

text型にしても同様にできますが、将来削除予定のデータ型で使うなと言われているので
あえてvarbinary型にしています。また文字列の切り取りは「substring関数」しか
まともに使えそうなのがないです。「left」や「right」といった関数はvarbinary型のまま文字列を切れないので
暗黙的にvarchar型に変換して切ってしまい、元の木阿弥です。

文字切れに対しては勝手に表示しないようにしてくれます。

VBA版はこっちで。

Group Byでの文字列連結[SQL Server]

「SQLCLR」で集計関数使ってもいいんですがもう少し手軽に
扱いたいなら「FOR XML PATH(”),TYPE」でselectの結果を
XML型にしValueメソッド(?)でvarcharやnvarcharに変換すると可能です。

サンプルSQL(グループの文字列はサブクエリになります)

create table #tmp(
	ID int identity(1,1) not null ,
	NUM int null,
	TXT varchar(50) null
)

insert into #tmp(NUM,TXT)
values(1,'fjak;lj')
	,(2,'fakdj')
	,(1,'f;laj')
	,(2,'dfjoji')
	,(3,'roa')
	,(2,'jfdosk')
	,(1,'ajfasj')
	,(2,'dfoaif')
	,(2,'djljfl')
	,(1,'kajl')
	,(3,'f')

select T.NUM
	,(
		select '##'+X.TXT
		from #tmp as X
		where X.NUM=T.NUM
		order by X.ID
		for xml path(''),type).value('.','nvarchar(max)'
	) as gpjoin
from #tmp as T
group by NUM

drop table #tmp

実際Valueメソッドは使わなくてもいいのですが
xmlのマークアップに使われる特殊文字は変換されますので
Valueメソッドを使うほうが安全です。<>→&lt;&gt;みたいな。

行挿入考察(3)

前回の投稿の続き
簡単な行挿入ロジックの条件は「挿入するデータのみで実現する」です。

小数点を使えばいけるんじゃないかと思ったのでやってみます。
・「[並び順]-0.1を並び順にする」
つまり今回の例で言うと

並び順:(2-0.1) 値:行挿入!

をInsertするということです。
Insertした結果はこうなります。

並び順:1 値:あいうえお
並び順:1.9 値:行挿入!
並び順:2 値:かきくけこ
並び順:3 値:さしすせそ

結果はOKです。しかしもう一度「【並び順:2 値:かきくけこ】に行挿入する」とどうでしょう。

並び順:1 値:あいうえお
並び順:1.9 値:行挿入!
並び順:1.9 値:行挿入?
並び順:2 値:かきくけこ
並び順:3 値:さしすせそ

これは…ダメです。並び順が1.9同士が揃い、2行目と3行目で並び順がわかりません。

この通り「同じデータ(この場合「【並び順:2 値:かきくけこ】のデータ)から何度も行挿入が行われる」
可能性があるため、一定の値を足したり引いたりして並び順を求めることはできないのです。
これが行挿入機能の実装の難しさになっています。

基本的には挿入後に各行にアクセスしてそれぞれの並び順を振り直すしかない様に思えます。
誰かこれを解決した画期的な行挿入ロジックはないものでしょうか?アイデアを求むですm(__)m