SQL 수업 예습

DODO·4일 전

sql

Review_for class

목록 보기

20/20

스키마와 테이블, 행과 열

Schema : 데이터베이스의 전체 구조, 데이터 타입, 제약 조건, 테이블 간의 관계 등을 정의한 메타데이터 == 설계

“”” 이 데이터베이스는 어떻게 생겼고, 어떤 데이터를 어떻게 저장할 것이다 ””” 라는 약속

역할 : 어떤 종류의 데이터를 어떤 형식으로 저장할 것인가 - 에 대한 규칙을 정함

주요 요소 : table, column, data_type, 제약조건(데이터의 무결성을 보장하기 위한 규칙 | PK,FK )
* 데이터 무결성이란?
데이터의 수명 주기 동안 정확성, 일관성, 유효성이 유지되고 승인 없이 변경되거나
손상되지 않은 상태

주요 유형 : 개체 무결성(PK | 고유값 | NOT NULL), 참조 무결성(FK | 테이블
간의 관계가 일관되게 유지되도록 보장), 도메인 무결성(필드의 데이터 타입, NULL여부, 허용된 값 범위 등 올바르게 입력되었는지 확인)

무결성이 중요한 이유 : 오류/실수 방지, 운영 효율성, 규정 준수

[ EX ]
CREATE TABLE Customers(
customer_id INT PRIMARY KEY, - 고객 ID, 정수형, 기본키
name VARCHAR(100) NOT NULL, - 이름, 최대 100자 문자열, NULL아님
email VARCHAR(255) UNIQUE, - 이메일, 최대 255자 문자열, 고유해야 함
phone_number VARCHAR(20) - 전화번호, 최대 20자 문자
);

SQL을 쓸 때는 거의 항상 데이터베이스.테이블 두 단어로 위치를 가리킴

EX) FROM pharma_sales.part_d_prescriber

SELECT 쿼리는 쓰는 순서와 읽는 순서가 다르다

읽는 순서
FROM : 먼저 어떤 테이블을 읽을지 정한다
WHERE : 원본 처방 행에서 주, 약물, 조건을 먼저 줄인다
SELECT : 강의와 분석에 필요한 컬럼만 남긴다
ORDER BY : 처방량, 비용처럼 볼 기준으로 줄을 세운다
LIMIT : 상위 몇 건만 꺼내 결과를 빠르게 확인한다

[ 집계 함수 ]

어떤 데이터를 집계하는 함수들을 의미.

COUNT
“ SELECT COUNT(*) FROM citykorea ; “
모든 레코드의 수를 출력하는 query

SUM
“ SELECT SUM(population) FROM citykorea ; ”
인구수 합계를 출력하는 query

MAX / MIN
“ SELECT name, max(population) FROM citykorea ; ”
가장 많은 인구수를 가진 도시의 이름과, 인구수를 출력한 query

AVG
“ SELECT AVG(population) FROM citykorea ; ”
citykorea의 평균 인구수를 출력하는 쿼리

[ Group Query ]

집계함수의 결과를 특정 컬럼을 기준으로 묶어 결과를 출력해주는 쿼리

GROUP BY
정의 : 같은 기준을 가진 여러 행을 한 행으로 접어 요약값을 만드는 문법
원본 행을 의사결정 단위로 접는다
원본에 대한 해상도는 떨어지지만 통합본은 볼 수 있음
SQL에서 GROUP BY에 넣는 함수를 제외하고 다 집계함수가 되어야 함
SELECT에 GROUP BY할 것만 둠
나머지 집계함수에 들어갈 것들을 COUNT() AS name
SELECT 와 GROUP BY 순서를 맞추면 좋음
“””
SELECT column, 집계함수(column)
FROM TABLE
GROUP BY column ;
“””

HAVING
정의 : GROUP BY로 만든 요약값에 조건을 거는 문법
WHERE과 차이 : WHERE는 집계 전 원본 형 필터, HAVING은 집계 후 그룹 필터
GROUP BY 이후에 생긴 SUM, AVG, COUNT 같은 요약값으로 거른다.
“””
SELECT column, 집계함수(column)
FROM TABLE
GROUP BY column
HAVING 집계함수(column) 부등호 data ;
“””

COUNT DISTINCT
중복을 제거한 고유한 행의 개수를 구하기 위해 적용
“ SELECT COUNT(DISTINCT name) FROM data_table ; ”
name 컬럼에서 중복을 제외한 이름의 총 개수

LEFT JOIN
왼쪽 테이블의 모든 데이터와 오른쪽 테이블에서 조건이 일치하는 데이터를 연결하여 반환하는 방
“””
SELECT *
FROM tableA A
LEFT JOIN tableB B
ON A.key = B.key ;
“””
table A는 항상 결과에 포함. A.key = B.key 값이 같은 행이 있다면 함께 출력,
없으면 table B자리에 NULL

[ CASE ]

기본 문법
CASE
WHEN 조건1 THEN 조건1 충족할 때 반환되는 값
WHEN 조건2 THEN 조건2 충족할 때 반환되는 값
ELSE 모든 조건 해당되지 않을 때 반환되는 값
END
WHEN - THEN 항상 같이 사용 | 조건문 마지막에 END 꼭 써주기
CASE 문을 사용할 때 별칭AS을 사용해주는 것이 좋음
--> EX
CASE WHEN total_claims >=100 THEN ‘고처방’ ELSE ‘저처방’ END AS name

[ DECIMAL ]

실수의 값을 정확하게 표현하기 위해 사용 , DECIMAL타입은 NUMERIC을 구현하여 만들어짐
DECIMAL(7,2) == -99999.99 ~ 99999.99 까지 실수를 저장할 수 있도록
DECIMAL(A,B) == A는 실수의 총 자릿수 | B는 소수 부분
“ CAST( table_name AS DECIMAL(num , num)) ”

[ with절 ]

임시 테이블 , 복잡한 서브쿼리를 가상의 테이블로 미리 정의해 재사용 할 수 있게 해주는 CTE 기능
쿼리를 단순화하고 가독성을 높일 수 있음

--> EX
WITH SalesEmployees AS (
SELECT FirstName, LastName, JobTitle, Salary
FROM employees e
JOIN departments d ON e.DepartmentID = d.DepartmentID
WHERE DepartmentName = ‘Sales’ )
SELECT FirstName, LastName, JobTitle, Salary
FROM SalesEmployees ;

[ WINDOW 함수 ]

정의 : 행을 줄이지 않고 계산값을 붙인다. 원본 행을 유지하고, 그룹 안에서 계산한 값을 새 컬럼으로 붙인다.
partition을 어떻게 나눌것인가? , order by 정렬 , row number/lag/sum 무엇을 넣을 것인가?
row_number : HCP별 가장 최근 접점 또는 첫 접점을 찾는다.
LAG : 이전 접점, 이전 처방량, 이전 방문값을 현재 행 옆에 붙인다
SUM OVER : 누적 지급액, 누적 처방량, 누적 방문 수를 만듦
원본 함수를 살리고 싶으면 window함수가 좋다.