user | data |
1 | 115 |
2 | 0 |
2 | 40 |
3 | 150 |
4 | 0 |
4 | 0 |
4 | 0 |
안녕하세요 쿼리를 짜는데 어려움이 있어서 질문드리게되었습니다
만약 위와같은 테이블이 있다면
해당 user 데이터가 전부 0보다 큼 => 2 ( 해당하는 유저 1 , 3 이니까 2개 )
해당 User 데이터가 전부 0임 => 1 ( 유저 4 가 해당 )
해당 user 데이터중 하나라도 0임 => 1 ( 유저 2 가 해당 )
전부 0보다 큼 | 전부 0임 | 하나라도 0임 |
2 | 1 | 1 |
원하는 결과는 위와같습니다..
이렇게 유저 단위로 묶어서 카운트를 하고싶은데 조언좀 부탁드립니다.
억지로 끼워맞춘 느낌이긴 합니다만..
with t1 as ( select 1 user, 115 data from dual union all select 2, 0 from dual union all select 2, 40 from dual union all select 3, 150 from dual union all select 4, 0 from dual union all select 4, 0 from dual union all select 4, 0 from dual ) select sum(c1), sum(c2), sum(c3) from ( select case when max(data) > 0 and min(data) > 0 then 1 else 0 end c1, case when max(data) = 0 then 1 else 0 end c2, case when max(data) > 0 and min(data) = 0 then 1 else 0 end c3 from t1 group by user) t2
WITH t AS ( SELECT 1 user, 115 data UNION ALL SELECT 2, 0 UNION ALL SELECT 2, 40 UNION ALL SELECT 3, 150 UNION ALL SELECT 4, 0 UNION ALL SELECT 4, 0 UNION ALL SELECT 4, 0 ) SELECT COUNT(CASE WHEN cnt_0 = 0 THEN 1 END) cnt_1 , COUNT(CASE WHEN cnt_1 = 0 THEN 1 END) cnt_2 , COUNT(CASE WHEN cnt_1 * cnt_0 > 0 THEN 1 END) cnt_3 FROM (SELECT user , SUM( SIGN(data)) cnt_1 , SUM(1 - SIGN(data)) cnt_0 FROM t GROUP BY user ) a ;
-- 테스트 목적으로 데이터를 일부 추가하였습니다. -- 이게 맞는지 확인 차 저도 올려봅니다. WITH t AS ( SELECT 1 user_id, 115 data FROM dual UNION ALL SELECT 2, 0 FROM dual UNION ALL SELECT 2, 40 FROM dual UNION ALL SELECT 3, 150 FROM dual UNION ALL SELECT 4, 0 FROM dual UNION ALL SELECT 4, 0 FROM dual UNION ALL SELECT 4, 0 FROM dual UNION ALL SELECT 5, 70 FROM dual UNION ALL SELECT 5, 80 FROM dual ) SELECT COUNT(CASE WHEN gb = cnt THEN user_id END) AS col1 , COUNT(CASE WHEN gb = 0 AND cnt > 1 THEN user_id END) AS col2 , COUNT(CASE WHEN gb >= 1 AND gb < cnt THEN user_id END) AS col3 FROM ( SELECT user_id , SUM(gb) AS gb , COUNT(gb) AS cnt FROM ( SELECT user_id , DECODE(data, 0, 0, 1) AS gb FROM t ) GROUP BY user_id ) ;