in 가이던스 on SQL (SQL) 데이터 그룹핑하기
우리는 그룹핑을 통해 데이터를 요약할 수 있다.
SQL은 GROUP BY절을 통해 데이터 그룹핑을 지원한다.
HAVING 절을 이용하여 그룹핑에 조건도 추가할 수 있다.
데이터 파악에 큰 도움을 주는 데이터 그룹핑에 대해 살펴보자.
1) 그룹핑하기 : GROUP BY
2) GROUP BY절의 주요 규칙
3) 그룹 필터링 : HAVING
4) HAVING vs WHERE
1. 그룹핑하기
그룹핑은 다음과 같은 상황에서 주로 쓰인다.
- 레코드가 종류별로 몇개가 있는지 세고 싶을 때.
- 레코드의 총합을 종류별로 알고 싶을 때.
- 레코드를 종류별로 집계하고, 조건에 따라 필터링하고 싶을 때.
SELECT 문에서 GROUP BY 절을 사용하여 그룹을 생성할 수 있다.
SELECT
vend_id
, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
;
위의 구문은 GROUP BY 절을 사용한 예시이다.
vend_id별로 그룹핑하고, 그룹별로 레코드의 개수를 출력했다.
(그룹 계산 함수로 COUNT 함수 사용)
2. GROUP BY 절의 주요 규칙
GROUP BY절의 주요 규칙은 다음과 같다.
GROUP BY 절은 원하는 만큼의 열을 써서, 중첩 그룹을 만들 수 있다.
GROUP BY 절은 WHERE 절 뒤에, 그리고 ORDER BY 절 앞에 와야 한다.
- ORDER BY절은 항상 구문 마지막에 사용하자
- GROUP BY절은 데이터 정렬이 안될 때도 많다.
따라서 정렬을 원한다면 ORDER BY를 이용하자.
3. 그룹 필터링 : HAVING
SQL은 데이터를 그룹핑하는 것 뿐만 아니라,
그룹 필터링도 가능하게 해준다.
대표적인 예로 다음과 같은 상황이 있다.
- 최소 3번 이상 주문한 고객 리스트.
- 총 주문금액이 30000원 이상인 고객 리스트
그룹 필터링은 HAVING 절을 이용한다.
HAVING절의 문법은 WHERE절의 문법과 동일하다.
따라서, HAVING 절은 연산자나 연산키워드를 포함하여 사용하면 된다.
| 연산자 | 설명 |
|---|
| = | 같다 |
| <> | 같지 않다. |
| != | 같지 않다. |
| < | ~ 보다 작다 |
| <= | ~ 이하 이다. |
| !< | ~ 보다 작지 않다. |
| > | ~ 보다 크다 |
| >= | ~이상 이다. |
| !> | ~ 보다 크지 않다. |
| BETWEEN | 두 개의 특정한 값 사이 |
| IS NULL | 값이 NULL 이다. |
GROUP BY와 HAVING 절을 이용하여
최소 3번 이상 주문한 고객 리스트를 뽑아보는 구문은 아래와 같다.
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
4. HAVING vs WHERE
앞서 살펴본 것처럼, >HAVING절과 WHERE절은 데이터를 필터링할 때 사용한다.
그렇다면, 이 둘의 차이는 무엇일까?
두 절의 차이는 WHERE절은 데이터가 그룹핑되기 전에 필터링하고,
HAVING 절은 데이터가 그룹핑된 후에 필터링한다는 점이다.
WHERE 절에서 필터링되어 제거된 행은 그룹에 포함되지 않는다.
WHERE절과 HAVING절을 동시에 사용하여 더 세부적인 필터링을 할 수 있다.
예를 들어, 지난 1년동안 두 번 이상 주문한 적이 있는 고객만을 뽑아내고 싶다고 하자.
이 경우, WHERE절로 지난 1년 동안 주문했던 고객을 필터링하고,
그 다음 , HAVING 절을 이용하여 결과에서 두 번 이상 주문한 고객을 필터링하면 된다.
구문은 다음과 같다.
SELECT
vend_id
, COUNT(*) AS num_prods
FROM Products
WHERE 1=1
AND prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
대다수 DBMS에서 GROUP BY가 명시되지 않는다면,
HAVING절과 WHERE 절은 똑같이 처리한다.
하지만 디버깅과 업무효율화를 위해,
이 둘을 꼭 구별하여 사용하자.
- GROUP BY 집계 전 필터링 : WHERE
- GROUP BY 집계 후 필터링 : HAVING
(python) json 파일을 데이터프레임으로 만들기
1. Json이란?
1-1 Json 의 정의
json은 JavaScript Object Notation의 약자로써 자료를 주고 받을 때 주로 사용하는 파일형식이다.
약자에서 알 수 있듯이, json은 자바스크립트에서 파생되었다.
따라서 json은 자바스크립트 구문형식을 따른다.
1-2 . Json의 특징 ( 장점 )
- 특정 프로그래밍 언어, 플랫폼에 종속적이지 않다.
- 따라서 다양한 프로그래밍 언어에서 사용 가능하다.
- 서로 다른 시스템과 객체를 교환할 때 유용하다.
- 텍스트 형식으로 되어 있어, 사람과 기계 모두 파일을 읽고 쓰기 편하다.
2. Json을 판다스 데이터프레임으로 불러오기.
미국 GDP 정보가 저장된 json파일이 로컬 디렉토리에 있다.
이 파일을 판다스를 통해 데이터프레임으로 변환해보자.
(크롤링을 통해 웹에 저장된 json파일을 불러오는 경우 , urlopen 모듈을 이용하면 된다.)
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-추가모드
위와 같이 json 파일을 로드한다.
json.loads( )로 파일을 불러올 경우, 아래 그림과 같이 오류가 발생할 수도 있다.
따라서 위 구문으로 json 파일을 로딩하자.
참고)
Expecting value : line 1 column 1 (char 0) 에러 발생이유
1) 인코딩 에러
2) 빈 문자열이 삽입되어 있을 경우.
contents 객체에 json 데이터가 저장되었다.
contents 객체의 구조는 위 그림과 같다.
2번 리스트에 저장된 정보를 데이터프레임으로 만들것이다.
우선 2번 리스트에 저장된 데이터를 확인해보자.
데이터가 딕셔너리 형태로 저장되어 있다.
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})
정상적으로 데이터프레임이 출력되었다.
in 가이던스 on SQL (SQL) 칼럼 연결하기 , 공백제거하기.
(SQL) 칼럼 연결하기 (CONCAT ), 공백 제거하기 ( TRIM, RTRIM, LTRIM )
회사명과 회사 위치를 함께 출력하고 싶지만, 두 정보가 서로 다른 테이블 열에 저장되어 있다.
시,도, 우편번호는 서로 다른 열에 저장되어 있지만, 배송지 주소를 인쇄하는 응용 프로그램에서는 하나의 필드로 가져와야 한다.
위 두 상황을 해결하기 위해서는 칼럼을 서로 연결해줘야 한다.
칼럼을 연결할 때 사용하는 함수인 Concat, || 에 대해 알아보자.
추가로, 칼럼의 공백을 제거해주는 함수인 TRIM에 대해서도 살펴보자.
✋🏾 <손에 잡히는 10분 SQL _ 인사이트> 교재를 참고해 작성한 포스팅입니다.
✋🏾 샘플데이터 다운로드 링크
1. 칼럼 연결하기 : CONCAT, ||
칼럼을 연결하는 문법은 사용하는 DBMS에 따라 다르다.
MYSQL과 MariaDB의 경우, CONCAT 함수를 이용하여 칼럼을 연결해준다.
SELECT CONCAT(vend_name,'(' , vend_country,')') AS vend_total
FROM Vendors
CONTCAT함수는 괄호안에, 연결하고자 하는 칼럼, 문자를 넣어주면 된다.
위 코드의 경우 , vend_name 칼럼과 vend_country 칼럼을 연결해준다.
추가로, 소괄호를 함수안에 삽입해 vend_country를 괄호로 감싼 것을 알 수 있다.
위 표와 같이 vend_name, vend_country과 연결되어 출력된다.
PostgreSql은 칼럼을 연결하기 위해 || 문법을 사용한다.
SELECT vend_name || '(' || vend_country || ')' AS vend_total
FROM Vendors
연결하고자 하는 대상 사이에 || 를 넣어주면 된다.
( CONCAT 함수보다 더 간단하고 직관적인 느낌이다)
2. 공백제거하기 : TRIM, RTRIM, LTRIM
칼럼을 서로 연결하다 보면 공백문자가 사이에 채워져 있는 경우가 많다.
연결하자하는 레코드 자체에 공백이 포함되어 있어 나타나는 현상이다.
대부분의 DBMS는 열 길이에 맞춰 텍스트를 저장하기 때문에
공백도 하나의 문자로 취급한다.
그러나 문제는 공백은 눈에 잘 띄지 않는다는 점이다.
따라서,공백을 고려하지 않고 데이터를 처리한다면
향후에 데이터 검색이나 계산시 문제가 발생할 수 있다.
다행히도 많은 DBMS가 공백제거 함수를 제공한다.
TRIM ( ) , RTRIM( ) , LTRIM ( ) 함수를 이용하면 레코드의
공백을 제거 할 수 있다.
함수명에서 눈치챈 사람도 있겠지만, 세 함수의 차이는 아래와 같다.
TRIM ( ) : 양쪽에 있는 공백 제거
LTRIM ( ) : 왼쪽에 있는 공백 제거
RTRIM( ) : 오른쪽에 있는 공백 제거
다음 코드는 공백을 제거하여 칼럼을 연결하는 코드이다.
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')')
FROM Vendors
ORDER BY vend_name; -- MYSQL
SELECT RTRIM(vend_name) || '(' || RTRIM(vend_country) || ')'
FROM Vendors
ORDER BY vend_name; -- Postgresql
in 가이던스 on SQL (SQL) 와일드 카드 문자를 이용한 필터링
(SQL) 와일드 카드 문자를 이용한 필터링
와일드카드 문자는 검색 패턴을 만들어
데이터를 필터링할 때 사용한다.
와일드카드 문자를 사용할 수 있다면,
데이터 필터링의 폭이 훨씬 넓어질 것이다.
문자열 데이터 필터링의 핵심인
와일드카드 문자에 대해 알아보자.
✋🏾 <손에 잡히는 10분 SQL _ 인사이트> 교재를 참고해 작성한 포스팅입니다.
✋🏾 샘플데이터 다운 링크 : https://forta.com/books/0135182794/
1. 와일드카드 문자란?
와일드카드 문자란
여러 데이터에서 부분적으로 일치하는 값이 있는지
확인할 때 사용되는 특수문자를 말한다.
앞서 언급했듯이,
와일드카드 문자는 검색패턴을 만들어
데이터를 필터링할 때 사용한다.
2. 와일드카드 문자의 특징.
와일드카드 문자는 다음과 같은 특징이 있다.
- 검색절 (WHERE)절에서 와일드카드 문자를 이용하려면
반드시 LIKE 연산자를 함께 사용해야 한다.
- 와일드카드 문자를 이용한 검색은
텍스트 열(문자열)에서만 사용할 수 있다.
3. 와일드카드 문자의 종류와 사용예시.
1) % 와일드카드
검색할 문자열에서 %는 임의의 수의 문자를 의미한다.
예를 들어, Fish라는 단어로 시작하는 제품을 찾고 싶다면,
다음과 같이 쿼리를 작성하면 된다.
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';
제품명이 Fish로 끝나는 제품을 찾고 싶다면,
Fish앞에 %를 써주면 된다.
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%Fish';
-- 와일드카드는 검색 패턴 내 어디에서나 사용할 수 있고, 여러 개의 와일드카드를 같이 사용할 수도 있다.
와일드카드를 검색 패턴의 중간에 사용하는 경우가
많이는 없지만, 이메일 주소를 검색하는 경우 유용하다.
SELECT cust_email
FROM Customers
WHERE cust_email LIKE 'b%forta.com';
예시코드에서 알수 있듯이,
% 와일드카드는 검색패턴을 만들때, 글자 수 제한이 없어
범용성이 높고, 많이 이용되는 문자다.
또한, %와일드카드를 이용하면
필터링할 때 데이터의 앞 뒤 공백으로 인해 생기는
검색 오류를 방지할 수도 있다.
SELECT prod_id,prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';
-- bean bag 앞이나 뒤에 어떤 문자열(공백)이 와도 관계없다.
2) 언더라인(_) 와일드카드
언더라인 와일드카드는 %와 비슷하게 사용되지만,
%와일드카드가 여러 문자열을 대신할 수 있는 것과는 달리
언더라인 와일드카드는 단 한 개의 문자를 대신한다.
(언더라인 와일드카드를 연달아쓰면 , 그 개수만큼 문자수를 매칭한다)
언더라인 와일드카드는
검색조건으로 글자수를 정해놓고 필터링 할 때 유용하다.
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';
--두자리수 인치인 테디베어만 검색
4. 와일드카드 문자 사용시 주의해야 할 점!
1) NULL은 와일드카드로 매칭이 안됨!
2) 와일드카드를 이용한 검색 패턴은 다른 필터링 조건보다 처리가 오래 걸린다.
–> 와일드카드를 남용하지 말자
3) 와일드카드를 잘못된 곳에 사용한다면, 의도와 다른 데이터가 검색된다.
–> 와일드카드를 조심해서 사용하자
(회고록) 인하대학교 빅데이터 동아리 ‘인빅’
설립과 운영, 그리고 나의 성장
내가 대학생활 동안 가장 열정을 쏟고 도전적으로 몰입했던 일은 인하대학교 최초의 빅데이터 동아리 인빅을 설립하고 운영하여 중견동아리로 성장시킨 것이다.
나는 데이터 분석에 관심이 많았지만 비전공자로서 독학에 어려움을 느꼈었다.
그리고 교내 커뮤니티를 통해 나와 유사한 고민을 안고 있는 사람이 많다는 것을 알게 되었다.
이는 정기적으로 데이터 분석 공부를 하고 정보를 공유할 수 있는 ‘빅데이터 커뮤니티’를 만들자는 생각으로 이어졌다.
그렇게 해서 만든 동아리가 인하대학교 빅데이터 동아리 ‘인빅’이다.
이 회고록을 통해 ‘인빅’ 을 설립하며 느꼈던 보람과 기쁨을 상기시킴과 동시에 어려웠던 점과 배웠던 점을 되새기고자 한다.
이 회고록은 세 파트로 구성되어 있다.
1. 인빅 소개
인하대학교 빅데이터 동아리 인빅은 2021년 2학기 기준 5기까지 운영되었으며,
총 120여 명의 회원이 활동한 교내 최초의 빅데이터 동아리이다.
인빅의 활동은 팀스터디로 진행되며 기초스터디, 시각화단계, 머신러닝단계로 커리큘럼을 나누어 진행한다.
각 단계에 따라 필요한 스터디를 진행하고 정기 세미나에서 실습 내용을 발표를 한다.
이후 학기 말에 진행되는 종강세미나에서 최종 프로젝트를 발표하면 한 기수가 끝이 나게 된다.
기초스터디는 데이터 전처리 능력을 기르기 위해 만들어진 단계이며,
파이썬 기초와 판다스,넘파이를 배우게 된다. 많은 비전공자 회원들이 처음 거치게 되는 단계이다.
시각화스터디는 데이터 시각화 능력을 기르기 위해 만들어진 커리큘럼이며,
태블로를 이용하여 프로젝트를 수행하게 된다.
2주마다 데이터 예제 실습을 진행하고 세미나에서 발표하였다.
특히 왜 이 차트를 사용했는지 공유하고 실습은 어떻게 하였는지를
태블로 창을 띄어서 소개하는 방식으로 진행하였다.
데이터를 활용하기 위한 인사이트를 얻는 것이 최종 목표인 단계다.
머신러닝 단계는 사이킷런, 텐서플로를 이용하여
머신러닝 , 딥러닝을 학습하는 것을 목표로 만들어진 단계이다.
공학적인 내용 , 즉 이론적인 내용까지 건드리는 것이 목표였다.
이와 같은 커리큘럼으로 인빅은 운영되었다.
이를 바탕으로 인빅구성원들로 이루어진 팀이 ‘더 존 빅데이터 경진대회’에서 우수상을 수상하고,
수료한 회원들이 라인, 모두의 연구소, 퍼블릭에이아이 ML 개발자로 취직하는 등 인빅은 외부활동에서도 우수한 성과를 보이고있다.
2.인빅 설립 과정
나는 빅데이터 스터디를 같이 진행하던 네명과 함께 인빅 설립 준비에 들어갔다.
주 3회 이상 회의를 진행하고 지속적으로 연락을 취하는 등 많은 시간과 노력을 쏟았다.
이 설립 준비기간동안 크게 두가지 사항을 중점적으로 고려했다.
- 체계적인 시스템을 갖추자
- 동기부여가 될 수 있는 커리큘럼을 만들자
이를 위해 타 유명 빅데이터 동아리에 컨택을 해 시스템에 대한 조언을 얻는 한편
빅데이터 전공 교수님과 현직자 선배들에게 자문을 구해 필요한 커리큘럼에 대한 정보를 얻을 수 있었다.
정리하면 다음과 같다.
- 시스템 아이디어
- 팀 프로젝트가 효과적
- 고정된 커리큘럼이 있어야 한다.
- 정기 컨퍼런스 시간이 있어야 한다.
- 커리큘럼 아이디어
- 머신러닝 모델링 뿐만 아니라 데이터 시각화 공부도 필요
- 문제 제기 능력을 기를 수 있는 커리큘럼
- 기초 코딩 지식이 없는 회원들을 위한 기초 스터디 단계 필요
이 과정에서 데이터 직군에 근무하시는 현직자 선배들을
수소문하여 컨택하고 도움을 요청했던 것이 특히 기억에 남는다.
무턱대고 메일 주소를 알아내어 도움을 요청했는데 다행히 적극적인 자세를 좋게 봐주셔서 직접 만나뵙고 정보를 얻을 수 있었다.
현업에서는 데이터 툴 활용능력도 중요하지만 어떤 문제를 해결할지 고민하는 능력이 가장 중요하다는 점을 알 수 있었다.
문제 제기 능력은 동아리 커리큘럼의 방향성이 됐을뿐만 아니라 나의 데이터 분석 공부 방향성이 되었다.
이렇게 얻은 정보들을 바탕으로 팀 스터디 기반의 3단계 커리큘럼을 만들 수 있었다.
동아리 시스템 구축을 위한 준비를 이와 같이 거쳤지만 사실 내가 더 신경쓴건 동아리 설립을 같이 한 운영진들과의 소통이었다.
내가 의도한바와 그들이 받아들이는 바가 같은지 혹은 내가 운영진이 주장하는 것을
제대로 이해하고 있는지를 확인하는 과정에 많은 노력을 기울였다.
이를 위해 많은 회의를 진행했고 지속적으로 메신저를 통해 연락을 취했다.
많은 회의를 진행하다보니 운영진들이 피로한게 느껴졌고 스트레스도 많다는 것을 알 수 있었다.
앞으로 동아리를 같이 운영해나가야할 운영진이기 때문에,
그들의 불만사항을 체크하는데도 많은 신경을 썼다.
너무 많은 회의에 지치지 않도록 주3회였던 오프라인 회의를 주 1회 오프라인 주1회 화상으로 대체하고, 회의를 컴팩트하게 진행했다.
또 그들에게 칭찬과 격려를 지속적으로 하고, 우리가 빅데이터 커뮤니티를 만들면 서로 도움을 많이 받을 수 있다는 동기부여를 지속적으로 하였다.<br
이렇게 약 3개월의 준비기간을 거쳐 인빅을 시작할 수 있었다.
3.느낀 점
교내 최초의 빅데이터 동아리 인빅의 설립은 나에게 큰 도전이자 성장할 수 있는 계기였다.
- 빅데이터 전문가가 아닌 내가 동아리를 만들 수 있을까?
- 내가 한 조직을 처음부터 만들 수 있을까?
이 두가지 질문이 동아리 설립준비를 망설이게 하였지만,향후 취업에서 메리트, 빅데이터 전문가로서의 성장, 리더쉽까지 기를 수 있는 좋은 기회이기 때문에 불안감과 걱정을 핑계로 포기하면 후회가 많이 남을 것 같았다.
그리고 결과적으로 동아리 설립을 통해 내가 원하는 바를 다 이룰 수 있었다.
데이터 분석 역량을 기를 수 있었고, 빅데이터에 관심있는 많은 친구들을 알게 되었다. 또 내가 조직을 이끌 수 있는 리더십이 있구나 라는 자신감 또한 얻을 수 있었다.
무엇보다도 후에 대학생활을 돌아볼 때, 인빅을 만들었기 때문에
자신있게 열심히 살았다 라고 말할 수 있을 것 같다.
Pagination
