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

count 문의합니다.

by 김인덕 [SQL Query] count [2017.11.16 11:21:33]

결과 : count = 1

결과 : count = 2

with stat(cd,dd) as(
select 'A',1 from dual
union all select 'B',2 from dual
union all select 'B',3 from dual
union all select 'B',4 from dual
union all select 'B',5 from dual
union all select 'C',6 from dual
union all select 'A',7 from dual
union all select 'B',8 from dual
union all select 'B',9 from dual
union all select 'A',10 from dual
union all select 'B',11 from dual
union all select 'C',12 from dual
union all select 'D',13 from dual
)
select
cd
,dd
from stat

결과 : count = 3

dd는 seq로 보시면 됩니다.

위 데이터 에서 순서는 그대로(dd,cd) 일때
A와 A사이 count?를 1 로
A마지막 이후 count 1 로
처리하고 싶은데 방법이 없을까요? ^^

내용 추가 합니다..

카운트는 A를 시작으로 진행됩니다.

A가 없을 경우는 결과는 0입니다.

with stat(cd,dd) as(
select 'A',1 from dual
union all select 'B',2 from dual
)
select
cd
,dd
from stat;

결과 : 1

with stat(cd,dd) as(
select 'B',1 from dual
union all select 'B',2 from dual
union all select 'C',3 from dual
)
select
cd
,dd
from stat;

결과 : 0

with stat(cd,dd) as(
select 'A',1 from dual
)
select
cd
,dd
from stat;

결과 : 0

with stat(cd,dd) as(
select 'B',2 from dual
union all select 'A',3 from dual
union all select 'B',4 from dual
)
select
cd
,dd
from stat;

결과 : 1

by jkson [2017.11.16 11:34:11]

질문이 잘 이해가 안 되네요.

A와 A사이를 1로 카운트

A 이후가 1로 카운트이면

A의 갯수를 구하는 거랑 똑같은 거 같은데

select count(1) cnt from stat where cd = 'A'

이렇게 하면 안 되나요?

by 김인덕 [2017.11.16 12:08:06]

질문이 모호했네요. 첫번째 데이터 인경우는 카운트가 1이 되야합니다.

by jkson [2017.11.16 12:22:32]

첫 번째 케이스 결과가 없어서 헷갈렸네요.

그럼 마지막 A가 나온 이후 데이터는 각각 1로 카운트되는 건가요?

 select 'A','20171101' from dual
 union all select 'B','20171102' from dual
 union all select 'B','20171102' from dual
 union all select 'B','20171103' from dual
 union all select 'B','20171104' from dual
 union all select 'C','20171105' from dual
 union all select 'A','20171106' from dual
 union all select 'B','20171107' from dual
 union all select 'C','20171107' from dual

의 경우 결과값은 어떻게 나와야 하는지요?

by 김인덕 [2017.11.16 12:26:32]

음.. 결과는 2 입니다. A와 A는 1 + 마지막 A 이후 다른값 존재시 1 입니다.

by jkson [2017.11.16 12:35:11]

with stat(cd,dd) as(
           select 'A','20171101' from dual
 union all select 'B','20171102' from dual
 union all select 'B','20171102' from dual
 union all select 'B','20171103' from dual
 union all select 'B','20171104' from dual
 union all select 'C','20171105' from dual
 union all select 'A','20171106' from dual
 union all select 'B','20171107' from dual
 )
select sum(fg) cnt
  from
  (
  select case when cd != 'A' and lag(cd) over (order by dd) = 'A' then 1 end fg
    from stat
  )

아.. 그런데 만들고 보니.. A가 연속으로 오는 경우는요?

by 마농 [2017.11.16 12:44:15]

좀 더 다양한 예가 필요합니다.
1. A 로 시작했는데 두번째 A 가 안나오는 경우는?
- dd : 1 ~ 3 ==> ?
2. A 한건만 있는 경우 건수는?
- dd : 1 ~ 1 ==> ?
3. A 로 시작하지 않을 경우는?
- dd : 2 ~ 4 ==> ? A 가 없는 경우
- dd : 2 ~ 7 ==> ? A 로 끝나는 경우
- dd : 2 ~ 8 ==> ? A 가 나오고 이후 더 있는 경우
4. A 만 연속으로 있는 경우?
- dd : 1, 7 ==> ?
- dd : 1, 7, 10 ==> ?

by 김인덕 [2017.11.16 13:46:35]

혹시 스칼라 서브쿼리로 가능할까요?

1. A 로 시작했는데 두번째 A 가 안나오는 경우는?
- dd : 1 ~ 3 ==> ?
>> count : 1
2. A 한건만 있는 경우 건수는?
- dd : 1 ~ 1 ==> ?
>> count : 0
3. A 로 시작하지 않을 경우는?
- dd : 2 ~ 4 ==> ? A 가 없는 경우
>> count : 0
- dd : 2 ~ 7 ==> ? A 로 끝나는 경우
>> count : 0
- dd : 2 ~ 8 ==> ? A 가 나오고 이후 더 있는 경우
>> count : 1
4. A 만 연속으로 있는 경우?
>> count : 0
- dd : 1, 7 ==> ?
- dd : 1, 7, 10 ==> ?

by 마농 [2017.11.16 14:06:13]

WITH stat(cd, dd) AS
(
-- 주석 라인 바꿔가며 테스트 해보세요. --
SELECT 'x', 0 FROM dual WHERE 1=2
--UNION ALL SELECT 'A',  1 FROM dual
--UNION ALL SELECT 'B',  2 FROM dual
--UNION ALL SELECT 'B',  3 FROM dual
UNION ALL SELECT 'B',  4 FROM dual
UNION ALL SELECT 'B',  5 FROM dual
UNION ALL SELECT 'C',  6 FROM dual
UNION ALL SELECT 'A',  7 FROM dual
UNION ALL SELECT 'B',  8 FROM dual
UNION ALL SELECT 'B',  9 FROM dual
UNION ALL SELECT 'A', 10 FROM dual
--UNION ALL SELECT 'B', 11 FROM dual
--UNION ALL SELECT 'C', 12 FROM dual
--UNION ALL SELECT 'D', 13 FROM dual
)
-- 정의 : 최초 A 가 발생된 이후 자료중 A 가 아닌 연속된 집합의 수
SELECT COUNT(DISTINCT gb) cnt  -- 연속집합의 수
  FROM (SELECT cd, dd
             , MIN(DECODE(cd, 'A', dd)) OVER() start_dd -- 최초A
             , ROW_NUMBER() OVER(ORDER BY dd)
             - ROW_NUMBER() OVER(PARTITION BY DECODE(cd, 'A', 1, 2) ORDER BY dd) gb -- 연속집합
          FROM stat
        )
 WHERE dd > start_dd    -- 최초 A 이후
   AND cd != 'A'        -- A가 아닌
;

by 김인덕 [2017.11.16 16:22:48]

답변 감사합니다. 근데 이구조로는 서브쿼리가 안될 듯 싶네요. 그쵸?

한번더 조회 해서 조인을 해야하는지요.

by 마농 [2017.11.16 17:53:20]

왜 서브쿼리가 필요한지?
왜 조인이 필요한지?
질문만 봐서는 모르겠네요.

by 더기 [2017.11.17 10:45:41]

-- 죄송합니다. 메인을 빼고 문의를 드린게 실수였네요.

-- gbn이 001인 a_not_cnt를 gg_a_not_cnt처럼 해결하려고 문의를 드렸던겁니다.

-- 알려주신 쿼리로는 조인으로 밖에는 생각나는게 없어서요.

-- 다른방법이 있으면 알려주시면 감사하겠습니다.

WITH
mm(gbn,cd) AS(
SELECT '001', 'x' FROM dual WHERE 1=2
UNION ALL SELECT '001', 'D' FROM dual
UNION ALL SELECT '002', 'B' FROM dual
UNION ALL SELECT '003', 'A' FROM dual
UNION ALL SELECT '004', 'B' FROM dual
UNION ALL SELECT '005', 'A' FROM dual
UNION ALL SELECT '006', 'C' FROM dual
UNION ALL SELECT '007', 'C' FROM dual
UNION ALL SELECT '008', 'A' FROM dual
),
stat(gbn, cd, dd) AS(
SELECT '001', 'x', 0 FROM dual WHERE 1=2
UNION ALL SELECT '001', 'A', 1 FROM dual
UNION ALL SELECT '001', 'B', 2 FROM dual
UNION ALL SELECT '001', 'B', 3 FROM dual
UNION ALL SELECT '001', 'B', 4 FROM dual
UNION ALL SELECT '001', 'B', 5 FROM dual
UNION ALL SELECT '001', 'C', 6 FROM dual
UNION ALL SELECT '001', 'A', 7 FROM dual
UNION ALL SELECT '001', 'B', 8 FROM dual
UNION ALL SELECT '001', 'B', 9 FROM dual
UNION ALL SELECT '001', 'A', 10 FROM dual
UNION ALL SELECT '001', 'B', 11 FROM dual
UNION ALL SELECT '001', 'C', 12 FROM dual
UNION ALL SELECT '001', 'D', 13 FROM dual
UNION ALL SELECT '002', 'A', 1 FROM dual
UNION ALL SELECT '002', 'B', 2 FROM dual
UNION ALL SELECT '002', 'B', 3 FROM dual
UNION ALL SELECT '003', 'A', 1 FROM dual
UNION ALL SELECT '004', 'B', 2 FROM dual
UNION ALL SELECT '004', 'B', 3 FROM dual
UNION ALL SELECT '004', 'B', 4 FROM dual
UNION ALL SELECT '005', 'A', 1 FROM dual
UNION ALL SELECT '005', 'A', 7 FROM dual
UNION ALL SELECT '005', 'A', 10 FROM dual
UNION ALL SELECT '006', 'B', 1 FROM dual
UNION ALL SELECT '006', 'A', 2 FROM dual
UNION ALL SELECT '006', 'B', 3 FROM dual
UNION ALL SELECT '006', 'C', 4 FROM dual
UNION ALL SELECT '007', 'B', 1 FROM dual
UNION ALL SELECT '007', 'A', 2 FROM dual
UNION ALL SELECT '007', 'B', 3 FROM dual
UNION ALL SELECT '007', 'A', 4 FROM dual
UNION ALL SELECT '007', 'B', 5 FROM dual
UNION ALL SELECT '007', 'C', 6 FROM dual
UNION ALL SELECT '008', 'B', 1 FROM dual
UNION ALL SELECT '008', 'A', 2 FROM dual
UNION ALL SELECT '008', 'B', 3 FROM dual
UNION ALL SELECT '008', 'A', 4 FROM dual
)
select
tt.gbn
,tt.cd
,tt.a_dd_min
,tt.a_dd_max
,tt.dd_max
,(select
count(*)
from stat c
where c.gbn = tt.gbn
and c.dd >= tt.a_dd_min -- A최초
and c.dd <= tt.dd_max -- 마지막
and c.cd <> 'A'
) a_not_cnt -- 연속 포함 카운트(X)
,nvl(tt.cnt,0) gg_a_not_cnt -- 연속 제외 카운트(O)
from (
select
t.gbn
,t.cd
,t.a_dd_min
,t.a_dd_max
,(select
decode(t.cd,'A',max(b.dd),min(b.dd))
from stat b
where b.gbn = t.gbn
and b.dd >= decode(t.cd,'A',t.a_dd_min,t.a_dd_max)
and b.cd in ('B','C','D')
) dd_max -- 마지막 비교대상
,gg.cnt
from (
select
mm.gbn
,mm.cd -- 현재상태
,(select min(a.dd) from stat a where a.gbn = mm.gbn and a.cd = 'A') a_dd_min -- A최초
,(select max(a.dd) from stat a where a.gbn = mm.gbn and a.cd = 'A') a_dd_max -- A마지막
from mm
where 1=1
and gbn in (
select gbn
from stat
where stat.cd = 'A' -- A가 존재하는 대상
)
) t -- A가 존재하는 대상
left outer join
(
select
gbn
,count(distinct gb) cnt
from (SELECT gbn,cd, dd
, MIN(DECODE(cd, 'A', dd)) OVER(PARTITION BY gbn) start_dd -- 최초A
, ROW_NUMBER() OVER(PARTITION BY gbn ORDER BY dd)
- ROW_NUMBER() OVER(PARTITION BY gbn,DECODE(cd, 'A', 1, 2) ORDER BY gbn,dd) gb -- 연속집합
FROM stat
)
where dd > start_dd -- 최초 A 이후
and cd != 'A' -- A가 아닌
group by gbn
) gg on gg.gbn = t.gbn
) tt
order by gbn
;

by 마농 [2017.11.23 19:22:25]

SELECT a.gbn
     , a.cd
     , b.a_min_dd
     , b.max_dd
     , b.cnt
  FROM mm a
 INNER JOIN
       (SELECT gbn
             , a_min_dd
             , MAX(dd) max_dd
             , COUNT(DISTINCT gb) cnt
          FROM (SELECT gbn, cd, dd
                     , MIN(DECODE(cd, 'A', dd)) OVER(PARTITION BY gbn) a_min_dd
                     , ROW_NUMBER() OVER(PARTITION BY gbn ORDER BY dd)
                     - ROW_NUMBER() OVER(PARTITION BY gbn, DECODE(cd, 'A', 1, 2) ORDER BY dd) gb
                  FROM stat
                )
         WHERE dd > a_min_dd
           AND cd != 'A'
         GROUP BY gbn, a_min_dd
        ) b
    ON a.gbn = b.gbn
 ORDER BY a.gbn
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

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

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

데이터베이스 질문과 답변