데이터베이스 질문과 답변

3개의 테이블을 이용하여 Sum과 Count를 구하는 방법/MS-SQL 0 10 1,830

by 갈매기 [SQL Query] join sum count [2018.05.10 04:11:03]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
;WITH Canister(ID, status,  Chage_dtm,  NDC,    MED_qty) AS
(
            SELECT  '16143088887721430094', '25',   '20170117115105',   '00378021610',  '100',''
UNION   All SELECT  '16143088887721430094', '30',   '20170119120354',   '00378021610',  '100',''
UNION   All SELECT  '16143088887721430094', '40',   '20170119120536',   '00378021610',  '',''
UNION   All SELECT  '16143088887721430094', '41',   '20170310101115',   '00378021610',  '0',''
UNION   All SELECT  '16143088887721430094', '50',   '20170530154629',   '00378021610',  '0',''
UNION   All SELECT  '16143088887721430094', '60',   '20170531154629',   '00378021610',  '0',''
UNION   All SELECT  '16143088887721430094', '12',   '20170531154629',   '00378021610',  '0' ,'0'
UNION   All SELECT  '16143088887721430094', '25',   '20180101120000',   '00378021610',  '150',''
UNION   All SELECT  '16143088887721430094', '30',   '20180102120000',   '00378021610',  '150',''
UNION   All SELECT  '16143088887721430094', '40',   '20180103120000',   '00378021610',  '',''
UNION   All SELECT  '16143088887721430094', '41',   '20180103131211',   '00378021610',  '',''
UNION   All SELECT  '16143088887721430094', '40',   '20180104121211',   '00378021610',  '',''
UNION   All SELECT  '16143088887721430094', '41',   '20180201000000',   '00378021610',  '',''
UNION   All SELECT  '16143088887721430094', '50',   '20180202000000',   '00378021610',  '20',''
UNION   All SELECT  '16143088887721430094', '60',   '20180203000000',   '00378021610',  '20',''
UNION   All SELECT  '16143088887721430094', '12',   '20180203000000',   '00378021610',  '20','21'
 
)
 
, dsps(ID,dsps_dtm,dsps_qty) AS
(
            SELECT  '16143088887721430094', '20170120115105',   '80'
UNION   All SELECT  '16143088887721430094', '20170120215105',   '10'
UNION   All SELECT  '16143088887721430094', '20170125121200',   '5'
UNION   All SELECT  '16143088887721430094', '20170202120000',   '4'
UNION   All SELECT  '16143088887721430094', '20170529154629',   '1'
UNION   All SELECT  '16143088887721430094', '20180103121522',   '10'
UNION   All SELECT  '16143088887721430094', '20180104120111',   '100'
UNION   All SELECT  '16143088887721430094', '20180105120111',   '10'
UNION   All SELECT  '16143088887721430094', '20180106120111',   '5'
UNION   All SELECT  '16143088887721430094', '20180107120111',   '4'
 
)
, alarm(ID, alarm_dtm,alarm_num) AS
(
            SELECT  '16143088887721430094', '20170119120354',   '21'
UNION   All SELECT  '16143088887721430094', '20170119120536',   '121'
UNION   All SELECT  '16143088887721430094', '20170310101115',   '21'
UNION   All SELECT  '16143088887721430094', '20170310101129',   '121'
UNION   All SELECT  '16143088887721430094', '20170310101158',   '21'
UNION   All SELECT  '16143088887721430094', '20170310101159',   '10'
UNION   All SELECT  '16143088887721430094', '20170310101215',   '15'
UNION   All SELECT  '16143088887721430094', '20180103131220',   '21'
UNION   All SELECT  '16143088887721430094', '20180104121322',   '121'
UNION   All SELECT  '16143088887721430094', '20180131000000',   '21'
 
)

안녕하세요. 3개의 테이블이 있습니다. 

Canister table, Dsps tabel, alarm table

--필요한 내역은 canister tabel에서 특정 기간(Status:25 ~ Status:12) 을 찾습니다. 그 기간동안 dsps table에서 dsps_qty의 Sum을, alarm table에서 Count(*)값을 구하고자 합니다. 

--예를 들면. 

Canister table
ID status change_dtm NDC Med_qty CountQty
1.61431E+19 25 2.01701E+13 378021610 100  
1.61431E+19 30 2.01701E+13 378021610 100  
1.61431E+19 40 2.01701E+13 378021610    
1.61431E+19 41 2.01703E+13 378021610 0  
1.61431E+19 50 2.01705E+13 378021610 0  
1.61431E+19 60 2.01705E+13 378021610 0  
1.61431E+19 12 2.01705E+13 378021610 0 0
1.61431E+19 25 2.01801E+13 378021610 150  
1.61431E+19 30 2.01801E+13 378021610 150  
1.61431E+19 40 2.01801E+13 378021610    
1.61431E+19 41 2.01801E+13 378021610    
1.61431E+19 40 2.01801E+13 378021610    
1.61431E+19 41 2.01802E+13 378021610    
1.61431E+19 50 2.01802E+13 378021610 20  
1.61431E+19 60 2.01802E+13 378021610 20  
1.61431E+19 12 2.01802E+13 378021610 20 21

 

Dsps table
ID dsps_dtm dsps_qty
16143088887721430094 20170120115105 80
16143088887721430094 20170120215105 10
16143088887721430094 20170125121200 5
16143088887721430094 20170202120000 4
16143088887721430094 20170529154629 1
16143088887721430094 20180103121522 10
16143088887721430094 20180104120111 100
16143088887721430094 20180105120111 10
16143088887721430094 20180106120111 5
16143088887721430094 20180107120111 4

 

alarm table
ID alarm_dtm alarm_num
16143088887721430094 20170119120354 21
16143088887721430094 20170119120536 121
16143088887721430094 20170310101115 21
16143088887721430094 20170310101129 121
16143088887721430094 20170310101158 21
16143088887721430094 20170310101159 10
16143088887721430094 20170310101215 15
16143088887721430094 20180103131220 21
16143088887721430094 20180104121322 121
16143088887721430094 20180131000000 21

 

 

이렇게 상기 3개의 테이블을 가지고, 

하기와 같은 값을 얻고자 합니다. 보기 편하게 빨간색과 파란색으로 구분했습니다. 

ID refilled refilled_date reclaimed_date Cycle DSPSed Jam
16143088887721430094 100 20170117115105 20170531154629 00378021610_1 100 3
16143088887721430094 150 20180101120000 20180203000000 00378021610_2 129 5

 

 

원하는 값 설명

ID refilled refilled reclaimed Cycle DSPSed Jam
16143088887721430094 Status:25 일때, Med_qty Status: 일때 chage_dtm Status:12일때 chage_dtm NDC_일련번호 refilled 부터 reclaimed 까지동안의 Sum(dsps_qty) refilled 부터 reclaimed 까지
 동안의 count(*), alarm_num 가 21, 121 만 카운트

 

 

by 갈매기 [2018.05.10 04:19:49]

부가 설명:

핵심은 Cycle을 만드는 건데요. Canister table 에서 Status:25 에서 Status:12 까지가 하나의 Cycle입니다. 그 Cycle을 기간을 가지고 다른 두 테이블에서 값을 계산하여 원하는 값을 얻는 것입니다. 

by 우리집아찌 [2018.05.10 09:44:35]

STATUS 25~12은 고정인가요?

Canister 테이블과 Dsps 테이블의 관계는 ID 뿐인가요?

Canister 테이블과 alarm 테이블의 관계는 ID 뿐인가요?

by 마농 [2018.05.10 10:27:30]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SELECT a.ID
     , a.refilled
     , a.refilled_date
     , a.reclaimed_date
     , a.NDC
     , a.Cycle
     , a.DSPSed
     , COUNT(b.ID) Jam
  FROM (SELECT a.ID
             , a.refilled
             , a.refilled_date
             , a.reclaimed_date
             , a.NDC
             , a.Cycle
             , SUM(b.dsps_qty) DSPSed
          FROM (SELECT ID
                     , MIN(CASE WHEN status = '25' THEN MED_qty END) refilled
                     , MIN(Chage_dtm) refilled_date
                     , MAX(Chage_dtm) reclaimed_date
                     , NDC
                     , Cycle
                  FROM (SELECT ID, status, Chage_dtm, NDC, MED_qty
                             , SUM(CASE WHEN status = '25' THEN 1 END)
                               OVER(PARTITION BY ID, NDC ORDER BY Chage_dtm) Cycle
                          FROM Canister
                        ) a
                 GROUP BY ID, NDC, Cycle
                ) a
          LEFT OUTER JOIN dsps b
            ON a.ID = b.ID
           AND b.dsps_dtm BETWEEN a.refilled_date AND a.reclaimed_date
         GROUP BY a.ID, a.refilled, a.refilled_date, a.reclaimed_date, a.NDC, a.Cycle
        ) a
  LEFT OUTER JOIN alarm b
    ON a.ID = b.ID
   AND b.alarm_dtm BETWEEN a.refilled_date AND a.reclaimed_date
   AND b.alarm_num IN (21, 121)
 GROUP BY a.ID, a.refilled, a.refilled_date, a.reclaimed_date, a.NDC, a.Cycle, a.DSPSed
;

 

by 우리집아찌 [2018.05.10 10:47:30]

와우...

릴레이션이 없는줄 알았는데.. 그냥 MIN - MAX 로 잡아서 처리하셨네요...

by 우리집아찌 [2018.05.10 10:28:36]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
WITH Canister(ID, status,  Chage_dtm,  NDC,    MED_qty) AS
(
            SELECT  '16143088887721430094', '25',   '20170117115105',   '00378021610',  '100'  
UNION   All SELECT  '16143088887721430094', '30',   '20170119120354',   '00378021610',  '100' 
UNION   All SELECT  '16143088887721430094', '40',   '20170119120536',   '00378021610',  '' 
UNION   All SELECT  '16143088887721430094', '41',   '20170310101115',   '00378021610',  '0' 
UNION   All SELECT  '16143088887721430094', '50',   '20170530154629',   '00378021610',  '0' 
UNION   All SELECT  '16143088887721430094', '60',   '20170531154629',   '00378021610',  '0' 
UNION   All SELECT  '16143088887721430094', '12',   '20170531154629',   '00378021610',  '0' 
UNION   All SELECT  '16143088887721430094', '25',   '20180101120000',   '00378021610',  '150' 
UNION   All SELECT  '16143088887721430094', '30',   '20180102120000',   '00378021610',  '150' 
UNION   All SELECT  '16143088887721430094', '40',   '20180103120000',   '00378021610',  '' 
UNION   All SELECT  '16143088887721430094', '41',   '20180103131211',   '00378021610',  '' 
UNION   All SELECT  '16143088887721430094', '40',   '20180104121211',   '00378021610',  '' 
UNION   All SELECT  '16143088887721430094', '41',   '20180201000000',   '00378021610',  '' 
UNION   All SELECT  '16143088887721430094', '50',   '20180202000000',   '00378021610',  '20' 
UNION   All SELECT  '16143088887721430094', '60',   '20180203000000',   '00378021610',  '20' 
UNION   All SELECT  '16143088887721430094', '12',   '20180203000000',   '00378021610',  '20' 
  
)
  
, dsps(ID,dsps_dtm,dsps_qty) AS
(
            SELECT  '16143088887721430094', '20170120115105',   '80' 
UNION   All SELECT  '16143088887721430094', '20170120215105',   '10' 
UNION   All SELECT  '16143088887721430094', '20170125121200',   '5'  
UNION   All SELECT  '16143088887721430094', '20170202120000',   '4'  
UNION   All SELECT  '16143088887721430094', '20170529154629',   '1'  
UNION   All SELECT  '16143088887721430094', '20180103121522',   '10' 
UNION   All SELECT  '16143088887721430094', '20180104120111',   '100'
UNION   All SELECT  '16143088887721430094', '20180105120111',   '10' 
UNION   All SELECT  '16143088887721430094', '20180106120111',   '5'  
UNION   All SELECT  '16143088887721430094', '20180107120111',   '4'  
  
)
, alarm(ID, alarm_dtm,alarm_num) AS
(
            SELECT  '16143088887721430094', '20170119120354',   '21' 
UNION   All SELECT  '16143088887721430094', '20170119120536',   '121'
UNION   All SELECT  '16143088887721430094', '20170310101115',   '21' 
UNION   All SELECT  '16143088887721430094', '20170310101129',   '121'
UNION   All SELECT  '16143088887721430094', '20170310101158',   '21' 
UNION   All SELECT  '16143088887721430094', '20170310101159',   '10' 
UNION   All SELECT  '16143088887721430094', '20170310101215',   '15' 
UNION   All SELECT  '16143088887721430094', '20180103131220',   '21' 
UNION   All SELECT  '16143088887721430094', '20180104121322',   '121'
UNION   All SELECT  '16143088887721430094', '20180131000000',   '21' 
  
)
 
SELECT A.ID , A.GRP
     , MIN(CASE WHEN A.STATUS = 25 THEN A.MED_qty END ) AS refilled
     , MIN(CASE WHEN A.STATUS = 25 THEN A.Chage_dtm END ) AS refilled_DATE
     , MIN(CASE WHEN A.STATUS = 12 THEN A.Chage_dtm END ) AS refilled_DATE
     , CONCAT( MIN(NDC) , '_' , A.GRP ) AS CYCLE
     , SUM(CONVERT(NUMERIC, B.dsps_qty)) AS dsps_qty
     , SUM(C.CNT) AS JAM
  FROM (SELECT AA.*
             , SUM(CASE WHEN AA.STATUS = 25 THEN 1 END) OVER(PARTITION BY ID   ORDER BY AA.Chage_dtm) GRP -- Chage_dtm 대신 순번 컬럼있으시면 대체해주세요.
          FROM CANISTER AA ) A
        LEFT OUTER JOIN
        dsps B
        ON  A.ID = B.ID
       AND A.Chage_dtm = B.dsps_dtm
        LEFT OUTER JOIN
       (SELECT CC.ID , CC.alarm_dtm , COUNT(*) CNT
          FROM alarm CC
         WHERE CC.alarm_num IN ( '21' , '121' )
         GROUP BY CC.ID , CC.alarm_dtm  )  C
        ON A.ID        = C.ID
       AND A.Chage_dtm = C.alarm_dtm
GROUP BY A.ID , A.GRP

 

by 갈매기 [2018.05.11 01:33:44]

우리집 아찌 님, 마농님, 답변 감사합니다. 돌려보고 상황보고 할께요~

by 갈매기 [2018.05.15 06:36:31]

추가 질문 드립니다. 

canister 테이블에 countQty 항목을 추가 하였습니다. 추가 된 항목을 가지고 마지막 결과를 제시하는 항목에 나타내고 싶습니다. canister 테이블에 Status=12 일때 CountQty항목에 숫자가 수기로 기입 됩니다. 실제 남은 수량과 DB에서 계산한 수량을 체크해야 합니다. 

D refilled refilled_date reclaimed_date Cycle DSPSed Jam CountQty
1.61431E+19 100 2.01701E+13 2.01705E+13 00378021610_1 100 3 0
1.61431E+19 150 2.01801E+13 2.01802E+13 00378021610_2 129 5 21

 

질문내용을 필요한 방향으로 수정하겠습니다. 단순히 countQty를 코드에 삽입하니, Group by 에서 부터 막히네요. 고견 부탁 드립니다. 

by 마농 [2018.05.15 07:47:13]

제 쿼리 결과는 jam 이 5, 3 이 나옵니다.
올려주신 결과는 3, 5 네요?
제쿼리가 맞고 올려주신 결과가 틀린걸까요? 아니면 제 쿼리가 틀린 걸까요?
테이블간의 관계에 대한 설명이 부족하여 나름 해석했는데 결과가 일치하지 않네요?
3,5 가 맞다면 정확한 설명 부탁드립니다.
5,3 이 맞다면? 질문하시기 전에 꼼꼼히 확인하고 질문해 주세요.
예시 자료까지 제시해 가며 질문하신 부분은 좋았지만...
전체적으로 질문이 불명확합니다.
qty 항목은 문자 항목처럼 따옴표로 표현하셨지만 실제로는 숫자항목일 듯 하구요.
테이블간의 관계를 명확하게 설명하지 못했네요.
결과(3,5)도 불명확하구요.

by 마농 [2018.05.15 07:52:19]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
WITH Canister(ID, status, Chage_dtm, NDC, MED_qty, CountQty) AS
(
          SELECT '16143088887721430094', '25', '20170117115105', '00378021610',  100, null
UNION All SELECT '16143088887721430094', '30', '20170119120354', '00378021610',  100, null
UNION All SELECT '16143088887721430094', '40', '20170119120536', '00378021610', null, null
UNION All SELECT '16143088887721430094', '41', '20170310101115', '00378021610',    0, null
UNION All SELECT '16143088887721430094', '50', '20170530154629', '00378021610',    0, null
UNION All SELECT '16143088887721430094', '60', '20170531154629', '00378021610',    0, null
UNION All SELECT '16143088887721430094', '12', '20170531154629', '00378021610',    0,    0
UNION All SELECT '16143088887721430094', '25', '20180101120000', '00378021610',  150, null
UNION All SELECT '16143088887721430094', '30', '20180102120000', '00378021610',  150, null
UNION All SELECT '16143088887721430094', '40', '20180103120000', '00378021610', null, null
UNION All SELECT '16143088887721430094', '41', '20180103131211', '00378021610', null, null
UNION All SELECT '16143088887721430094', '40', '20180104121211', '00378021610', null, null
UNION All SELECT '16143088887721430094', '41', '20180201000000', '00378021610', null, null
UNION All SELECT '16143088887721430094', '50', '20180202000000', '00378021610',   20, null
UNION All SELECT '16143088887721430094', '60', '20180203000000', '00378021610',   20, null
UNION All SELECT '16143088887721430094', '12', '20180203000000', '00378021610',   20,   21
)
, dsps(ID, dsps_dtm, dsps_qty) AS
(
          SELECT '16143088887721430094', '20170120115105',  80
UNION All SELECT '16143088887721430094', '20170120215105',  10
UNION All SELECT '16143088887721430094', '20170125121200',   5
UNION All SELECT '16143088887721430094', '20170202120000',   4
UNION All SELECT '16143088887721430094', '20170529154629',   1
UNION All SELECT '16143088887721430094', '20180103121522',  10
UNION All SELECT '16143088887721430094', '20180104120111', 100
UNION All SELECT '16143088887721430094', '20180105120111',  10
UNION All SELECT '16143088887721430094', '20180106120111',   5
UNION All SELECT '16143088887721430094', '20180107120111',   4
)
, alarm(ID, alarm_dtm, alarm_num) AS
(
          SELECT '16143088887721430094', '20170119120354', '21'
UNION All SELECT '16143088887721430094', '20170119120536', '121'
UNION All SELECT '16143088887721430094', '20170310101115', '21'
UNION All SELECT '16143088887721430094', '20170310101129', '121'
UNION All SELECT '16143088887721430094', '20170310101158', '21'
UNION All SELECT '16143088887721430094', '20170310101159', '10'
UNION All SELECT '16143088887721430094', '20170310101215', '15'
UNION All SELECT '16143088887721430094', '20180103131220', '21'
UNION All SELECT '16143088887721430094', '20180104121322', '121'
UNION All SELECT '16143088887721430094', '20180131000000', '21'
)
SELECT a.ID
     , a.refilled
     , a.refilled_date
     , a.reclaimed_date
     , a.NDC
     , a.Cycle
     , a.DSPSed
     , a.CountQty
     , COUNT(b.ID) Jam
  FROM (SELECT a.ID
             , a.refilled
             , a.refilled_date
             , a.reclaimed_date
             , a.NDC
             , a.Cycle
             , a.CountQty
             , SUM(b.dsps_qty) DSPSed
          FROM (SELECT ID
                     , MIN(CASE WHEN status = '25' THEN MED_qty END) refilled
                     , MIN(Chage_dtm) refilled_date
                     , MAX(Chage_dtm) reclaimed_date
                     , SUM(CountQty ) CountQty
                     , NDC
                     , Cycle
                  FROM (SELECT ID, status, Chage_dtm, NDC, MED_qty, CountQty
                             , SUM(CASE WHEN status = '25' THEN 1 END)
                               OVER(PARTITION BY ID, NDC ORDER BY Chage_dtm) Cycle
                          FROM Canister
                        ) a
                 GROUP BY ID, NDC, Cycle
                ) a
          LEFT OUTER JOIN dsps b
            ON a.ID = b.ID
           AND b.dsps_dtm BETWEEN a.refilled_date AND a.reclaimed_date
         GROUP BY a.ID, a.refilled, a.refilled_date, a.reclaimed_date, a.NDC, a.Cycle, a.CountQty
        ) a
  LEFT OUTER JOIN alarm b
    ON a.ID = b.ID
   AND b.alarm_dtm BETWEEN a.refilled_date AND a.reclaimed_date
   AND b.alarm_num IN ('21', '121')
 GROUP BY a.ID, a.refilled, a.refilled_date, a.reclaimed_date, a.NDC, a.Cycle, a.CountQty, a.DSPSed
;

 

by 갈매기 [2018.05.15 11:26:19]

마농님 의견에 동의 합니다. 제가 세심하지 못했네요. 귀한 시간 내 주셔서 감사합니다. 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입