Common Table Expression(CTE; 공통 테이블 표현)을 사용하는 경우

Common Table Expression(CTE; 공통 테이블 표현)을 사용하는 경우

Common Table Expression에 대해 읽기 시작했는데 사용할 필요가 있는 사용 사례가 생각나지 않습니다.파생된 테이블에서도 동일한 작업을 수행할 수 있기 때문에 중복된 것으로 보입니다.제가 뭔가 놓치거나 잘 이해하지 못하는 부분이 있나요?CTE의 경우를 만들기 위해 정기적인 선택, 파생 또는 임시 테이블 쿼리에 대한 제한의 간단한 예를 들어줄 수 있습니까?어떤 간단한 예라도 매우 감사할 것입니다.

예를 들어, 같은 데이터 세트를 여러 번 참조/조인해야 하는 경우 CTE를 정의함으로써 이를 수행할 수 있습니다.따라서 코드 재사용의 한 형태가 될 수 있습니다.

자기 참조의 예는 재귀입니다.CTE를 사용한 재귀 쿼리

온라인 서적에서 가져온 Microsoft 정의:

CTE는 다음 용도로 사용할 수 있습니다.

• 재귀 쿼리를 만듭니다.자세한 내용은 일반 테이블 식을 사용한 재귀 쿼리를 참조하십시오.

• 뷰를 일반적으로 사용할 필요가 없는 경우 보기를 대체합니다. 즉, 정의를 메타데이터에 저장할 필요가 없습니다.

• 스칼라 하위 선택에서 파생된 열 또는 결정적이지 않거나 외부 액세스 권한이 있는 함수에 의한 그룹화를 활성화합니다.

• 같은 문장에서 결과 표를 여러 번 참조합니다.

복잡한 쿼리, 특히 복잡한 조인 및 하위 쿼리를 분할하는 데 사용합니다.질의의 의도를 이해하는 데 도움이 되는 '의사 뷰'로 점점 더 많이 사용하고 있다는 것을 알게 되었습니다.

그것들에 대한 나의 유일한 불만은 그것들을 재사용할 수 없다는 것이다.예를 들어 동일한 CTE를 사용할 수 있는2개의 update 스테이트먼트를 가진 stored proc가 있는 경우가 있습니다.그러나 CTE의 '범위'는 첫 번째 질문일 뿐입니다.

문제는 '단순한 예'는 CTE가 필요하지 않을 수 있다는 것입니다.

그래도 아주 편리하네요.

cte를 사용하는 이유는 두 가지가 있습니다.

where 절에서 계산된 값을 사용합니다.이것은 파생표보다 조금 더 깨끗한 것 같습니다.

질문 및 답변과 질문이 결합된 두 개의 표가 있다고 가정합니다.ID = 응답합니다.Question_Id(및 퀴즈 ID)

WITH CTE AS
(
    Select Question_Text,
           (SELECT Count(*) FROM Answers A WHERE A.Question_ID = Q.ID) AS Number_Of_Answers
    FROM Questions Q
)
SELECT * FROM CTE
WHERE Number_Of_Answers > 0

다음은 질문과 답변 목록을 얻고자 하는 또 다른 예입니다.답변과 결과의 질문을 그룹화해 주세요.

WITH cte AS
(
    SELECT [Quiz_ID] 
      ,[ID] AS Question_Id
      ,null AS Answer_Id
          ,[Question_Text]
          ,null AS Answer
          ,1 AS Is_Question
    FROM [Questions]

    UNION ALL

    SELECT Q.[Quiz_ID]
      ,[Question_ID]
      ,A.[ID] AS  Answer_Id
      ,Q.Question_Text
          ,[Answer]
          ,0 AS Is_Question
        FROM [Answers] A INNER JOIN [Questions] Q ON Q.Quiz_ID = A.Quiz_ID AND Q.Id = A.Question_Id
)
SELECT 
    Quiz_Id,
    Question_Id,
    Is_Question,
    (CASE WHEN Answer IS NULL THEN Question_Text ELSE Answer END) as Name
FROM cte    
GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question 
order by Quiz_Id, Question_Id, Is_Question Desc, Name

CTE를 사용하는 데 유용한 시나리오 중 하나는 하나 이상의 열을 기반으로 데이터 행을 가져오지만 테이블의 모든 열을 반환하는 경우입니다.표준 쿼리를 사용할 경우 먼저 개별 값을 임시 테이블에 덤프한 다음 원래 테이블에 다시 결합하여 나머지 열을 가져오거나 결과를 한 번에 반환할 수 있지만 대부분의 경우 읽을 수 없고 성능 문제가 발생합니다.

단, CTE를 사용함으로써(Tim Schmelter가 응답한 대로 레코드의 첫 번째 인스턴스를 선택합니다)

WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
    FROM myTable 
)
SELECT * FROM CTE
WHERE RN = 1

보다시피, 이것은 읽고 유지보수가 훨씬 더 쉽습니다.다른 쿼리에 비해 퍼포먼스가 훨씬 뛰어납니다.

CTE를 단일 쿼리에 사용되는 뷰의 대용품으로 생각하는 것이 더 의미 있는 일이 될 수 있습니다.그러나 공식적인 뷰의 오버헤드, 메타데이터 또는 지속성이 필요하지 않습니다.다음과 같은 경우에 매우 유용합니다.

• 재귀 쿼리를 만듭니다.
• 질의에서 CTE 결과 세트를 여러 번 사용합니다.
• 동일한 서브쿼리의 큰 청크를 줄임으로써 쿼리의 명확성을 높일 수 있습니다.
• CTE 결과 집합에서 파생된 열을 기준으로 그룹화 사용

다음으로 간단한 예를 제시하겠습니다.

WITH [cte_example] AS (
SELECT 1 AS [myNum], 'a num' as [label]
UNION ALL
SELECT [myNum]+1,[label]
FROM [cte_example]
WHERE [myNum] <=  10
)
SELECT * FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_all' FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_odd' FROM [cte_example] WHERE [myNum] % 2 = 1
UNION
SELECT SUM([myNum]), 'sum_even' FROM [cte_example] WHERE [myNum] % 2 = 0;

즐거운 시간 되세요.

오늘은 SQL Server 2005에서 도입되어 이후 버전에서도 사용할 수 있는 새로운 기능인 Common table expression에 대해 알아보겠습니다.

[Common table expression] :- 공통 테이블식은 임시 결과 집합으로 정의하거나 SQL Server에서 보기를 대체할 수 있습니다.공통 테이블 표현은 정의된 문 배치에서만 유효하며 다른 세션에서는 사용할 수 없습니다.

CTE(Common Table Expression) 선언 구문:-

with [Name of CTE]
as
(
Body of common table expression
)

예를 들어 보겠습니다.-

CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)

insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')

CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

테이블 직원과 부서 두 개를 만들고 각 테이블에 5개의 행을 삽입했습니다.이제 이 테이블을 결합하여 임시 결과 세트를 생성하여 더 사용할 수 있도록 하겠습니다.

With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee 
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

문장의 한 줄씩 읽고 이해합시다.

CTE를 정의하려면 "with" 절을 쓴 다음 테이블 식에 이름을 지정합니다. 여기서는 "CTE_Example"로 이름을 지정합니다.

다음으로 "As"라고 쓰고 코드를 2개의 괄호(---)로 묶습니다.따라서 여러 테이블을 괄호로 묶을 수 있습니다.

마지막 줄에서는 "Select * from CTE_Example"을 사용했습니다.이거는 코드의 마지막 줄에 있는 Common table expression을 참조하고 있습니다.따라서 뷰와 비슷하다고 할 수 있습니다.이 뷰는 단일 배치로 정의 및 사용되며 CTE는 영구 객체로 데이터베이스에 저장되지 않습니다.하지만 그것은 마치 풍경처럼 행동한다.CTE에서 delete 및 update 스테이트먼트를 실행할 수 있으며 이는 CTE에서 사용되는 참조 테이블에 직접적인 영향을 미칩니다.이 사실을 이해하기 위해 예를 들어보자.

With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT 
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'

위의 스테이트먼트에서는 CTE_Example에서 행을 삭제합니다.CTE에서 사용되고 있는 참조 테이블 "DEPT"에서 데이터가 삭제됩니다.

이 기능은 "주문된 업데이트"를 수행할 때 매우 유용합니다.

MS SQL에서는 ORDER BY를 UPDATE와 함께 사용할 수 없지만 CTE의 도움을 받아 다음과 같이 할 수 있습니다.

WITH cte AS
(
    SELECT TOP(5000) message_compressed, message, exception_compressed, exception
    FROM logs
    WHERE Id >= 5519694 
    ORDER BY Id
)
UPDATE  cte
SET     message_compressed = COMPRESS(message), exception_compressed = COMPRESS(exception)

자세한 내용은 여기를 참조해 주세요.ms sql을 사용하여 업데이트 및 주문하는 방법

아직 지적되지 않은 한 가지는 속도이다.오래된 질문인 것은 알지만, 직접 코멘트/답변할 필요가 있다고 생각합니다.

파생된 테이블에서도 동일한 작업을 수행할 수 있기 때문에 중복된 것으로 보입니다.

처음 CTE를 사용했을 때 그 속도에 완전히 놀랐습니다.교과서처럼 CTE에 매우 적합한 케이스였지만, 지금까지 CTE를 사용해 본 모든 케이스에서 상당한 속도 향상을 볼 수 있었습니다.첫 번째 쿼리는 파생된 테이블로 복잡하여 실행하는 데 시간이 오래 걸렸습니다.CTE를 사용하면 몇 초 만에 실현될 수 있다는 사실에 충격을 받았습니다.

 ;with cte as
  (
  Select Department, Max(salary) as MaxSalary
  from test
  group by department
  )  
  select t.* from test t join cte c on c.department=t.department 
  where t.salary=c.MaxSalary;

이거 먹어봐

언급URL : https://stackoverflow.com/questions/4740748/when-to-use-common-table-expression-cte