回答編集履歴
5
追記
answer
CHANGED
|
@@ -7,6 +7,7 @@
|
|
|
7
7
|
上記で畳んだ情報に対してナンバリングし、最後に配列を展開(**unnest**)します。
|
|
8
8
|
※質問のデータでは、３階層までですが、サンプル的に階層４までにしています
|
|
9
9
|
※複数の**unnest**で展開できるかは確認してないので、駄目な様なら、結合したもので**array_agg**して、展開後に分解して下さい。
|
|
10
|
+
※このselect文を元に**create table**や **insert** するなりして下さい。
|
|
10
11
|
```SQL
|
|
11
12
|
with node1 as (-- ノード1と経路起点の情報
|
|
12
13
|
select システムID1 as node_key1, ユーザーID1 as node_key2
|
4
変更
answer
CHANGED
|
@@ -60,14 +60,16 @@
|
|
|
60
60
|
ノード１時点でユニークIDを求めておけば、配列操作は不要だったので、追記。
|
|
61
61
|
```SQL
|
|
62
62
|
with node1 as (-- ノード1と経路起点の情報
|
|
63
|
+
select *, row_number() over(order by システムID, ユーザーID) as ユニークID
|
|
64
|
+
from (
|
|
63
65
|
select システムID1 システムID, ユーザーID1 as ユーザーID
|
|
64
|
-
, row_number() over(order by システムID1, ユーザーID1) as ユニークID
|
|
65
66
|
from tbl t
|
|
66
67
|
where not exists(
|
|
67
68
|
select 1 from tbl
|
|
68
69
|
where システムID2=t.システムID1 and ユーザーID2=t.ユーザーID1
|
|
69
70
|
)
|
|
70
71
|
group by システムID1, ユーザーID1
|
|
72
|
+
) t
|
|
71
73
|
), node2 as (
|
|
72
74
|
select n2.システムID2 as システムID, n2.ユーザーID2 as ユーザーID, n1.ユニークID
|
|
73
75
|
from node1 n1 inner join tbl n2
|
3
推敲
answer
CHANGED
|
@@ -61,7 +61,7 @@
|
|
|
61
61
|
```SQL
|
|
62
62
|
with node1 as (-- ノード1と経路起点の情報
|
|
63
63
|
select システムID1 システムID, ユーザーID1 as ユーザーID
|
|
64
|
-
, row_number() over(
|
|
64
|
+
, row_number() over(order by システムID1, ユーザーID1) as ユニークID
|
|
65
65
|
from tbl t
|
|
66
66
|
where not exists(
|
|
67
67
|
select 1 from tbl
|
2
推敲
answer
CHANGED
|
@@ -61,7 +61,7 @@
|
|
|
61
61
|
```SQL
|
|
62
62
|
with node1 as (-- ノード1と経路起点の情報
|
|
63
63
|
select システムID1 システムID, ユーザーID1 as ユーザーID
|
|
64
|
-
, row_number() over(order by システムID1, ユーザーID1) as ユニークID
|
|
64
|
+
, row_number() over(partition by システムID1, ユーザーID1 order by システムID1, ユーザーID1) as ユニークID
|
|
65
65
|
from tbl t
|
|
66
66
|
where not exists(
|
|
67
67
|
select 1 from tbl
|
1
追記
answer
CHANGED
|
@@ -53,4 +53,36 @@
|
|
|
53
53
|
ノードの深さ（最大で幾つのシステム跨るか）がどれ位なのか分かりませんが、多くなれば記述は冗長になりますが、敢えて再帰処理を作成して行うかどうかですね。
|
|
54
54
|
|
|
55
55
|
一応以下に参考になりそうなものをリンクしておきます
|
|
56
|
-
[経理処理の定式化とその実装、もしくはBigQueryにおける再帰](https://moneyforward.com/engineers_blog/2019/06/03/business-analysis/)
|
|
56
|
+
[経理処理の定式化とその実装、もしくはBigQueryにおける再帰](https://moneyforward.com/engineers_blog/2019/06/03/business-analysis/)
|
|
57
|
+
|
|
58
|
+
追記
|
|
59
|
+
--
|
|
60
|
+
ノード１時点でユニークIDを求めておけば、配列操作は不要だったので、追記。
|
|
61
|
+
```SQL
|
|
62
|
+
with node1 as (-- ノード1と経路起点の情報
|
|
63
|
+
select システムID1 システムID, ユーザーID1 as ユーザーID
|
|
64
|
+
, row_number() over(order by システムID1, ユーザーID1) as ユニークID
|
|
65
|
+
from tbl t
|
|
66
|
+
where not exists(
|
|
67
|
+
select 1 from tbl
|
|
68
|
+
where システムID2=t.システムID1 and ユーザーID2=t.ユーザーID1
|
|
69
|
+
)
|
|
70
|
+
group by システムID1, ユーザーID1
|
|
71
|
+
), node2 as (
|
|
72
|
+
select n2.システムID2 as システムID, n2.ユーザーID2 as ユーザーID, n1.ユニークID
|
|
73
|
+
from node1 n1 inner join tbl n2
|
|
74
|
+
on n1.システムID=n2.システムID1 and n1.ユーザーID=n2.ユーザーID1
|
|
75
|
+
), node3 as (
|
|
76
|
+
select n3.システムID2 as システムID, n3.ユーザーID2 as ユーザーID, n2.ユニークID
|
|
77
|
+
from node2 n2 inner join tbl n3
|
|
78
|
+
on n2.システムID=n3.システムID1 and n2.ユーザーID=n3.ユーザーID1
|
|
79
|
+
), node4 as (
|
|
80
|
+
select n4.システムID2 as システムID, n4.ユーザーID2 as ユーザーID, n3.ユニークID
|
|
81
|
+
from node3 n3 inner join tbl n4
|
|
82
|
+
on n3.システムID=n4.システムID1 and n3.ユーザーID=n4.ユーザーID1
|
|
83
|
+
)
|
|
84
|
+
select * from node1
|
|
85
|
+
union all select * from node2
|
|
86
|
+
union all select * from node3
|
|
87
|
+
union all select * from node4
|
|
88
|
+
```
|