teratail
回答率
85.49%
質問するログイン新規登録
トップSQLに関する質問OUTER JOINの際のエラー

編集履歴

回答編集履歴

5

訂正

2019/05/01 07:56

投稿

sazi
sazi

スコア25173

test CHANGED
@@ -36,13 +36,15 @@
36
36
 
37
37
  後はネストさせると可読性は上がると思います。
38
38
 
39
- 上記を踏まえると、以下のようなSQLになります。
39
+ 上記を踏まえ色々と無駄を省くと、以下のようなSQLになります。
40
40
 
41
41
  ```SQL
42
42
 
43
43
  select
44
44
 
45
+ substring(CARD_ISSUE_MONTH, 1, 4) As YEAR, substring(CARD_ISSUE_MONTH, 6, 2) AS MMONTH
46
+
45
- YEAR_PART, MONTH_PART, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
47
+ , CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
46
48
 
47
49
  , COUNT(PMP_MEMBER_ID) AS MEMBER_COUNT
48
50
 
@@ -58,9 +60,7 @@
58
60
 
59
61
  TBL_MEMBER.*
60
62
 
61
- , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 4) AS YEAR_PART
63
+ , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 7) AS CARD_ISSUE_MONTH
62
-
63
- , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 6, 2) AS MONTH_PART
64
64
 
65
65
  , CASE
66
66
 
@@ -100,10 +100,10 @@
100
100
 
101
101
  GROUP BY
102
102
 
103
- YEAR_PART, MONTH_PART, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
103
+ CARD_ISSUE_MONTH, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
104
104
 
105
105
  ORDER BY
106
106
 
107
- YEAR_PART, MONTH_PART
107
+ CARD_ISSUE_MONTH
108
108
 
109
109
  ```

4

訂正

2019/05/01 07:56

投稿

sazi
sazi

スコア25173

test CHANGED
@@ -86,7 +86,7 @@
86
86
 
87
87
  LEFT OUTER JOIN [DWH_PONTA].[dbo].[DAILY_TRX_PLMS] AS TBL_TRX
88
88
 
89
- AND TBL_MEMBER.[PMP_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID]
89
+ ON TBL_MEMBER.[PMP_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID]
90
90
 
91
91
  AND TBL_MEMBER.[PMP_ISSUER_CODE]=TBL_TRX.[DTP_COMPANY]
92
92
 

3

修正

2019/05/01 07:50

投稿

sazi
sazi

スコア25173

test CHANGED
@@ -42,15 +42,7 @@
42
42
 
43
43
  select
44
44
 
45
- YEAR_PART
46
-
47
- , MONTH_PART
48
-
49
- , CARD_TYPE
50
-
51
- , PMP_ISSUER_CODE
45
+ YEAR_PART, MONTH_PART, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
52
-
53
- , PMP_ISSUER_NAME
54
46
 
55
47
  , COUNT(PMP_MEMBER_ID) AS MEMBER_COUNT
56
48
 
@@ -108,20 +100,10 @@
108
100
 
109
101
  GROUP BY
110
102
 
111
- YEAR_PART
112
-
113
- , MONTH_PART
114
-
115
- , CARD_TYPE
116
-
117
- , PMP_ISSUER_CODE
103
+ YEAR_PART, MONTH_PART, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
118
-
119
- , PMP_ISSUER_NAME
120
104
 
121
105
  ORDER BY
122
106
 
123
- YEAR_PART
124
-
125
- , MONTH_PART
107
+ YEAR_PART, MONTH_PART
126
108
 
127
109
  ```

2

修正

2019/05/01 07:48

投稿

sazi
sazi

スコア25173

test CHANGED
@@ -64,7 +64,9 @@
64
64
 
65
65
  SELECT
66
66
 
67
+ TBL_MEMBER.*
68
+
67
- substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 4) AS YEAR_PART
69
+ , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 4) AS YEAR_PART
68
70
 
69
71
  , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 6, 2) AS MONTH_PART
70
72
 
@@ -79,12 +81,6 @@
79
81
  ELSE 0
80
82
 
81
83
  END AS CARD_TYPE
82
-
83
- , TBL_MEMBER.[PMP_ISSUER_CODE]
84
-
85
- , TBL_MEMBER.[PMP_ISSUER_NAME]
86
-
87
- , TBL_MEMBER.[PMP_MEMBER_ID]
88
84
 
89
85
  , CASE WHEN TBL_MEMBER.[PMP_LAST_TRX_DATE] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT
90
86
 

1

追記

2019/05/01 07:47

投稿

sazi
sazi

スコア25173

test CHANGED
@@ -19,3 +19,113 @@
19
19
  ```
20
20
 
21
21
  のように文字列の部分的な抽出とすれば良いかと。
22
+
23
+
24
+
25
+ 追記
26
+
27
+ ---
28
+
29
+ > 問題になっている個所以外にも課題があるかもしれませんが
30
+
31
+
32
+
33
+ SQLの記述的には、抽出条件と結合条件が明確になっていませんね。
34
+
35
+ ※記述変更するとインデックスの状況によっては、レスポンスが変化する可能性があります。
36
+
37
+ 後はネストさせると可読性は上がると思います。
38
+
39
+ 上記を踏まえると、以下のようなSQLになります。
40
+
41
+ ```SQL
42
+
43
+ select
44
+
45
+ YEAR_PART
46
+
47
+ , MONTH_PART
48
+
49
+ , CARD_TYPE
50
+
51
+ , PMP_ISSUER_CODE
52
+
53
+ , PMP_ISSUER_NAME
54
+
55
+ , COUNT(PMP_MEMBER_ID) AS MEMBER_COUNT
56
+
57
+ , SUM(TRANSACT) AS TRANSACT_COUNT
58
+
59
+ , SUM(TRANSACT_003) AS TRANSACT_003_COUNT
60
+
61
+ , SUM(ACTIVATED) AS ACTIVATED_COUNT
62
+
63
+ from (
64
+
65
+ SELECT
66
+
67
+ substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 4) AS YEAR_PART
68
+
69
+ , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 6, 2) AS MONTH_PART
70
+
71
+ , CASE
72
+
73
+ WHEN TBL_MEMBER.[PMP_CARD_TYPE] IS NULL THEN 0
74
+
75
+ WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 1 THEN 1
76
+
77
+ WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 0 THEN 0
78
+
79
+ ELSE 0
80
+
81
+ END AS CARD_TYPE
82
+
83
+ , TBL_MEMBER.[PMP_ISSUER_CODE]
84
+
85
+ , TBL_MEMBER.[PMP_ISSUER_NAME]
86
+
87
+ , TBL_MEMBER.[PMP_MEMBER_ID]
88
+
89
+ , CASE WHEN TBL_MEMBER.[PMP_LAST_TRX_DATE] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT
90
+
91
+ , CASE WHEN TBL_TRX.[DTP_MEMBER_ID] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT_003
92
+
93
+ , CASE WHEN TBL_MEMBER.[PMP_MOBILE_PHONE] IS NOT NULL THEN 1 ELSE 0 END AS ACTIVATED
94
+
95
+ FROM
96
+
97
+ [DWH_PONTA].[dbo].[PLMS_MEMBER_PROFILE] AS TBL_MEMBER
98
+
99
+ LEFT OUTER JOIN [DWH_PONTA].[dbo].[DAILY_TRX_PLMS] AS TBL_TRX
100
+
101
+ AND TBL_MEMBER.[PMP_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID]
102
+
103
+ AND TBL_MEMBER.[PMP_ISSUER_CODE]=TBL_TRX.[DTP_COMPANY]
104
+
105
+ WHERE
106
+
107
+ TBL_MEMBER.[PMP_ISSUER_CODE] = '003'
108
+
109
+ AND TBL_MEMBER.[PMP_CARD_ISSUE_DATE] <> '0000-00-00'
110
+
111
+ ) as base
112
+
113
+ GROUP BY
114
+
115
+ YEAR_PART
116
+
117
+ , MONTH_PART
118
+
119
+ , CARD_TYPE
120
+
121
+ , PMP_ISSUER_CODE
122
+
123
+ , PMP_ISSUER_NAME
124
+
125
+ ORDER BY
126
+
127
+ YEAR_PART
128
+
129
+ , MONTH_PART
130
+
131
+ ```