(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 데이터 기초 주제별 데이터셋 다운로드 사이트 총정리!
주제별 데이터셋 다운로드 사이트 총정리!
공공데이터 공공데이터 관련해서 각 부처가 별도의 사이트를 운영하고 있는 경우가 많다.
공공데이터 포털 : 공공데이터를 분야별로 종합해서 모아놓은 곳서울 열린데이터 광장 : 서울시가 운영하는 공공데이터포털 경기 데이터드림 : 경기도가 운영하는 공공데이터포털 한국관광 데이터랩 : 한국관광공사에서 운영하고 있는 데이터셋 관련 사이트.관광방문객수 데이터 제공 광역지자체 관광지출액 데이터 제공 관광수지, 기타 관광관련 데이터 제공 우리지역 관광상황판, 관광이슈분석 등 간단한 분석도 제공 국토교통부 데이터 통합 채널 국토교통 해커톤 수상작, 국민 참여 분석 프로젝트 소개 주택관련 데이터 : 공동주택 공시가격 데이터 교통물류 관련 데이터 : 자동차 등록대수 데이터 항공 안전현황 관련 데이터 국가 공간정보 포털 지적도 관련 데이터 제공 지형도 관련 데이터 제공 도로, 국토계획 관련 데이터 제공 국가 대중교통 정보센터 네이버지도(대중교통) 경로검색 데이터 제공 카카오맵(대중교통) 경로검색 데이터 제공 국가 물류 통합정보센터 내륙화물 통계 데이터 제공 항공화물 통계 데이터 제공 운송수단 통계 데이터 제공 생활물류 통계 데이터 제공 데이터 거래소 데이터 거래소는 데이터를 사거나 팔 수 있는 플랫폼이다.
11 May 2021 in 가이던스 on Python OR 과 isin()을 이용한 인덱싱
(python) 칼럼의 값에 따라 행 뽑기 (열과 행 혼합 인덱싱)
1. 문제 발생
# 예제 데이터프레임
df_clean = pd . DataFrame ({ 'country' : [ 'IT' , 'JP' , 'KR' , 'FR' ]
, 'income' :[ 100 , 200 , 300 , 400 ]})
2. 문제 해결
df_clean [( df_clean [ 'country' ] == "IT" ) | ( df_clean [ 'country' ] == "JP" )]
또다른 방법은 isin()을 이용하는 것이다.
value_list = [ "IT" , "JP" ]
df_clean [ df_clean [ 'country' ]. isin ( value_list )]
3. 배운 점 및 피드백 어러 값을 가지고 있는 리스트가 있을 때, isin( )을 활용하면 효율적인 코딩이 가능하다. 24 Apr 2021 in 가이던스 on SQL (SQL) 데이터 그룹핑하기
(SQL) 데이터 그룹핑
우리는 그룹핑을 통해 데이터를 요약할 수 있다.
1) 그룹핑하기 : GROUP BY
✋🏾 <손에 잡히는 10분 SQL _ 인사이트> 교재를 참고해 작성한 포스팅입니다. 샘플데이터 다운로드 링크
1. 그룹핑하기 : GROUP BY 그룹핑은 다음과 같은 상황에서 주로 쓰인다.
레코드가 종류별로 몇개가 있는지 세고 싶을 때. 레코드의 총합을 종류별로 알고 싶을 때. 레코드를 종류별로 집계하고, 조건에 따라 필터링하고 싶을 때. SELECT 문에서 GROUP BY 절을 사용하여 그룹을 생성할 수 있다.
SELECT vend_id , COUNT ( * ) AS num_prods
FROM Products
GROUP BY vend_id ;
위의 구문은 GROUP BY 절을 사용한 예시이다.
vend_id별로 그룹핑하고,
2. GROUP BY 절의 주요 규칙 GROUP BY절의 주요 규칙은 다음과 같다.
GROUP BY 절은 자동으로 각 그룹에 대해 계산하기 때문에, GROUP BY 절은 원하는 만큼의 열을 써서, GROUP BY 절에 중첩된 그룹이 있다면,
GROUP BY 절에 있는 열은 가져오는 열이거나,
SELECT 절에서 수식을 사용한다면,
그룹핑하는 열의 행에 NULL 값이 있다면, GROUP BY 절은 WHERE 절 뒤에,ORDER BY절은 항상 구문 마지막에 사용하자 GROUP BY절은 데이터 정렬이 안될 때도 많다. 3. 그룹 필터링 : HAVING SQL은 데이터를 그룹핑하는 것 뿐만 아니라,
대표적인 예로 다음과 같은 상황이 있다.
최소 3번 이상 주문한 고객 리스트. 총 주문금액이 30000원 이상인 고객 리스트 그룹 필터링은 HAVING 절을 이용한다.
HAVING절의 문법은 WHERE절의 문법과 동일하다.
따라서, HAVING 절은 연산자나 연산키워드를 포함하여 사용하면 된다.
연산자 설명 = 같다 <> 같지 않다. != 같지 않다. < ~ 보다 작다 <= ~ 이하 이다. !< ~ 보다 작지 않다. > ~ 보다 크다 >= ~이상 이다. !> ~ 보다 크지 않다. BETWEEN 두 개의 특정한 값 사이 IS NULL 값이 NULL 이다.
GROUP BY와 HAVING 절을 이용하여
SELECT cust_id , COUNT ( * ) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT ( * ) >= 2 ;
4. HAVING vs WHERE
앞서 살펴본 것처럼,
WHERE절과 HAVING절을 동시에 사용하여,
예를 들어
지난 1년동안 두 번 이상 주문한 적이 있는 고객만을 뽑아내고 싶다고 하자.
이 경우, WHERE절로 지난 1년 동안 주문했던 고객을 필터링하고,
그 다음 , HAVING 절을 이용하여 결과에서 두 번 이상 주문한 고객을 필터링하면 된다.
구문은 다음과 같다.
SELECT vend_id , COUNT ( * ) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT ( * ) >= 2 ;
대다수 DBMS에서 GROUP BY가 명시되지 않는다면,
HAVING절과 WHERE 절은 똑같이 처리한다.
하지만 디버깅과 업무효율화를 위해,
행 필터링 : WHERE 그룹 필터링 : HAVING
(python) json 파일을 데이터프레임으로 만들기
1. JSON 이란? 1-1 Json 의 정의
json은 JavaScript Object Notation의 약자로써,
약자에서 알 수 있듯이, json은 자바스크립트에서 파생되었다.
1-2 . Json의 특징 ( 장점 )
특정 프로그래밍 언어, 플랫폼에 종속적이지 않다.따라서 다양한 프로그래밍 언어에서 사용 가능하다. 서로 다른 시스템과 객체를 교환할 때 유용하다. 텍스트 형식으로 되어 있어, 사람과 기계 모두 파일을 읽고 쓰기 편하다.
2. Json을 판다스 데이터프레임으로 불러오기.
import pandas as pd # pandas 모듈 로드
import json # json 모듈 로드
우선 pandas와 json 모듈을 임포트한다.
json_file_path = "./NY.GDP.MKTP.CD.json" # 파일로드 (파일명 : NY.GDP.MKTP.CD.json )
with open ( json_file_path , 'r' ) as j :
contents = json . loads ( j . read ()) # open : r - 읽기모드, w-쓰기모드, a-추가모드
참고)
Expecting value : line 1 column 1 (char 0) 에러 발생이유
1) 인코딩 에러
2) 빈 문자열이 삽입되어 있을 경우.
contents 객체에 json 데이터가 저장되었다.
contents 객체의 구조는 위 그림과 같다.
데이터가 딕셔너리 형태로 저장되어 있다.
key값을 칼럼으로, value를 레코드로 사용할 것이다.
이를 위해 , 3단계의 과정을 거치면 된다.
key와 같은 이름을 가진 빈 리스트를 만들기. for문과 인덱싱을 이용하여 각 리스트에 value 집어넣기. pd.DataFrame( {칼럼명 : 리스트} ) 이용. date = []
value = []
unit = []
obs_status = []
decimal = []
for i in range ( len ( contents [ 1 ])):
date . append ( contents [ 1 ][ i ][ "date" ])
value . append ( contents [ 1 ][ i ][ "value" ])
unit . append ( contents [ 1 ][ i ][ "unit" ])
obs_status . append ( contents [ 1 ][ i ][ "obs_status" ])
decimal . append ( contents [ 1 ][ i ][ "decimal" ])
USA_GDP = pd . DataFrame ({ "date" : date ,
"value" : value ,
"unit" : unit ,
"obs_status" : obs_status ,
"decimal" : decimal })
정상적으로 데이터프레임이 출력되었다.
