데이터베이스 질문과 답변
| da | id | bu | flag_cnt |
|
10 |
100 |
390 |
0 |
| 10 | 100 | 350 | 0 |
| 20 | 101 | 10 | 0 |
| 20 | 100 | 20 | 1 |
| 20 | 102 | 330 | 0 |
| 30 | 109 | 50 | 0 |
| 30 | 100 | 50 | 1 |
flag_cnt를 구하는 쿼리를 짜고 싶습니다...
da가 10인행을 제외한 나머지 행이 첫번째로우까지 id를 계속 비교하면서 같은 값이 있으면 1로 아니면 0으로 카운터를 구하고자 합니다...
재귀를 써야 할까요?
by 우리집아찌
[2017.10.27 16:11:36]
-- 능력이 안되서..
WITH T ( da , id , bu ) AS (
SELECT '10' , '100' ,'390' FROM DUAL UNION ALL
SELECT '10' , '100' ,'350' FROM DUAL UNION ALL
SELECT '20' , '101' ,'10' FROM DUAL UNION ALL
SELECT '20' , '100' ,'20' FROM DUAL UNION ALL
SELECT '20' , '102' ,'330' FROM DUAL UNION ALL
SELECT '30' , '109' ,'50' FROM DUAL UNION ALL
SELECT '30' , '100' ,'50' FROM DUAL
) , T2 AS (
SELECT T.*
, ROW_NUMBER() OVER(ORDER BY ROWNUM) RN
FROM T
)
SELECT A.RN
, A.DA
, A.ID
, A.BU
, CASE WHEN DA = 10 THEN 0
WHEN ( SELECT COUNT(*) FROM T2 B WHERE B.RN < A.RN AND B.ID = A.ID ) > 0 THEN 1
ELSE 0
END
FROM T2 A
ORDER BY A.RN
by 우리집아찌
[2017.10.27 16:51:13]
-- 다시
WITH T ( da , id , bu ) AS (
SELECT '10' , '100' ,'390' FROM DUAL UNION ALL
SELECT '10' , '100' ,'350' FROM DUAL UNION ALL
SELECT '20' , '101' ,'10' FROM DUAL UNION ALL
SELECT '20' , '100' ,'20' FROM DUAL UNION ALL
SELECT '20' , '102' ,'330' FROM DUAL UNION ALL
SELECT '30' , '109' ,'50' FROM DUAL UNION ALL
SELECT '30' , '100' ,'50' FROM DUAL
) , T2 AS (
SELECT T.*
, ROW_NUMBER() OVER(ORDER BY ROWNUM) RN
FROM T
)
SELECT Q.DA , Q.ID , Q.BU
, CASE WHEN DA = 10 THEN 0
WHEN GB > 0 THEN 1
ELSE 0
END FLAG_CNT
FROM (SELECT A.RN
, MAX(A.DA) DA
, MAX(A.ID) ID
, MAX(A.BU) BU
,COUNT(CASE WHEN A.ID = B.ID THEN 1 END) GB
FROM T2 A
, T2 B
WHERE A.RN > B.RN(+)
GROUP BY A.RN
ORDER BY A.RN
) Q
by jkson
[2017.10.27 17:01:57]
WITH T ( da , id , bu ) AS (
SELECT '10' , '100' ,'390' FROM DUAL UNION ALL
SELECT '10' , '100' ,'350' FROM DUAL UNION ALL
SELECT '20' , '101' ,'10' FROM DUAL UNION ALL
SELECT '20' , '100' ,'20' FROM DUAL UNION ALL
SELECT '20' , '102' ,'330' FROM DUAL UNION ALL
SELECT '30' , '109' ,'50' FROM DUAL UNION ALL
SELECT '30' , '100' ,'50' FROM DUAL
)
select da, id, bu
, case when da = '10' then 0
when row_number() over(partition by id order by da, rownum) = 1 then 0
else 1
end fg
from t
order by da, rownum
by 우리집아찌
[2017.10.27 17:39:22]
아... 갯수를 그냥 ID 구룹핑 카운트하면 되는구낭.. 어렵게 짰네.
첫번째것만 아니면되니까...
by 초보개발
[2017.10.27 17:12:05]
너무 감사드립니다....
by 마농
[2017.10.30 09:32:06]
SELECT da, id, bu
, SIGN(DENSE_RANK() OVER(PARTITION BY id ORDER BY da) - 1) dr
FROM t
ORDER BY da, ROWNUM
;
) 버튼을 클릭하여 작성 하시면 됩니다.