[Coursera Google] GCC 데이터 애널리틱스 : 데이터 정리 | SQL을 사용한 데이터 정리

 

반응형

 

 

 

 

 

 

 

SQL을 사용하여 데이터 정리

 

SQL 기능 이해

 

SQL이란 무엇인가?

SQL(Structured Query Language, 구조적 쿼리 언어)은 데이터베이스와 상호작용하고 데이터를 처리하는 데 사용되는 언어입니다. 데이터 애널리스트는 SQL을 통해 대량의 데이터 세트를 효율적으로 처리하고, 수조 개의 행을 몇 초 만에 다룰 수 있습니다. 이를 통해 방대한 데이터를 빠르고 정확하게 처리할 수 있는 SQL의 강점이 부각됩니다.

예를 들어, 전 세계 80억 명의 이름이 담긴 데이터 세트를 처리할 때 사람이 이를 모두 읽는 데 101년이 걸리지만, SQL은 이를 단 몇 초 만에 처리할 수 있습니다. 이는 스프레드시트와 같은 도구로는 처리하기 힘든 대규모 데이터를 SQL로 처리하는 이유 중 하나입니다.

 

SQL의 역사

SQL의 역사는 1970년대로 거슬러 올라갑니다.

• 1970년: 에드거 F. 커드(Edgar F. Codd)가 관계형 데이터베이스 이론을 개발했습니다. 이 관계형 데이터베이스는 여러 개의 테이블로 구성된 데이터베이스로, 테이블 간의 연결을 통해 관계를 형성합니다.
• 1970년대 초반: IBM은 System R이라는 관계형 데이터베이스 관리 시스템을 개발하였고, 이를 기반으로 데이터 검색과 조작을 쉽게 하기 위해 새로운 쿼리 언어(SQL)를 개발하기 시작했습니다.
• 1979년: SQL이 최초로 공개되었으며, 이후로 관계형 데이터베이스와의 통신을 위한 표준 언어로 자리 잡았습니다.
• 1986년: SQL은 공식적으로 관계형 데이터베이스 표준 언어로 채택되었습니다. 이로 인해 전 세계적으로 데이터 애널리스트와 개발자들이 동일한 언어로 데이터베이스와 소통할 수 있게 되었습니다.

SQL을 사용하는 이유

SQL이 데이터 애널리스트에게 중요한 이유는 바로 그 강력한 데이터 처리 능력과 표준성에 있습니다. SQL은 수십 년 동안 표준화된 언어로 자리 잡았으며, 다양한 데이터베이스 관리 시스템에서 일관된 방식으로 데이터를 처리할 수 있습니다. 이를 통해 데이터 애널리스트는 대규모 데이터 세트에서 효율적으로 데이터를 추출하고 분석할 수 있습니다.

 

개인 경험을 통한 SQL 학습

SQL을 학습하는 것은 많은 애널리스트에게 필수적인 과정입니다. 실제로 한 애널리스트는 SQL에 대한 사전 지식 없이도 입사 과정에서 요구된 SQL 테스트를 준비하며, 온라인 리소스를 활용해 SQL을 독학했습니다. 이는 SQL이 처음 접하더라도 독학으로도 충분히 습득할 수 있는 언어임을 보여줍니다.

 

SQL을 학습하기 위해서는 다양한 온라인 리소스를 활용할 수 있으며, 이를 통해 여러분도 SQL을 빠르게 익힐 수 있습니다.

 

 

---

스프레드시트와 SQL의 장단점

스프레드시트의 특징SQL 데이터베이스의 특징  

비교적 작은 데이터 세트	비교적 큰 데이터 세트
수동으로 데이터 입력	데이터베이스 전체에서 테이블 액세스
동일한 프로그램에서 그래프 및 시각화 자료 생성	다른 소프트웨어에서 추가 분석을 위한 데이터 준비
맞춤법 검사 및 기타 유용한 기능 내장	빠르고 강력한 기능
단독 프로젝트 작업 시 가장 유용	공동작업 및 모든 사용자가 실행한 쿼리 추적에 적합

기본적으로 데이터가 있는 위치에 따라 사용할 도구가 결정됩니다. 스프레드시트에 있는 데이터로 작업하고 있다면 스프레드시트에서 분석하게 될 가능성이 크고, 데이터베이스에 저장된 데이터로 작업하는 경우에는 SQL이 최고의 분석 도구가 될 것입니다. 모든 비즈니스 문제를 각각 가장 적합한 도구로 해결할 수 있도록 지금부터 SQL에 대해 더 자세히 배워보겠습니다. 

 

 

---

스프레드시트와 SQL의 차이

 

1. 스프레드시트와 SQL의 공통점

• 용도: 두 도구 모두 데이터의 정리, 연산, 수식 사용, 데이터 결합 등의 작업에 사용됩니다.
• 예시: 스프레드시트에서 COUNTIF 함수로 조건에 맞는 데이터 수를 세는 것과 SQL에서 COUNT와 WHERE 쿼리를 사용하여 특정 조건에 맞는 행의 개수를 세는 작업은 유사합니다.
• 데이터 처리: 스프레드시트에서 배운 데이터 처리 기술을 SQL에서도 활용할 수 있으며, SQL은 스프레드시트의 기능을 보다 복잡하게 수행할 수 있습니다.

2. 스프레드시트와 SQL의 차이점

• 형태 및 사용 방식:
  • 스프레드시트: Excel, Google Sheets와 같은 프로그램에서 사용되며, 데이터의 저장과 처리가 주로 로컬에서 이루어집니다.
  • SQL: Oracle, MySQL, Microsoft SQL Server와 같은 데이터베이스 시스템에서 사용되며, 데이터베이스와 상호작용하기 위한 쿼리 언어입니다.
• 데이터 처리 용량:
  • 스프레드시트: 상대적으로 소규모 데이터 처리에 적합하며, 대규모 데이터 세트를 처리하는 데는 한계가 있습니다.
  • SQL: 수백만 개의 데이터 행과 여러 데이터 소스를 처리할 수 있으며, 대규모 데이터 세트에 더 적합합니다.
• 데이터 접근 및 자동화:
  • 스프레드시트: 사용자가 직접 데이터를 입력하거나 수정하며, 데이터가 주로 개인 하드 드라이브나 클라우드에 저장됩니다.
  • SQL: 데이터베이스 내 여러 위치에서 데이터를 자동으로 가져올 수 있으며, 여러 애널리스트가 협업하는 대규모 팀에서 유리합니다.
• 기능 및 편리성:
  • 스프레드시트: 맞춤법 검사와 같은 편리한 기능이 내장되어 있어 소규모 작업에 유용합니다.
  • SQL: 데이터베이스와의 통신 표준 언어로 사용되며, 쿼리의 변경 사항을 기록하여 팀 작업 시 변경 사항을 추적할 수 있습니다.

3. 스프레드시트와 SQL의 활용 예시

• 스프레드시트: 개인이나 소규모 데이터 분석에서 유용하며, 사용자가 수동으로 데이터를 입력하고 분석합니다.
• SQL: 대규모 데이터베이스에서 데이터를 검색하고 처리하며, 데이터베이스에서 직접 쿼리를 작성하여 복잡한 데이터 작업을 수행합니다.

 

---

SQL 언어 및 용도

이 읽기 자료에서 여러분은 SQL 언어와 각각의 용도를 배울 수 있습니다. 잠깐 복습하자면 구조적 쿼리 언어(SQL)는 데이터베이스에 명령을 내리는 데 사용되는 언어입니다. SQL을 배우는 것은 새로운 언어를 배우는 것과 아주 비슷할 수 있으며, 언어에는 대개 다양한 방언이 있습니다. 일부 데이터베이스 제품에서는 해당 데이터베이스 제품과 통신하는 데 도움이 되는 고유한 SQL 버전을 사용합니다.

이러한 SQL 언어는 회사마다 다르고, 회사에서 다른 데이터베이스 시스템으로 바꾸는 경우 시간이 지남에 따라 SQL 언어가 변경될 수도 있습니다. 그러므로 많은 애널리스트가 표준 SQL로 시작한 다음 작업 중인 데이터베이스에 따라 사용하는 언어를 조정합니다. 표준 SQL은 대부분의 데이터베이스에서 작동하며 다른 버전에 맞추려면 약간의 구문 변형이 필요합니다.

주니어 데이터 애널리스트는 SQL 언어 간에 약간의 차이가 있음을 알아야 합니다. 본 과정에서 소개하는 표준 SQL을 마스터하면 모든 데이터베이스에서 SQL을 사용할 수 있게 됩니다. 

추가 정보

모든 SQL 언어를 알 필요는 없지만 다양한 버전이 존재한다는 것을 알아두는 것이 좋습니다. SQL 언어의 종류와 각각의 용도를 자세히 알아보려면 다음 리소스에서 추가 정보를 확인하세요.

• LearnSQL 블로그: SQL 언어란 무엇이고 어떤 것을 배워야 할까요?
• Software Testing Help의 기사: SQL, MySQL, SQL Server의 차이점
• Datacamp 블로그: SQL Server, PostgreSQL, MySQL... 무엇이 다를까요? 어디에서 시작해야 할까요?이 블로그 게시물에는 오류가 있습니다. 비교표에 SQlite가 윈도우 함수 대신 하위 쿼리를 사용한다고 잘못 나와 있습니다. 이에 대한 설명은 SQLite 윈도우 함수문서를 참조하세요.

SQL Tutorial의 튜토리얼: SQL이란

 

 

---

기본 SQL 쿼리 학습

널리 사용되는 SQL 쿼리

 

1. SQL 쿼리란?

• 정의: 쿼리는 데이터베이스에서 원하는 결과를 얻기 위해 입력하는 요청입니다.
• 중요성: SQL(구조적 쿼리 언어)에서 쿼리는 데이터 조작 및 검색에 필수적입니다.

2. 기본 SQL 쿼리 사용법

a. SELECT 쿼리

• 용도: 데이터베이스에서 원하는 데이터를 추출합니다.
• 구문: SELECT 열1, 열2 FROM 테이블명;
• 예시: SELECT name, city FROM customer_data.customer_address;
  • customer_data 데이터 세트의 customer_address 테이블에서 name과 city 정보를 가져옵니다.

b. INSERT INTO 쿼리

• 용도: 데이터베이스의 테이블에 새로운 데이터를 추가합니다.
• 구문: INSERT INTO 테이블명 (열1, 열2) VALUES (값1, 값2);
• 예시:
  • 먼저 customer_address 테이블의 열을 확인한 후, 새로운 데이터를 삽입할 위치를 지정합니다.
  • INSERT INTO customer_address (name, city) VALUES ('John Doe', 'New York');

c. UPDATE 쿼리

• 용도: 데이터베이스 테이블의 기존 데이터를 수정합니다.
• 구문: UPDATE 테이블명 SET 열1 = 값1 WHERE 조건;
• 예시:
  • UPDATE customer_address SET city = 'Los Angeles' WHERE name = 'John Doe';
  • name이 'John Doe'인 고객의 city를 'Los Angeles'로 업데이트합니다.

d. CREATE TABLE 쿼리

• 용도: 새로운 테이블을 데이터베이스에 생성합니다.
• 구문: CREATE TABLE IF NOT EXISTS 테이블명 (열1 데이터타입, 열2 데이터타입);
• 예시:
  • CREATE TABLE IF NOT EXISTS new_table (id INT, name VARCHAR(100));
  • 새로운 테이블 new_table을 생성합니다. 이미 존재하면 생성하지 않습니다.

e. DROP TABLE 쿼리

• 용도: 데이터베이스에서 테이블을 삭제합니다.
• 구문: DROP TABLE IF EXISTS 테이블명;
• 예시:
  • DROP TABLE IF EXISTS old_table;
  • old_table이 존재하면 삭제합니다.

3. 테이블 및 데이터 관리

• 데이터 저장: 쿼리로 추출한 데이터는 로컬 메모리에 저장되며, CSV 파일로 다운로드하거나 새로운 테이블로 저장할 수 있습니다.
• 정기적인 업데이트: 자주 사용하는 데이터는 자동으로 업데이트되는 테이블을 생성하여 보고서 작성 시 유용하게 활용할 수 있습니다.
• 테이블 정리: 오래된 또는 중복된 테이블은 DROP TABLE IF EXISTS 문을 사용하여 정리하는 것이 좋습니다. 데이터베이스에서 중요한 데이터를 삭제하지 않도록 주의해야 합니다.

 

---

SQL을 사용하여 문자열 변수 정리

 

1. 중복 항목 삭제하기

• 문제: 데이터베이스에 중복된 데이터가 있을 수 있습니다.
• 해결: DISTINCT를 사용하여 중복 항목을 제거합니다.

예시:

sql

SELECT DISTINCT customer_id FROM customer_data.customer_address;

• 이 쿼리는 중복된 고객 ID를 제거하여 고유한 고객 ID 목록을 제공합니다.

 

2. 문자열 변수 정리하기

• 문자열 정리의 중요성: 데이터 분석 전에 문자열 변수의 일관성을 확인하는 것이 중요합니다.

a. LENGTH 함수

• 용도: 문자열 변수의 길이를 반환합니다.
• 구문: LENGTH(열이름)

예시:

sql

SELECT LENGTH(country) AS letters_in_country FROM customer_data.customer_address;

• 각 국가 코드의 길이를 반환하여 데이터 일관성을 확인합니다.
• WHERE LENGTH(country) > 2를 사용하여 길이가 2보다 큰 값을 필터링합니다.

b. SUBSTRING 함수

• 용도: 문자열의 일부분을 추출합니다.
• 구문: SUBSTRING(열이름, 시작위치, 길이)

예시:

sql

SELECT customer_id FROM customer_data.customer_address WHERE SUBSTRING(country, 1, 2) = 'US';

• 각 국가 코드의 처음 두 글자만 추출하여 'US'로 시작하는 값을 필터링합니다.

c. TRIM 함수

• 용도: 문자열의 앞뒤 공백을 제거합니다.
• 구문: TRIM(열이름)

예시:

sql

SELECT DISTINCT customer_id FROM customer_data.customer_address WHERE TRIM(state) = 'OH';

• 공백을 제거한 뒤 'OH'와 일치하는 고객 ID를 반환합니다.

3. SQL 쿼리 작성 시 주의사항

• 중복 항목 제거: DISTINCT를 사용하여 결과에서 중복을 제거합니다.
• 문자열 일관성 확인: LENGTH, SUBSTRING, TRIM 함수로 문자열 데이터를 정리하여 오류를 방지합니다.

4. 데이터 정리의 중요성

• 정리: 문자열 변수를 정리하여 데이터의 일관성을 유지하고 분석 시 오류를 방지할 수 있습니다.
• 도구: LENGTH, SUBSTRING, TRIM과 같은 함수는 데이터 정리를 위한 강력한 도구입니다.

 

 

 

 

---

데이터 변환

고급 데이터 정리 함수, 파트 1

 

1. CAST 함수 소개

• 목적: 데이터베이스에서 데이터 유형을 변환할 때 사용됩니다. 예를 들어, 문자열 데이터를 숫자형 데이터로 변환할 수 있습니다.
• 사용 예시: 구매 가격(purchase_price) 데이터를 숫자로 변환하여 올바르게 정렬할 수 있습니다.

2. 구매 가격 데이터 정렬 문제

• 문제: purchase_price 데이터가 문자열로 인식되어 숫자로서 정렬되지 않음.

예시 쿼리:

sql

SELECT purchase_price FROM customer_data.customer_purchase ORDER BY purchase_price DESC;

• 현재 쿼리는 문자열로 정렬되기 때문에 89.85가 799.99보다 앞에 표시됩니다.

3. 데이터 형식 확인 및 CAST 함수 사용

• 문제: purchase_price는 부동 소수점 숫자로 저장되어야 하지만 문자열로 인식됨.
• 해결 방법: CAST 함수를 사용하여 purchase_price를 부동 소수점 숫자로 변환합니다.
• CAST 함수 구문:
  sql

  코드 복사

  CAST(열이름 AS 데이터타입)

예시 쿼리:

sql

SELECT CAST(purchase_price AS FLOAT64) AS purchase_price FROM customer_data.customer_purchase ORDER BY CAST(purchase_price AS FLOAT64) DESC;

• 이 쿼리는 purchase_price를 FLOAT64로 변환하여 올바르게 정렬합니다.

4. 문자열을 다른 데이터 유형으로 변환하기

• 기타 데이터 유형 변환: CAST 함수는 문자열을 날짜 및 시간과 같은 다른 데이터 유형으로 변경하는 데에도 사용됩니다.

예시: 문자열을 날짜로 변환

sql

SELECT CAST(date_string AS DATE) AS formatted_date FROM some_table;

 

5. CAST 함수의 중요성

• 데이터 애널리스트로서, 다양한 데이터 소스를 다룰 때 데이터 유형을 올바르게 변환하여 분석에 문제를 일으키지 않도록 해야 합니다.
• CAST 함수는 데이터를 정리하고 분석하는 데 유용한 도구입니다.

 

 

---

고급 데이터 정리 함수, 파트 2

 

1. CAST 함수 활용

• 목표: 날짜/시간 필드를 날짜 형식으로 변환하여 데이터를 필터링할 수 있습니다.
• 예시:
  sql

  코드 복사

  SELECT CAST(date_field AS DATE) AS clean_date, purchase_price FROM customer_data.customer_purchase WHERE CAST(date_field AS DATE) BETWEEN '2020-12-01' AND '2020-12-31';
  • 이 쿼리는 날짜 필드를 날짜 형식으로 변환하여 2020년 12월의 구매 내역만 표시합니다.

2. CONCAT 함수 사용

• 목표: 여러 문자열을 결합하여 고유 키를 생성합니다.
• 예시:
  sql

  코드 복사

  SELECT CONCAT(product_code, '-', product_color) AS unique_product_key FROM customer_data.customer_purchase WHERE product = 'couch';
  • 이 쿼리는 product_code와 product_color를 결합하여 소파의 고유 키를 생성합니다.

3. COALESCE 함수 활용

• 목표: NULL 값을 처리하여 대체 값을 제공할 수 있습니다.
• 예시:
  sql

  코드 복사

  SELECT COALESCE(product_name, product_code) AS product_info FROM customer_data.customer_purchase;
  • 이 쿼리는 product_name이 NULL인 경우 product_code를 대체 값으로 사용하여 제품 정보를 표시합니다.

4. 실습과 응용

• CAST, CONCAT, COALESCE 함수는 데이터 정리와 분석에서 매우 유용한 도구입니다.
  • CAST: 데이터 유형 변환
  • CONCAT: 문자열 결합
  • COALESCE: NULL 처리

 

 

 

728x90