programing

실생활 예제, SQL에서 OUTER / CROSS APPLE을 사용하는 경우

stoneblock 2023. 5. 4. 17:57

실생활 예제, SQL에서 OUTER / CROSS APPLE을 사용하는 경우

나는 계속 보고 있었습니다.CROSS / OUTER APPLY우리는 그것들을 어디에 사용해야 하는지에 대한 실제 사례를 찾기 위해 고군분투하고 있습니다.

저는 "INEER JOIN 위에 CROSS APPLY를 사용해야 하는 경우"를 살펴보고 구글링을 해봤지만, 주요 (단일) 예는 꽤 이상해 보입니다(테이블의 행 수를 사용하여 다른 테이블에서 선택할 행 수를 결정함).

저는 이 시나리오가 도움이 될 것이라고 생각했습니다.OUTER APPLY:

연락처 표(각 연락처에 대해 하나의 레코드가 포함됨) 통신 항목 표(각 연락처에 대해 전화, 팩스, 전자 메일을 포함할 수 있음)

하지만 하위 쿼리, 일반적인 표식을 사용하면OUTER JOIN와 함께RANK()그리고.OUTER APPLY모두가 똑같이 행동하는 것 같습니다.이것은 시나리오가 적용되지 않는다는 것을 의미하는 것 같습니다.APPLY.

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

의 일부 용도APPLY그건...

그룹당 상위 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

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

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.

두 개의 테이블이 있다고 가정합니다.

마스터 탭LE

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.

만약 우리가 2개의 테이블에 합류하고 싶다면,TOP n와의 결과.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.

필요할 때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



                                                            외부 적용

만약 우리가 2개의 테이블에 합류하고 싶다면,TOP n는 와의결과입니다.LEFT JOIN

ID 및 이름을 선택해야 하는지 고려합니다.Master그리고 각 아이디에 대한 마지막 두 날짜.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와 표Id우리가 합류하더라도Id은 따서적해결은책을 사용하는 입니다.OUTER 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

가 요할때가 필요할 때.LEFT JOIN을 사용한 functions.

OUTER APPLY▁withment로 대체하여 사용할 수 .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

를 할 때UNPIVOT져다오를 FROMDATE그리고.TODATE하나의 열에, 그것은 제거할 것입니다.NULL기본값입니다.

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

에는 이한경우a.CROSS APPLY또는OUTER APPLY할 

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

과 같은 하고 다과같결형유고다니지합성하과를은음다▁and를 유지합니다.Id은 그가가있는곳에치곳에▁where▁is입니다.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

외부 적용 쿼리가 더 효율적이라는 것을 알 수 있습니다. (새로운 사용자이기 때문에 계획을 첨부할 수 없습니다...도.)

언급URL : https://stackoverflow.com/questions/9275132/real-life-example-when-to-use-outer-cross-apply-in-sql

'programing' 카테고리의 다른 글

각도 테스트가 실패하고 'XMLHttpRequest'에서 'send'를 실행하지 못함  (0) 2023.05.09
있나요?SQLite용 NET/C# 래퍼?  (0) 2023.05.04
런타임 오류:입력 유형(토치).FloatTensor) 및 체중 유형(torch.cuda).FloatTensor)는 같아야 합니다.  (0) 2023.05.04
오류 - IIS 메타베이스에 액세스할 수 없습니다.  (0) 2023.05.04
각도:라이프사이클 후크가 구성 요소에서 사용할 수 있는 데이터를 입력하는 경우  (0) 2023.05.04

'programing'의 다른글

  • 현재글실생활 예제, SQL에서 OUTER / CROSS APPLE을 사용하는 경우

관련글

댓글

티스토리툴바