回答編集履歴
5
追記
test
CHANGED
|
@@ -15,6 +15,8 @@
|
|
|
15
15
|
※質問のデータでは、３階層までですが、サンプル的に階層４までにしています
|
|
16
16
|
|
|
17
17
|
※複数の**unnest**で展開できるかは確認してないので、駄目な様なら、結合したもので**array_agg**して、展開後に分解して下さい。
|
|
18
|
+
|
|
19
|
+
※このselect文を元に**create table**や **insert** するなりして下さい。
|
|
18
20
|
|
|
19
21
|
```SQL
|
|
20
22
|
|
4
変更
test
CHANGED
|
@@ -122,9 +122,11 @@
|
|
|
122
122
|
|
|
123
123
|
with node1 as (-- ノード1と経路起点の情報
|
|
124
124
|
|
|
125
|
+
select *, row_number() over(order by システムID, ユーザーID) as ユニークID
|
|
126
|
+
|
|
127
|
+
from (
|
|
128
|
+
|
|
125
129
|
select システムID1 システムID, ユーザーID1 as ユーザーID
|
|
126
|
-
|
|
127
|
-
, row_number() over(order by システムID1, ユーザーID1) as ユニークID
|
|
128
130
|
|
|
129
131
|
from tbl t
|
|
130
132
|
|
|
@@ -137,6 +139,8 @@
|
|
|
137
139
|
)
|
|
138
140
|
|
|
139
141
|
group by システムID1, ユーザーID1
|
|
142
|
+
|
|
143
|
+
) t
|
|
140
144
|
|
|
141
145
|
), node2 as (
|
|
142
146
|
|
3
推敲
test
CHANGED
|
@@ -124,7 +124,7 @@
|
|
|
124
124
|
|
|
125
125
|
select システムID1 システムID, ユーザーID1 as ユーザーID
|
|
126
126
|
|
|
127
|
-
, row_number() over(
|
|
127
|
+
, row_number() over(order by システムID1, ユーザーID1) as ユニークID
|
|
128
128
|
|
|
129
129
|
from tbl t
|
|
130
130
|
|
2
推敲
test
CHANGED
|
@@ -124,7 +124,7 @@
|
|
|
124
124
|
|
|
125
125
|
select システムID1 システムID, ユーザーID1 as ユーザーID
|
|
126
126
|
|
|
127
|
-
, row_number() over(order by システムID1, ユーザーID1) as ユニークID
|
|
127
|
+
, row_number() over(partition by システムID1, ユーザーID1 order by システムID1, ユーザーID1) as ユニークID
|
|
128
128
|
|
|
129
129
|
from tbl t
|
|
130
130
|
|
1
追記
test
CHANGED
|
@@ -109,3 +109,67 @@
|
|
|
109
109
|
一応以下に参考になりそうなものをリンクしておきます
|
|
110
110
|
|
|
111
111
|
[経理処理の定式化とその実装、もしくはBigQueryにおける再帰](https://moneyforward.com/engineers_blog/2019/06/03/business-analysis/)
|
|
112
|
+
|
|
113
|
+
|
|
114
|
+
|
|
115
|
+
追記
|
|
116
|
+
|
|
117
|
+
--
|
|
118
|
+
|
|
119
|
+
ノード１時点でユニークIDを求めておけば、配列操作は不要だったので、追記。
|
|
120
|
+
|
|
121
|
+
```SQL
|
|
122
|
+
|
|
123
|
+
with node1 as (-- ノード1と経路起点の情報
|
|
124
|
+
|
|
125
|
+
select システムID1 システムID, ユーザーID1 as ユーザーID
|
|
126
|
+
|
|
127
|
+
, row_number() over(order by システムID1, ユーザーID1) as ユニークID
|
|
128
|
+
|
|
129
|
+
from tbl t
|
|
130
|
+
|
|
131
|
+
where not exists(
|
|
132
|
+
|
|
133
|
+
select 1 from tbl
|
|
134
|
+
|
|
135
|
+
where システムID2=t.システムID1 and ユーザーID2=t.ユーザーID1
|
|
136
|
+
|
|
137
|
+
)
|
|
138
|
+
|
|
139
|
+
group by システムID1, ユーザーID1
|
|
140
|
+
|
|
141
|
+
), node2 as (
|
|
142
|
+
|
|
143
|
+
select n2.システムID2 as システムID, n2.ユーザーID2 as ユーザーID, n1.ユニークID
|
|
144
|
+
|
|
145
|
+
from node1 n1 inner join tbl n2
|
|
146
|
+
|
|
147
|
+
on n1.システムID=n2.システムID1 and n1.ユーザーID=n2.ユーザーID1
|
|
148
|
+
|
|
149
|
+
), node3 as (
|
|
150
|
+
|
|
151
|
+
select n3.システムID2 as システムID, n3.ユーザーID2 as ユーザーID, n2.ユニークID
|
|
152
|
+
|
|
153
|
+
from node2 n2 inner join tbl n3
|
|
154
|
+
|
|
155
|
+
on n2.システムID=n3.システムID1 and n2.ユーザーID=n3.ユーザーID1
|
|
156
|
+
|
|
157
|
+
), node4 as (
|
|
158
|
+
|
|
159
|
+
select n4.システムID2 as システムID, n4.ユーザーID2 as ユーザーID, n3.ユニークID
|
|
160
|
+
|
|
161
|
+
from node3 n3 inner join tbl n4
|
|
162
|
+
|
|
163
|
+
on n3.システムID=n4.システムID1 and n3.ユーザーID=n4.ユーザーID1
|
|
164
|
+
|
|
165
|
+
)
|
|
166
|
+
|
|
167
|
+
select * from node1
|
|
168
|
+
|
|
169
|
+
union all select * from node2
|
|
170
|
+
|
|
171
|
+
union all select * from node3
|
|
172
|
+
|
|
173
|
+
union all select * from node4
|
|
174
|
+
|
|
175
|
+
```
|