SQLServerで複数レコードの文字列を結合

SQLServerにて、複数レコードの文字列を
結合して、単一フィールドの値として取得します。

例:グループに所属するユーザーを列挙する
以下の様なテーブルでユーザーとユーザーが所属するグループを管理している場合。

テーブル名:test_user(ユーザーマスタ)
WS000001

テーブル名:test_group(グループマスタ)
WS000002

テーブル名;group_user(グループとユーザーの連結情報)
WS000003

グループに所属するユーザーを取得しようと考える場合、
以下の様なSQLになるかと思います。


 SELECT A.group_code, A.group_name, C.user_name
 FROM test_group A
 INNER JOIN group_user B ON A.group_code = B.group_code
 INNER JOIN test_user C ON B.user_code = C.user_code
 ORDER BY A.group_code, C.user_code
 

結果はこの様になります。
WS000004

これはこれでいいのですが、
ユーザー名を羅列して、1フィールドの値として扱いたい場合、
ストアドプロシージャでカーソルを利用し、
user_nameを結合する必要が有ります。
もしくは、プログラムでこの結果を取得した後、
ループ処理を行うなど、何かしらの処理が必要になります。

もし、結合した結果を取得できれば、
上記の様な処理を記述する必要もなくなります。

SQLServerには結果をXML形式で取得する、
FOR XML句という記述形式が有ります。
FOR XML (SQL Server)
上記の例を使用し、以下のSQLを実行します。


 SELECT A.group_code, A.group_name,
 (SELECT '・' + D.user_name
 FROM test_group B
 INNER JOIN group_user C
 ON B.group_code = C.group_code
 INNER JOIN test_user D
 ON C.user_code = D.user_code
 WHERE B.group_code = A.group_code
 ORDER BY D.user_code
 FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)') AS group_user_name
 FROM test_group as A
 

結果はこの様になります。
WS000005

普通の結合では2レコードになるGroupAのユーザーを
1レコードで取得しています。

FOR XML句を使い、XML形式で出力したSQLの結果に対して、
value()メソッドを使い、Varchar型で取得し、
サブクエリとして呼び出しております。

サブクエリでの処理について解説します。
まずは、SELECT ~ ORDER BY D.user_codeまでは普通のSQLです。
SELECT句で「’・’」とuser_nameを結合しており、
WHEREでサブクエリ内のgroup_codeを指定しておりますので、
出力文字列はA.group_code=’G001’のときは、「・アイチャン」「・ワークン」
A.group_code=’G002’のときは「・アイコック 太郎」となります。

通常のSQLで処理し、行で取得した際には、「・アイチャン」「・ワークン」は
2レコードで出力されますが、
FOR XML句を使用しておりますので、XML形式で出力されます。
PATH(”)として、属性を付けておりませんので、
出力内容はこの様になります。
WS000006

もし、PATH(‘name’)と属性名を与えた場合、出力されるXMLはこの様になります。
WS000007
(ちなみに、属性名を付けていても、上記のSQLの結果は変わらないので、属性名を付ける付けないはお好みで良いかと思います)

FOR XML句で出力した値はXML形式で出力されるため、他のフィールドとは扱いが異なりますので、
value()メソッドを使い、通常のSQL型に変換します。
サブクエリを表す()の後に「.value(‘.’, ‘VARCHAR(MAX)’)」と記述しております。
value()メソッドの第一引数はXQuery式で、第二引数はSQL型となります。

第一引数の「’.’」はXQuery式「self::node()」の省略形、
第二引数では、ユーザー名を扱い、出力文字数の制限をしたくないので、
VARCHAR(MAX)としており、
出力されたXML形式のデータからvalue値を取得するため、
属性が指定されていようが関係なく、「・アイチャン・ワークン」といったvalue値を取得し、
第二引数で指定されたVARCHARとして、SQL型の結果を返します。
value() メソッド (xml データ型)

こうすることで、複数レコードを結合したデータを単一のレコードとして扱う事が可能になります。

ユーザー名をカンマ区切りで表示したい場合なんかは、
サブクエリのSELECT句を「SELECT user_name + ‘,’」と記述することで
可能となります。
ただし、文字列の最後にカンマがついている状態なので、
SUBSTRING()メソッドを使い、最後の文字を除外するなどの一手間は必要になります。

サブクエリをスカラー値関数として作成し、
抽出条件を引数として指定できるようにしておけば、
上記のカンマ区切りを実現するためのSUBSTRING()メソッドも書きやすくなるのではないかと思います。

文章で書くと難しくなりがちですが、
文字列を結合する程度の事であれば、簡単にできるため、
使う機会が有れば、試してみてはいかがでしょうか。

情報処理技術者試験の受験料が改定

以前、このブログの中で情報処理技術者試験について書きました。
都合がつかなく、今年の秋季試験は結局見送りました。
来年の春季試験もおそらく受験ができないので、来年の秋季試験をできるだけ受験できるように、、、と思いながらIPAのホームページを除いてると、受験料改定のお知らせが公開されていました。

http://www.jitec.ipa.go.jp/1_00topic/topic_20151222.html

受験者が増加することで、試験を運用する上での経費が増加するようになってきたのかなと推測。
個人的に負担が増えるのは残念ですが、仕方ないことですかね。
来年の春季試験から改定されるそうなので、受験される方はお気をつけください。

さて、このicocTechの2015年の更新は、私の当番で最後となります。
今年もicocTechを閲読していただき、ありがとうございました。

来年以降もさまざまな情報を書いていきますので、引き続きよろしくお願いいたします。

どうぞ良いお年をお迎えください。

OpenCV 3.0.0 正式版リリース

しばらく使ってないうちに、OpenCV 3.0.0の正式版がリリースされてましたので、調査してみました。

OpenCVがどんなものか知りたい人はこちらのサイトから。

OpenCVとは? 最新3.0の新機能概要とモジュール構成 – Build Insider

オープンソースなのに特許権を持つ特徴量検出アルゴリズムSURF、SIFTがリポジトリから外れ、代わりにライセンス的に使用しやすい『AKAZE』『KAZE』が追加されるという素晴らしいアップデートが行われております。

試してみました。

base1

base2

base3

 

被写体のアングルを変えた3枚の画像から特徴点のマッチングを行ってみます。

result1

result2

多少の誤認識はありますが、かなりの高精度でマッチングできてます。

感覚的にはSURFと同等くらいに実感しました。

その他詳しいベンチマーク・比較結果は以下のサイトで検証されてます。

OpenCV3でAKAZE特徴量を検出する

AKAZE特徴量の紹介と他特徴量との比較

ライセンス関係が緩くなってきたことから、OpenCV界隈でもかなり盛り上がってきているようなので、今後の動向が楽しみです。

[Oracle初心者運用メモ]SQLServerへのデータ移行

今回は、Oracleというより
SQLServerの技術情報になります。

客先のシステムでOracleを使っていましたが、
Windows Server 2003のサポート終了に伴いDBサーバを新しくすることになり
新サーバのOSに対応したバージョンのOracleを購入するか
SQL Serverに移行するかの検討を行い、
DBの規模がそこまで大きくなかったため、SQL Serverに移行することになりました。

プログラムの方は、Oracle独自のSQL構文を修正、
データ移行は、SQL Server Management Studioの「データのインポート」機能を
使用して移行を行いました。

■データのインポート
コピー元(Oracle側)の指定は
データソースの選択で「.Net Framework Data Provider for Oracle」を選択
ユーザID、Password、Data Sourceを指定します。

インポート

後は、通常のSQL Server間でインポートする時と同様の方法
となりますが、
OracleとSQL Serverでデータ型が異なる項目については
「コピー元のテーブルおよびビューの選択」画面で
テーブル毎に「マッピングの編集」で変換先の型を指定する必要があります。

今回、変換先の型を指定したものは次になります。
<Oracle> DATE ⇒ <SQL Server> datetime
<Oracle> VARCHAR2 ⇒ <SQL Server> varchar ※1バイト文字コードのみ項目の場合

 

テーブル毎に「マッピングに編集」を設定する作業が多少面倒ではありましたが
比較的簡単にデータ移行を行うことができました。

SQL Server移行後のシステムでのデータアクセス速度も
Oracleの時と同じくらいの速度で動作しており安心しました。

 

発話訓練システム

スピーチ関連で面白いと感じた記事があったので紹介します。

「スピーチマイスター」という商品名で、
開発元はブラザー工業株式会社となっています。

スピーチマイスターは、
通信カラオケの採点機能で培った「音声解析技術」や「採点技術」、
「モチベーション維持手法」などを応用したもので、
手本となるスピーチと練習者のスピーチを比較して採点をするというものです。
これにより熟練者や外部講師がいなくても自身の成長度合いを確認しながら、
練習を行うことができるようになります。

手本となるデータが必要とはなりますが、
抑揚や滑舌、速さなどが点数化されたり、
ランキングや過去の練習結果の履歴などもあり、
どれだけ成長できたのかが理解しやすいと感じました。

参考URL
http://www.brother.co.jp/news/2015/151109_speech/index.htm