SASとMySQLにおける「null値」の扱い方の違いによる問題

以前に携わった案件でRDBMSとしてMySQLを使い、帳票作成やデータの更新処理はSASを通して行うシステムの開発がありました。

その際に、SASとMySQLにおける「null値」の扱い方の違いで苦しめられたことがあったので、自分の備忘録も兼ねて書いておこうと思います。

まず、SASには【In-Datebase】というサービスがあります。
これがどういったものかざっくり説明すると
『DB側で処理できる処理はDB側で処理してもらっちゃおう！』
というものです。名前のまんまですね。

パススルーを用いてSQL文を直接DB側に投げて処理させることもできるのですが、この【In-Database】のすごいところは
『SASのコードで書いた処理でも、DB側で処理した方が良い処理は、自動変換してDB側で処理させちゃう！』
ところです。賢いですね！

ただ今回の件は、この賢さが故に起きてしまった問題なのですが・・・


それでは前置きはこのくらいにして、今回起きた問題について書いていこうと思います。

例えば、以下に示すようなデータがMySQL上に存在したとします。

テーブル名「sample」
スキーマに割り当てたライブラリ参照名「db」
※四郎さんの郵便番号は「ブランク」、五郎さんの郵便番号は「null値」

上記データを「郵便番号」・「年齢」の昇順でソートしたデータをSASのワーク領域に抽出したい場合

  proc sort data = db.sample
                out  = work.sample ;
    by post_no age ;
  run ;

となります。

なんの変哲もないただのソート文なのですが・・・
SASは「null値」と「ブランク」は同じ文字値の欠損値として扱うのに対し
DB上では「null値」と「ブランク」は別のものとして扱うため
上記のソート文を実行した時に作成される「work.sample」は
【In-Database】で処理を行った場合とそうでない場合で以下のような違いが生まれます。

【In-Database】で処理していない場合

【In-Database】で処理した場合

抽出後のデータはSASデータセットなので、「null値」は「ブランク」に変換されます。
そして、注目していただきたいのは四郎さんと五郎さんの並びです！

【In-Database】を用いない場合は、DBからデータをSAS側に抽出してからソートを行うため、ソートの段階で「null値」と「ブランク」の区別はありません。

そのため、四郎さんと五郎さんの「郵便番号」は同一となり、第２ソートキーの「年齢」によって昇順にソートされます。

これに対して、【In-Database】を用いた場合は、先ほどのソートはDB側で行われて、ソートした状態のデータをSAS側に送ります。

DB側では「null値」と「ブランク」は区別され、「null値」は「ブランク」よりも更に小さい値として処理されてしまうため、五郎さんの方が、昇順で上にきてしまいます。


さて、ソート順くらい。。。と思う方もいらっしゃるかと思うのですが、、、
ここで問題発生です！！！

上記の【In-Database】を用いてソートを行ったはずのデータセットですが、並び順が本来SASでソートした順番とは違うため、DATAステップ等でグループ処理を行おうとすると、もちろんエラーになります。

つまり、以下のプログラムを【In-Database】のサービスを利用している状況下で実行するとエラーになります。

  proc sort data = db.sample
                out  = work.sample ;
    by post_no age ;
  run ;

  data work.test ;
    set work.sample ;
    by post_no age ;
    if first.age = 1 then output ;
  run ;

ログには『「work.sample」はちゃんとソートされていません。』的なログが出ます。
目の前でソートしてるのに・・・


対処法としては、「null値」と「ブランク」が混在する可能性があるデータの場合、ソート処理の前にSASデータセットとして抽出してから処理を行うことです。

もっとそもそもな方法としては
『テーブルを作成する段階で制約を設けて「null値」で登録できないようにすること』
もしくは
『「null値」と「ブランク」が混在しないようなシステムにすること』
になります。

DBのテーブル定義は思ったより奥が深く、本当に色んなことができます。
DBをただのデータの入れ物にしないように、これからも勉強を続けていきたいです。

ちなみに今回の「null値、ブランク」問題は単純なデータ抽出(where句等で)を行う際にも立ちはだかるようです。
便利なサービスも使いこなしてこそですね。

SQLのトランザクション処理におけるデッドロックについて

今回は、以前に開発していたシステムで実際に起きたＳＱＬにおけるデッドロックについて、備忘録として書いていこうと思います。

まずはデッドロックについて少し前置きを・・・

例えば、ＤＢに対してトランザクション処理（一連の処理をまとめたもの）を行った際に、違うトランザクション処理と競合してしまい、処理結果が求めていたものと違う形になってしまったら、もちろん困りますよね？

もちろんこういった困ったことが起きないように、現在広く使われているＲＤＢＭＳでは、処理を行う対象のテーブルにロックをかけてから処理を行うことでこのような問題を防いでいます。

ただし！！

今回の問題は、このロックをかけることによって起きてしまいます。。

再び例え話なのですが、私がＤＢ上のあるテーブルに対してロックをかけようとした時に、既に違う誰かがロックをかけていたら、もちろんその人がロックを解くのを待つしかありませんね？

ただ、もしその既にロックをかけていた人も私がロックを解くのを待っていたとしたら？

お互いがお互いのロックが解かれるのを待っているのですから、一生待ち続けることになります。

そして、ロックをかけないと処理を行うことはできないため、この時点でトランザクション処理自体が先に進むことができなくなり、死んでしまいます。

つまり『デッドロック』です！

さて、そもそもお互いがお互いのロックを待つなんて不思議な状況は本当にあり得るのでしょうか？

まぁあり得なかったらこの記事はなんのための記事か分からないのですが・・・

全てのことの発端は、ロックには「排他ロック」と「共有ロック」という２種類のロックが存在することから始まります。

それぞれのロックについて簡単に説明すると・・・

【排他ロック】

専有ロックとも言われ、その名の通り対象のテーブルを専有してしまうロックです。

このロックがかかっている間は他の人はそのテーブルには手出しできません。

【共有ロック】

こちらも名前のまんまなのですが、他の人と共有できるロックになります。

私が共有ロックをかけている場合でも、違う人は同じテーブルに共有ロックをかけることができます。
ただし、排他ロックをかけることはできません。

はい。ざっくりではありますが、ここまででデッドロックとロックの種類に関する前置きは終了です。

それでは、今回起きた現象について、書いていこうと思います。

今回のシステムで実行していたトランザクション処理を言葉にすると

「テーブルにデータを登録した後に、不要になったデータを削除する」

という処理です。

例えば、テーブルに50件データを保持しておきたくて、データを新しく追加する度に古いデータから削除していく場合です。

今回はテスト用に適当なテーブルを用意して処理を行います。

  create table sample(

    id INT(11) NOT NULL AUTO_INCREMENT ,

    detail VARCHAR(10) ,

    PRIMARY KEY (id) ) ;

※「id」にはAUTO_INCREMENTを指定しているので、値を指定しない場合は前に挿入したレコードにの値に＋1した値を設定していきます。

それでは以下トランザクションのＳＱＬ文です。

【トランザクション１】

  begin ;

    /* インサート */

    insert into sample(detail) value ("test1") ;

    /* 削除対象「id」の値を変数に格納 */
    set @del_id1 = (select max(id) - 50 from sample) ;

    /* デリート */
    delete from sample where id < @del_id1 ;

  commit ;

【トランザクション２】

  begin ;

    /* インサート */

    insert into sample(detail) value ("test2") ;

    /* 削除対象「id」を変数に格納 */

    set @del_id2 = (select max(id) - 50 from sample) ;

    /* デリート */

    delete from sample where id < @del_id2 ;

  commit ;

※テーブルに50件以上データが入っていない場合は、削除処理は実行されますが、処理結果は0件です。

この２つのトランザクション処理について、【トランザクション１】のインサート文が実行されてから、デリート文が実行されるまでの間に、【トランザクション２】のインサート文が実行された場合、この２つのトランザクションの間でデッドロックが発生します。

さて、なぜデッドロックは発生したのでしょうか？

ここで大事になってくるのが、インサート文を実行することでテーブルにかけるロックは共有ロック、デリート文を実行することでテーブルにかけるロックは排他ロックだということです。

【トランザクション１】でインサート文を実行した時点で、【トランザクション１】はテーブルに共有ロックをかけます。

しかし、先程書いた通り、共有ロックがかかっている間でも、他の人も共有ロックを取得することができてしまうため、【トランザクション２】はインサート文を実行し、共有ロックを取得できます。

この状態で【トランザクション１】も【トランザクション２】もテーブルに対して共有ロックをかけている状態になります。

そして、それぞれのロックはコミットまたはロールバックされるまで解かれることはありません。

もうお分かりですよね？

つまり、【トランザクション１】はデリート文を実行するために排他ロックをかけようとした時に【トランザクション２】の共有ロックがはずれるのを待つことになり、【トランザクション２】もデリート文を実行するために排他ロックをかけようとした時に【トランザクション１】の共有ロックがはずれるのを待つことになるため、デッドロックが完成します。

言葉にすると上記の通りなのですが、図で説明すると以下の通りです。

ただ、少し考えれば分かることなのですが、対処法は簡単です。

『共有ロックをかける前に排他ロックをかけてあげる』だけです。

排他ロックを最初にかけてしまえば、もう一方のトランザクションはロックが解けてから排他ロックをかけて処理を行うため、デッドロックは発生しません。

つまり、デリート文を先に実行してあげる、もしくは排他ロックをトランザクションの一番最初に取得するだけで解決できます。

ちなみに以下のＳＱＬ文で簡単に排他ロックを取得できます。

  select *
  from [テーブル名]
  for update ;

そのため、トランザクションの一番最初で対象のテーブルに対して、上記SQL文で排他ロックを取得してあげれば、デッドロックは発生しません。

ちなみに、排他ロックについては、where句で行単位に取得することができたり、ロックをかけようとして、既に違う人がロックをかけていた場合の待ち時間を指定したりもできるので、興味がある方は是非調べてみて下さい！

SQLプロシジャのちょっとしたTips2つ

FEEDBACKオプション

proc sql ;
  create table temp1 as
  select t1.*,
         t2.product_name,
         t2.product_price
  from sales as t1 left join master as t2
  on t1.product_code=t2.product_code
quit ;

のようにSELECT句で*を使用する場合に、

proc sql feedback ;
  create table temp1 as
  select t1.*,
     ～

と書くと、ログに*の内容が出力される

DESCRIBEステートメント

■Viewの場合
例えば上記と同じようなSQLプロシジャでViewを定義してある場合、

proc sql ;
  create view temp1_vw as
  select t1.*,
         t2.product_name,
         t2.product_price
  from sales as t1 left join master as t2
  on t1.product_code=t2.product_code
quit ;

以下のようなSQLプロシジャを実行すると、

proc sql ;
  describe view temp1_vw ;
quit ;

ログにViewの定義情報が出力される（クエリ内容）

■Tableの場合
同じようにSQLプロシジャでTableを作成してある場合、

proc sql ;
  create table temp1_tb as
  select t1.*,
         t2.product_name,
         t2.product_price
  from sales as t1 left join master as t2
  on t1.product_code=t2.product_code
quit ;

以下のようなSQLプロシジャを実行すると、

proc sql ;
  describe table temp1_tb ;
quit ;

ログにTableの定義情報が出力される（ラベル、フォーマット、レングス等）

SQLプロシジャを利用したSASデータセットの存在チェック

photo credit: bugeaters

売上データなどを利用して分析をする場合、複数のデータテーブルを同時に読み込んで処理を実行することがあるかと思います。

例えば・・・

　・売上データを過去10年分読み込み売上の推移を求める。
　・直近1年分の売上データ、予算目標データを読み込んで売上目標に満たない店舗を求める。

などなど様々な要件が考えられます。

その様々な要件に応じて対象となるデータを読み込むように処理を作る必要があるわけです。
プログラムを組むという観点では、処理を実行する前に対象となるデータテーブル(SASの場合だとSASデータセット)が存在している事を確認した上で処理を実行するほうが望ましいです。

SASデータセットの存在確認のプログラム例

%SYSFUNC(EXIST(データセット名)) ;

しかし、上記の例で上げたように過去10年分のデータを読み込むなどの場合は、存在確認を10年分記述するのは大変かと思います。
データ件数が膨大だと予想される売上データなどは、月毎にデータを分けて保存していると考えられます。
10年分なので、120ヶ月分の存在確認を記述することになります。
正直大変なので、そのままの方法ではやりたくありません。

WORKの下に以下のようにデータがあるとします。



以下のようなプログラムを使用して、データの確認を行うことが出来ます。

proc sql;
　select
　　min(substr(memname,8,6)) as MIN,
　　max(substr(memname,8,6)) as MAX,
　　count(*) as COUNT_TBL
　from
　　dictionary.tables
　where
　　libname = "ライブラリ名" and
　　substr(memname,1,7) = "データセット名称（一部）"
　;
quit;

※上記のデータセットの存在を確認する場合は、ライブラリ名に"WORK"、データセット名称には"URIAGE_"を入れます。

実行結果例






DICTIONARY.TABLES にはlibnameで追加された先のSASデータセット（ビュー）、workで作成されたSASデータセット（ビュー）の情報が格納されています。
データセットの作成、または削除された時に意識しなくても更新がかかります。
このテーブルには　どのライブラリに存在するかを示す"libname"、とSASデータセット名称を示す"memname"があります。
それを利用してデータセットの確認をしてしまうという方法です。

上記のプログラムはWORKライブラリにある、データセット名称の頭７文字が"URIAGE_"で始まるデータセットの件数を求めるプログラムです。

　・件数が処理対象になる年月の数と同じになること。
　・SQLで返される結果の最大の年月が、処理対象の最大年月と同じになることを確認する。
　・SQLで返される結果の最小の年月が、処理対象の最小年月と同じになることを確認する。

このことにより論理的にデータのチェックを行うことが出来ます。


ただし、この方法には弱点として、読み込む対象のSASデータセット名称が一定の規則で作成されていることが条件になります。
何らかの方法で対象のデータセット群だけを特定する方法が明確である必要があります。

例）　売上データの場合、URIAGE_[年月]という名称の13文字のデータセットで作成する。

などの場合は頭文字７文字分が『URIAGE_』であれば売上のデータとして特定できるため使用ができますが、仮にURIAGE_YOSANというデータセットが存在した場合は正しい件数が取得できなくなる恐れがありますので、抽出条件を変えて特定できるようにするなど工夫が必要です。

投稿者：NI原

[google]

便利な条件分岐処理 CASE式を使ってみよう！

条件分岐処理は、SASに限らず様々なプログラミング言語で不可欠なものです。

SASのDATAステップ処理においては、おなじみの

・IF　条件式　THEN　処理　;

・IF　条件式　THEN　処理１　;

ELSE　処理２　;

・IF　条件式１　THEN　処理１　;

ELSE IF　条件式２　THEN　処理２　;

や、SELECTステートメントを使用した、

・SELECT（変数名）　;

WHEN（条件１）処理１　;

WHEN（条件２）処理２　;

OTHERWISE　;

END　;

・SELECT　；

WHEN（条件式１）処理１　;

WHEN（条件式２）処理２　;

OTHERWISE　;

END ;

などの書き方があります。

では、SQLプロシジャでの条件分岐処理はどのように記述すればいいでしょうか。

例えば、以下のようなSASデータセットがあるとき、



変数VAR2の値が偶数のときは、変数VAR3に"偶数"を、

変数VAR2の値が奇数のときは、変数VAR3に"奇数"を格納する処理をしてみます。

proc sql ;

create table temp2 as

select var1,

var2,

case mod(var2,2)

when 0 then "偶数"

else "奇数"

end as var3

from temp ;

quit ;

構文は以下の通りです。

CASE 変数名　WHEN 変数の値１　THEN 返す値１　WHEN 変数の値２　THEN 返す値２　～　ELSE 返す値　END

CASEの後に変数名と書きましたが、上記プログラムのように関数を直接記述することも可能です。

その場合は、変数の値と書いた箇所を関数の戻り値と解釈して下さい。

また、CASE式は「IF　～　THEN　～　; ELSE IF　～　THEN　～　; ELSE　～　;」のように評価していきます。

よって、真と評価された時点でCASE式を抜けます。また、全て偽と評価されればELSE以降の処理が行われます。

記述方法は少し異なりますが、以下のような書き方も可能です。

proc sql ;

create table temp3 as

select var1,

var2,

case

when mod(var2,2)=0 then "偶数"

else "奇数"

end as var3

from temp ;

quit ;

最後にそれぞれの実行ログと出力結果を掲載しておきます。

[google]

2つのテーブルの片方にだけ存在するキーを抽出する方法

SASでは、当然のことながらテーブルの結合は欠かせません。

DATAステップで行うのであれば、

縦結合の場合はSETステートメント、横結合の場合はMERGEステートメントが一般的です。

SQL（SQLプロシジャ）でもそのほとんどの処理（あるいはそれ以上の処理）を

記述することができます。

今回は、記事のタイトルの通り、

「2つのテーブルの片方にだけ存在するキーを抽出する」方法を掲載します。

以下のようなSASデータセット『temp1』と『temp2』を使います。

『temp1.sas7bdat』



『temp2.sas7bdat』



ここでは、『temp1』にだけ存在するキーを抽出してみます。

つまり、変数KEYが5のレコードのみを抽出します。

まずは、DATAステップのMERGEステートメントでの実装例です。

data temp3_mrg ;

merge temp1(in=in1) temp2(in=in2) ;

by key ;

if in1 and ^in2 ;

run ;

これは基本ですね。

IN=オプションで、「in1」「in2」という変数名をMERGEステートメントで指定して、

BYステートメントでは結合キー（ここでは変数KEY）を指定します。

サブセット化IFステートメントでは、結合の際の抽出条件を指定します。

『temp1』にだけ存在するレコードを抽出するので、"in1 and ^in2"という条件文になります。

出力結果は以下の通りです。



では、これと同じ結果をSQLプロシジャで出してみます。

まずは、サブクエリを使用するパターンです。

proc sql ;

create table temp3_sql1 as

select temp1.key from temp1

where not exists

(select * from temp2

where temp1.key = temp2.key

)

;

quit ;

NOT EXISTSとサブクエリを併用します。

サブクエリでは、変数KEYでマッチする『temp2』のレコードを抽出。

サブクエリの結果に対してNOT EXISTSな『temp1』のレコードを抽出。

結果、『temp1』にだけ存在するレコードが抽出されます。

次は、サブクエリを使用しない方法です。

LEFT JOIN（左外部結合）とWHERE句の組み合わせで可能になります。

proc sql ;

create table temp3_sql2 as

select temp1.key

from temp1 left join temp2

on temp1.key = temp2.key

where temp2.key = .

;

quit ;

左外部結合は、左側のテーブル（ここでは『temp1』）を優先して全レコードを出力します。

それだけでは、『temp1』のレコードが全て抽出されるだけなので、

抽出条件をWHERE句に記述します。

ここでは、『temp1』にだけ存在する変数KEYを出力したいので、

"temp2.key = .(←欠損値)"という抽出条件を追加します。

なぜ、このような条件文になるかというと、

まず、変数KEYが1～3のときは、『temp2』にも同じ値が存在するので条件文を満たしません。

次に、変数KEYが5のときは、『temp2』に同じ値が存在しないので『temp2』の変数KEYは欠損値になります。

（ちなみに、『temp2』だけに存在する値（ここでは4）は、結合方法が左外部結合なので評価されません。）

参考までに、両方の実行ログと出力結果を載せておきます。

[google]

COUNT関数の注意点

SQLのCOUNT関数は、クエリ結果の件数を返します。

非常に便利で手軽に使える関数ですが、若干注意が必要です。

まず、以下のようなプログラムを実行してみます。

data temp ;

do i=1 to 10 ;

output ;

end ;

run ;

proc sql noprint ;

select count(i) into: cnt

from temp

;

quit ;

%put cnt ***** &cnt ***** ;

結果は下記のとおりです。



なんのことはないですね。10が返ってきています。

では、次のプログラムを実行するとどうでしょうか？

data temp ;

do i=1 to 10 ;

if mod(i,2) then j=i ;

else j=. ;

output ;

end ;

run ;

proc sql noprint ;

select count(j) into: cnt

from temp

;

quit ;

%put cnt ***** &cnt ***** ;

MOD関数を使って、変数iが奇数のときは変数jに変数iの値を、

変数iが偶数のときは変数jに欠損値を格納した場合の、変数jの件数を求めるクエリです。

結果は下記のとおりです。



10ではなく5が返ってきました。

この結果を見れば分かると思いますが、

COUNT関数は、指定した引数が欠損値の場合は件数をカウントしません。

もちろん、有効な値が入っている件数を求める目的であれば問題ありませんが、

有効な値・欠損値を問わずに件数を求める場合には期待する結果が得られません。

欠損値を含んでいるか否かが分からない場合は、

下記のように条件を加えてあげるといいでしょう。

proc sql noprint ;

select count(case j when . then 0 else 1 end) into: cnt

from temp

;

quit ;

%put cnt ***** &cnt ***** ;

[google]