[천재교육] 프로젝트 기반 빅데이터 서비스 개발자 양성 과정 9기 학습일 : 2024.09.30
📕학습 목록
AWS
SQL
RDBMS
📗 기억할 내용
1. AWS 개념 정리
AWS(Amazon Web Services) : 아마존에서 제공하는 클라우드 컴퓨팅 서비스
기업이나 개인이 데이터 저장, 서버 운영, 애플리케이션 개발 등을 클라우드 상에서 할 수 있도록 다양한 인프라와 플랫폼 서비스를 제공
주요 서비스로는 EC2(가상 서버), S3(파일 저장), RDS(관계형 데이터베이스), Lambda(서버리스 컴퓨팅) 등이 있음
1) 계정 및 권한 관리
Root 계정
AWS 계정을 처음 생성할 때 사용하는 최고 관리자 계정
모든 권한을 가지고 있으며, 계정 관리, 결제 설정 등 중요한 작업에만 사용
IAM 계정
AWS에서 리소스 접근 권한을 관리하기 위한 사용자 계정
Root 계정과 달리 할당된 권한 내에서만 작업을 수행할 수 있음
일상적인 작업을 위해 IAM 사용자 또는 역할을 통해 권한을 세분화하여 사용
2) 컴퓨팅 서비스
EC2 (Elastic Compute Cloud)
AWS에서 제공하는 가상 서버 서비스
사용자가 원하는 만큼의 컴퓨팅 리소스(CPU, 메모리 등)를 제공하고, 확장 가능
애플리케이션 구동, 데이터 처리, 서버 역할 등 다양한 컴퓨팅 작업에 활용
3) 스토리지 및 데이터 관리
S3 (Simple Storage Service)
AWS에서 제공하는 객체 스토리지 서비스
데이터 파일을 무제한으로 저장할 수 있으며, 높은 내구성과 가용성을 제공
주로 백업, 로그 저장, 정적 웹 사이트 호스팅 등에 사용
S3 버전 관리
S3 버킷에서 파일의 여러 버전을 저장하여 버전 관리를 수행
객체가 업데이트되거나 삭제되었을 때도 이전 버전으로 롤백하거나 복구 가능
실수로 삭제된 파일을 복구하거나 데이터 무결성을 관리할 때 유용
4) 데이터 분석 및 쿼리
Athena
서버리스 쿼리 서비스로, S3에 저장된 데이터를 SQL을 사용해 분석할 수 있음
대규모 데이터를 효율적으로 쿼리하는 데 사용되며, ETL 작업 없이 바로 데이터 조회 가능
주로 로그 분석이나 비즈니스 인텔리전스 작업에서 사용
SQL (Structured Query Language)
관계형 데이터베이스 관리 시스템(RDBMS)과 소통하며 데이터베이스의 데이터를 다루기 위한 언어
데이터의 조회, 삽입, 업데이트, 삭제를 위한 명령어를 제공
SQL ⊃ DDL, DML, DCL
Parquet
Parquet은 컬럼 지향 저장 형식 : 테이블에 ID, Name, Age, Country 컬럼이 있을 때, 각각의 컬럼 데이터는 따로 저장됨
이 저장 형식을 사용할 때 데이터 파티션화가 가능
데이터 저장 공간을 절약하고, 빠른 쿼리 성능을 제공
주로 대용량 데이터 처리 및 빅데이터 분석에 활용
[tip] 컬럼 지향 저장 예시 테이블 ① 행 지향 저장 방식(Row-Oriented) : 각 행이 순차적으로 저장됨
1, Alice, 25, USA
2, Bob, 30, Canada
3, Charlie, 35, UK
→ 한 행에 해당하는 데이터를 한 번에 읽는 데는 유리하지만, 특정 컬럼(예: Age 컬럼)만을 읽고 싶을 때는 모든 데이터를 읽어야 하기 때문에 비효율적
② 컬럼 지향 저장 방식(Column-Oriented) : 각 컬럼의 값들이 모여서 저장됨
ID 컬럼: 1, 2, 3
Name 컬럼: Alice, Bob, Charlie
Age 컬럼: 25, 30, 35
Country 컬럼: USA, Canada, UK
→ 각 컬럼이 독립적으로 저장되기 때문에, 특정 컬럼의 데이터만 읽어야 하는 경우(예: Age 컬럼) 매우 효율적
[tip] 파티션(Partition) - "데이터를 파티션화하여 저장" : 하나의 테이블을 여러 개의 작은 부분으로 나누어 저장하는 것(하나의 컬럼을 통째로 쪼개서 저장하는게x, 하나의 컬럼의 특정 값에 대해 따로 저장o) - 파티션화된 데이터는 필요할 때 언제든지 쿼리 엔진이 각 파티션을 모아서 하나의 테이블처럼 사용할 수 있기 때문에, 저장 및 처리 측면에서 매우 유리함 - 파티션화 과정: 1) 파티션 기준 설정: 예를 들어, 날짜(date=2024-10-01), 지역(region=seoul) 등을 기준으로 데이터를 나눔 2) 파티션별 저장: Parquet 파일 형식으로 S3나 다른 스토리지에 나뉘어 저장. 예를 들어, date=2024-10-01/ 폴더 안에 해당 날짜의 데이터가 저장됨 3) 자동 병합: 쿼리를 통해 데이터를 읽을 때, 쿼리 엔진(Athena, Hive, Spark 등)이 자동으로 각 파티션을 하나의 테이블처럼 병합하여 처리
5) 데이터 저장 및 처리
데이터스토어(Data Store)
데이터를 저장하고 관리하는 모든 시스템을 총칭하는 용어
관계형 데이터베이스(RDBMS), NoSQL, 데이터웨어하우스(DWH), 객체 스토리지(S3) 등 다양한 형태의 데이터 저장소
데이터의 장기적인 저장 및 검색을 목적으로 함
데이터마트(Data Mart)
특정 주제나 부서에 맞춘 소규모 데이터웨어하우스
데이터웨어하우스의 하위 집합으로, 특정 목적에 맞춘 데이터 저장소
주로 특정 부서(예: 마케팅, 재무)에서 필요한 데이터를 제공하고 분석하는 데 사용
데이터스토어 아키텍처
데이터가 수집, 처리, 저장되는 구조를 시각적으로 표현한 아키텍처
데이터는 ETL 도구(Glue, MWAA 등)를 통해 수집되고, ODS, RAW, SERV 저장소를 거쳐 최종적으로 사용자에게 제공되는 데이터 흐름을 나타냄
6) ETL (Extract, Transform, Load)
추출(Extract): 데이터 소스에서 데이터를 가져오는 과정
변환(Transform): 데이터를 적절한 형태로 가공하는 과정
적재(Load): 변환된 데이터를 목적지(데이터 웨어하우스, 데이터 레이크 등)에 저장하는 과정
① 데이터 처리 서비스 (ETL 및 데이터 파이프라인 관리 도구)
Glue: AWS의 서버리스 ETL 서비스로, 데이터를 추출하고 변환 및 적재하는 데 사용
MWAA (Managed Workflows for Apache Airflow): Apache Airflow 기반 워크플로우 관리 서비스로, 데이터 파이프라인을 정의하고 관리
EMR (Elastic MapReduce): AWS에서 제공하는 대규모 데이터 처리 클러스터 서비스로, Hadoop, Spark 등의 도구를 사용해 대용량 데이터 분석 수행
② 데이터 처리 단계 결과물 (ETL 과정에서의 데이터 상태; ODS → RAW → SERV)
ODS (Operational Data Store)
운영 데이터 저장소로, 운영 시스템에서 데이터를 실시간 또는 빈번하게 추출해 임시로 저장하는 단계
실시간 처리나 빈번하게 업데이트되는 데이터를 임시로 보관하는 중간 단계
RAW
ODS에서 데이터를 가져와 약간의 전처리를 거친 상태의 데이터
아직 완전한 처리 전의 데이터로, 추가적인 정제나 변환이 가능
SERV
최종적으로 완전히 처리된 데이터로, RAW 데이터를 조합하여 최종 사용자가 접근할 수 있는 완성된 데이터 세트
데이터 분석, 보고서 작성, 애플리케이션 활용 등 최종 사용을 위한 상태의 데이터
7) 데이터 수집 및 크롤링
Crawler (AWS Glue Crawler)
AWS Glue에서 제공하는 기능으로, 데이터 저장소에 있는 데이터의 스키마를 자동으로 탐지하여 데이터 카탈로그에 저장
S3, RDS, Redshift 등 여러 데이터 저장소의 구조와 형식을 자동으로 분석하여 테이블을 생성
데이터를 탐지한 후, 이를 바탕으로 ETL 작업을 쉽게 구성 가능
8) 데이터 파이프라인 자동화 및 관리
AWS Lambda
이벤트 기반으로 작동하는 서버리스 함수
특정 이벤트가 발생하면 자동으로 실행되며, 주로 짧고 단일 작업을 처리하는 데 사용
서버를 관리하지 않아도 되며, 트래픽 증가에 따라 자동으로 확장됨
대표적인 사용 사례: S3 파일 업로드 트리거 처리, API 요청 처리, 실시간 데이터 처리
- Lambda는 주로 짧고 독립적인 작업을 처리하며, 이벤트가 발생할 때마다 실행되는 방식. 코드 실행 시간이 짧고, 필요한 경우에만 실행됨. 주로 특정 이벤트(예: S3에 파일 업로드, DynamoDB 변경 등)를 처리하는 작은 작업에 적합
Apache Airflow
데이터 파이프라인을 관리하고, 복잡한 워크플로우에서여러 작업 간의 의존성을 관리하며 자동화
DAG(Directed Acyclic Graph)구조를 사용해,작업 간의 순서를 정의하고, 이 순서대로 실행
주로긴 시간 동안 실행되는 작업이나여러 단계를 거치는 복잡한 데이터 파이프라인을 처리
AWS에서는 MWAA(Managed Workflows for Apache Airflow)로 서버리스 제공 가능
대표적인 사용 사례: 데이터 파이프라인 스케줄링, ETL 작업 자동화, 복잡한 데이터 처리 워크플로우 관리
- Apache Airflow는 복잡한 데이터 파이프라인이나 여러 단계로 이루어진 작업을 관리하는 데 사용되며, 작업 간의 의존성을 정의할 수 있어 여러 단계의 작업을 자동화할 수 있습니다. 특히 긴 실행 시간을 갖는 작업이나 여러 단계를 포함하는 파이프라인에 적합
2. SQL 문법
1) SQL 문법 종류
DDL (Data Definition Language, 데이터 정의 언어)
데이터베이스 구조(Schema)를 정의∙관리하는 명령어 ex) CREATE, ALTER, DROP, RENAME
DML (Data Manipulation Language, 데이터 조작 언어)
데이터베이스 내의 데이터를 삽입, 조회, 수정, 삭제하는 명령어 ex) CRUD : INSERT(CREATE), SELECT(READ), UPDATE, DELETE
2) SELECT 쿼리 구조
* 실행 순서 : GROUP BY → SELECT 이므로, GROUP BY 에서 사용하지 않은 컬럼은 SELECT 에 단독으로("컬럼 명") 적을 수 X; 집계 함수를 사용해서 적어야 함
* WHERE: 단일 행에 대해 필터링 (집계 함수 적용 전) * HAVING: 집계 함수로 요약된 그룹에 대해 필터링 (집계 함수 적용 후)
작성 순서
vs
실행 순서
SELECT"컬럼 명" 데이터 선택
FROM
FROM "테이블 명" 테이블 선택
WHERE
WHERE "조건 식" 개별행에 조건 지정(1st 필터링)
GROUP BY
GROUP BY "컬럼 명" 같은 값을 가지는 ROW를 그룹화
(HAVING)SELECT 전
(HAVING) "추가 조건 식" 그룹화된 데이터에 조건 지정(2nd 필터링)
SELECT
ORDER BY "컬럼 명" ASC/DESC 출력될 행들의 정렬 순서를 지정
ORDER BYSELECT 후
3) SQL 작성 규칙
① 대/소문자
SQL 문법∙함수∙기능 : 대문자
테이블 명∙컬럼 명 : 소문자
값 : 대/소문자
② 따옴표
데이터베이스∙테이블∙컬럼 : 큰따옴표(" ") → 데이터베이스 - 테이블은 온점(.)으로 구분
값 : 작은따옴표(' ')
4) SQL 주요 표현들
① 주석
-- : 한 줄 주석
/* */ : 여러 줄 주석
② 데이터 선택 및 조회
* (Asterisk) : 테이블의 모든 컬럼 조회 (하지만 테이블 구조 변경을 대비해, 컬럼명을 직접 입력하는 것이 더 좋음)
AS : 데이터에 별명을 지정
LIMIT : 출력할 데이터의 개수를 지정
DISTINCT : 중복 제거
TRIM : 문자열 앞/뒤에 있는 공백을 제거
LENGTH : 문자열의 길이를 반환
집계함수 : 여러 행의 값을 하나의 결과 값으로 요약하는 함수
COUNT : 특정 컬럼에서 데이터의 개수를 세는 함수
MAX : 특정 컬럼에서 최대값을 구하는 함수
MIN : 특정 컬럼에서 최소값을 구하는 함수
AVG : 특정 컬럼에서 평균값을 구하는 함수
SUM : 특정 컬럼에서 합계를 구하는 함수
③ 조건 및 논리 연산자
IF(조건, 참일 경우의 값, 거짓일 경우의 값) : 조건 생성
CASE (WHEN ~ THEN)반복 ~ ELSE ~ END AS ~ : 다수의 조건 생성
a AND b : a, b 둘 다 True일 때 True
a OR b : a, b 둘 중 하나라도 True이면 True
NOT a : a가 아니면 True
-- IF
SELECT
"name",
IF("score" >= 60, 'Pass', 'Fail') AS "result"
FROM "students";
-- CASE
SELECT
"name",
CASE
WHEN "score" >= 90 THEN 'A'
WHEN "score" >= 80 THEN 'B'
WHEN "score" >= 70 THEN 'C'
ELSE 'F'
END AS "grade"
FROM "students";
-- AND
SELECT *
FROM "employees"
WHERE "age" >= 30
AND "department" = 'Sales';
-- OR
SELECT *
FROM "employees"
WHERE "age" >= 50
OR "department" = 'HR';
-- NOT
SELECT *
FROM "employees"
WHERE NOT "department" = 'Finance';
④ 범위 및 목록 선택
BETWEEN a AND b : a ≤ 값 ≤ b 범위의 데이터를 선택
IN (a, b, c, ...) : 목록에 포함된 데이터를 선택
⑤ 패턴 확인 * 정규표현식 : Regular Expression
“컬럼명” LIKE '패턴' : 문자열이 특정 패턴을 포함하는지를 확인하는 연산자
REGEXP_LIKE(“컬럼명”, ‘정규표현식 패턴‘) : 정규표현식을 사용하여 문자열 패턴을 매칭
-- LIKE
SELECT *
FROM "employees"
WHERE "name" LIKE '%Kim%';
-- REGEXP_LIKE
SELECT *
FROM "employees"
WHERE REGEXP_LIKE("email", '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
⑥ NULL 값 확인
* NULL - NULL : 값이 없는 상태를 의미 ⊃ 비어있는 셀(값을 입력하지 않음) - 공백 : 실제로 공백 문자가 포함된 값이며, NULL과는 다름 - N/A : 의미상으로 '해당 없음'을 뜻하는 텍스트. 문자열 값. 값이 존재하는 상태. NULL과는 다름
IS NULL : 데이터가 NULL인지 아닌지를 확인
-- IS NULL
SELECT *
FROM "employees"
WHERE "phone_number" IS NULL;
⑦ 데이터 타입 변환
CAST (값 AS 데이터 타입) : 특정 값을 다른 데이터 타입으로 변환
문자열 : CHAR, VARCHAR, TEXT
숫자 : INT, SMALLINT, BIGINT, FLOAT, DECIMAL
날짜, 시간 : DATE, TIME, DATETIME, TIMESTAMP + 날짜, 시간 함수(CURRENT_DATE, NOW,...)도 CAST 에 사용 가능
불리언 : BOOLEAN
이진 : BLOB
-- 1. 문자열을 숫자로 변환
SELECT CAST('123' AS INT) AS "int_value";
-- 2. 문자열을 날짜로 변환
SELECT CAST('2024-10-03' AS DATE) AS "date_value";
-- 3. 숫자를 문자열로 변환
SELECT CAST(123 AS VARCHAR(10)) AS "string_value";
-- 4. 소수점을 가진 숫자를 정수로 변환
SELECT CAST(123.45 AS INT) AS "int_value";
-- 5. 날짜를 문자열로 변환
SELECT CAST(CURRENT_DATE AS VARCHAR(20)) AS "string_date";
5) 쿼리문 일부 실행
일부 선택 : shift + 방향키
일부 실행 : ctrl + enter
3. RDBMS
1) DBMS vs RDBMS
구분
DBMS (Database Management System)
RDBMS (Relational Database Management System)
데이터 구조
데이터를 파일 형식 또는 단순한 테이블 형식으로 저장
데이터를 관계형 테이블로 저장하며, 각 테이블은 행과 열로 구성
데이터 관계
데이터 간의 관계가 명확하지 않음
데이터 간의 명확한 관계(관계형 모델)를 정의하여 관리
데이터 중복
데이터 중복을 막을 수 없음
정규화를 통해 데이터 중복을 줄이고 데이터 무결성을 보장
데이터 무결성
기본적인 무결성만 지원
참조 무결성 및 데이터 무결성을 강하게 지원
ACID 속성
일부 DBMS는 ACID 속성을 완전히 지원하지 않음
ACID 속성(원자성, 일관성, 고립성, 지속성)을 완전하게 지원
복잡한 쿼리
간단한 쿼리만 가능
복잡한 SQL 쿼리 및 JOIN 연산을 지원
데이터 처리
소규모 데이터 처리에 적합
대규모 데이터 처리 및 복잡한 트랜잭션에 적합
사용 사례
단일 사용자 또는 소규모 데이터베이스에서 사용
다중 사용자 환경 및 대규모 엔터프라이즈에서 사용
예시
Microsoft Access, 파일 기반 DB
Oracle, MySQL, PostgreSQL, MSSQL, Tibero
2) RDBMS 종류 비교
RDBMS 종류
개발사
라이선스
성능
확장성
Oracle
Oracle Corporation
상용
높은 성능
대규모 엔터프라이즈 지원
MSSQL
Microsoft
상용
Microsoft 기반, Windows에 최적화
제한적 확장성
MySQL
Oracle Corporation
오픈 소스
경량화, 빠른 처리 성능
오픈 소스 기반으로 다양한 확장 가능
Tibero
TmaxSoft
상용
국산 DBMS, 안정성 강조
엔터프라이즈 지원
PostgreSQL
PostgreSQL Global Development Group
오픈 소스
고성능, 확장성 우수
대규모 데이터 웨어하우스에 적합
📘 쿼리 실습 및 트러블 슈팅
1. AWS 쿼리문 작성 화면
2. 쿼리문 작성
1) SELECT 단독 사용 가능
① 기본 수치 연산 및 값 출력
SELECT 1, 2, 3;
-- 1, 2, 3 값을 각각 데이터로 갖는 임의의 컬럼 생성
SELECT 1+2;
-- 1 + 2 = 3 을 출력
SELECT 2-1;
-- 2 - 1 = 1 을 출력
SELECT
10.0 / 3 AS 나눈값, -- 나눈 값 (둘 중 하나가 실수여야 나눈값도 실수로 출력됨)(둘 다 정수일 경우 정수로 출력됨)
10 DIV 3 AS 몫, -- 몫
10 % 3 AS 나머지 -- 나머지 (또는 MOD(10, 3))
;
② 문자열 출력 및 연결
SELECT 'A', 'B';
-- 문자형 값 'A', 'B'를 임의의 컬럼으로 출력
SELECT '가나다';
-- 문자형 값 '가나다'를 출력
SELECT CONCAT('가', '나');
-- '가'와 '나'를 연결한 문자열 '가나'를 출력
③ 날짜 및 시간 출력
SELECT CURRENT_DATE;
-- 현재 날짜를 출력
SELECT CURRENT_TIMESTAMP;
-- 현재 날짜 + 시간을 출력
④ 논리 연산 및 비교 연산
SELECT 1>2;
-- 1이 2보다 큰가? False를 출력
SELECT 1<=2;
-- 1이 2보다 작거나 같은가? True를 출력
SELECT 1=2;
-- 1과 2가 같은가? False를 출력
SELECT 1!=2;
-- 1과 2가 다른가? True를 출력 (다르다 표현1)
SELECT 1<>2;
-- 1과 2가 다른가? True를 출력 (다르다 표현2)
SELECT 1 IN (1, 2, 3);
-- 1이 리스트 (1, 2, 3) 안에 있는가? True를 출력
SELECT 1 NOT IN (1, 2, 3);
-- 1이 리스트 (1, 2, 3) 안에 없는가? False를 출력
SELECT 'A' IN ('A', 'B', 'C');
-- 'A'가 리스트 ('A', 'B', 'C') 안에 있는가? True를 출력
SELECT 'A'='a';
-- 대소문자 구분 없이 'A'와 'a'가 같은가? False를 출력
⑤ NOT 연산
SELECT NOT true;
-- NOT 연산을 통해 true의 반대 값인 False를 출력
2) 테이블 조회
① * + LIMIT 을 통해 테이블 형태 우선 확인
SELECT *
FROM "learning_analytics"."e_milkt_study"
LIMIT 5;
-- 테이블의 상위 5개 행을 조회
② 테이블 메타데이터 조회
Q: "information_schema"."columns" 에서 information_schema라는 데이터베이스는 어디에 존재?
A: information_schema는 사용자가 따로 만든 것이 아니라, AWS Glue와 같은 시스템에서 기본적으로 제공되는 메타데이터 데이터베이스임.
이는 Glue에서 데이터베이스와 테이블을 저장하고, 크롤러를 실행하면 자동으로 생성되는 데이터베이스임.
이 데이터베이스에는 테이블 및 컬럼에 대한 코멘트를 추가할 수 있는 기능도 제공함
-- 특정 테이블의 컬럼 정보를 확인하기 위한 쿼리
-- 테이블 스키마와 테이블 이름을 바꾸면 해당 테이블의 컬럼 설명을 조회할 수 있음
SELECT *
FROM "information_schema"."columns"
WHERE 1=1
AND "table_schema" = 'learning_analytics'
AND "table_name" = 'e_assessment';
③ 열에 별칭 부여하여 출력
-- 전체 테이블에 특정 열만 별칭 부여
SELECT *, "proc_ym" AS "temp"
FROM "learning_analytics"."e_assessment"
LIMIT 10;
-- 별칭 부여된 열만 출력
SELECT "proc_ym" AS "처리일자"
FROM "learning_analytics"."e_milkt_study"
LIMIT 5;
④ 특정 열 선택
SELECT "proc_ym", "proc_ymd", "userid", "mcode"
FROM "learning_analytics"."e_milkt_study"
LIMIT 5;
⑤ 하나의 상수를 값으로 갖는 열 출력
-- 열 이름에는 문자열만 올 수 있음(∴ 상수 1은 열 이름을 의미하지X)
-- "proc_ym"에 별칭을 부여하고, 1이라는 고정된 값을 가진 새로운 열 "number"를 추가하여 출력
-- 1 이라는 상수값(고정된 값)에 별칭("number")를 부여함; 새로운 열 이름이 "number"이 되고, 그 열의 모든 행에 1이라는 고정된 값이 들어감
SELECT *, "proc_ym" AS "처리일자", 1 AS "number"
FROM "learning_analytics"."e_milkt_study"
LIMIT 5;
⑥ 조건 지정(WHERE)
-- WHERE 조건식
-- WHERE 1=1 : 항상 참(True)인 조건. 조건을 추가하기 쉽게 하기 위해 사용. 이 구문은 쿼리의 기능에 영향을 주지 않으며, 다음 조건들을 논리적으로 연결할 때 줄 맞춤이나 추가적인 조건들을 쉽게 처리하기 위해 사용
-- "userid"와 "proc_ymd"를 조건으로 행을 필터링하여 상위 5개의 데이터를 출력
SELECT *
FROM "learning_analytics"."e_milkt_study"
WHERE 1=1
AND "userid" = '00103eba-5b1d-4986-a071-3aecd9bb8bd9'
AND "proc_ymd" = '20240826'
LIMIT 5;
-- COUNT 함수 : 테이블에 몇 개의 행이 있는지 출력
-- AND "content_grade" = 3: content_grade가 3인 행을 조건으로 필터링. 1=1과 AND로 연결되어 있으며, "content_grade가 3인 데이터"를 필터링하는 역할
-- OR "content_grade" = 4: content_grade가 4인 행을 조건으로 필터링. 이 조건은 이전의 조건과 OR로 연결되어 있음
-- "content_grade"가 3 또는 4인 행의 개수를 계산. 다만, OR 연산이 적용되므로 content_grade가 4인 데이터는 AND와 상관없이 선택됨
SELECT COUNT(*)
FROM "learning_analytics"."e_content_meta"
WHERE 1=1
AND "content_grade" = 3
OR "content_grade" = 4;
-- AND와 OR 우선순위
-- AND가 OR보다 우선순위가 높음
-- 주어진 쿼리는 **WHERE 1=1 OR ("content_grade" = 3 AND "content_grade" = 4)로 해석됨
-- 즉, "content_grade" = 3 AND "content_grade" = 4 조건은 불가능하지만, 1=1이 항상 참(True)이기 때문에 쿼리는 모든 행을 선택하게 됨
SELECT COUNT(*)
FROM "learning_analytics"."e_content_meta"
WHERE 1=1
OR "content_grade" = 3
AND "content_grade" = 4;
⑦ LIKE; 특정 문자열이 포함된 행을 출력
-- "l_template_mm" 컬럼에서 '탭'이 포함된 행을 찾고, 상위 10개의 데이터를 출력
-- %: 와일드카드(wildcard)로, 아무 문자나, 0개 이상의 문자가 올 수 있음을 의미
-- 탭: 찾고자 하는 문자열. 이 경우 "탭"이라는 단어를 의미
-- 따라서 LIKE '%탭%' : l_template_mm 열의 값 중에 "탭"이라는 문자열이 포함된 모든 행을 선택
SELECT *
FROM "learning_analytics"."e_content_meta"
WHERE 1=1
AND "l_template_mm" LIKE '%탭%'
LIMIT 10;
⑧ 자료형 변환
-- CAST AS : 자료형 변환
-- 문자열 → TIMESTAMP 형태로 변환; 해당 날짜의 00시 00분 부터(까지) 조회 가능
SELECT *
FROM "learning_analytics"."e_milkt_study"
WHERE 1=1
AND "lrn_s_date" BETWEEN CAST('2024-03-01' AS TIMESTAMP) AND CAST('2024-03-02' AS TIMESTAMP);
⑨ HAVING; 그룹화된 데이터에 조건을 적용 (≠ WHERE : 개별 행에 조건을 적용)
-- HAVING : 풀이 이력 수가 3,000만 건 이상인 달을 필터링하기 위한 조건
SELECT
"proc_ym",
COUNT(*) AS "assessment_cnt"
FROM "learning_analytics"."e_assessment_detail"
GROUP BY "proc_ym"
HAVING COUNT(*) >= 30000000;
3) 정규표현식
① 정의
정규표현식(Regular Expression, REGEX) : 특정한 패턴을 가진 문자열을 찾거나, 변경하거나, 검증하는 데 사용하는 문자열 검색 방법
프로그래밍에서 정규표현식은 텍스트 처리나 데이터 검색에 매우 유용하게 사용됨
② 주요 목적
문자열 검색: 특정 패턴을 만족하는 문자열을 찾는 것
문자열 변환: 패턴을 찾아서 그 부분을 다른 문자열로 교체
문자열 검증: 문자열이 특정 형식에 맞는지 확인(예: 이메일 주소, 전화번호 형식)
③ 기본 구성 요소
문자
a, b, 1, 9 등 개별 문자 자체를 검색
와일드카드
. : 한 개의 임의의 문자를 의미(정규표현식의 와일드카드)
(% : LIKE 절의 와일드카드. 0개 이상의 임의의 문자를 의미)
메타문자
^ : 문자열의시작을 나타냄
$ : 문자열의끝을나타냄
* : 앞의 문자가0회 이상반복됨을 의미
+ : 앞의 문자가1회 이상반복됨을 의미
? : 앞의 문자가0회 또는 1회나올 수 있음을 의미
문자 클래스
[abc] : a, b, c 중 하나와 일치
[0-9] : 숫자 0부터 9 사이의 문자와 일치
특수 문자
\d :숫자와 일치 (0-9와 동일)
\w :단어문자와 일치 (알파벳 대소문자, 숫자, 언더스코어 포함)
\s :공백 문자와 일치
그룹화
(abc) : abc를 하나의 그룹으로 묶어서 처리
④ 괄호의 의미
[] (대괄호): 문자 클래스로 사용
설명: 대괄호 안에 있는 문자들 중 하나와 일치하는 패턴을 정의. 또한, 특정 문자의 범위를 지정할 때도 사용됨
예시:
[abc]+: a, b, c 중 하나의 문자가 1회 이상 반복됨. 예: "a", "b", "c", "aa", "bb", "abc" 등이 모두 일치
[0-9]: 0에서 9까지의 숫자 하나와 일치. 예: "0", "5", "9" 등이 해당
{} (중괄호): 반복 횟수 지정
설명: 중괄호는 반복 횟수를 지정하는 메타문자. 앞에 나온 패턴이 몇 번 반복될지를 정할 수 있음
{n}: 정확히 n회 반복됨을 의미
{n,}: 최소 n회 이상 반복됨을 의미
{n,m}: n회에서 m회 사이로 반복됨을 의미
예시:
\d{2,4}: 숫자가 2회에서 4회 사이로 반복됨. 예: "12", "123", "1234"
[a-z]{3}: 알파벳 소문자가 정확히 3회 반복됨. 예: "abc", "xyz"
() (소괄호): 그룹화와 캡처
설명: 소괄호는 여러 문자를 하나의 그룹으로 묶어 반복이나 조건을 적용할 때 사용. 또한, 캡처 그룹을 만들어서 나중에 그 값을 재사용할 수 있음
예시:
(abc)+: **abc**라는 문자열 자체가 1회 이상 반복됨. 예: "abc", "abcabc", "abcabcabc"
(\d{3})-(\d{4}): 세 자리 숫자와 네 자리 숫자 사이에 **하이픈(-)**이 있는 패턴. 예: "123-4567"
⑤ Lookahead Assertion : 전방 탐색(확인) 조건
문자열의 앞으로 나올 부분이 특정 조건에 맞는지확인만 하고, 그조건을 매칭 결과에는 포함시키지 않음
Positive Lookahead (?=): 조건이 맞는지 확인
(?= ...) : Positive Lookahead Assertion의 기본 형태
?= : 이 부분은 Lookahead를 나타냄
... : 조건을 의미하며, 이 부분에 패턴이 들어감
문자열 'abc123 '에 대해
(?=abc): "abc가 존재하는구나" 하고 확인만 함
(?=abc).*: "abc가 존재하니까, 그때 전체 문자열을 매칭해라" 라는 의미.최종적으로 abc123 을 반환
Negative Lookahead (?!): 조건이 맞지 않는지 확인
-- 1. 이메일 주소 패턴
^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$
/* 설명
[a-zA-Z0-9._%+-] : 대문자, 소문자, 숫자, 특수기호 중 하나의 문자를 반환
[a-zA-Z0-9._%+-]+ : 하나 이상의 문자열을 반환
- 알파벳 소문자: a부터 z까지
- 알파벳 대문자: A부터 Z까지
- 숫자: 0부터 9까지
- 문자 클래스 [] 안의 특수문자들
. (점): 문자 그대로의 점
_ (언더스코어): 문자 그대로의 언더스코어
% (퍼센트): 문자 그대로의 퍼센트 기호
+ (플러스): 문자 그대로의 플러스 기호
- (하이픈): 문자 그대로의 하이픈
- @ : 문자 그대로의 '@' 기호
- \. : 문자 그대로의 점(.)
{2,} : 앞에 나온 패턴이 최소 2회 이상 반복됨(상한이 없으므로 2회 이상이면 몇 번이든 반복 가능)
*/
-- 2. 전화번호 패턴 (하이픈 포함)
^\d{3}-\d{3,4}-\d{4}$
/* 설명
- 문자: 숫자 1, 9 등이 사용될 수 있음
- 메타문자:
^: 문자열의 시작을 나타냄
$: 문자열의 끝을 나타냄
- 문자 클래스: \d (숫자를 의미), {3}, {4} (숫자가 3자리 또는 4자리로 나와야 함)
- 특수 문자: - (하이픈)
*/
-- 3. 비밀번호 패턴 (알파벳, 숫자, 특수 문자 포함)
^(?=.*[A-Za-z])(?=.*\d)(?=.*[@$!%*?&])[A-Za-z\d@$!%*?&]{8,}$
/* 설명
- (?=.*[A-Za-z]) : Lookahead Assertion(1. 대소문자 알파벳이 포함되어 있는지 확인)어떤 문자든 몇 개든 상관없이 이어지다가, 그 뒤에 알파벳 대소문자가 있으면 확인
.* : 임의의 문자가 0회 이상 반복됨
(?= ...) : Lookahead, 특정 조건이 존재하는지 확인
- (?=.*\d) : Lookahead Assertion(2. 숫자가 포함되어 있는지 확인)
- (?=.*[@$!%*?&]) : Lookahead Assertion(3. 특수문자 (@$!%*?&) 중 하나가 포함되어 있는지 확인)
- [A-Za-z\d@$!%*?&]{8,} : Lookahead로 알파벳, 숫자, 특수문자가 포함되었다고 확인되면, 실제로 비밀번호가 대소문자 알파벳, 숫자, 특수문자 중 하나로 이루어진 8자 이상의 문자열 패턴인지 검사
*/
-- "l_template_mm" 컬럼에서 정규표현식을 만족하는 행을 찾고, 상위 10개의 데이터를 출력
-- ^: 문자열의 시작
-- .+: 한 개 이상의 임의의 문자(최소 한 글자가 있어야 함)
-- _: 언더스코어(밑줄) 문자가 와야 한다는 의미
-- .*: 0개 이상의 임의의 문자를 의미 (문자가 있을 수도 있고, 없을 수도 있음)
-- $: 문자열의 끝
-- 정규표현식 '^.+_.*$' 의 의미: 언더스코어(_)가 포함된 문자열
SELECT *
FROM "learning_analytics"."e_content_meta"
WHERE 1=1
AND REGEXP_LIKE("l_template_mm", '^.+_.*$')
LIMIT 10;
4) IF문 / CASE 문
① IF 문
목적: 하나의 조건을 평가하고, 그 조건이 참(True)이면 특정 작업을 수행
구조: 조건이 참일 때와 거짓일 때 각각 다른 동작을 수행
사용 상황: 단일 조건 또는 간단한 조건을 처리할 때 사용
SQL 예시:
조건에 따라 값을 반환하는 함수처럼 사용
IF(condition, true_value, false_value)의 형식
프로그래밍 언어 예시:
if-else 구문을 통해 참일 경우와 거짓일 경우의 동작을 정의
② CASE 문
목적: 여러 조건을 순차적으로 평가하고, 참인 조건에 맞는 작업을 수행
구조: 여러 WHEN 조건을 두고, 각각의 조건에 맞는 값을 반환하거나 작업을 수행
사용 상황: 여러 조건을 처리해야 할 때 사용, 복잡한 조건 분기 처리에 적합
SQL 예시:
여러 조건을 평가하고 해당 조건에 맞는 값을 반환
CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE value3 END의 형식
프로그래밍 언어 예시:
일부 프로그래밍 언어에서 switch-case 구문으로 처리
Python 같은 언어에서는 if-elif-else 구문으로 비슷하게 구현
* 주요 차이점
IF 문: 단일 조건에 대해 참과 거짓을 평가하여 두 가지 결과 중 하나를 반환
CASE 문: 여러 조건을 평가하여 각 조건에 맞는 작업을 처리하거나 값을 반환
-- IF 문을 사용하여 조건에 따른 값을 출력
SELECT *, IF("mm" = '01', '1월', 'X') AS "1월인가?"
FROM "learning_analytics"."e_milkt_study"
LIMIT 5;
-- CASE 문을 사용하여 여러 조건에 따른 값을 출력
SELECT *,
CASE
WHEN "mm" IN ('01', '02', '03') THEN '1분기'
WHEN "mm" IN ('04', '05', '06') THEN '2분기'
ELSE '기타'
END AS "quarter"
FROM "learning_analytics"."e_milkt_study"
LIMIT 5;
3. 트러블 슈팅
- 실행 순서에 따른 오류 해결
1) 오류 발생 : 실행 순서 에러
SELECT
"proc_ymd", "userid",
COUNT("point") AS "count",
MAX("point") AS "max",
MIN("point") AS "min",
AVG("point") AS "average",
SUM("point") AS "sum"
FROM "learning_analytics"."e_point"
WHERE 1=1
AND "proc_ymd" = '20230324'
GROUP BY "proc_ymd", "userid"
HAVING "max" >= 30 -- ERROR) "max"란 컬럼은 없다고 나옴; 실행순서가 HAVING -> SLECET 이기 때문
;
2) 오류 해결
SELECT
"proc_ymd", "userid",
COUNT("point") AS "count",
MAX("point") AS "max",
MIN("point") AS "min",
AVG("point") AS "average",
SUM("point") AS "sum"
FROM "learning_analytics"."e_point"
WHERE 1=1
AND "proc_ymd" = '20230324'
GROUP BY "proc_ymd", "userid"
HAVING MAX("point") >= 30 -- 별칭 부여 전의 함수식으로 대체
;
