SQL 수업 예습

Об этом предложении

스키마와 테이블, 행과 열 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 쿼리는 쓰는 순서와 읽는 순서가 다르다 쓰는 순서 SELECT | FROM | WHERE | AND | ORDER BY | LIMIT 읽는 순서 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함수가 좋다. [ 기본 문법 ] “ SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY column] [ODER BY column] [WINDOWING 절] ) FROM 테이블명 ; ” --> EX “ SELECT name, age, sal, sum(sal) OVER(ORDER BY sal ROWS BETWEEN unbounded preceding AND CURRENT row) 누적 연봉 FROM test” 처음부터 현재 행까지의 누적 GOOGLE_DRIVE