윈도우 함수 기본문법

SELECT WINDOW_FUNCTION () OVER(PARTITION BY 컬럼 ORDER BY 컬럼)
FROM 테이블명

일반 집계함수 사용 예

select user_id, MAX(review_date)
from 테이블
group by user_id
;
select user_id, chat_text, review_Date
from( select user_id, chat_text, review_Date
, row_number() over(partition by USER_ID order by REVIEW_DATE desc )as rown
from 테이블
)as a
where rown=1

윈도우 함수 - LAG 함수 예제

select *
, LAG(SALARY) OVER (ORDER BY NAME) as PREV_SAL
from basic.window1

윈도우 함수 - LAG 함수 예제2: 2번째 전 값 구하기

select *
, LAG(SALARY,2) OVER (ORDER BY NAME) as PREV_SAL
from basic.window1

윈도우 함수 - PERCENT_RANK 함수 예제

select *
, PERCENT_RANK() OVER (partition by JOB order BY SALARY)
from basic.window1

with 구문 활용 예시1

with soso as # with 뒤쪽에 임시테이블명 지정
( select etc_str2, etc_str1, count(distinct game_actor_id)as actor_cnt
from basic.users
group by etc_str2, etc_str1 # 임시테이블을 만들 때 활용할 테이블
)
select *
from soso # WITH절에서 지정한 임시테이블명

;

#####################################################################

with 구문 활용 예시2 - 경험치가 가장 많은 캐릭터 정보 조회하기

with dodo as # with 뒤쪽에 임시테이블명 지정
( select
from basic.users
)
select
from( select max(exp)as maxexp
from dodo
)as a
inner join
( select *
from dodo
)as b
on a.maxexp=b.exp
;

#####################################################################

with 구문 활용 예시3 - 다중 with 구문

with gogo as # 첫번째 with 절
( select game_account_id, exp
from basic.users
where level >50
),
hoho as # 두번째 with 절, with 구문은 처음 한번만 작성합니다.
( select distinct game_account_id, pay_amount, approved_at
from basic.payment
where pay_type='CARD'
) # 이 부분에서 with 구문이 종료됩니다.
select case when b.game_account_id is null then '결제x' else '결제o' end as gb
, count(distinct a.game_account_id)as accnt
from gogo as a
left join hoho as b
on a.game_account_id=b.game_account_id
group by case when b.game_account_id is null then '결제x' else '결제o' end
;