이상값은 엄연히 따지면 에러는 아니지만 분석에 방해가 되는 노이즈에 해당되기 때문에 전처리가 필요하다. 이상값 역시 이상값의 여부를 먼저 확인하고 이상값의 원인이 되는 요인을 제거해주는 방식으로 접근한다.
1) 데이터의 분산 확인하기
이상값을 찾는 방법 중 하나는 분산을 확인하고 분산에서 많이 벗어난 값을 찾는 것이다.
① 세션 별 조회수 구하기 → 어떤 세션의 조회수가 극단적으로 많다면 크롤러이거나 내부 IP일 가능성이 있다.
with session_cnt as (
select
session,
count(session) as cnt
from action_log_with_noise
group by session)
select
session,
cnt,
rank() over (order by cnt desc) as rnk,
round(percent_rank() over (order by cnt desc),2) as pct_rnk
from session_cnt
✔ 세션 별 조회수를 구하고 조회수가 많은 순서로 순위(rank)와 순위의 비율(percent_rank)을 구함 ✔pct_rnk에 조건을 부여하여조회수가 극단적으로 많은 세션을 찾아낸다
세션별 분산 확인하기-1
② 페이지 별 조회수 구하기 → 어떤 페이지의 조회수가 극단적으로 적다면 색인 생성 오류가 있을 가능성이 있다.
with url_cnt as (
select
url,
count(url) as cnt
from action_log_with_noise
group by url)
select
url,
cnt,
rank() over (order by cnt asc) as rnk,
round(percent_rank() over (order by cnt asc),2) as pct_rnk
from url_cnt
✔ 페이지 별 조회수를 구하고 조회수가 적은 순서로 순위(rank)와 순위의 비율(percent_rank)을 구함 ✔ pct_rnk에 조건을 부여하여 조회수가 극단적으로 적은 페이지를 찾아낸다
페이지별 분산 확인하기-1
percent_rank() 임의의 행의 백분율 순위를 계산합니다. 백분율 순위를 구하는 공식은 다음과 같습니다. (x - 1) / (the number of rows in the window or partition - 1) 출처:PERCENT_RANK 창 함수
분산으로 이상값을 확인했다면 크롤러인지 사용자인지 판별하고이상값을 처리하는 방법은 다음과 같다.
2) 이상값 처리하기
① 문자열 규칙을 활용하는 방법
select
*
from action_log_with_noise
where not (user_agent like '%bot%'
or user_agent like '%crawler%'
or user_agent like '%spider%'
or user_agent like '%archiver%')
✔ 특정 문자열을 포함하는 user_agent를 제외함 ✔ 규칙이 많아지고 여러 곳에서 사용될수록 조건을 관리하기 번거로움
② 마스터 데이터를 활용하는 방법
-- 마스터 데이터 생성
with mst_bot as (
select '%bot%' as rule
union all select '%crawler%' as rule
union all select '%spider%' as rule
union all select '%archiver%' as rule
)
-- 상관 서브쿼리 사용하여 조건 부여
, except_bot_log as (
select *
from action_log_with_noise as l
where not exists (select 1 from mst_bot as m where l.user_agent like m.rule)
)
select
*
from except_bot_log
✔ 특정 문자열에 대한 조건이 담긴 마스터 데이터 생성(rule 컬럼) ✔ 상관 서브쿼리를 사용하여 mst_bot의 규칙에 해당(user_agent like rule)하지 않은 데이터만 남김
이상값 처리하기
상관 서브쿼리 In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query.
댓글