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

SQL Server

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