Data

[1028 SQL]Ⅱ. SQL 기본과 활용-제2장 서브쿼리, 집합연산자, 그룹함수, 윈도우함수, Top N쿼리, 계층형, 뷰

출처: SQL 전문가 가이드 : 네이버 도서 (naver.com)  & Chat GPT

 

SQL 전문가 가이드 : 네이버 도서

네이버 도서 상세정보를 제공합니다.

search.shopping.naver.com

 

과목 Ⅰ. 데이터 모델링의 이해

과목 Ⅱ. SQL 기본과 활용

제2장 SQL 활용
제1절 서브 쿼리

서브쿼리 1. 서브쿼리는 괄호로 감싸서 사용. 
2. 단일행, 복수행 비교 연산자와 함께 사용가능. 
3. 서브쿼리는 order by  사용불가. 
  연관/비연관 서브쿼리 un-correlated
비연관 서브쿼리		 서브쿼리가 메인쿼리 컬럼을 가지고 있지않음, 메인쿼리값제공

SELECT EmployeeID, Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

  correlated
연관 서브쿼리		 서브쿼리 메인쿼리 컬럼 가짐, 서브쿼리에서 조건이 맞는지 확인용
ex) 비교연산, 다중행. 범위, 조건정함

SELECT EmployeeID, Name
FROM Employees e
WHERE EXISTS (
    SELECT 1
    FROM Departments d
    WHERE d.DepartmentID = e.DepartmentID
      AND d.Location = 'New York'
);

  반환되는 데이터수 단일행서브쿼리 비교연산자(=,<,<=,>=,<>)와 함께 사용될때 반드시 1건. 
아니면 Run Time 오류.
  다중행 서브쿼리 in, all, any, some, exists, having
  any = SOME SELECT EmployeeID, Name
FROM Employees
WHERE Salary < ANY (SELECT Salary FROM Employees WHERE DepartmentID = 1);
  all SELECT EmployeeID, Name
FROM Employees
WHERE Salary > ALL (SELECT Salary FROM Employees WHERE DepartmentID = 1);
  다중컬럼 서브쿼리 in
  그외위치사용 select, from , having, update 의 set, insert문의 value


제2절 집합 연산자

집합연산자 select ..
[UNION|UNION ALL | INTERSECT | MINUS]
select..
[ORDER BY 컬럼 [ASC/DESC]]
  UNION 합집합 (중복 1번만)  
  UNION ALL 합집합(중복반복포함) UNION 보다 자원효율적
  INTERSECTION 교집합  
  DIFFERENCE
Except
Minus		 차집합 부정연산(<>)은 인덱스를 사용못함 차집합이용해서 인덱스사용가능. 
  PRODUCT   곱집합, cross product


제3절 그룹 함수

ANSI/ISO SQL 표준 데이터분석    
     1. aggregate function 집계함수 count, sum, avg, min, max
     2. group function 그룹함수 group_concat(), string_agg(), count(distint)
     3. window function   윈도우함수 over(), rownum(), rank()
그룹함수   INSERT INTO sales VALUES
(2023, 1, 'Product A', 1000.00)
, (2023, 1, 'Product B', 1500.00)
, (2023, 2, 'Product A', 1200.00)
, (2023, 2, 'Product B', 1700.00);
  ROLLUP 연도별, 월별 합계계산시 유용

SELECT year, month, SUM(sales_amount) as total_sales
FROM sales
GROUP BY ROLLUP (year, month);		 year   month total_sales 
2023  1         2500.00
2023  2         2900.00
2023 NULL   5400.00
  CUBE 가능한 모든 조합에 대해 데이터 요약

SELECT year, month, product, SUM(sales_amount) as total_sales
FROM sales
GROUP BY CUBE (year, month, product);		 year month product total_sales 
2023 1 Product A 1000.00 
2023 1 Product B 1500.00 
2023 1 NULL 2500.00 
2023 2 Product A 1200.00 
2023 2 Product B 1700.00 
2023 2 NULL 2900.00 
2023 NULL Product A 2200.00 
2023 NULL Product B 3200.00 
2023 NULL NULL 5400.00 
NULL 1 Product A 1000.00 
NULL 1 Product B 1500.00 
NULL 1 NULL 2500.00 
NULL 2 Product A 1200.00 
NULL 2 Product B 1700.00 
NULL 2 NULL 2900.00 
NULL NULL NULL 5400.00
  GROUPING SET 사용자 지정조합에 데이터 요약

SELECT year, SUM(sales_amount) as total_sales
FROM sales
GROUP BY GROUPING SETS ( (year), () );		 year total_sales 
2023 5400.00 
NULL 5400.00
       
       
  GROUP_CONCAT()
STRING_AGG()		 -- SQL Server
SELECT DepartmentID, STRING_AGG(Name, ', ') AS EmployeeNames
FROM Employees
GROUP BY DepartmentID;

-- MySQL
SELECT DepartmentID, GROUP_CONCAT(Name) AS EmployeeNames
FROM Employees
GROUP BY DepartmentID;		  


제4절 윈도우 함수

윈도우함수 SELECT WINDOW_FUNCTION (ARGUMENTS) OVER
( [PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절] )
FROM 테이블명 ;
특정 윈도우(행의범위)에서 행에대한 계산
  순위관련 RANK SELECT EmployeeID, Name, Salary,
       RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rank
FROM Employees;
  DENSE_RANK SELECT EmployeeID, Name, Salary,
       DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DenseRank
FROM Employees;
  ROW_NUMBER SELECT EmployeeID, Name, Salary,
       ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum
FROM Employees;
  집계
aggregate		 NTILE(4) OVER 
분위수.급여가 높은 4개까지(중복반복)		 SELECT EmployeeID, Name, Salary,
       NTILE(4) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Quartile
FROM Employees;
  SUM(Salary) OVER
누적급여		 SELECT EmployeeID, Name, Salary,
       SUM(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary) AS CumulativeSalary
FROM Employees;
  AVG(Salary) OVER
현재행, 그전 2명까지 평균급여		 SELECT EmployeeID, Name, Salary,
       AVG(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Employees;
  MAX SELECT EmployeeID, Name, Salary,
       MAX(Salary) OVER (PARTITION BY DepartmentID) AS MaxSalary
FROM Employees;
  MIN SELECT EmployeeID, Name, Salary,
       MIN(Salary) OVER (PARTITION BY DepartmentID) AS MinSalary
FROM Employees;
  AVG SELECT EmployeeID, Name, Salary,
       AVG(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Employees;
  COUNT SELECT EmployeeID, Name, Salary,
       AVG(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Employees;
  행순서 first_value SELECT EmployeeID, Name, Salary,
       FIRST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary) AS FirstSalary
FROM Employees;
  last_value SELECT EmployeeID, Name, Salary,
       LAST_VALUE(Salary) OVER (PARTITION BY DepartmentID ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSalary
FROM Employees;
  lag
현재행 이전행		 SELECT EmployeeID, Name, Salary,
       LAG(Salary, 1) OVER (PARTITION BY DepartmentID ORDER BY Salary) AS PreviousSalary
FROM Employees;
  lead
현재행 지정된 다음행		 SELECT EmployeeID, Name, Salary,
       LEAD(Salary, 1) OVER (PARTITION BY DepartmentID ORDER BY Salary) AS NextSalary
FROM Employees;
  비율 cume_dist
누적분포		 SELECT EmployeeID, Name, Salary,
       CUME_DIST() OVER (PARTITION BY DepartmentID ORDER BY Salary) AS CumulativeDistribution
FROM Employees;
  순위가 현재의 행값보다 작거나 같은행수 / 총 행의수
  percent_rank
상대적순위를 백분율로		 SELECT EmployeeID, Name, Salary,
       PERCENT_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary) AS PercentRank
FROM Employees;
  현재행의순위 -1 / 총행의수 -1
  NTILE
n개 그룹으로 나누고 각 행에 그룹번호 반환
[n타일(동일크기)]		 SELECT EmployeeID, Name, Salary,
       NTILE(4) OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Quartile
FROM Employees;
  급여순으로 그룹을 나누어 그룹번호 부여
- 상위 25%  => 1
- 25~50 %  => 2
- 50~75 %  => 3
- 75~100% => 4
  ratio_to_report
현재 행의 값을 값의 합계에 대한비율		 SELECT EmployeeID, Name, Salary,
       RATIO_TO_REPORT(Salary) OVER (PARTITION BY DepartmentID) AS SalaryRatio
FROM Employees;
  현재행의 컬럼값 / 전채헹의 컬럼값. 

제5절 Top N 쿼리

Top N 쿼리    
  rownum   인라인뷰 처리
  top(n)   order by 가 적용된 n번째로까지 조회
  row limiting    


제6절 계층형 질의와 셀프 조인

계층형  Hierarchical queries
Oracle 질의어 start with  루트데이터지정
connect by 조인지점
prior 자식=부모  : 부모 -> 자식
부모=자식 : 
noncycle 순환 발생해도 쿼리진행
order by SIBLINGS by 형제노드를 이름으로 정렬. 

SELECT 
    employee_id,
    name,
    manager_id,
    LEVEL,
    CONNECT_BY_ISLEAF AS is_leaf,
    CONNECT_BY_ISCYCLE AS is_cycle
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR employee_id = manager_id
ORDER SIBLINGS BY 
    name;

where  
level  
connect_by_isleaf 해당 데이터가 리프데이터이면 1 아니면 0

SELECT 
    employee_id,
    name,
    manager_id,
    LEVEL,
    CONNECT_BY_ISLEAF AS is_leaf
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR employee_id = manager_id;

connect_by_iscycle 해당 데이터가  순환이 발생하면 1

SELECT 
    employee_id,
    name,
    manager_id,
    LEVEL,
    CONNECT_BY_ISLEAF AS is_leaf,
    CONNECT_BY_ISCYCLE AS is_cycle
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    NOCYCLE PRIOR employee_id = manager_id;

함수 sys_connect_by_path

SELECT 
    employee_id,
    name,
    manager_id,
    LEVEL,
    SYS_CONNECT_BY_PATH(name, ' -> ') AS path
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR employee_id = manager_id
ORDER SIBLINGS BY 
    name;

connect_by_root SELECT 
    employee_id,
    name,
    manager_id,
    LEVEL,
    CONNECT_BY_ROOT name AS root_name
FROM 
    employees
START WITH 
    manager_id IS NULL
CONNECT BY 
    PRIOR employee_id = manager_id
ORDER SIBLINGS BY 
    name;
sql server with CTE 
Common Table Expression

- Anchor Member:
- Recursive Member		 WITH EmployeeHierarchy AS (
    SELECT 
        EmployeeID,
        Name,
        ManagerID,
        0 AS Level,
        CAST(Name AS NVARCHAR(MAX)) AS Path
    FROM 
        Employees
    WHERE  
        ManagerID IS NULL     --Anchor Member:
    UNION ALL
    SELECT 
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.Path + ' -> ' + e.Name AS NVARCHAR(MAX))
    FROM 
        Employees e
    INNER JOIN 
        EmployeeHierarchy eh    --Recursive Member
    ON 
        e.ManagerID = eh.EmployeeID   
)
SELECT 
    EmployeeID,
    Name,
    ManagerID,
    Level,
    Path
FROM 
    EmployeeHierarchy
ORDER BY 
    Path;
Path  
Level  


제7절 PIVOT 절과 UNPIVOT 절
제8절 정규 표현식

view virtual table 가상테이블
장점 독립성 테이블 구조가 변경되도 뷰를 사용하는 곳은 변경안해도됨
편리성 복잡한 질의를 단순하게
보안성 숨기고 싶은 정보 감춤
       
       
       
       

'Data' 카테고리의 다른 글

[1028 SQL]Ⅲ. SQL 고급 활용 및 튜닝-제1장 SQL 수행 구조-아키텍처, I/O  (0) 2024.06.24
[1028 SQL]Ⅱ. SQL 기본과 활용-제3장 DML, TCL, DDL, DCL, 절차형SQL(프로시저)  (0) 2024.06.24
[1028 SQL]Ⅱ. SQL 기본과 활용-제1장 SQL, ERD, 함수(집계함수 외), select 외  (0) 2024.06.20
[1028 SQL]Ⅰ. 데이터 모델링-제2장 정규화, JOIN, 트랜잭션, null, 본질식별자&인조식별자  (0) 2024.06.19
[1028 SQL]Ⅰ. 데이터 모델링-제1장 모델링, 엔터티, 속성, 관계(ERD), 식별자  (1) 2024.06.18

    티스토리툴바