SQL Deep Dive : 마스터 링 창 함수, 공통 테이블 표현식 (CTE) 및 저장 절차

SQL은 세 가지 강력한 기능, 즉 창 함수, 공통 테이블 표현식 (CTE) 및 저장 프로 시저를 제공합니다. 1. 창 함수는 데이터 세트를 변경하지 않고 그룹화 및 정렬 작업을 허용합니다. 2. CTE는 복잡한 쿼리를 단순화하기 위해 임시 결과 세트를 제공합니다. 3. 저장된 절차는 효율성과 일관성을 향상시키기 위해 반복적으로 실행할 수있는 사전 컴파일 된 SQL 코드 블록입니다.

소개

데이터 중심의 세계에서 SQL은 쿼리 언어 일뿐 만 아니라 예술이기도합니다. 오늘날 우리는 SQL의 세 가지 강력한 기능, 즉 창 함수, 공통 테이블 표현식 (CTE) 및 저장 절차로 뛰어들 것입니다. 이 기사를 통해 이러한 도구를 사용하여 복잡한 데이터 문제를 처리하고 SQL 기술을 향상 시키며 데이터 분석 및 관리를 편하게하는 방법을 배웁니다.

기본 지식 검토

SQL의 매력은 단순성과 강력한 기능에 있습니다. 창 함수를 사용하면 데이터 세트를 변경하지 않고 데이터를 그룹화하고 정렬 할 수 있습니다. CTE는 복잡한 쿼리를보다 읽기 쉽고 관리 할 수있는 임시 결과 세트 방법을 제공합니다. 저장된 절차는 효율성과 일관성을 향상시키기 위해 반복적으로 실행할 수있는 사전 컴파일 된 SQL 코드 블록입니다.

핵심 개념 또는 기능 분석

창 함수

창 함수는 데이터 세트를 변경하지 않고 데이터를 그룹화하고 정렬 할 수있는 SQL의 마법 도구입니다. 이동 평균, 순위, 누적 합계 등을 계산하는 데 도움이 될 수 있기 때문에 데이터 분석에 매우 유용합니다.

 선택하다 
    Employee_id,
    급여,
    AVG_DEPARTMENT_SALARY로 AVG (SALARY) OVER (부서 별 파티),
    salary_rank로 랭크 () 오버 (Salary desc의 주문)
에서 
    직원;

이 예에서는 각 직원 부서의 평균 급여를 계산하고 급여에 따라 직원을 순위합니다. 윈도우 함수의 힘은 하위 쿼리 또는 자체 주인을 사용하지 않고 동일한 쿼리에서 여러 계산을 수행 할 수 있다는 것입니다.

공통 테이블 표현식 (CTE)

CTE는 SQL의 임시 결과 세트이며 복잡한 쿼리의 구조를 단순화하고 코드를보다 읽기 쉽고 유지 관리합니다. CTE는 재귀 쿼리에 특히 유용합니다.

 재귀 적 Employee_hierarchy as (
    Employee_id, manager_id, 0을 레벨로 선택하십시오
    직원들로부터
    여기서 manager_id가 null입니다
    모두
    e.employee_id, e.manager_id, eh.level 1을 선택하십시오
    직원으로부터 e
    E.Manager_id = eh.employee_id에서 Employee_hierarchy eh에 가입하십시오
))
Employee_hierarchy에서 *를 선택하십시오.

이 예에서는 CTE를 사용하여 직원의 계층 구조를 구축합니다. CTE의 재귀 적 특성을 통해 복잡한 자기 연결 쿼리를 작성하지 않고 직원 트리 전체를 쉽게 가로 질러 쉽게 통과 할 수 있습니다.

저장된 절차

저장된 절차는 반복적으로 실행될 수있는 SQL 코드의 사전 컴파일 블록입니다. 네트워크 트래픽과 컴파일 시간을 줄이기 때문에 복잡한 논리를 수행하거나 성능을 향상시켜야 할 때 유용합니다.

 프로 시저 생성 get_employee_details (in emp_id int)
시작하다
    선택하다 
        e.employee_id,
        e.first_name,
        e.last_name,
        d.department_name
    에서 
        직원 e
    가입하다 
        Department_id = d.department_id의 부서 d
    어디 
        e.employee_id = emp_id;
끝;

이 예에서는 직원 세부 정보를 얻기 위해 저장된 절차를 만듭니다. 저장된 절차의 장점은 복잡한 논리를 캡슐화하고 여러 번 호출하여 코드 재사용 성과 일관성을 향상시킬 수 있다는 것입니다.

사용의 예

기본 사용

창 함수의 기본 사용은 매우 간단합니다. OVER 절을 사용하여 창을 정의하고 다양한 집계 함수를 사용하여 결과를 계산할 수 있습니다.

 선택하다 
    product_id,
    sale_date,
    Sale_amount,
    sum (sale_amount) over (sale_date에 의해 product_id order by sale_date)
에서 
    매상;

이 예에서는 각 제품의 누적 판매량을 계산합니다. 조항 PARTITION BY 그룹 데이터와 ORDER BY Windows의 순서를 정의합니다.

CTE의 기본 사용도 매우 간단합니다. WITH 키워드를 사용하여 CTE를 정의한 다음 후속 쿼리로 참조 할 수 있습니다.

 top_sellers와 함께 (
    Product_id, sum (sale_amount)을 Total_sales로 선택하십시오
    판매에서
    product_id에 의해 그룹
    Total_sales desc의 주문
    제한 10
))
top_sellers에서 *를 선택하십시오.

이 예에서는 CTE를 사용하여 판매가 가장 높은 10 개의 제품을 찾습니다. CTE는 쿼리 구조를 더 명확하고 쉽게 관리 할 수 ​​있도록합니다.

저장된 절차의 기본 사용도 매우 간단합니다. CREATE PROCEDURE 문을 사용하여 저장된 프로 시저를 정의한 다음 CALL 문을 사용하여 호출 할 수 있습니다.

 get_employee_details (1);

이 예에서는 이전에 정의 된 저장된 절차를 호출하여 직원 ID 1로 직원 세부 정보를 얻습니다.

고급 사용

창 함수의 고급 사용에는 ROWS 또는 RANGE 조항을 사용하여 창의 범위를 정의하고 전면 및 후면 행의 데이터에 액세스하기 위해 LAG 및 LEAD 기능을 사용하는 것이 포함됩니다.

 선택하다 
    product_id,
    sale_date,
    Sale_amount,
    lag (sale_amount) Over (prevery_date의 product_id 주문에 의한 파티션), previous_sale,
    lead (sale_amount) over (sale_date의 product_id 주문에 의한 파티션) next_sale으로
에서 
    매상;

이 예에서는 LAG 및 LEAD 기능을 사용하여 각 제품의 이전 및 다음 판매를 얻습니다. 이러한 고급 사용량은보다 복잡한 데이터 분석을 수행하는 데 도움이 될 수 있습니다.

CTE의 고급 사용에는 재귀 CTE를 사용하여 계층 적 데이터를 처리하고 복잡한 쿼리를 단순화하기 위해 여러 CTE를 사용하는 것이 포함됩니다.

 재귀 범주 _hierarchy as (
    범주 _id, parent_category_id, 0을 레벨로 선택하십시오
    카테고리에서
    여기서 parent_category_id는 null입니다
    모두
    C.category_id, c.parent_category_id, ch.level 1을 선택하십시오
    카테고리에서 c
    c.parent_category_id = ch.category_id에서 category_hierarchy ch에 가입하십시오
),)
product_categories as (
    p.product_id, ch.category_id, ch.level을 선택하십시오
    제품에서 p
    p.category_id = ch.category_id에서 category_hierarchy ch에 가입하십시오
))
Product_Categories에서 *를 선택하십시오.

이 예에서는 재귀 CTE를 사용하여 제품 범주의 계층 구조를 구축 한 다음 다른 CTE를 사용하여 제품을 해당 카테고리와 연결합니다. 이러한 고급 사용량은 복잡한 계층 적 데이터를 처리하는 데 도움이 될 수 있습니다.

저장된 절차의 고급 사용에는 복잡한 비즈니스 논리를 구현하기 위해 커서 사용, 예외 처리 및 거래 관리가 포함됩니다.

 프로 시저 작성 update_employee_salary (emp_id int, new_salary decimal (10, 2))
시작하다
    Sqlexection에 대한 Exit 핸들러를 선언하십시오
    시작하다
        롤백;
        사임;
    끝;

    트랜잭션 시작;
    직원 업데이트
    SALARY = NEW_SALARY를 설정하십시오
    여기서 Employee_id = emp_id;
    저지르다;
끝;

이 예에서는 직원 급여를 업데이트하기위한 저장된 절차를 만듭니다. 저장된 절차는 트랜잭션 관리 및 예외 처리를 사용하여 데이터 일관성 및 무결성을 보장합니다.

일반적인 오류 및 디버깅 팁

창 함수를 사용할 때 일반적인 실수는 OVER 절을 사용하는 것을 잊어 버리는 것입니다. 이로 인해 SQL 엔진이 창 함수를 올바르게 구문 분석하지 못하게됩니다.

 - 오류 예제 선택 
    Employee_id,
    급여,
    AVG (SALARY) - 조항에서 누락 
    직원;

이 오류를 피하려면 창 함수를 사용할 때 OVER 항이 항상 포함되어 있는지 확인하십시오.

CTE를 사용할 때 일반적인 실수는 CTE의 모든 필수 열을 정의하는 것을 잊어 버리는 것입니다. 이로 인해 후속 쿼리는 CTE에서 데이터를 올바르게 참조하지 못합니다.

 - top_sellers as (top_sellers as)의 오류 예제
    Product_ID를 선택하십시오 -Total_Sales 열은 판매에서 누락되었습니다
    product_id에 의해 그룹
    Total_sales desc의 주문
    제한 10
))
top_sellers에서 *를 선택하십시오.

이 오류를 피하려면 CTE를 정의 할 때 필요한 모든 열을 포함하십시오.

저장된 절차를 사용할 때 일반적인 실수는 예외를 처리하는 것을 잊는 것입니다. 이로 인해 저장된 절차가 오류가 발생할 때 트랜잭션을 올바르게 롤백하지 못할 수 있습니다.

 - 오류 예제 프로 시저 생성 update_employee_salary (emp_id int, in new_salary decimal (10, 2))
시작하다
    직원 업데이트
    SALARY = NEW_SALARY를 설정하십시오
    여기서 Employee_id = emp_id;
끝;

이 오류를 피하려면 저장된 절차에 예외 처리 및 트랜잭션 관리를 포함시켜야합니다.

성능 최적화 및 모범 사례

창 함수를 사용하는 경우 성능 최적화의 핵심 사항은 적절한 창 프레임을 선택하는 것입니다. ROWS 이나 RANGE 조항을 사용하면 쿼리 성능이 크게 향상 될 수 있습니다.

 - 최적화 예제 선택 
    product_id,
    sale_date,
    Sale_amount,
    sum (sale_amount) Over (runching_total과 같이 Unbounded Preceding과 현재 행 사이의 Sale_Date 행에 의한 Product_id 순서에 의한 파티션)
에서 
    매상;

이 예에서는 ROWS 절단을 사용하여 쿼리 성능을 향상시킬 수있는 창 프레임 워크를 정의합니다.

CTE를 사용할 때 성능 최적화의 핵심 사항은 CTE에서 복잡한 계산을 사용하지 않는 것입니다. CTE는 복잡한 계산이 포함 된 경우 쿼리 성능에 영향을 줄 수있는 임시 결과 세트입니다.

 - sales_summary as (sales_summary as)의 최적화 예제
    Product_id, sum (sale_amount)을 Total_sales로 선택하십시오
    판매에서
    product_id에 의해 그룹
))
sales_summary에서 *를 선택하십시오.

이 예에서는 쿼리 성능을 향상시키기 위해 CTE 이외의 복잡한 계산을 넣습니다.

저장 프로 시저를 사용하는 경우 성능 최적화의 핵심 사항은 저장된 절차에서 커서를 사용하지 않는 것입니다. 커서는 데이터 라인별로 처리해야하므로 성능 저하를 유발할 수 있습니다.

 - 최적화 예제 프로 시저 업데이트 update_employee_salaries ()
시작하다
    직원 업데이트
    급여 설정 = 급여 * 1.1;
끝;

이 예에서는 커서를 사용하지 않고 배치 업데이트 작업을 사용하여 성능을 향상시킵니다.

SQL 코드를 작성할 때 모범 사례에는 의미있는 별칭 사용, 코드 주석, 코드를 읽을 수 있고 유지 관리 가능하게 유지하는 것이 포함됩니다.

 - 모범 사례 예제 선택 
    e.employee_id as Emp_id, - 의미있는 별칭 사용 e.first_name, - 댓글 코드 e.last_name,
    D.DEPARTMENT_NAME- 코드 가독성과 유지 관리를 유지하십시오 
    직원 e
가입하다 
    e.department_id = d.department_id의 부서 d;

이러한 모범 사례를 따르면보다 효율적이고 유지 관리 가능한 SQL 코드를 작성할 수 있습니다.

SQL을 탐색하는 과정에서, 우리는 창 함수, CTE 및 저장 절차의 기본 및 고급 사용량을 마스터했을뿐만 아니라 일반적인 오류를 피하고 성능을 최적화하는 방법을 배웠습니다. 이 기사가 이러한 강력한 SQL 기능을 더 잘 이해하고 적용하고 데이터 분석 및 관리에서 더 큰 성공을 달성하도록 도와주기를 바랍니다.

위 내용은 SQL Deep Dive : 마스터 링 창 함수, 공통 테이블 표현식 (CTE) 및 저장 절차의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!