인프런 커뮤니티 질문&답변
작성한 질문수
누적 백분위 구하기 질문
해결된 질문
작성
·
213
0
선생님 안녕하세요
쿼리로 누적 백분위 구하려고하는데 질문드립니다
A 10
B 20
C 30
D 40
E 50
Sum: 150
이렇게 있을때 상위 10%, 20%, 30% .. 등등에 속하는 개수는 몇개인지 구하려하는데요
예를 들어 150의 10%인 15를 만족하는 갯수는 전체 중 1개( A = 10)
20%인 30 을 만족하는 갯수는 전체 중 2개 (A와 B)
전체 중 100%는 5개 (A, B, C, D, E) 이런식으로 누적 백분위를 구하고싶은데 이렇게도 가능할까요?
그럼 A+B 값이 10% 에 드는지 20%에 드는지
A+B+C 값은 몇프로에 드는지 각각 case when을 써서 비교해보려고했는데
누적 합산을 A+B, A+B+C 이렇게 끊어서 비교하는 방법을 모르겠습니다
답변 1
0
안녕하세요?
어려운 쿼리문을 작성하고 계시네요^^
우선 제가 이해한 수준에서 답변 드립니다.
먼저 다음과 같이 예제 데이터를 만들었습니다.
CREATE TABLE Test (
col1 varchar(10),
col2 int
);
INSERT INTO test VALUES('A', 10);
INSERT INTO test VALUES('B', 20);
INSERT INTO test VALUES('C', 30);
INSERT INTO test VALUES('D', 40);
INSERT INTO test VALUES('E', 50);이 데이터에 대해 다음과 같은 방법으로 누적합(sum1), 전체합(sum2), 비율(pcnt)를 구할 수 있습니다.
SELECT col1,
col2,
SUM(col2) OVER(ORDER BY col1) AS sum1,
SUM(col2) OVER() AS sum2,
SUM(col2) OVER(ORDER BY col1) * 100 / SUM(col2) OVER() AS pcnt
FROM test;
/*
col1 col2 sum1 sum2 pcnt
------------------------------------
A 10 10 150 6
B 20 30 150 20
C 30 60 150 40
D 40 100 150 66
E 50 150 150 100
*/그럼 이 테이터를 서브쿼리로 해서, pcnt 값을 비교해 원하는 것을 얻을 수 있습니다. 10%, 20%, 30%, 100%만 확인해 본 쿼리와 결과는 다음과 같습니다.
-- 10%
SELECT col1
FROM (
SELECT col1,
col2,
SUM(col2) OVER(ORDER BY col1) AS sum1,
SUM(col2) OVER() AS sum2,
SUM(col2) OVER(ORDER BY col1) * 100 / SUM(col2) OVER() AS pcnt
FROM test
) AS t
WHERE pcnt <= 10;
/*
col1
-----
A
*/
-- 20%
SELECT col1
FROM (
SELECT col1,
col2,
SUM(col2) OVER(ORDER BY col1) AS sum1,
SUM(col2) OVER() AS sum2,
SUM(col2) OVER(ORDER BY col1) * 100 / SUM(col2) OVER() AS pcnt
FROM test
) AS t
WHERE pcnt <= 20;
/*
col1
-----
A
B
*/
-- 30%
SELECT col1
FROM (
SELECT col1,
col2,
SUM(col2) OVER(ORDER BY col1) AS sum1,
SUM(col2) OVER() AS sum2,
SUM(col2) OVER(ORDER BY col1) * 100 / SUM(col2) OVER() AS pcnt
FROM test
) AS t
WHERE pcnt <= 30;
/*
col1
-----
A
B
*/
-- 100%
SELECT col1
FROM (
SELECT col1,
col2,
SUM(col2) OVER(ORDER BY col1) AS sum1,
SUM(col2) OVER() AS sum2,
SUM(col2) OVER(ORDER BY col1) * 100 / SUM(col2) OVER() AS pcnt
FROM test
) AS t
WHERE pcnt <= 100;
/*
col1
-----
A
B
C
D
E
*/이러한 것을 원하신 건지 모르겠네요.
위 쿼리문 참고하시고, 혹시 해결이 안되는 부분 있으면 다시 질문해 주세요.
질문해 주셔서 감사합니다~
네, 가능합니다.
예를 들면 다음과 같은 방법이 있습니다.
WITH pct AS (
SELECT col1,
col2,
SUM(col2) OVER(ORDER BY col1) AS sum1,
SUM(col2) OVER() AS sum2,
SUM(col2) OVER(ORDER BY col1) * 100 / SUM(col2) OVER() AS pcnt
FROM test
)
SELECT CONCAT(p.value, '%') AS pcent,
(SELECT COUNT(*) FROM pct WHERE pcnt <= p.value) AS cnt
FROM STRING_SPLIT('10,20,30,40,50,60,70,80,90,100', ',') AS p;
/*
pcent cnt
-----------
10% 1
20% 2
30% 2
40% 3
50% 3
60% 3
70% 4
80% 4
90% 4
100% 5
*/위 구문 참고하셔서, 적절히 수정해 보세요.
화이팅!!! 입니다^^
답변 감사드립니다, 선생님께서 이해해주신게 맞습니다
하지만 결과값을 컬럼단위가 아닌 카운팅해서 한번에 추출하는 방법도 가능할까요?
10% 1개
20% 2개
30% 3개
100% 5개
이렇게요!