[240104] SQL: 특강/코드카타 91 & 파이썬: 코드카타 6-7

[SQL 특강: SQL 처음부터 끝까지] 

1. SQL 기초(초급)

 

- 데이터베이스 및 테이블 생성, 데이터 삽입 예시 쿼리 

--1.데이터베이스 생성
CREATE DATABASE IF NOT EXISTS SampleDB;

--2.데이터베이스 사용
USE SampleDB;

--3.테이블 생성
CREATE TABLE IF NOT EXISTS Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Position VARCHAR(50),
    Salary DECIMAL(10, 2)
);

--4.데이터 삽입 
INSERT INTO Employees (ID, Name, Position, Salary)
VALUES (1, 'Alice', 'Developer', 60000.00),
       (2, 'Bob', 'Designer', 55000.00);

--5.데이터 조회
SELECT * FROM Employees;

 

- 데이터베이스 및 테이블 목록 조회

--데이터베이스 목록 조회
SHOW DATABASES;

--특정 데이터베이스의 테이블 목록 조회
USE SampleDB;
SHOW TABLES;

 

- 기본 구성 요소 : 데이터베이스(Database), 테이블(Table), 필드(Field/열), 레코드(Record/) 

 

- SQL 문법 

└ DDL: CREAT(DB나 테이블 생성), ALTER (DB 수정), DROP (DB나 테이블 삭제)

└ DML: INSERT(데이터 삽입), SELECT(데이터 조회), UPDATE(데이터 수정), DELETE(데이터 삭제)

└ DCL: GRANT(권한 부여), REVOKE(권한 제거)  

└ TCL : COMMIT(트랜잭션 변경 사항 확정), ROLLBACK (트랜잭션 변경 사항 취소)

 

- SQL 기초 쿼리 (자주 안 쓴 쿼리만) 

·  문자열 함수 

└ TRIM: 문자열 앞뒤 공백을 제거

└ LOWER: 문자열을 소문자로 변환
└ UPPER: 문자열을 대문자로 변환

· 수치형 함수

└  ABS: 절대값을 반환

 

2. 중급 SQL 

- 서브쿼리: 데이터 용량이 큰 경우에는 사용을 자제하기도 함. 

 

- 집합연산 

└ UNION: 두 개 이상의 쿼리 결과를 결합하여 중복을 제거한 단일 결과 집합을 생성

   * UNION ALL은 중복 포함 
└ INTERSECT: 두 쿼리 결과에서 공통되는 요소만을 선택하여 새로운 결과 집합을 생성
└ EXCEPT: 첫 번째 쿼리 결과에서 두 번째 쿼리 결과를 제외한 요소들로 구성된 결과 집합을 생성

 

- 윈도우 함수 

└ 이동평균(MOVING AVERAGE): 시간에 따른 데이터의 추세나 패턴을 이해하는 데 도움되는 함수로 ORDER 절에 사용

 * ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING >> ORDER BY 칼럼 기준으로 위에 행 1개와 아래행 1개의 평

SELECT
    EmployeeID,
    SaleDate,
    AVG(SaleAmount) OVER (ORDER BY SaleDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingAvg
FROM
    Sales;

 

- LEAD(): 현재 행으로부터 지정된 수만큼 이후의 행 참조
- LAG(): 현재 행으로부터 지정된 수만큼 이전의 행 참조

 * LEAD/LAG(칼럼명, 기준 행 수, 행 없을 때 반환할 기본값 - 미설정시 NULL로 반환)

SELECT
    EmployeeID,
    Department,
    SaleAmount,
    LEAD(SaleAmount, 1) OVER (PARTITION BY Department ORDER BY SaleDate) AS NextSale,
    LAG(SaleAmount, 1) OVER (PARTITION BY Department ORDER BY SaleDate) AS PrevSale
FROM
    Sales;

 

+) LEAD/LAG와 PRECEDING/FOLLOWING의 차이는? 

① LEAD/LAG: 현재 행을 기준으로 이전/이후 값을 참조하는 함수  

② PRECEDING/FOLLOWING: 윈도우 함수가 적용될 행이나 값의 범위를 지정. 윈도우 함수의 WHERE 역할

 

└ ROWS는 행의 수를 선택할 때 사용하고 RANGE는 값의 범위를 선택할 때 사용

└ UNBOUNDED PRECEDING은 최상위(맨 위) 행, UNBOUNDED FOLLOWING은 최하위(맨 밑) 행

--현재 행을 기준으로 이후 10개 행의 값 
RANGE BETWEEN CURRENT ROW AND 10 FOLLOWING

--맨 위 행부터 현재 행까지의 행의 수 
ROWS UNBOUNDED PRECEDING  #시작점만 적으면 CURRENT NOW까지로 자동 계산

--현재 행으로부터 위로 10개, 아래로 10개 행의 값
RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING

 

- 뷰(Views): 재사용 가능한 가상 테이블. 실제 데이터베이스에 저장되지 않음. 특정 데이터만 사용자에게 노출 가능(보안).

 └ with문은 view와 유사하나 일회용으로만 사용 가능

-- VIEW 생성
CREATE VIEW SalesSummary AS
                        SELECT Department, SUM(SaleAmount) AS TotalSales
                        FROM Sales
                        GROUP BY Department;

-- VIEW 사용
SELECT * FROM SalesSummary;

 

- 인덱스(INDEX): 데이터 검색 속도를 향상시키는 중요한 기능.

 └  별도 저장공간을 차지하고, 데이터 수정 시 추가 연산 필요한 점은 단점

-- 인덱스 생성
--'Sales' 테이블의 'Department' 컬럼에 인덱스를 생성
CREATE INDEX idx_department ON Sales(Department); 

-- 인덱스 사용
-- 생성된 인덱스를 활용해 'Sales' 테이블 내 'Department'에 속한 데이터를 빠르게 검색
SELECT * FROM Sales WHERE Department = 'Sales';

 

- EXPLAIN: 도구를 사용하여 쿼리 실행 계획을 분석하고 성능을 평가합니다.

 

- SQL 쿼리 작성 시 주요 팁

└ 명확하고 간결한 쿼리 구성: 쿼리의 목적에 맞게 필요한 데이터만 선택. * 보다는 전체 칼럼명을 기재
└ 적절한 명명 규칙 사용: 테이블과 컬럼에 명확한 이름을 사용. 별칭도 이니셜보다는 약어로 지정
└ 주석 사용: 다른 사용자가 쿼리를 이해하고 수정하기 쉽도록 쿼리의 목적과 구조 설명
└ 복잡한 쿼리의 단계적 구성: 큰 쿼리는 여러 단계로 나누어 작성해 복잡성을 줄이고 가독성 개선 

---

[SQL 코드카타]

 

1. 피벗테이블 복습을 활용한 문제 풀이  

620. Not Boring Movies https://leetcode.com/problems/not-boring-movies/

 

1) 어떤 문제가 있었나

유저 id 중 홀수를 추출하는 조건에서 적절한 함수가 떠오르지 않음 

 

2) 내가 시도해본 건 무엇인가

구글링을 통해 mod 함수를 알아보다가 SQLD 강의에서 %로 나머지를 계산할 수 있었다는 점을 기억해 TIL자료 복습 후 활용 

 

3) 어떻게 해결했나

id를 2로 나눴을 때 나머지가 0(짝수)이 아닌 값을 조건으로 설정

나머지 연산 시 짝수는 무조건 2로 나누어져 나머지 값이 0이 됨 

select id, movie, description, rating
  from Cinema
where description <> 'boring' 
      and id%2 <> 0   --- mod 함수 활용 가능 / MOD(id, 2) <> 0
order by rating desc

 

4) 무엇을 새롭게 알았나

- MOD(숫자1, 숫자2): 숫자1을 숫자2로 나누어 나머지 값을 계산하는 함수. % 연산자로 대체 가능

 

 

[파이썬 코드카타]

- 나누기한 값을 정수로 출력: int() 함수를 사용하거나, 혹은 // 로 나누기 하면 소수점 버려지고 몫만 

1) answer = int((num1/num2)*1000)
    return answer

2) answer = (num1/num2)*1000)
    return answer//1 

 

(cf) 정수 % 정수 = 몫은 버리고 나머지만 / 정수 ** 정수 거듭제곱  

- answer은 지역변수로 활용 시 비용이 증가하므로, 풀이할 때 곧바로 return에 답을 적어도 됨