결합시 최대 값을 선택하는 쿼리

사용자 테이블이 있습니다.

|Username|UserType|Points|
|John    |A       |250   |
|Mary    |A       |150   |
|Anna    |B       |600   |

그리고 수준

|UserType|MinPoints|Level  |
|A       |100      |Bronze |
|A       |200      |Silver |
|A       |300      |Gold   |
|B       |500      |Bronze |

그리고 각 사용자의 수준을 얻는 쿼리를 찾고 있습니다. 다음과 같은 내용이 있습니다.

SELECT *
FROM Users U
INNER JOIN (
    SELECT TOP 1 Level, U.UserName
    FROM Levels L
    WHERE L.MinPoints < U.Points
    ORDER BY MinPoints DESC
    ) UL ON U.Username = UL.Username

결과는 다음과 같습니다.

|Username|UserType|Points|Level  |
|John    |A       |250   |Silver |
|Mary    |A       |150   |Bronze |
|Anna    |B       |600   |Bronze |

커서를 사용하지 않고 어떻게 할 수 있는지에 대한 아이디어 나 제안이 있습니까?

답변

기존 쿼리는 사용할 수있는 것과 비슷하지만 약간만 변경하면 쉽게 결과를 얻을 수 있습니다. APPLY연산자 를 사용하도록 쿼리를 변경 하고 구현 CROSS APPLY합니다. 그러면 요구 사항에 맞는 행이 반환됩니다. 사용할 수있는 버전은 다음과 같습니다.

SELECT 
  u.Username, 
  u.UserType,
  u.Points,
  lv.Level
FROM Users u
CROSS APPLY
(
  SELECT TOP 1 Level
  FROM Levels l
  WHERE u.UserType = l.UserType
     and l.MinPoints < u.Points
  ORDER BY l.MinPoints desc
) lv;

다음 은 데모 가 포함 된 SQL Fiddle입니다 . 결과는 다음과 같습니다.

| Username | UserType | Points |  Level |
|----------|----------|--------|--------|
|     John |        A |    250 | Silver |
|     Mary |        A |    150 | Bronze |
|     Anna |        B |    600 | Bronze |

답변

다음 솔루션은 Levels테이블을 한 번 스캔하는 공통 테이블 표현식을 사용합니다 . 이 스캔에서 “다음”포인트 레벨은 LEAD()윈도우 기능을 사용하여 발견 되므로 MinPoints(행에서) 및 MaxPoints( MinPoints현재 에서 다음 ) 을 갖습니다 UserType.

그런 다음 공통 테이블 표현식 lvls, on UserType및 MinPoints/ MaxPointsrange를 다음과 같이 간단히 결합 할 수 있습니다 .

WITH lvls AS (
    SELECT UserType, MinPoints, [Level],
           LEAD(MinPoints, 1, 99999) OVER (
               PARTITION BY UserType
               ORDER BY MinPoints) AS MaxPoints
    FROM Levels)

SELECT U.*, L.[Level]
FROM Users AS U
INNER JOIN lvls AS L ON
    U.UserType=L.UserType AND
    L.MinPoints<=U.Points AND
    L.MaxPoints> U.Points;

창 기능을 사용하면 모든 종류의 재귀 솔루션을 제거하고 성능을 크게 향상시킬 수 있다는 이점이 있습니다. 최상의 성능을 위해 Levels테이블 에서 다음 색인을 사용하십시오 .

CREATE UNIQUE INDEX ... ON Levels (UserType, MinPoints) INCLUDE ([Level]);

답변

기초 작업 인 INNER JOIN, GROUP BY 및 MAX 만 사용하여 어떻습니까?

SELECT   U1.*,
         L1.Level

FROM     Users AS U1

         INNER JOIN
         (
          SELECT   U2.Username,
                   MAX(L2.MinPoints) AS QualifyingMinPoints
          FROM     Users AS U2
                   INNER JOIN
                   Levels AS L2
                   ON U2.UserType = L2.UserType
          WHERE    L2.MinPoints <= U2.Points
          GROUP BY U2.Username
         ) AS Q
         ON U1.Username = Q.Username

         INNER JOIN
         Levels AS L1
         ON Q.QualifyingMinPoints = L1.MinPoints
            AND U1.UserType = L1.UserType
;

답변

다음과 같은 기능을 INNER JOIN사용하여 성능 문제로 사용할 수도 있다고 생각합니다 .LEFT JOINROW_NUMBER()

SELECT 
    Username, UserType, Points, Level
FROM (
    SELECT u.*, l.Level,
      ROW_NUMBER() OVER (PARTITION BY u.Username ORDER BY l.MinPoints DESC) seq
    FROM 
        Users u INNER JOIN
        Levels l ON u.UserType = l.UserType AND u.Points >= l.MinPoints
    ) dt
WHERE
    seq = 1;

SQL 바이올린 데모

답변