✋🏾 PostgreSQL을 이용하여 쓰인 포스팅입니다.
1. 윈도함수란?
윈도함수란 테이블 내부에 ‘윈도’라는 범위 를 정의하고 ,
윈도함수를 통해 데이터를 집약 하거나 그룹 내부의 순서를 배열 할 수 있다.
윈도 함수를 사용하기 위해서는 윈도 함수 내부에
OVER 구문 내부에 ORDER BY 구문을 사용하여 윈도 내부에 있는 데이터의 순서를 정의한다.
일반적인 윈도 함수 , 특히 OVER 구문은 다음과 같다.
SELECT < 칼럼명 >
, < 함수 > () OVER ( PARTITION BY < 칼럼명 > ORDER BY < 칼럼명 > DESC ROWS BETWEEN "start" AND "end" )
FROM < 테이블명 >
2. 윈도함수 상세 설명
윈도우 함수 사용시 주목할 부분이 네 군데가 있다.
SELECT < 칼럼명 >
, < 함수 > () OVER ( PARTITION BY < 칼럼명 > ORDER BY < 칼럼명 > DESC ROWS BETWEEN "start" AND "end" )
FROM < 테이블명 >
**1) <함수>**
SELECT 다음에 <함수> 부분을 살펴보자.
ROW_NUMBER(): 점수 순서로 유일한 순위를 붙임
RANK() : 같은 순위를 허용해서 순위를 붙임
DENSE_RANK() : 같은 순위가 있을 때 같은 순위 다음에 있는 순위를 건너 띄고 순위를 붙임
**2) PARTITION BY <칼럼명>**
두번째는 OVER( ) 다음에 바로 나오는 PARTITION BY <칼럼명> 이다.
**3) ORDER BY <칼럼명>**
세번째는 ORDER BY <칼럼명>이다. 윈도우를 세팅하는 과정이다.
4) ROWS BETWEEN “start” AND “end”
마지막으로, OVER( )구문 안에 ROWS BETWEEN “start” AND “end”를 보자.
CURRENT ROW : 현재의 행 n PRECEDING : n행 앞 n FOLLOWING : n행 뒤 UNBOUNDED PRECEDING : 이전 행 전부 UNBOUNDED FOLLOWING : 이후 행 전부
3. 윈도함수 예시
두가지 예시를 통해 윈도함수 대해 더 알아보자.
나는 윈도함수를 사용해 카테고리들의 순위 를 계산하고 싶다.
이럴 경우, 윈도함수를 사용하면 추출할 수 있다.
SELECT
category
, product_id
, score
, ROW_NUMBER () over ( PARTITION BY category ORDER BY score DESC ) AS row
, RANK () OVER ( PARTITION BY category ORDER BY score DESC ) AS rank
, DENSE_RANK () OVER ( PARTITION BY category ORDER BY score DESC ) AS dense_rank
FROM popular_products
ORDER BY category , row ;
결과가 다음과 같이 나온다.
두번째 예시이다.
윈도 함수를 이용해 카테고리별 주요 수치를 추출하고 싶다.
SELECT
product_id
, score
, row_number () over ( ORDER BY score DESC ) AS row -- 점수 순서로 유일한 순위를 붙임
, SUM ( score ) OVER ( ORDER BY SCORE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cum_score -- 순위 상위부터 누계 점수 계산하기
, AVG ( score ) OVER ( ORDER BY score DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS local_avg -- 현재 행과 앞 뒤의 행이 가진 값을 기반으로 평균 점수 계산하기
, FIRST_VALUE ( product_id ) OVER ( ORDER BY SCORE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS first_value -- 순위가 높은 상품 ID 추출하기
, LAST_VALUE ( product_id ) OVER ( ORDER BY SCORE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_value -- 순위가 낮은 상품 ID 추출하기
FROM popular_products
;
결과가 다음과 같이 나온다.
✋🏾 PostgreSQL을 이용하여 쓰인 포스팅입니다. 샘플데이터 다운로드 링크
1. CASE문이란?
CASE
WHEN < 조건식 > THEN < 조건을 만족할 때의 값 >
END
WHEN 뒤에 조건식을 만족하면 데이터를 THEN 다음에 나오는 값으로 바꿔준다.
2. CASE문의 실제 사용 예시
다음은 Vendors테이블이다.
나는 vend_country에 기록된 국가명을 각 국가가 속한 대륙명으로 바꾸고 싶다.
이럴 경우, CASE문을 사용하면 간단히 데이터를 원하는대로 바꿀 수 있다.
SELECT vend_id ,
CASE
WHEN vend_country = 'USA' THEN 'America'
WHEN vend_country = 'England' Then 'Europe'
WHEN vend_country = 'France' Then 'Europe'
END AS vend_continent -- 칼럼명을 vend_continent로 변경
FROM tysql . vendors ;
결과가 다음과 같이 나온다.
SQL내에서 데이터 전처리를 할 때
01 Aug 2021 in 가이던스 on 데이터 기초
AB Test란 무엇일까?
AB Test의 정의 AB Test의 대상이 되는 요소 AB Test 프로세스
1. AB Test의 정의
AB Test란 온라인으로 접속한 사용자를 임의로 여러개의 그룹으로 분리하고
쉽게말하자면, A안과 B안을 만들어 비교하고 더 나은 것을 찾는 방법이라 할 수 있다.
2. AB Test의 대상이 되는 요소
AB Test의 대상이 되는 요소는 대표적으로 Headline Text와 CTA Text가 있다.
Headline Text페이지 방문자가 처음으로 읽게 될 문구 CTA Text ( Call-to-action )페이지 방문자에게 목표 행동을 유도할 문구 ex) 목표행동 : 회원가입, 구매, 사이트 방문 3. AB Test 프로세스
AB Test는 크게 6가지 프로세스를 통해 수행된다.
데이터 수집전환율이 낮은 트래픽 구역 찾고 , 데이터 수집 목표 설정ex) 버튼 클릭, 구매링크 클릭, 회원 가입 가설 수립현재(기존) 버전보다 나을 것 같은 아이디어를 기준으로 가설 수립 비교 버전 만들기AB Test 소프트웨어 이용하여 비교 버전 만들기 ex) CTA 버튼 색깔 변경, 문구 변경 실험 수행페이지 방문자들에게 랜덤으로 페이지 배정 (A or B) 하여 테스트 결과 분석두 버전의 퍼포먼스 비교 이 분석 결과가 통계적으로 유의미한지 일반적으로 P-value, AA Test를 통해 통계적 유의미성 확인함 (SQL) 예제를 통해 서브쿼리를 어떻게 사용하는지 알아보자
✋🏾 <손에 잡히는 10분 SQL _ 인사이트> 교재를 참고해 작성한 포스팅입니다. 샘플데이터 다운로드 링크
1. 서브쿼리란? 서브쿼리는 용어 그대로 쿼리 안에 쿼리를 넣는 것이다.
다음 예제를 통해 더 자세히 알아보자.
이 DB에서 나는 RGAN01 이라는 제품을 구매한 고객의 목록을 가져오고 싶다.
1) RGAN01을 주문한 주문 번호를 가져온다
이 세 단계는 별도의 쿼리로 수행가능하다.
SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01' ; -- 1
SELECT cust_id
FROM Orders
WHERE order_num IN ( 200007 , 20008 ) --2
SELECT cust_name , cust_contact
FROM Customers
WHERE cust_id IN ( 1000000004 , 1000000005 ); --3
서브쿼리를 이용하면 이 세 단계를 하나의 쿼리로 합쳐서 진행할 수 있다.
SELECT cust_name , cust_contact
FROM Customers
WHERE cust_id IN ( SELECT cust_id
FROM ORders
WHERE order_num IN ( SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01' ))
이처럼 서브쿼리를 사용하면 강력하고 유연한 SQL문을 작성할 수 있다.
2. 서브쿼리를 사용할 때 주의할 점 서브쿼리를 사용할 때 주의할 점은 다음과 같다.
서브쿼리가 항상 효율적인 것은 아니다.사용할 수 있는 서브쿼리의 수에는 제한이 없다. 너무 많은 쿼리를 사용하면 성능이 저하될 수 있다. 서브쿼리는 하나의 열만 검색할 수 있다. 서브쿼리는 항상 안에 있는 쿼리를 먼저 처리하고 , 이후 바깥쪽에 있는 쿼리를 처리한다. 3. 추가 문제 (서브 쿼리 이용) 1) 서브쿼리를 사용하여 10 또는 그 이상의 가격으로 제품을 구매한 고객 목록을 반환하라.
SELECT cust_id
FROM Orders
WHERE order_num IN ( SELECT order_num
FROM OrderItems
WHERE item_price >= 10 );
2) Products 테이블에서 모든 제품명(prod_name)과 quant_sold를 가져오는 SQL 문을 작성하라.
SELECT prod_name ,
( SELECT SUM ( quantity )
FROM OrderItems
WHERE Products . prod_id = OrderItems . prod_id )
FROM Products ;
28 May 2021 in 가이던스 on 데이터 기초 주제별 데이터셋 다운로드 사이트 총정리!
주제별 데이터셋 다운로드 사이트 총정리!
공공데이터 공공데이터 관련해서 각 부처가 별도의 사이트를 운영하고 있는 경우가 많다.
공공데이터 포털 : 공공데이터를 분야별로 종합해서 모아놓은 곳서울 열린데이터 광장 : 서울시가 운영하는 공공데이터포털 경기 데이터드림 : 경기도가 운영하는 공공데이터포털 한국관광 데이터랩 : 한국관광공사에서 운영하고 있는 데이터셋 관련 사이트.관광방문객수 데이터 제공 광역지자체 관광지출액 데이터 제공 관광수지, 기타 관광관련 데이터 제공 우리지역 관광상황판, 관광이슈분석 등 간단한 분석도 제공 국토교통부 데이터 통합 채널 국토교통 해커톤 수상작, 국민 참여 분석 프로젝트 소개 주택관련 데이터 : 공동주택 공시가격 데이터 교통물류 관련 데이터 : 자동차 등록대수 데이터 항공 안전현황 관련 데이터 국가 공간정보 포털 지적도 관련 데이터 제공 지형도 관련 데이터 제공 도로, 국토계획 관련 데이터 제공 국가 대중교통 정보센터 네이버지도(대중교통) 경로검색 데이터 제공 카카오맵(대중교통) 경로검색 데이터 제공 국가 물류 통합정보센터 내륙화물 통계 데이터 제공 항공화물 통계 데이터 제공 운송수단 통계 데이터 제공 생활물류 통계 데이터 제공 데이터 거래소 데이터 거래소는 데이터를 사거나 팔 수 있는 플랫폼이다.
