T-SQLでn個ずつのグループにわける
前回はn個のグループにわけるクエリを書いてみました。
今回はn個ずつになるようにグルーピングするクエリをROW_NUMBER関数を使って書いてみたいと思います。
ROW_NUMBER (Transact-SQL) | Microsoft Docs
前回と同じように11から20までの数値があるとします。
-- 11から20までの数値があるとして select * from #Sequence; /* Value ----------- 11 12 13 14 15 16 17 18 19 20 */
この10個の数値を4個ずつのグループ（この場合は4個・4個・2個のグループ）にわかれるようにグループ番号を割り振ります。グループ番号はNTILE関数と同じように1からとしてみます。
-- 4個ずつのグループにわける declare @count int = 4; select *, -- グループ番号は1からにする (row_number() over (order By Value) - 1) / @count + 1 as GroupNo -- グループ番号が0からでよければ -- (row_number() over (order By Value) - 1) / @count as GroupNo from #Sequence; /* Value GroupNo ----------- -------------------- 11 1 12 1 13 1 14 1 15 2 16 2 17 2 18 2 19 3 20 3 */
4個ずつのグループにわけることができました。
T-SQLでn個のグループにわける
NTILE関数を使うと簡単にデータをn個のグループにわけることができます。正確には、n個のグループにわけた場合のグループ番号を割り振ると言ったほうがいいかもしれません。
NTILE (Transact-SQL) | Microsoft Docs
NTILE関数を使ったサンプルを書いてみます。
まず適当なデータ、今回は11から20までの10個の数値を用意します。
drop table if exists #Sequence; -- 11から20までの10個の数値を生成 with Source(Value) as( select 11 union all select Value + 1 from Source ) select top 10 * into #Sequence from Source; -- 確認 select * from #Sequence; /* Value ----------- 11 12 13 14 15 16 17 18 19 20 */
この11から20までの数値を3つのグループにわけたいと思います。
-- 3つのグループにわける declare @group int = 3; select *, -- Valueの昇順で3つのグループにわけたグループ番号を割り振る ntile(@group) over (order By Value) as GroupNo from #Sequence; /* Value GroupNo ----------- -------------------- 11 1 12 1 13 1 14 1 15 2 16 2 17 2 18 3 19 3 20 3 */
4個・3個・3個のグループにわけることができました。総数がグループ数で割り切れない場合、NTILE関数は最初のグループから多く割り振っていきます。
SQL Server - 拡張イベントで収集できるイベントデータを確認するクエリ
拡張イベントで収集できるイベントデータを確認するクエリです。
動的管理ビューを3つ使います。sys.dm_xe_packages、sys.dm_xe_objects、sys.dm_xe_object_columnsの3つ。
| 動的管理ビュー | 概要 |
|---|---|
| sys.dm_xe_packages (Transact-SQL) | パッケージ一覧。イベントはパッケージによってグルーピングされています。 |
| sys.dm_xe_objects (Transact-SQL) | イベント（オブジェクト）一覧。収集したいイベントを確認できます。 |
| sys.dm_xe_object_columns (Transact-SQL) | オブジェクトのカラム一覧。イベントが収集されるときにどんなデータが含まれるかを確認できます。イベントデータというかイベントの属性というか。 |
収集できるイベントデータを確認するクエリがこちらです。例としてsql_batch_completedイベントのイベントデータを確認しています。
-- sql_batch_completedイベントのイベントデータを確認する select xe_oc.name as column_name, xe_oc.column_type as column_type, xe_oc.type_name as type_name, xe_oc.description as column_description from sys.dm_xe_packages as xe_p inner join sys.dm_xe_objects as xe_o on xe_p.guid = xe_o.package_guid inner join sys.dm_xe_object_columns as xe_oc on xe_o.package_guid = xe_oc.object_package_guid and xe_o.name = xe_oc.object_name where -- sys.dm_xe_objectsにはアクション・ターゲットなどイベント以外も含まれるので -- object_type = N'event'でフィルタ xe_o.object_type = N'event' and xe_p.name = N'sqlserver' and xe_o.name = N'sql_batch_completed';
結果です。（空白などは調整してあります。）
column_name column_type type_name column_description ------------------- ------------- ------------------ ------------------------------------------------------------------------------------- UUID readonly guid_ptr グローバル一意 ID VERSION readonly uint8 イベント スキーマのバージョン CHANNEL readonly etw_channel ETW チャネル KEYWORD readonly keyword_map 関連キーワード collect_batch_text customizable boolean 0 に設定すると、collect_batch_text によって batch_text の収集が無効になります。既定では、batch_text 列が生成されます。 cpu_time data uint64 バッチによって使用される CPU 時間 (ミリ秒)。 duration data uint64 バッチ完了にかかった時間 (マイクロ秒)。 physical_reads data uint64 バッチによって発行された、物理ページの読み取り回数。 logical_reads data uint64 バッチによって発行された、論理ページの読み取り回数。 writes data uint64 NULL row_count data uint64 バッチによって返された行数。 result data rpc_return_result バッチの実行結果を示します。 batch_text data unicode_string 実行されているバッチのテキスト。
column_typeがdataの行が実行時に収集されるイベントデータです。column_typeがcustomizableの行は収集するかどうかを設定できます。readonlyは固定値？まだよくわからず。
ついでにrpc_completedイベントのイベントデータも確認しておきます。where句以外は上記と同じクエリです。
-- rpc_completedイベントのイベントデータを確認する select xe_oc.name as column_name, xe_oc.column_type as column_type, xe_oc.type_name as type_name, xe_oc.description as column_description from sys.dm_xe_packages as xe_p inner join sys.dm_xe_objects as xe_o on xe_p.guid = xe_o.package_guid inner join sys.dm_xe_object_columns as xe_oc on xe_o.package_guid = xe_oc.object_package_guid and xe_o.name = xe_oc.object_name where xe_o.object_type = N'event' and xe_p.name = N'sqlserver' and xe_o.name = N'rpc_completed';
結果。
column_name column_type type_name column_description -------------------------- ------------- ------------------------- -------------------------------------------------------------------------------------------------------------- UUID readonly guid_ptr グローバル一意 ID VERSION readonly uint8 イベント スキーマのバージョン CHANNEL readonly etw_channel ETW チャネル KEYWORD readonly keyword_map 関連キーワード collect_statement customizable boolean 0 に設定すると、collect_statement によって statement の収集が無効になります。既定では、statement 列が生成されます。 collect_data_stream customizable boolean 1 に設定すると、collect_data_stream によって data_stream の収集が有効になります。既定では、data_stream 列は生成されません。 collect_output_parameters customizable boolean 1 に設定すると、collect_output_parameters によって output_parameters の収集が有効になります。既定では、output_parameters 列は生成されません。 cpu_time data uint64 NULL duration data uint64 リモート プロシージャ呼び出しが完了するまでにかかった時間 (マイクロ秒)。 physical_reads data uint64 リモート プロシージャ呼び出しによって発行された、物理ページの読み取り回数。 logical_reads data uint64 リモート プロシージャ呼び出しによって発行された、論理ページの読み取り回数。 writes data uint64 NULL result data rpc_return_result リモート プロシージャ呼び出しからの戻り値。 row_count data uint64 リモート プロシージャ呼び出しによって返された行数。 connection_reset_option data connection_reset_option 接続のリセットに使用されたオプション。このフィールドは、接続のリセット リモート プロシージャ呼び出しでのみ使用できます。 object_name data unicode_string リモート プロシージャ呼び出しで参照されていたオブジェクトの名前。 statement data unicode_string リモート プロシージャ呼び出しで実行されたステートメントのテキスト。 data_stream data binary_data トレースの再生に使用される、リモート プロシージャ呼び出しのバイナリ ストリーム。 output_parameters data xml リモート プロシージャ呼び出しから返された出力パラメーター。
SQL Server 2016で確認しています。
SQL Server - トレースイベントに対応する拡張イベントを確認するクエリ
拡張イベントを調べています。勉強した小ネタを残していこうと思います。
今回はトレースイベントに対応する拡張イベントを確認するクエリです。sys.trace_eventsとsys.trace_xe_event_mapを使います。
例として次の2つのトレースイベントに対応する拡張イベントを取得したいとします。
クエリは次のような感じに。
-- トレースイベントに対応する拡張イベントを確認する select te.trace_event_id as te_event_id, -- トレースイベントID te.name as te_event_name, -- トレースイベント名 xe.package_name as xe_package_name, -- 拡張イベントパッケージ名 xe.xe_event_name as xe_event_name -- 拡張イベント名 from sys.trace_events as te inner join sys.trace_xe_event_map as xe on te.trace_event_id = xe.trace_event_id where te.name in (N'RPC:Completed', N'SQL:BatchCompleted'); -- 結果 /* te_event_id te_event_name xe_package_name xe_event_name ------------ -------------------- -------------------- -------------------- 10 RPC:Completed sqlserver rpc_completed 12 SQL:BatchCompleted sqlserver sql_batch_completed */
いずれは拡張イベントでいわゆるスロークエリを取得したいと思っているのでこの2つのイベントを例として選んでみました。
T-SQL - OVER句のROWS句のサンプル
OVER句のROWS句がすごいなと思ったので、少し整理していくつかサンプルを残しておきます。
OVER句を使うと行をグルーピングしたり、行に順位付けをしたり、行セットの範囲を指定したりといったことができます。
OVER句の括弧の中には次にようにPARTITION BY句、ORDER BY句、ROWS句を書きますが、今回はグルーピングするためのPARTITION BY句は使わず、集約関数とあわせて使うORDER BY句、ROWS句を見ていきたいと思います。
OVER(
PARTITION BY カラム名
ORDER BY カラム名
ROWS(RANGE) なんとか
)
ROWS句
ROWS句は結果セットの範囲を指定する機能です。現在の行からみた前後の行の範囲を指定します。おそらくこの範囲のことを「ウィンドウ」と言うのかなと。
次の書き方で行セットをAからBの範囲内に絞ります。
rows between A and B
AとBの書き方としては5パターンあります。
-- 最初の行 unbounded preceding -- 現在の行からみてn行前の行 n preceding -- 現在の行 current row -- 現在の行からみてn行後の行 n following -- 最後の行 unbounded following
AとBを埋めていくつかROWS句を書いてみます。
-- 最初の行から現在の行まで rows between unbounded preceding and current row -- 1つ前の行から1つの後の行まで rows between 1 preceding and 1 following -- 1つ前の行から1つ前の行まで（つまり1つ前の行） rows between 1 preceding and 1 preceding
ROWS句を使ったサンプル
ROWS句を使ったクエリを書いてみます。まずは月別の売上みたいなテストデータがあるとします。
drop table if exists #Sale; select * into #Sale from (values (N'201610', 100), (N'201611', 200), (N'201612', 300), (N'201701', 200), (N'201702', 400), (N'201703', 200)) as Src([Date], Sale); select * from #Sale order by [Date]; /* Date Sale ------ ----------- 201610 100 201611 200 201612 300 201701 200 201702 400 201703 200 */
移動平均を求める
例えば直近3ヶ月間の平均といったものを移動平均と言うようです。
ROWS句に2行前の行から現在の行を指定して、直近3ヶ月の移動平均を計算してみます。わかりやすいように直近3ヶ月の合計も計算しておきます。
-- 移動平均 select *, sum(Sale) over( order by [Date] rows between 2 preceding and current row ) as 直近3ヶ月の合計, avg(Sale) over( order by [Date] rows between 2 preceding and current row ) as 直近3ヶ月の移動平均 from #Sale order by [Date]; /* Date Sale 直近3ヶ月の合計 直近3ヶ月の移動平均 ------ ----------- ----------- ----------- 201610 100 100 100 201611 200 300 150 201612 300 600 200 201701 200 700 233 201702 400 900 300 201703 200 800 266 */
1行目（Dateが201610）は前月と前々月の行がないので、合計も平均も現在の行の値そのままになっています。2行目（Dateが201611）は前々月の行がないので2ヶ月分の合計と平均になっています。
累計を求める
最初の行からその行までの累計を計算してみます。
-- 累計 select *, sum(Sale) over( order by [Date] rows between unbounded preceding and current row ) as 累計 from #Sale order by [Date]; /* Date Sale 累計 ------ ----------- ----------- 201610 100 100 201611 200 300 201612 300 600 201701 200 800 201702 400 1200 201703 200 1400 */
実はこれ数年前に試しました。
このとき書いたクエリはこのROWS句を省略したようなものでした。（このときはROWS句をよく理解していなかったような気が・・・。）
別の行と比較する
ROWS句では1つ前の1行、1つ後の1行というようにピンポイントで行を指定できるので、ある行と現在の行を比較することもできます。1つの前の行と比較して、前月からの増減を計算してみます。わかりやすいように前月のSaleの値も取得しています。
--- 前月からの増減 select *, sum(Sale) over( order by [Date] rows between 1 preceding and 1 preceding ) as 前月のSale, Sale - sum(Sale) over( order by [Date] rows between 1 preceding and 1 preceding ) as 前月からの増減 from #Sale order by [Date]; /* Date Sale 前月のSale 前月からの増減 ------ ----------- ----------- ----------- 201610 100 NULL NULL 201611 200 100 100 201612 300 200 100 201701 200 300 -100 201702 400 200 200 201703 200 400 -200 */
1行目（Dateが201610）は前月の行がないので前月の値がNULLになっています。
ROWS句すごいなと。
