중급 (3-2)통계, 윈도우함수, OVER(), rank, row_number, partition by, 괄호 묶고 WHERE
---
https://youtube.com/playlist?list=PLyQR2NzLKOCbOYNJHKpCax4ija38yCqBN
이거 보는 중
---
강의 보기 전에 일단 윈도우 함수가 뭔지 알아야 이해가 될 것 같아서
DATA ON-AIR - https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=351
를 정리한게 https://for-my-wealthy-life.tistory.com/48 여기인 듯
기존의 RDB는 컬럼과 컬럼간의 연산, 비교, 연결은 쉬워
근데 행과 행간의 관계정의, 비교, 연산을 하나의 SQL문으로 처리하는게 어려웠어 sub query를 만들거나.. 해야했는데 쉽게 해결하려고 나온게 WINDOW FUNCTION
WINDOW FUNCTION(ANSI/ISO SQL 표준) = ANALYTIC FUNCTION = RANK FUNCTION
문법은
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] ) FROM 테이블 명;
SELECT절에 쓴다
WINDOW 함수에는 OVER() 문구가 키워드로 필수 포함된다. - SUM() OVER()까지 해야 윈도우 함수?
크게 다섯 개의 그룹이지만 난 일단 1) 2) 만 본다
1)그룹 내 순위 관련 함수 RANK, DENSE_RANK, ROW_NUMBER
2)SUM, MAX, MIN, AVG, COUNT - SQL Server의 경우 집계함수는 OVER절 내의 ORDER BY 구문을 지원하지 않는대
1)
RANK() OVER (ORDER BY ~) 이런식이야. ORDER BY를 포함해야 한다. ORDER BY 한 값의 순서대로 랭크를 메김
RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) - 파티션을 쓰면 파티션 내의 순위
DENSE_RANK, ROW_NUMBER도 마찬가지
ROW_NUMBER는 어떤 순서가 정해질 지 알 수 없다. Oracle의 경우 rowid가 적은 행이 먼저 나온다. 추가적으로 ORDER BY 절을 이용해서 정렬 기준을 정의해야 한다.
2)
AVG(SALARY) OVER() 이런식이야
SUM(SALARY) OVER(PARTITION BY JOB_ID ORDER BY JOB_ID) - 파티션을 쓰면 파티션별 윈도우의 합
https://jhnyang.tistory.com/469
사원 연봉이 쭉 적혀 있는 테이블에 오른쪽에 컬럼으로(테이블 원래 컬럼 말고 연산한것) 평균이나 연봉 총합을 같이 띄우고 싶어
100 24,000 6,424 691,416
101 17,000 6,424 691,416
102 15,000 6,424 691,416
이런식으로
저렇게 띄우고 싶어도, SELECT SALARY, SUM(SALARY) 이렇겐 못 쓴다
select location_id, sum(location_id) from hr.locations; -> ORA-00937: not a single-group group function
▽SUM()만 해보면 레코드 하나만 반환하는데 그래서 그런가? https://finepiz.tistory.com/216 여기 보면 여러개의 데이터가 이 함수를 거치고 난 뒤, 결론이 하나의 데이터가 돼야 한대
그래서 sub query를 써서 컬럼에 계속 그것만 나오게 하기 위해 아예 값을 줘버림.
select location_id, (select sum(location_id) from hr.locations) from hr.locations;
이런 식으로
근데 OVER()를 이용하면 서브쿼리 안써도 된다
SUM(SALARY) OVER()하면 전체 행에 SUM결과를 해줘
select location_id, sum(location_id) over() from hr.locations; 는 돼. 그 컬럼은 모든 레코드에 전체의 합만 계속 써져 있어. 전체가 하나의 파티션이라고 보는 듯. 레코드 각각을 다 파티션이라고 보는게 아님.
select sum(location_id) from hr.locations; - 레코드 1개
select sum(location_id) over() from hr.locations; - 총 레코드 만큼.
AVG(SALARY)는 안되고
AVG(SALARY) OVER()는 돼?
OVER안에 아무것도 안쓰면 전체에 대해 인가 보네
주의 : OVER절은 HAVING절 이후에 계산이 돼서 COUNT(*)를 해도 WHERE 같은걸로 걸러진 것의 COUNT(*)이야
OVER를 GROUP BY절에서 쓰고 싶으면 PARTITION BY ○○○ 를 넣어줘. GROUP BY ○○○가 된 다음 그 그룹 안에서만 집계함수가 적용이 된다.
---
(7/44)
https://youtu.be/WP1E7xlqdzY - 데이터 통계를 위해 꼭 알아둬야 할 순위 함수, 윈도우 함수, 데이터베이스 실습
랭킹함수라고 부르는 윈도우 함수에 대해
저번에 했던 테이블로 순위를 메겨볼 것
날짜별로 몇잔을 팔았는지 COUNT(*) + GROUP BY + ORDER BY
이걸 순위 메겨보자
RANK() OVER()
제일 많이 판게 1위로 하려면 RANK() OVER(ORDER BY COUNT(*) 내림차순), 맨 밑에 ORDER BY 뺀다
동점이 나오면 저렇게 처리. 777 다음 10 위인데 바로 8로 만들고 싶으면
DENSE_RANK() OVER()
DENSE : 밀집한. 순위가 바로 붙어 있다 뭐 그런
동점이어도 순위가 다르게 나왔으면 좋겠어?
ROW_NUMBER() OVER()
이제 GROUP BY 개수 늘려보기
날짜별, 음료별
이걸 순위, 날짜별로 해야돼서 PARTITION BY가 들어감(날짜별로 파티션해서 순위를 메기라는 의미. 날짜별로 파티션 한 다음에 ORDER BY를 카운트 내림차순) ▽1일 안에서 순위를 메겨라 그런거구나
복습, 동점을 다른 순위로 하려면 ROW_NUMBER(▽ORDER BY ○○, ◇◇ 이렇게 해주면 된다)
1위를 차지한 음료만 출력하고 싶다? 전체를 한번 더 감싸주면 된다
댓
Q.왜 전체를 괄호로 묶고 다시 WHERE절을 사용해야하나요? 그렇게 하지 않고 그냥 GROUP BY 뒤에 HAVING RANK = 1 을 하면 오류가 뜨는데 이유가 궁금. GROUP BY가 SELECT보다 먼저 실행돼서 그런가요?
A., having절은 집계함수와 쓰이는 절이어서 row_number()와는 맞지 않답니다