sql-server Archives - Page 55 of 359

[sql] 실제 예, SQL에서 OUTER / CROSS APPLY를 사용하는 경우

저는 CROSS / OUTER APPLY동료와 함께 살펴 보았고 우리는 그것들을 어디에 사용할 것인지에 대한 실제 사례를 찾기 위해 고군분투하고 있습니다.

Inner Join보다 Cross Apply 를 언제 사용해야하나요? 인터넷 검색이 가능하지만 주된 (유일한) 예제는 매우 이상해 보입니다 (테이블의 행 개수를 사용하여 다른 테이블에서 선택할 행 수를 결정).

이 시나리오가 다음과 같은 이점을 얻을 수 있다고 생각했습니다 OUTER APPLY.

연락처 테이블 (각 연락처에 대해 1 개의 레코드 포함) 통신 항목 테이블 (각 연락처에 대해 n 개의 전화, 팩스, 이메일 포함 가능)

그러나 하위 쿼리, 공통 테이블 표현식을 사용하면 OUTER JOINwith RANK()및 OUTER APPLYall이 동일하게 수행되는 것 같습니다. 이것은 시나리오가에 적용되지 않는다는 것을 의미한다고 생각 APPLY합니다.

실제 사례를 공유하고 기능 설명을 도와주세요!

답변

에 대한 일부 용도 APPLY는 …

1) 그룹당 상위 N 개 쿼리 (일부 카디널리티의 경우 더 효율적일 수 있음)

SELECT pr.name,
       pa.name
FROM   sys.procedures pr
       OUTER APPLY (SELECT TOP 2 *
                    FROM   sys.parameters pa
                    WHERE  pa.object_id = pr.object_id
                    ORDER  BY pr.name) pa
ORDER  BY pr.name,
          pa.name

2) 외부 쿼리의 각 행에 대해 테이블 값 함수 호출

SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

3) 컬럼 별칭 재사용

SELECT number,
       doubled_number,
       doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (SELECT 2 * CAST(number AS BIGINT)) CA1(doubled_number)
CROSS APPLY (SELECT doubled_number + 1) CA2(doubled_number_plus_one)

4) 두 개 이상의 열 그룹 해제

1NF 위반 테이블 구조를 가정합니다 ….

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

     Foo1 INT, Foo2 INT, Foo3 INT,
     Bar1 INT, Bar2 INT, Bar3 INT
  );

2008+ VALUES구문을 사용한 예 .

SELECT Id,
       Foo,
       Bar
FROM   T
       CROSS APPLY (VALUES(Foo1, Bar1),
                          (Foo2, Bar2),
                          (Foo3, Bar3)) V(Foo, Bar);

2005 년에는 UNION ALL대신 사용할 수 있습니다.

SELECT Id,
       Foo,
       Bar
FROM   T
       CROSS APPLY (SELECT Foo1, Bar1
                    UNION ALL
                    SELECT Foo2, Bar2
                    UNION ALL
                    SELECT Foo3, Bar3) V(Foo, Bar);

답변

피할 수없는 상황 CROSS APPLY이나 OUTER APPLY.

두 개의 테이블이 있다고 가정하십시오.

마스터 테이블

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

세부 사항 표

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

교차 적용

우리는 교체 할 필요가 많은 상황이있다 INNER JOIN와 함께 CROSS APPLY.

1. 기능이있는 TOP n결과 에 대해 2 개의 테이블을 조인하려는 경우INNER JOIN

우리가 선택해야 할 경우 고려 Id및 Name에서 Master각 마지막 두 날짜 Id에서 Details table.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY
    FROM DETAILS D
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

위 쿼리는 다음 결과를 생성합니다.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

마지막 두 날짜의 마지막 두 날짜에 대한 결과를 생성 Id한 다음 의 외부 쿼리에서만 이러한 레코드를 결합했습니다 Id. 이는 잘못된 것입니다. 이를 수행하려면 CROSS APPLY.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY
    FROM DETAILS D
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

그는 결과를 따라 형성합니다.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

여기에 작업이 있습니다. 내부 쿼리 CROSS APPLY는 외부 테이블을 참조 INNER JOIN할 수 있지만이를 수행 할 수 없습니다 (컴파일 오류 발생). 마지막 두 날짜를 찾을 때, 내부에서 수행되는 접합 CROSS APPLY, 즉 WHERE M.ID=D.ID.

2. INNER JOIN함수를 사용하여 기능이 필요할 때 .

CROSS APPLY와 교체로 사용할 수 있습니다 INNER JOIN우리가에서 결과를 얻을 필요가있을 때 Master테이블과 function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

그리고 여기에 기능이 있습니다

CREATE FUNCTION FnGetQty
(
    @Id INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT ID,PERIOD,QTY
    FROM DETAILS
    WHERE ID=@Id
)

다음 결과를 생성했습니다.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x

외부 적용

1. 기능이있는 TOP n결과 에 대해 2 개의 테이블을 조인하려는 경우LEFT JOIN

테이블의 Master각 Id에 대해 Id 및 Name from 과 마지막 두 날짜 를 선택해야하는지 고려하십시오 Details.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
LEFT JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY
    FROM DETAILS D
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

다음 결과를 형성하는

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     |   NULL       |  NULL |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

이것은 잘못된 결과를 가져올 것입니다. 즉, 우리가와 조인하더라도 Details테이블 에서 최근 두 날짜 데이터 만 가져옵니다 . 따라서 적절한 솔루션은 .IdIdOUTER APPLY

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
OUTER APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY
    FROM DETAILS D
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

다음과 같은 원하는 결과를 형성합니다.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

2. 우리가 필요로 할 때 LEFT JOIN사용하는 기능을 functions.

OUTER APPLY와 교체로 사용할 수 있습니다 LEFT JOIN우리가에서 결과를 얻을 필요가있을 때 Master테이블과 function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
OUTER APPLY dbo.FnGetQty(M.ID) C

그리고 기능은 여기에 있습니다.

CREATE FUNCTION FnGetQty
(
    @Id INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT ID,PERIOD,QTY
    FROM DETAILS
    WHERE ID=@Id
)

다음 결과를 생성했습니다.

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

CROSS APPLY및의 공통 기능OUTER APPLY

CROSS APPLY또는 피벗을 해제 할 때 값 OUTER APPLY을 유지하는 데 사용할 수 있습니다 NULL.

아래 표가 있다고 생각하십시오.

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   |
|   1  |  2014-02-23 | 2014-02-27   |
|   2  |  2014-05-06 | 2014-05-30   |
|   3  |   NULL      |   NULL       |
x------x-------------x--------------x

AND 를 하나의 열로 UNPIVOT가져 오는 데 사용하면 기본적으로 값 이 제거 됩니다.FROMDATETODATENULL

SELECT ID,DATES
FROM MYTABLE
UNPIVOT (DATES FOR COLS IN (FROMDATE,TODATE)) P

아래 결과를 생성합니다. 우리는 Id숫자 의 기록을 놓쳤습니다.3

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 |
  x------x-------------x

이러한 경우 CROSS APPLY또는 OUTER APPLY유용합니다.

SELECT DISTINCT ID,DATES
FROM MYTABLE
OUTER APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

다음 결과를 형성하고 Id그 가치를 유지합니다 .3

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 |
  |  3   |     NULL    |
  x------x-------------x

답변

실제 사례 중 하나는 스케줄러가 있고 각 예약 된 작업에 대한 최신 로그 항목이 무엇인지 확인하려는 경우입니다.

select t.taskName, lg.logResult, lg.lastUpdateDate
from task t
cross apply (select top 1 taskID, logResult, lastUpdateDate
             from taskLog l
             where l.taskID = t.taskID
             order by lastUpdateDate desc) lg

답변

위의 요점에 답하려면 예를 들어보십시오.

create table #task (taskID int identity primary key not null, taskName varchar(50) not null)
create table #log (taskID int not null, reportDate datetime not null, result varchar(50) not null, primary key(reportDate, taskId))

insert #task select 'Task 1'
insert #task select 'Task 2'
insert #task select 'Task 3'
insert #task select 'Task 4'
insert #task select 'Task 5'
insert #task select 'Task 6'

insert  #log
select  taskID, 39951 + number, 'Result text...'
from    #task
        cross join (
            select top 1000 row_number() over (order by a.id) as number from syscolumns a cross join syscolumns b cross join syscolumns c) n

이제 실행 계획으로 두 쿼리를 실행합니다.

select  t.taskID, t.taskName, lg.reportDate, lg.result
from    #task t
        left join (select taskID, reportDate, result, rank() over (partition by taskID order by reportDate desc) rnk from #log) lg
            on lg.taskID = t.taskID and lg.rnk = 1

select  t.taskID, t.taskName, lg.reportDate, lg.result
from    #task t
        outer apply (   select  top 1 l.*
                        from    #log l
                        where   l.taskID = t.taskID
                        order   by reportDate desc) lg

외부 적용 쿼리가 더 효율적임을 알 수 있습니다. (신규 사용자이므로 계획을 첨부 할 수 없습니다 … Doh.)