おだのスペース

SQL Server のグラフを色々試していくシリーズ。

3回目はレコメンド検索です。
この商品を買った人はこんな商品も買っていますっていうよくあるやつです。

今回はイベントのセッションを対象にして、このセッションを受けた人はこんなセッションも受けていますっていうので試します。

いつものように比較用に通常のテーブルでも試します。
可視化は Neo4j ですが、見難いですね。
f:id:odashinsuke:20180108125814p:plain

ノードが、セッション、参加者でエッジがセッション参加者です。

drop table G_セッション
drop table G_参加者
drop table G_セッション参加者

create table G_セッション (
  Id int not null primary key,
  セッション名 nvarchar(20) not null,
  ルーム char(1) not null,
  コマ int not null 
) as Node
create table G_参加者 (
  Id int not null primary key,
  名前 nvarchar(20) not null
) as Node
create table G_セッション参加者 as Edge

insert into G_セッション values
(1, N'セッションA-1', 'A', 1),
(2, N'セッションA-2', 'A', 2),
(3, N'セッションA-3', 'A', 3),
(4, N'セッションA-4', 'A', 4),
(5, N'セッションB-1', 'B', 1),
(6, N'セッションB-2', 'B', 2),
(7, N'セッションB-3', 'B', 3),
(8, N'セッションB-4', 'B', 4)

insert into G_参加者 values
(1, N'参加者1'),
(2, N'参加者2'),
(3, N'参加者3'),
(4, N'参加者4'),
(5, N'参加者5'),
(6, N'参加者6'),
(7, N'参加者7'),
(8, N'参加者8'),
(9, N'参加者9'),
(10, N'参加者10'),
(11, N'参加者11'),
(12, N'参加者12'),
(13, N'参加者13'),
(14, N'参加者14'),
(15, N'参加者15'),
(16, N'参加者16'),
(17, N'参加者17'),
(18, N'参加者18'),
(19, N'参加者19'),
(20, N'参加者20')

declare @s1 nvarchar(max) = (select $node_id from G_セッション where Id = 1)
declare @s2 nvarchar(max) = (select $node_id from G_セッション where Id = 2)
declare @s3 nvarchar(max) = (select $node_id from G_セッション where Id = 3)
declare @s4 nvarchar(max) = (select $node_id from G_セッション where Id = 4)
declare @s5 nvarchar(max) = (select $node_id from G_セッション where Id = 5)
declare @s6 nvarchar(max) = (select $node_id from G_セッション where Id = 6)
declare @s7 nvarchar(max) = (select $node_id from G_セッション where Id = 7)
declare @s8 nvarchar(max) = (select $node_id from G_セッション where Id = 8)

declare @u1 nvarchar(max) = (select $node_id from G_参加者 where Id = 1)
declare @u2 nvarchar(max) = (select $node_id from G_参加者 where Id = 2)
declare @u3 nvarchar(max) = (select $node_id from G_参加者 where Id = 3)
declare @u4 nvarchar(max) = (select $node_id from G_参加者 where Id = 4)
declare @u5 nvarchar(max) = (select $node_id from G_参加者 where Id = 5)
declare @u6 nvarchar(max) = (select $node_id from G_参加者 where Id = 6)
declare @u7 nvarchar(max) = (select $node_id from G_参加者 where Id = 7)
declare @u8 nvarchar(max) = (select $node_id from G_参加者 where Id = 8)
declare @u9 nvarchar(max) = (select $node_id from G_参加者 where Id = 9)
declare @u10 nvarchar(max) = (select $node_id from G_参加者 where Id = 10)
declare @u11 nvarchar(max) = (select $node_id from G_参加者 where Id = 11)
declare @u12 nvarchar(max) = (select $node_id from G_参加者 where Id = 12)
declare @u13 nvarchar(max) = (select $node_id from G_参加者 where Id = 13)
declare @u14 nvarchar(max) = (select $node_id from G_参加者 where Id = 14)
declare @u15 nvarchar(max) = (select $node_id from G_参加者 where Id = 15)
declare @u16 nvarchar(max) = (select $node_id from G_参加者 where Id = 16)
declare @u17 nvarchar(max) = (select $node_id from G_参加者 where Id = 17)
declare @u18 nvarchar(max) = (select $node_id from G_参加者 where Id = 18)
declare @u19 nvarchar(max) = (select $node_id from G_参加者 where Id = 19)
declare @u20 nvarchar(max) = (select $node_id from G_参加者 where Id = 20)

insert into G_セッション参加者 values
(@u1, @s1),
(@u5, @s1),
(@u10, @s1),
(@u11, @s1),
(@u2, @s5),
(@u3, @s5),
(@u4, @s5),
(@u6, @s5),
(@u7, @s5),
(@u8, @s5),
(@u9, @s5),
(@u12, @s5),
(@u13, @s5),
(@u15, @s5),
(@u17, @s5),
(@u20, @s5),
(@u1, @s2),
(@u2, @s2),
(@u3, @s2),
(@u5, @s2),
(@u7, @s2),
(@u9, @s2),
(@u10, @s2),
(@u11, @s2),
(@u13, @s2),
(@u16, @s2),
(@u18, @s2),
(@u4, @s6),
(@u6, @s6),
(@u8, @s6),
(@u12, @s6),
(@u15, @s6),
(@u17, @s6),
(@u19, @s6),
(@u1, @s3),
(@u3, @s3),
(@u6, @s3),
(@u7, @s3),
(@u9, @s3),
(@u10, @s3),
(@u12, @s3),
(@u14, @s3),
(@u2, @s7),
(@u4, @s7),
(@u8, @s7),
(@u11, @s7),
(@u15, @s7),
(@u16, @s7),
(@u17, @s7),
(@u18, @s7),
(@u19, @s7),
(@u20, @s7),
(@u1, @s4),
(@u2, @s4),
(@u3, @s4),
(@u4, @s4),
(@u5, @s4),
(@u6, @s4),
(@u7, @s4),
(@u8, @s4),
(@u9, @s4),
(@u10, @s4),
(@u11, @s8),
(@u12, @s8),
(@u13, @s8),
(@u14, @s8),
(@u15, @s8),
(@u16, @s8),
(@u17, @s8),
(@u18, @s8),
(@u19, @s8),
(@u20, @s8)

ついでに通常のテーブルの方も作ってしまいましょう。

drop table セッション
drop table 参加者
drop table セッション参加者

create table セッション (
  Id int not null primary key, 
  セッション名 nvarchar(20) not null, 
  ルーム char(1) not null,
  コマ int not null 
)
create table 参加者 (
  Id int not null primary key, 
  名前 nvarchar(20) not null
)
create table セッション参加者 (
  参加者Id int not null, 
  セッションId int not null, 
  primary key (参加者Id, セッションId)
)

insert into セッション values
(1, N'セッションA-1', 'A', 1), 
(2, N'セッションA-2', 'A', 2), 
(3, N'セッションA-3', 'A', 3), 
(4, N'セッションA-4', 'A', 4), 
(5, N'セッションB-1', 'B', 1), 
(6, N'セッションB-2', 'B', 2), 
(7, N'セッションB-3', 'B', 3), 
(8, N'セッションB-4', 'B', 4)

insert into 参加者 values
(1, N'参加者1'), 
(2, N'参加者2'), 
(3, N'参加者3'), 
(4, N'参加者4'), 
(5, N'参加者5'), 
(6, N'参加者6'), 
(7, N'参加者7'), 
(8, N'参加者8'), 
(9, N'参加者9'), 
(10, N'参加者10'), 
(11, N'参加者11'), 
(12, N'参加者12'), 
(13, N'参加者13'), 
(14, N'参加者14'), 
(15, N'参加者15'), 
(16, N'参加者16'), 
(17, N'参加者17'), 
(18, N'参加者18'), 
(19, N'参加者19'), 
(20, N'参加者20')

insert into セッション参加者 values
(1, 1), 
(5, 1), 
(10, 1), 
(11, 1), 
(2, 5), 
(3, 5), 
(4, 5), 
(6, 5), 
(7, 5), 
(8, 5), 
(9, 5), 
(12, 5), 
(13, 5), 
(15, 5), 
(17, 5), 
(20, 5),
(1, 2), 
(2, 2),
(3, 2),
(5, 2), 
(7, 2),
(9, 2), 
(10, 2), 
(11, 2),
(13, 2),
(16, 2),
(18, 2),
(4, 6), 
(6, 6), 
(8, 6), 
(12, 6),
(15, 6),
(17, 6),
(19, 6),
(1, 3), 
(3, 3), 
(6, 3), 
(7, 3),
(9, 3),
(10, 3),
(12, 3),
(14, 3),
(2, 7),
(4, 7),
(8, 7),
(11, 7),
(15, 7),
(16, 7),
(17, 7),
(18, 7),
(19, 7),
(20, 7),
(1, 4), 
(2, 4),
(3, 4),
(4, 4),
(5, 4),
(6, 4),
(7, 4),
(8, 4),
(9, 4),
(10, 4),
(11, 8),
(12, 8),
(13, 8),
(14, 8),
(15, 8),
(16, 8),
(17, 8),
(18, 8),
(19, 8),
(20, 8)

レコメンドなので普通に繋いでいきます。
お題は、
セッション.Id = 3 を受講している参加者.Id = 1 以外の参加者が受講している他のセッションを受講者が多い順に並べる
でいきます。

select
  レコメンドセッション.Id
  , レコメンドセッション.セッション名
  , レコメンドセッション.ルーム
  , レコメンドセッション.コマ
  , count(*) as 受講者数
from
  G_参加者 対象参加者
  , G_セッション 対象セッション 
  , G_セッション参加者 対象セッション_E
  , G_参加者 対象セッション参加者_対象参加者除く 
  , G_セッション参加者 他の参加者が受けてるセッション
  , G_セッション レコメンドセッション
where
  対象参加者.Id = 1
  and 対象セッション.Id = 3
  and 対象セッション参加者_対象参加者除く.Id <> 対象参加者.Id
  and レコメンドセッション.Id <> 対象セッション.Id
  and match (対象セッション <- (対象セッション_E) - 対象セッション参加者_対象参加者除く 
             - (他の参加者が受けてるセッション) -> レコメンドセッション)
group by
  レコメンドセッション.Id
  , レコメンドセッション.セッション名
  , レコメンドセッション.ルーム
  , レコメンドセッション.コマ
order by 
  受講者数 desc

f:id:odashinsuke:20180108130217p:plain
そのままな感じの where の内容ですね。
match を使うことで繋がりが見やすくなってるのかな？

通常のテーブルはこちら。

select
  レコメンドセッション.Id
  , レコメンドセッション.セッション名
  , レコメンドセッション.ルーム
  , レコメンドセッション.コマ
  , count(*) as 受講者数
from
  参加者 対象参加者
  , セッション 対象セッション
  , セッション参加者 対象セッション参加者_対象参加者除く
  , セッション参加者 他の参加者が受けてるセッション
  , セッション レコメンドセッション
where
  対象参加者.Id = 1
  and 対象セッション.Id = 3
  and 対象セッション.Id = 対象セッション参加者_対象参加者除く.セッションId 
  and 対象参加者.Id <> 対象セッション参加者_対象参加者除く.参加者Id
  and 対象セッション参加者_対象参加者除く.参加者Id = 他の参加者が受けてるセッション.参加者Id
  and 他の参加者が受けてるセッション.セッションId <> 対象セッション.Id
  and 他の参加者が受けてるセッション.セッションId = レコメンドセッション.Id
group by
  レコメンドセッション.Id
  , レコメンドセッション.セッション名
  , レコメンドセッション.ルーム
  , レコメンドセッション.コマ
order by 
  受講者数 desc

and 対象セッション参加者_対象参加者除く.参加者Id = 他の参加者が受けてるセッション.参加者Id
and 他の参加者が受けてるセッション.セッションId = レコメンドセッション.Id

が match

match (対象セッション <- (対象セッション_E) - 対象セッション参加者_対象参加者除く - (他の参加者が受けてるセッション) -> レコメンドセッション)

の部分の代替ですが、そんな変わらないかな？

レコメンドの例は、Ignite の
Graph extensions in Microsoft SQL Server 2017 and Azure SQL Database
でも紹介されていましたが、こちらは通常のテーブルを使ったクエリの書き方が「非常に悪い」ためグラフテーブルのクエリが見やすく感じる内容になってます。

ですが、実際に書いた感じだとそんな変わらないかな？
SQL Server のグラフで最短経路と取ってみる - おだのスペース
 SQL Server のグラフで巡回セールスマン問題をやってみる - おだのスペース
前回、前々回と色々試してみましたが、クエリ書いた感じは通常のテーブルとそんな変わらないですが、パフォーマンスはどうなんでしょうねー？
グラフについては、今後の機能追加でクエリが書きやすくなるそうなので、そこに期待ですね。

今回も Neo4j の cypher 貼っときますが、そんな違いないかな？

create (s1:Session{Id:1, Name:"セッションA-1", Room:"A", Schedule:1}), 
  (s2:Session{Id:2, Name:"セッションA-2", Room:"A", Schedule:2}),
  (s3:Session{Id:3, Name:"セッションA-3", Room:"A", Schedule:3}), 
  (s4:Session{Id:4, Name:"セッションA-4", Room:"A", Schedule:4}), 
  (s5:Session{Id:5, Name:"セッションB-1", Room:"B", Schedule:1}), 
  (s6:Session{Id:6, Name:"セッションB-2", Room:"B", Schedule:2}),
  (s7:Session{Id:7, Name:"セッションB-3", Room:"B", Schedule:3}), 
  (s8:Session{Id:8, Name:"セッションB-4", Room:"B", Schedule:4}),  
  (u1:Attendee{Id:1, Name:"参加者1"}),
  (u2:Attendee{Id:2, Name:"参加者2"}),
  (u3:Attendee{Id:3, Name:"参加者3"}),
  (u4:Attendee{Id:4, Name:"参加者4"}),
  (u5:Attendee{Id:5, Name:"参加者5"}),
  (u6:Attendee{Id:6, Name:"参加者6"}),
  (u7:Attendee{Id:7, Name:"参加者7"}),
  (u8:Attendee{Id:8, Name:"参加者8"}),
  (u9:Attendee{Id:9, Name:"参加者9"}),
  (u10:Attendee{Id:10, Name:"参加者10"}),
  (u11:Attendee{Id:11, Name:"参加者11"}),
  (u12:Attendee{Id:12, Name:"参加者12"}),
  (u13:Attendee{Id:13, Name:"参加者13"}),
  (u14:Attendee{Id:14, Name:"参加者14"}),
  (u15:Attendee{Id:15, Name:"参加者15"}),
  (u16:Attendee{Id:16, Name:"参加者16"}),
  (u17:Attendee{Id:17, Name:"参加者17"}),
  (u18:Attendee{Id:18, Name:"参加者18"}),
  (u19:Attendee{Id:19, Name:"参加者19"}),
  (u20:Attendee{Id:20, Name:"参加者20"}),
  (u1)-[:Attend]->(s1),
  (u5)-[:Attend]->(s1),
  (u10)-[:Attend]->(s1),
  (u11)-[:Attend]->(s1),
  (u2)-[:Attend]->(s5),
  (u3)-[:Attend]->(s5),
  (u4)-[:Attend]->(s5),
  (u6)-[:Attend]->(s5),
  (u7)-[:Attend]->(s5),
  (u8)-[:Attend]->(s5),
  (u9)-[:Attend]->(s5),
  (u12)-[:Attend]->(s5),
  (u13)-[:Attend]->(s5),
  (u15)-[:Attend]->(s5),
  (u17)-[:Attend]->(s5),
  (u20)-[:Attend]->(s5),
  (u1)-[:Attend]->(s2),
  (u2)-[:Attend]->(s2),
  (u3)-[:Attend]->(s2),
  (u5)-[:Attend]->(s2),
  (u7)-[:Attend]->(s2),
  (u9)-[:Attend]->(s2),
  (u10)-[:Attend]->(s2),
  (u11)-[:Attend]->(s2),
  (u13)-[:Attend]->(s2),
  (u16)-[:Attend]->(s2),
  (u18)-[:Attend]->(s2),
  (u4)-[:Attend]->(s6),
  (u6)-[:Attend]->(s6),
  (u8)-[:Attend]->(s6),
  (u12)-[:Attend]->(s6),
  (u15)-[:Attend]->(s6),
  (u17)-[:Attend]->(s6),
  (u19)-[:Attend]->(s6),
  (u1)-[:Attend]->(s3),
  (u3)-[:Attend]->(s3),
  (u6)-[:Attend]->(s3),
  (u7)-[:Attend]->(s3),
  (u9)-[:Attend]->(s3),
  (u10)-[:Attend]->(s3),
  (u12)-[:Attend]->(s3),
  (u14)-[:Attend]->(s3),
  (u2)-[:Attend]->(s7),
  (u4)-[:Attend]->(s7),
  (u8)-[:Attend]->(s7),
  (u11)-[:Attend]->(s7),
  (u15)-[:Attend]->(s7),
  (u16)-[:Attend]->(s7),
  (u17)-[:Attend]->(s7),
  (u18)-[:Attend]->(s7),
  (u19)-[:Attend]->(s7),
  (u20)-[:Attend]->(s7),
  (u1)-[:Attend]->(s4),
  (u2)-[:Attend]->(s4),
  (u3)-[:Attend]->(s4),
  (u4)-[:Attend]->(s4),
  (u5)-[:Attend]->(s4),
  (u6)-[:Attend]->(s4),
  (u7)-[:Attend]->(s4),
  (u8)-[:Attend]->(s4),
  (u9)-[:Attend]->(s4),
  (u10)-[:Attend]->(s4),
  (u11)-[:Attend]->(s8),
  (u12)-[:Attend]->(s8),
  (u13)-[:Attend]->(s8),
  (u14)-[:Attend]->(s8),
  (u15)-[:Attend]->(s8),
  (u16)-[:Attend]->(s8),
  (u17)-[:Attend]->(s8),
  (u18)-[:Attend]->(s8),
  (u19)-[:Attend]->(s8),
  (u20)-[:Attend]->(s8)

match (s:Session {Id:3})<-[:Attend]-(a:Attendee)-[:Attend]->(r:Session) 
where a.Id <> 1 
return r, count(*) as cnt 
order by cnt desc

だいぶ前にこれ書いて放置してたんですが、
.NET Core で SqlClient と TransactionScope は 2.1 から？ - おだのスペース

ムッシュが何か書いてたので久々に検証してみました。
SQL Server 2017 の on Linux における分散トランザクションのサポート状況について at SE の雑記

相変わらず System.Data.SqlClient v4.4.2 では例外出るんですが、 v4.5 の preview では動くらしいので、v4.5 の preview で試してみました。
dotnet-core - System.Data.SqlClient 4.5.0-preview2-25707-02 - MyGet - Hosting your NuGet, npm, Bower, Maven, PHP Composer and Vsix packages

Install-Package System.Data.SqlClient -Version 4.5.0-preview2-25707-02 -Source https://dotnet.myget.org/F/dotnet-core/api/v3/index.json

これで動かすと System.Diagnostics.DiagnosticSource のバージョンが違うって例外が出ますが、System.Diagnostics.DiagnosticSource のバージョンも上げてあげると動きました。

Install-Package System.Diagnostics.DiagnosticSource -Version 4.5.0-preview2-25707-02 -Source https://dotnet.myget.org/F/dotnet-core/api/v3/index.json

これでちゃんとトランザクション効いてる動きしてます。

Dependencies 見ると他にも preview なのありますが、この程度のソースなら DiagnosticSource だけ preview にしたらいけるみたいです。

using System;
using System.Data.SqlClient;
using System.Transactions;

namespace ConsoleApp3
{
    class Program
    {
        static void Main(string[] args)
        {
            var connstr = @"～";
            using (var tran = new TransactionScope())
            using (var conn = new SqlConnection(connstr))
            {
                conn.Open();
                // 何か更新
                // 更新したデータ引っ張てくる
                // tran.Complete(); ロールバックされることを確認する
            }
        }
    }
}

というわけで v4.5 待ちですねー。
ほんと早く使わせてくれ。。

おだのスペース

SSMS スクリプト作成の詳細オプションで指定可能なエディションがいつの間にか増えてた

メモ T-SQL で Function の名前一覧を取る

メモ：xUnit.net で output

メモ：xUnit.net でテストを並列で動かすのを辞める (not run in parallel)

SQL Server のグラフでレコメンドをやってみる

01/23(火) SQLWorld★大阪#45 開催します

.NET Core で TransactionScope を使ってみる (Preview)