꿈꾸는 개발자, DBA 커뮤니티 구루비

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

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

;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]

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]


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]

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(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변