데이터베이스 질문과 답변
by 띵똥 [2018.11.21 12:02:21]
처리자별 처리건수를 확인하는 쿼리
아래 방식말고 쉽게 처리가 될꺼같아 질문 올립니다!
WITH t AS
(
SELECT '4001180900185' CUI/*접수*/, '1020020026' INS1/*처리자1*/, '1020170099' INS2/*처리자2*/ FROM dual
UNION ALL SELECT '4001180900185','1020020026','1020170099' FROM DUAL
UNION ALL SELECT '4001180900185','1020020026','1020170099' FROM DUAL
UNION ALL SELECT '4001180900185','1020020026','1020170099' FROM DUAL
UNION ALL SELECT '4035181002812','1020020030','ET20189637' FROM DUAL
UNION ALL SELECT '4020180901142','1020060513','ET20170002' FROM DUAL
UNION ALL SELECT '4034181000012','1019910304','ET19970141' FROM DUAL
UNION ALL SELECT '4031181300405','1019930067','1020180050' FROM DUAL
UNION ALL SELECT '4030181000802','1020120503','ET20189617' FROM DUAL
UNION ALL SELECT '4030181000802','1020120503','ET20189617' FROM DUAL
UNION ALL SELECT '4030181000802','1020120503','ET20189617' FROM DUAL
UNION ALL SELECT '4030181000802','1020120503','ET20189617' FROM DUAL
UNION ALL SELECT '4005180903192','1019940032','ET19972235' FROM DUAL
UNION ALL SELECT '4037180903442','1020150059','ET20171012' FROM DUAL
UNION ALL SELECT '4002180904782','1020120021','1020170150' FROM DUAL
UNION ALL SELECT '4001181308892','1020160730','1020070608' FROM DUAL
UNION ALL SELECT '4007180901142','1019930115','1020170062' FROM DUAL
UNION ALL SELECT '4008180800165','1020030201','1020180044' FROM DUAL
UNION ALL SELECT '4018180903942','1019950050','ET19971268' FROM DUAL
UNION ALL SELECT '4014181005022','1020040047','ET20020696' FROM DUAL
UNION ALL SELECT '4005180902092','1020160747','ET19970649' FROM DUAL
UNION ALL SELECT '4005180902092','1020160747','ET19970649' FROM DUAL
UNION ALL SELECT '4005180902092','1020160747','ET19970649' FROM DUAL
UNION ALL SELECT '4005180902092','1020160747','ET19970649' FROM DUAL
UNION ALL SELECT '4013180903412','1020120507','ET20110456' FROM DUAL
UNION ALL SELECT '4032180901032','1020110045','ET20170029' FROM DUAL
UNION ALL SELECT '4024181002582','1019960027','ET20020309' FROM DUAL
UNION ALL SELECT '4039180901362','1020060021','ET20000033' FROM DUAL
UNION ALL SELECT '4003180901692','1020150069','ET20189649' FROM DUAL
UNION ALL SELECT '4037181002492','1019950044','1020170083' FROM DUAL
UNION ALL SELECT '4008180905082','1020150057','ET20170040' FROM DUAL
UNION ALL SELECT '4024180902352','1020030710','ET20020309' FROM DUAL
UNION ALL SELECT '4001180806422','1019960018','1020170164' FROM DUAL
UNION ALL SELECT '4010180902262','1020050039','ET20185713' FROM DUAL
UNION ALL SELECT '4010180902262','1020050039','ET20185713' FROM DUAL
UNION ALL SELECT '4010180902262','1020050039','ET20185713' FROM DUAL
UNION ALL SELECT '4039180904592','1020141201','1020150019' FROM DUAL
UNION ALL SELECT '4037181002472','1019950044','1020170083' FROM DUAL
UNION ALL SELECT '4037181002472','1019950044','1020170083' FROM DUAL
UNION ALL SELECT '4034181304532','1020031003','ET19970141' FROM DUAL
UNION ALL SELECT '4001180905522','1020140018','ET19973771' FROM DUAL
UNION ALL SELECT '4004180905082','1019940002','1020130515' FROM DUAL
UNION ALL SELECT '4024180900092','1020150032','ET20130831' FROM DUAL
UNION ALL SELECT '4010180904512','1020140902','1020180062' FROM DUAL
UNION ALL SELECT '4039180901322','1019970006','ET20000033' FROM DUAL
UNION ALL SELECT '4013181005012','1020130011','1020140014' FROM DUAL
UNION ALL SELECT '4013181005012','1020130011','1020140014' FROM DUAL
UNION ALL SELECT '4013181005012','1020130011','1020140014' FROM DUAL
UNION ALL SELECT '4011181005062','1020130601','ET20189643' FROM DUAL
UNION ALL SELECT '4024180902002','1019960027','ET19971251' FROM DUAL
UNION ALL SELECT '4018180901132','1020130009','1019930091' FROM DUAL
UNION ALL SELECT '4018180901132','1020130009','1019930091' FROM DUAL
UNION ALL SELECT '4018180901132','1020130009','1019930091' FROM DUAL
UNION ALL SELECT '4018180901132','1020130009','1019930091' FROM DUAL
UNION ALL SELECT '4001180904932','1020000015','ET20050177' FROM DUAL
UNION ALL SELECT '4029180800022','1020160757','ET20150563' FROM DUAL
UNION ALL SELECT '4029180800022','1020160757','ET20150563' FROM DUAL
UNION ALL SELECT '4009181000395','1019950014','1020180051' FROM DUAL
UNION ALL SELECT '4009181000395','1019950014','1020180051' FROM DUAL
UNION ALL SELECT '4009180905152','1019950014','1020180051' FROM DUAL
)
SELECT X.INS, SUM(CNT)
FROM (SELECT INS, CUI, 1 AS CNT
FROM (SELECT CUI,INS1 AS INS
FROM T
UNION ALL
SELECT CUI,INS2 AS INS
FROM T)A
GROUP BY A.INS, CUI)X
GROUP BY X.INS
SELECT ins
, COUNT(DISTINCT cui) cnt
FROM t
UNPIVOT (ins FOR gb IN (ins1, ins2))
GROUP BY ins
;
SELECT * FROM t UNPIVOT (ins FOR gb IN (ins1 AS 1, ins2 AS 1)) PIVOT (COUNT(DISTINCT cui) FOR gb IN (1 cnt)) ;
감사합니다.. 피봇이라는 함수가 있었네요!
하나더 궁금한게 있는데..
WITH T AS (
SELECT '4006180804342' CSTMR_GUIDANCE_NO,'1020140327' MAIN_INSPCTR_CD, 'ET19971929' SUB_INSPCTR_CD, 'Y' OVER_CNT FROM DUAL
UNION ALL
SELECT '4006180804342','1020140327','ET19971929','Y' FROM DUAL
UNION ALL
SELECT '4006180804342','1020140327','ET19971929','Y' FROM DUAL
UNION ALL
SELECT '4006180804344','1020140327','ET19971929','Y' FROM DUAL
UNION ALL
SELECT '4006180804343','1020140327','ET19971929','N' FROM DUAL
UNION ALL
SELECT '4006180804343','1020140327','ET19971929','N' FROM DUAL)
SELECT COUNT(DISTINCT CSTMR_GUIDANCE_NO)
FROM T
지금 이대로는 3이 나오는데 OVER_CNT가 Y인 CSTMR_GUIDANCE_NO를 또 합산해서 보여주려합니다..
위 쿼리로 보면 3,2 이렇게 나오게 하려고 하는데 어떻게 해야하나요?!
CSTMR_GUIDANCE_NO건은 3건 OVER_CNT='Y'인 CSTMR_GUIDANCE_NO건은 2건 이렇게..
SELECT COUNT(*), SUM(DECODE(OC,'N',0,1))
FROM (SELECT CSTMR_GUIDANCE_NO,
MAX(OVER_CNT) AS OC
FROM T
GROUP BY CSTMR_GUIDANCE_NO)
이 방식 말고.. 서브쿼리 안쓰고 한번에 될까요?!
SELECT COUNT(DISTINCT cstmr_guidance_no) cnt
, COUNT(DISTINCT DECODE(over_cnt, 'Y', cstmr_guidance_no)) cnt_y
FROM t
;
SELECT COUNT(COUNT(*)) cnt
, COUNT(NULLIF(MAX(over_cnt), 'N')) cnt_y
FROM t
GROUP BY cstmr_guidance_no
;
아~ 정말 감사합니다~ 많은걸 배웁니다
) 버튼을 클릭하여 작성 하시면 됩니다.