스프레드 시트와 SQL
스프레드시트의 특징 | SQL 데이터베이스의 특징 |
비교적 작은 데이터 세트 | 비교적 큰 데이터 세트 |
수동으로 데이터 입력 | 데이터베이스 전체에서 테이블 액세스 |
동일한 프로그램에서 그래프 및 시각화 자료 생성 | 다른 소프트웨어에서 추가 분석을 위한 데이터 준비 |
맞춤법 검사 및 기타 유용한 기능 내장 | 빠르고 강력한 기능 |
단독 프로젝트 작업 시 가장 유용 | 공동작업 및 모든 사용자가 실행한 쿼리 추적에 적합 |
여러 데이터베이스 프로그램과 함께 적용 및 사용 가능 | |
데이터 정리를 위한 강력한 도구 제공 | |
팀 전체의 변경사항 추적 가능 | |
데이터베이스 프로그램과의 상호작용 가능 | |
서로 다른 데이터베이스 소스에서 정보 가져오기 가능 |
공통으로 사용할 수 있는 기능
•
수식 사용
•
연산 수행
•
데이터 결합
표준 SQL
•
표준 SQL을 다른 버전에 맞추려면 약간의 구문 변경이 필요합니다.
•
표준 SQL은 대부분의 데이터베이스에서 작동합니다.
고급 함수
스프레드시트
SEARCH
•
텍스트 내에서 특정 문자열이 처음으로 나타나는 위치를 찾는 데 사용됩니다.
•
데이터를 직접 반환하지 않습니다.
COUNTIF
•
지정된 조건에 맞는 셀의 개수를 계산합니다.
VLOOKUP
•
지정된 값과 일치하는 행을 찾아 해당 행의 다른 열에서 데이터를 반환 합니다.
•
특정 값을 기준으로 수직으로 데이터를 검색할 때 매우 유용 합니다.
SORT
•
역으로 정렬하고 싶다면 false 조건을 걸어야 합니다.
◦
SQL 의 경우 DESC 입니다.
FIND()
•
문자열에 포함된 특정 문자를 찾을 수 있습니다.
SQL
CAST()
CAST(expression AS data_type)
SQL
복사
•
하나의 데이터 유형을 다른 데이터 유형으로 변환할 때 사용됩니다.
•
데이터를 비교, 계산 또는 출력 형식을 지정할 때 유용합니다.
•
expression 은 변환할 데이터를 의미하고, data_type 은 목표 데이터 유형을 의미합니다.
•
예를 들어, 문자열을 정수로 변환하거나 날짜 형식을 변경할 때 사용할 수 있습니다.
CONCAT()
CONCAT(string1, string2, ..., stringN)
SQL
복사
•
두 개 이상의 문자열을 하나로 결합할 때 사용됩니다.
COALESCE()
COALESCE(expression1, expression2, ..., expressionN)
SQL
복사
•
인자 리스트 중에서 첫 번째 널이 아닌 값을 반환합니다.
•
데이터에 널 값이 포함될 경우 기본값을 지정하는 데 유용합니다.
•
expression1, expression2 등은 검사할 값을 의미합니다. COALESCE 함수는 이 값들 중 첫 번째 널이 아닌 값을 반환합니다. 모든 값이 널인 경우, 결과도 널이 됩니다.
•
예시 1
예를 들어, 직원 테이블(employees)에 phone_number 컬럼이 있고, 몇몇 직원은 전화번호를 등록하지 않아 해당 필드가 널일 수 있다고 가정해 보겠습니다. 여기서 COALESCE 를 사용하여 전화번호가 널인 경우 기본 문자열을 표시할 수 있습니다.
SELECT name, COALESCE(phone_number, 'No Phone Number') AS phone
FROM employees;
SQL
복사
이 쿼리는 각 직원의 이름과 전화번호를 반환합니다. 만약 직원의 phone_number 가 널이라면, 'No Phone Number' 라는 문자열을 대신 반환합니다.
•
예시 2
직원에게 사무실 전화번호(office_phone), 휴대폰 번호(mobile_phone), 홈 전화번호(home_phone)가 있다고 가정하고, 연락처 정보 중 하나만 사용하려 합니다. 이 경우, COALESCE 를 사용하여 가능한 첫 번째 전화번호를 선택할 수 있습니다.
SELECT name,
COALESCE(mobile_phone, office_phone, home_phone, 'No Contact Info') AS contact_info
FROM employees;
SQL
복사
이 쿼리는 mobile_phone, office_phone, home_phone 컬럼을 순서대로 검사하여 첫 번째 널이 아닌 값을 contact_info 로 반환합니다. 모든 전화번호 컬럼이 널인 경우, 'No Contact Info' 라는 기본 문자열을 반환합니다.
데이터 정리의 최종 단계
•
검증의 목표는 데이터 정리 작업이 제대로 됐는지 결과는 믿을 수 있는지 확인하는 것입니다
•
검증의 첫 단계는 정리되지 않은 원본 데이터 세트를 정리된 현재의 데이터 세트와 비교하는 것입니다.
사용되는 함수와 도구
스프레드 시트
•
TRIM
•
중복 삭제
•
피벗 테이블
◦
데이터 베이스에 저장된 정보를 정렬하거나 재구성, 그룹화 하고 계수, 총계, 평균 등을 계산합니다.
•
Edit → Find and replace
•
COUNTA
◦
지정된 범위에 있는 숫자 값의 개수를 셉니다.
•
COUNT
SQL
•
CASE … WHEN … THEN
•
DISTINCT
History 보기
스프레드 시트
Google Sheets | 1. 셀을 마우스 오른쪽 버튼으로 클릭하여 Show edit history를 선택합니다.
2. 기록에서 왼쪽 화살표 < 또는 오른쪽 화살표 >를 클릭하여 원하는 만큼 앞뒤로 이동합니다. |
Microsoft Excel | 1. 스프레드시트에 Track Changes가 활성화된 경우 Review를 클릭합니다.
2. Track Changes에서 Accept/Reject Changes 옵션을 클릭하여 변경사항을 수락 또는 거부합니다. |
•
시트의 버전 기록은 개별 셀부터 전체 워크시트까지 모든 변경사항을 실시간으로 추적하고 변경한 사용자를 알려줍니다.
•
다른 사람도 시트의 버전 기록을 볼 수 있게 하려면 권한을 할당해야 합니다
SQL
•
SQL에서 데이터를 정리하면서 코멘트를 남깁니다. 나중에 이를 참고하여 변경 로그를 구성할 수 있습니다.
•
Query History 에서 이전 쿼리 버전으로 되돌리거나 이전 버전을 열어서 변경사항을 볼 수 있습니다. (bigQuery)
•
각 쿼리 오른쪽에 있는 아이콘을 클릭하면 쿼리를 Query Editor로 가져올 수 있습니다. (bigQuery)