回答編集履歴
2
追記
test
CHANGED
|
@@ -1,4 +1,6 @@
|
|
|
1
1
|
DB2を試せる環境は無いので、保証などはできませんが。
|
|
2
|
+
|
|
3
|
+
相関副問合せ版
|
|
2
4
|
|
|
3
5
|
```SQL
|
|
4
6
|
|
|
@@ -35,3 +37,43 @@
|
|
|
35
37
|
WHERE 役割 = '取引者'
|
|
36
38
|
|
|
37
39
|
```
|
|
40
|
+
|
|
41
|
+
インラインビュー版
|
|
42
|
+
|
|
43
|
+
```SQL
|
|
44
|
+
|
|
45
|
+
select t3.基準日, t3.取引NO
|
|
46
|
+
|
|
47
|
+
, count(distinct t3.ユーザーID) as 取引回数
|
|
48
|
+
|
|
49
|
+
, count(distinct t4.ユーザーID) as 取引相手
|
|
50
|
+
|
|
51
|
+
from (
|
|
52
|
+
|
|
53
|
+
select t1.*, t2.取引NO as ユーザー別取引NO
|
|
54
|
+
|
|
55
|
+
from 取引明細 t1
|
|
56
|
+
|
|
57
|
+
inner join 取引明細 t2
|
|
58
|
+
|
|
59
|
+
on t1.基準日>=t2.基準日
|
|
60
|
+
|
|
61
|
+
and t1.ユーザーID=t2.ユーザーID
|
|
62
|
+
|
|
63
|
+
and t1.役割=t2.役割
|
|
64
|
+
|
|
65
|
+
WHERE t1.役割='取引者'
|
|
66
|
+
|
|
67
|
+
) t3
|
|
68
|
+
|
|
69
|
+
left join 取引明細 t4
|
|
70
|
+
|
|
71
|
+
on t3.基準日>=t4.基準日
|
|
72
|
+
|
|
73
|
+
and t3.ユーザー別取引NO=t4.取引NO
|
|
74
|
+
|
|
75
|
+
and t4.役割='出品者'
|
|
76
|
+
|
|
77
|
+
group by t3.基準日, t3.取引NO
|
|
78
|
+
|
|
79
|
+
```
|
1
変更
test
CHANGED
|
@@ -20,7 +20,13 @@
|
|
|
20
20
|
|
|
21
21
|
and 役割='出品者'
|
|
22
22
|
|
|
23
|
+
and 取引NO in (
|
|
24
|
+
|
|
25
|
+
select 取引NO from 取引明細
|
|
26
|
+
|
|
23
|
-
|
|
27
|
+
where 基準日<=t1.基準日 and ユーザーID=t1.ユーザーID
|
|
28
|
+
|
|
29
|
+
)
|
|
24
30
|
|
|
25
31
|
) 取引相手
|
|
26
32
|
|