복붙노트

[SQL] 실생활의 예를 들어, 때 OUTER / CROSS는 SQL에 적용 사용

SQL

실생활의 예를 들어, 때 OUTER / CROSS는 SQL에 적용 사용

나는 동료와 함께 적용 CROSS / OUTER에서 찾고있다 그리고 우리는 그것들을 사용하는 곳의 실제 사례를 찾기 위해 애 쓰고 있습니다.

나는 십자가 내부에 걸쳐 적용 사용해야 가입하는 경우에 시간이 찾고 꽤 많이 보냈어요? 그리고 인터넷 검색을하지만 주 (전용) 예 (다른 테이블에서 선택하는 행 수를 결정하기 위해 테이블에서 행 개수를 사용하여) 꽤 이상한 것 같다.

나는 OUTER 혜택을 누릴 수 있습니다이 시나리오가 적용 생각 :

연락처 표 (각 연락처에 대해 한 기록을 포함) 통신 항목 표 (포함 N 수 있습니다 전화, 팩스, 각 연락처에 이리저리 이메일)

그러나 하위 쿼리를 사용하여, 공통 테이블 표현식은 OUTER는 모두가 동등하게 수행하는 것 적용 RANK ()와 OUTER에 가입하세요. 나는 시나리오가 적용에 적용되지 않습니다 수단을 추측하고있어.

몇 가지 실제 사례를 공유하시기 바랍니다 도움이 기능을 설명!

해결법

  1. ==============================

    1.에 대한 몇 가지 용도는 적용 ...

    에 대한 몇 가지 용도는 적용 ...

    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) 열 이상의기를 Unpivoting

    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);
  2. ==============================

    2.당신은 CROSS 적용하거나 외부 적용 피할 수있는 여러 가지 상황이 있습니다.

    당신은 CROSS 적용하거나 외부 적용 피할 수있는 여러 가지 상황이 있습니다.

    당신이 두 개의 테이블을 고려하십시오.

    마스터 테이블

    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를 교체해야 적용 CROSS에 가입 많은 상황이있다.

    우리는 INNER로 2 개 위에 테이블 n 결과에 가입하려면 1. 기능 가입

    우리는 정보 테이블에서 각 ID에 대한 마스터와 마지막 두 날짜에서 ID와 이름을 선택해야하는 경우 고려하십시오.

    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가 적용 사용해야합니다.

    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 내부 쿼리 INNER이 할 수없는 외부 조인 테이블을 참조 할 수 있습니다 적용 (컴파일 오류가 발생합니다). 마지막 두 날짜를 찾을 때, CROSS 내부에서 수행되는 접합은 WHERE M.ID = D.ID, 즉 적용됩니다.

    2. 우리는 INNER이 기능을 사용하여 기능을 가입이 필요합니다.

    우리는 마스터 테이블과 함수의 결과를 얻을 필요가있을 때 INNER와 교체 가입으로 CROSS 사용할 수 있습니다 적용됩니다.

    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 결과에 가입하려면 1. 기능 가입

    우리는 정보 테이블에서 각 ID에 대한 마스터와 마지막 두 날짜에서 ID와 이름을 선택해야하는 경우 고려하십시오.

    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

    이것은 우리가 아이디로 가입하더라도 그것은 상관없이 아이디의 정보 테이블에서만 최신 두 날짜 데이터를 가져올 것이다, 잘못된 결과 즉 가져올 것이다. 그래서 적절한 솔루션은 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

    2. 우리는 LEFT 함수를 사용하여 기능을 가입이 필요합니다.

    우리는 마스터 테이블과 함수의 결과를 얻을 필요가있을 때 LEFT와 교체 가입으로 OUTER 사용할 수 있습니다 적용됩니다.

    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 적용 또는 OUTER은 교환 할 수있다 unpivoting 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

    당신은 하나 개의 컬럼에 FROMDATE 및 TODATE을 가지고 UNPIVOT을 사용하면 기본적으로 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

    이러한 경우에서 CROSS 적용 또는 OUTER 유용 할 것입니다 적용

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

    이는 다음과 같은 결과를 형성하고, 그 값이 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
  3. ==============================

    3.당신이 스케줄러를했고, 가장 최근의 로그 항목이 각각의 예약 된 작업에 대한 무엇인지보고 싶었다 경우 하나의 실생활의 예로들 수 있습니다.

    당신이 스케줄러를했고, 가장 최근의 로그 항목이 각각의 예약 된 작업에 대한 무엇인지보고 싶었다 경우 하나의 실생활의 예로들 수 있습니다.

    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
  4. ==============================

    4.예를 노크 위의 지점에 대답하려면 :

    예를 노크 위의 지점에 대답하려면 :

    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

    당신은 외부 쿼리가 더 효율적 적용된다는 것을 알 수있다. (나는 ... 도현 새 사용자 해요로 계획을 첨부 할 수 없습니다.)

    5. from https://stackoverflow.com/questions/9275132/real-life-example-when-to-use-outer-cross-apply-in-sql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 단일 쿼리에 삽입 여러 행에 대한 MySQL의 ON DUPLICATE KEY의 UPDATE  (0) 2020.04.03
[SQL] 행이 존재하는 경우, 그렇지 않으면 삽입, 확인  (0) 2020.04.02
[SQL] SQL 데이터베이스 설계에 대한 초보자 가이드 [마감]  (0) 2020.04.02
[SQL] 하나의 SQL 문에 모든 테이블, 저장 프로 시저, 트리거, 제약 및 모든 종속성을 드롭  (0) 2020.04.02
[SQL] SQL 문을 오류 : "열 ... 존재하지 않습니다"  (0) 2020.04.02
,

티스토리툴바