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

編集履歴

回答編集履歴

5

訂正

2019/05/01 07:56

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -17,10 +17,11 @@
17
17
  SQLの記述的には、抽出条件と結合条件が明確になっていませんね。
18
18
  ※記述変更するとインデックスの状況によっては、レスポンスが変化する可能性があります。
19
19
  後はネストさせると可読性は上がると思います。
20
- 上記を踏まえると、以下のようなSQLになります。
20
+ 上記を踏まえ色々と無駄を省くと、以下のようなSQLになります。
21
21
  ```SQL
22
22
  select
23
+ substring(CARD_ISSUE_MONTH, 1, 4) As YEAR, substring(CARD_ISSUE_MONTH, 6, 2) AS MMONTH
23
- YEAR_PART, MONTH_PART, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
24
+ , CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
24
25
  , COUNT(PMP_MEMBER_ID) AS MEMBER_COUNT
25
26
  , SUM(TRANSACT) AS TRANSACT_COUNT
26
27
  , SUM(TRANSACT_003) AS TRANSACT_003_COUNT
@@ -28,8 +29,7 @@
28
29
  from (
29
30
  SELECT
30
31
  TBL_MEMBER.*
31
- , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 4) AS YEAR_PART
32
+ , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 7) AS CARD_ISSUE_MONTH
32
- , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 6, 2) AS MONTH_PART
33
33
  , CASE
34
34
  WHEN TBL_MEMBER.[PMP_CARD_TYPE] IS NULL THEN 0
35
35
  WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 1 THEN 1
@@ -49,7 +49,7 @@
49
49
  AND TBL_MEMBER.[PMP_CARD_ISSUE_DATE] <> '0000-00-00'
50
50
  ) as base
51
51
  GROUP BY
52
- YEAR_PART, MONTH_PART, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
52
+ CARD_ISSUE_MONTH, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
53
53
  ORDER BY
54
- YEAR_PART, MONTH_PART
54
+ CARD_ISSUE_MONTH
55
55
  ```

4

訂正

2019/05/01 07:56

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -42,7 +42,7 @@
42
42
  FROM
43
43
  [DWH_PONTA].[dbo].[PLMS_MEMBER_PROFILE] AS TBL_MEMBER
44
44
  LEFT OUTER JOIN [DWH_PONTA].[dbo].[DAILY_TRX_PLMS] AS TBL_TRX
45
- AND TBL_MEMBER.[PMP_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID]
45
+ ON TBL_MEMBER.[PMP_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID]
46
46
  AND TBL_MEMBER.[PMP_ISSUER_CODE]=TBL_TRX.[DTP_COMPANY]
47
47
  WHERE
48
48
  TBL_MEMBER.[PMP_ISSUER_CODE] = '003'

3

修正

2019/05/01 07:50

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -20,11 +20,7 @@
20
20
  上記を踏まえると、以下のようなSQLになります。
21
21
  ```SQL
22
22
  select
23
- YEAR_PART
24
- , MONTH_PART
25
- , CARD_TYPE
26
- , PMP_ISSUER_CODE
23
+ YEAR_PART, MONTH_PART, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
27
- , PMP_ISSUER_NAME
28
24
  , COUNT(PMP_MEMBER_ID) AS MEMBER_COUNT
29
25
  , SUM(TRANSACT) AS TRANSACT_COUNT
30
26
  , SUM(TRANSACT_003) AS TRANSACT_003_COUNT
@@ -53,12 +49,7 @@
53
49
  AND TBL_MEMBER.[PMP_CARD_ISSUE_DATE] <> '0000-00-00'
54
50
  ) as base
55
51
  GROUP BY
56
- YEAR_PART
57
- , MONTH_PART
58
- , CARD_TYPE
59
- , PMP_ISSUER_CODE
52
+ YEAR_PART, MONTH_PART, CARD_TYPE, PMP_ISSUER_CODE, PMP_ISSUER_NAME
60
- , PMP_ISSUER_NAME
61
53
  ORDER BY
62
- YEAR_PART
63
- , MONTH_PART
54
+ YEAR_PART, MONTH_PART
64
55
  ```

2

修正

2019/05/01 07:48

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -31,7 +31,8 @@
31
31
  , SUM(ACTIVATED) AS ACTIVATED_COUNT
32
32
  from (
33
33
  SELECT
34
+ TBL_MEMBER.*
34
- substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 4) AS YEAR_PART
35
+ , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 4) AS YEAR_PART
35
36
  , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 6, 2) AS MONTH_PART
36
37
  , CASE
37
38
  WHEN TBL_MEMBER.[PMP_CARD_TYPE] IS NULL THEN 0
@@ -39,9 +40,6 @@
39
40
  WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 0 THEN 0
40
41
  ELSE 0
41
42
  END AS CARD_TYPE
42
- , TBL_MEMBER.[PMP_ISSUER_CODE]
43
- , TBL_MEMBER.[PMP_ISSUER_NAME]
44
- , TBL_MEMBER.[PMP_MEMBER_ID]
45
43
  , CASE WHEN TBL_MEMBER.[PMP_LAST_TRX_DATE] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT
46
44
  , CASE WHEN TBL_TRX.[DTP_MEMBER_ID] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT_003
47
45
  , CASE WHEN TBL_MEMBER.[PMP_MOBILE_PHONE] IS NOT NULL THEN 1 ELSE 0 END AS ACTIVATED

1

追記

2019/05/01 07:47

投稿

sazi
sazi

スコア25430

answer CHANGED
@@ -8,4 +8,59 @@
8
8
  substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 4) as YERA_PART
9
9
  , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 6, 2) as MONTH_PART
10
10
  ```
11
- のように文字列の部分的な抽出とすれば良いかと。
11
+ のように文字列の部分的な抽出とすれば良いかと。
12
+
13
+ 追記
14
+ ---
15
+ > 問題になっている個所以外にも課題があるかもしれませんが
16
+
17
+ SQLの記述的には、抽出条件と結合条件が明確になっていませんね。
18
+ ※記述変更するとインデックスの状況によっては、レスポンスが変化する可能性があります。
19
+ 後はネストさせると可読性は上がると思います。
20
+ 上記を踏まえると、以下のようなSQLになります。
21
+ ```SQL
22
+ select
23
+ YEAR_PART
24
+ , MONTH_PART
25
+ , CARD_TYPE
26
+ , PMP_ISSUER_CODE
27
+ , PMP_ISSUER_NAME
28
+ , COUNT(PMP_MEMBER_ID) AS MEMBER_COUNT
29
+ , SUM(TRANSACT) AS TRANSACT_COUNT
30
+ , SUM(TRANSACT_003) AS TRANSACT_003_COUNT
31
+ , SUM(ACTIVATED) AS ACTIVATED_COUNT
32
+ from (
33
+ SELECT
34
+ substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 1, 4) AS YEAR_PART
35
+ , substring(TBL_MEMBER.[PMP_CARD_ISSUE_DATE], 6, 2) AS MONTH_PART
36
+ , CASE
37
+ WHEN TBL_MEMBER.[PMP_CARD_TYPE] IS NULL THEN 0
38
+ WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 1 THEN 1
39
+ WHEN TBL_MEMBER.[PMP_CARD_TYPE] = 0 THEN 0
40
+ ELSE 0
41
+ END AS CARD_TYPE
42
+ , TBL_MEMBER.[PMP_ISSUER_CODE]
43
+ , TBL_MEMBER.[PMP_ISSUER_NAME]
44
+ , TBL_MEMBER.[PMP_MEMBER_ID]
45
+ , CASE WHEN TBL_MEMBER.[PMP_LAST_TRX_DATE] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT
46
+ , CASE WHEN TBL_TRX.[DTP_MEMBER_ID] IS NOT NULL THEN 1 ELSE 0 END AS TRANSACT_003
47
+ , CASE WHEN TBL_MEMBER.[PMP_MOBILE_PHONE] IS NOT NULL THEN 1 ELSE 0 END AS ACTIVATED
48
+ FROM
49
+ [DWH_PONTA].[dbo].[PLMS_MEMBER_PROFILE] AS TBL_MEMBER
50
+ LEFT OUTER JOIN [DWH_PONTA].[dbo].[DAILY_TRX_PLMS] AS TBL_TRX
51
+ AND TBL_MEMBER.[PMP_MEMBER_ID] = TBL_TRX.[DTP_MEMBER_ID]
52
+ AND TBL_MEMBER.[PMP_ISSUER_CODE]=TBL_TRX.[DTP_COMPANY]
53
+ WHERE
54
+ TBL_MEMBER.[PMP_ISSUER_CODE] = '003'
55
+ AND TBL_MEMBER.[PMP_CARD_ISSUE_DATE] <> '0000-00-00'
56
+ ) as base
57
+ GROUP BY
58
+ YEAR_PART
59
+ , MONTH_PART
60
+ , CARD_TYPE
61
+ , PMP_ISSUER_CODE
62
+ , PMP_ISSUER_NAME
63
+ ORDER BY
64
+ YEAR_PART
65
+ , MONTH_PART
66
+ ```