다른 열의 순서를 기준으로 그룹에서 값 하나를 선택합니다.

다른 열의 순서를 기준으로 그룹에서 값 하나를 선택합니다.

문제

테이블이 .tab(이용 가능).

| g | a | b |     v |
---------------------
| 1 | 3 | 5 |   foo |
| 1 | 4 | 7 |   bar |
| 1 | 2 | 9 |   baz |
| 2 | 1 | 1 |   dog |
| 2 | 5 | 2 |   cat |
| 2 | 5 | 3 | horse |
| 2 | 3 | 8 |   pig |

행행 rows로 .g을 컬럼에 v단, 값은 필요 없습니다만, 최대값이 있는 행의 값을 원합니다.a그 에서 '최대'가 있는 b는 「」, 「」이 한다

| 1 |   bar |
| 2 | horse |

현재의 솔루션

이를 달성하기 위한 쿼리를 알고 있습니다.

SELECT grps.g,
(SELECT v FROM tab
 WHERE g = grps.g
 ORDER BY a DESC, b DESC
 LIMIT 1) AS r
FROM (SELECT DISTINCT g FROM tab) grps

질문.

하지만 나는 이 질문이 좀 추하다고 생각한다.주로 종속 서브쿼리를 사용하기 때문에 퍼포먼스 킬러처럼 느껴집니다.그래서 나는 이 문제에 대해 더 쉬운 해결책이 없을지 궁금하다.

예상되는 답변

이 질문에 대한 가장 유력한 답변은 이 기능을 제공하는 MySQL(또는 MariaDB)용 애드온 또는 패치입니다.하지만 나는 다른 유용한 영감을 환영할 것이다.종속 서브쿼리 없이 작동하는 모든 것은 해답으로 적합할 것이다.

즉 column,,,,,,, if 、 " 。cat ★★★★★★★★★★★★★★★★★」horse그 답변은 자유롭게 제안해 주십시오.또한 대부분의 사용 사례에서 여전히 도움이 될 것으로 생각합니다.를 들어, 「」라고 하는 것은,100*a+b하나의 표현식만 사용하면서 위의 데이터를 두 열로 정렬하는 것이 좋습니다.

몇 가지 해법을 생각해 두고 있습니다.잠시 후에 추가할 수도 있지만, 우선 새로운 솔루션이 쏟아져 나올지 어떤지를 확인해 보겠습니다.

---

벤치마크 결과

보기만으로는 여러 답을 비교하기 어렵기 때문에 몇 가지 벤치마크를 해봤습니다.이것은 MySQL 5.1을 사용하여 내 데스크톱에서 실행되었습니다.그 숫자들은 다른 어떤 시스템과는 비교가 되지 않을 것이다. 단지 서로 비교만 할 뿐이다.성능이 애플리케이션에 중요한 경우 실제 데이터를 사용하여 자체 테스트를 수행하는 것이 좋습니다.새로운 답변이 들어오면 스크립트에 추가하고 모든 테스트를 재실행합니다.

• 100,000 、 1000 、 InnoDb :
  1. MvG의 경우 0.166s (질문부터)
  2. Richard의 경우 0.520s키위
  3. xdazz의 경우 2.199s
  4. 19.24s(Dems용) (시퀀스 서브쿼리)
  5. AC의 경우 48.72초
• 100,000 、 50,000 、 InnoDb :
  1. xdazz의 경우 0.356s
  2. Richard의 경우 0.640키위
  3. MvG의 경우 0.764s (질문부터)
  4. AC의 경우 51.50초
  5. Dems(순차 하위 쿼리)에 대해 너무 깁니다.
• InnoDb: 100 、 InnoDb :
  1. MvG의 경우 0.163s (질문부터)
  2. Richard의 경우 0.523초키위
  3. Dems용 2.072s(시퀀스 서브쿼리)
  4. xdazz의 경우 17.78초
  5. AC의 경우 49.85초

따라서 지금까지의 내 해결책은 종속 서브쿼리를 사용하더라도 그리 나쁘지 않은 것 같습니다.놀랍게도, 의존적인 서브쿼리도 사용하고 있기 때문에, 거의 같은 것을 고려했을지도 모르는, acatt에 의한 솔루션의 퍼포먼스는 훨씬 나빠지고 있습니다.MySQL 옵티마이저로는 대처할 수 없는 것 같습니다.해결책 리처드제안된 The Kiwi는 전체적인 성능도 좋은 것 같습니다.다른 두 가지 솔루션은 데이터 구조에 크게 의존합니다.많은 소규모 그룹에서는 xdazz' 접근법이 다른 모든 그룹보다 뛰어난 반면, Dems의 솔루션은 소수의 대규모 그룹에서는 최고의 성능을 발휘합니다(예외적으로 우수하지는 않지만).

SELECT g, a, b, v
  FROM (
            SELECT *, 
                   @rn := IF(g = @g, @rn + 1, 1) rn, 
                   @g := g
              FROM (select @g := null, @rn := 0) x, 
                   tab
          ORDER BY g, a desc, b desc, v
       ) X
 WHERE rn = 1;

싱글 패스다른 모든 솔루션은 O(n^2)로 보입니다.

이 방법에서는 서브쿼리가 사용되지 않습니다.

SELECT t1.g, t1.v
FROM tab t1
LEFT JOIN tab t2 ON t1.g = t2.g AND (t1.a < t2.a OR (t1.a = t2.a AND t1.b < t2.b))
WHERE t2.g IS NULL

설명:

왼쪽 조인(LEFT JOIN)은 t1.a가 최대값일 때 더 큰 값을 가진 s2.a가 존재하지 않으며 s2 행 값이 NULL이 되는 것을 기반으로 작동합니다.

많은 RDBMS에는 이 문제에 특히 적합한 구조가 있습니다.MySQL은 그 중 하나가 아닙니다.

이를 통해 세 가지 기본적인 접근법이 제공됩니다.

• 각 레코드를 체크하여 원하는 레코드인지 여부를 확인합니다.이 레코드는 EXISTES 및 EXIST 절의 관련 서브쿼리를 사용합니다.(@acatt의 답변입니다만, MySQL이 항상 이것을 최적화하는 것은 아닙니다. MySQL이 이 작업을 제대로 수행하지 못할 것으로 가정하기 전에 복합 인덱스가 켜져 있는지 확인하십시오.)

• 반 데카르트 곱을 해서 같은 수표에 가득 채우세요.가입하지 않은 레코드는 타깃레코드가 됩니다각 그룹('g')이 클 경우 퍼포먼스가 급속히 저하될 수 있습니다(의 고유값마다 레코드가 10개 있는 경우 최대 50개의 레코드가 생성되어 49가 폐기됩니다). 그룹 사이즈가 100인 경우 최대 5000개의 레코드가 생성되어 4999개의 레코드가 폐기되지만, 소규모 그룹 사이즈의 경우 매우 적합합니다(@xdazz의 답변).

• 또는 여러 서브쿼리를 사용하여 MAX(a)를 판별하고 다음으로 MAX(b)를 판별합니다.

여러 개의 순차 하위 쿼리...

SELECT
  yourTable.*
FROM
  (SELECT g,    MAX(a) AS a FROM yourTable GROUP BY g   ) AS searchA
INNER JOIN
  (SELECT g, a, MAX(b) AS b FROM yourTable GROUP BY g, a) AS searchB
    ON  searchA.g = searchB.g
    AND searchA.a = searchB.a
INNER JOIN
  yourTable
    ON  yourTable.g = searchB.g
    AND yourTable.a = searchB.a
    AND yourTable.b = searchB.b

MySQL이 두 번째 하위 쿼리를 최적화하는 방법에 따라 다른 옵션보다 성능이 더 좋을 수도 있고 그렇지 않을 수도 있습니다.단, 이 코드는 특정 태스크에 대해 가장 긴(유지보수가 가장 어려운) 코드입니다.

세 가지 검색 필드 모두에 대한 복합 색인 가정(g, a, b), 나는 그것이 대규모 그룹 규모에 가장 적합하다고 추측한다.g하지만 그건 시험해봐야 해

그룹 규모가 작은 경우g@xdazz의 대답으로 하겠습니다.

편집

무차별적인 접근도 있다.

• AUTO_INCREMENT 열을 ID로 사용하여 동일한 테이블을 만듭니다.
• 테이블을 g, a, b 순서로 이 클론에 삽입합니다.
• 그러면 ID는 다음과 같이 찾을 수 있습니다.SELECT g, MAX(id).
• 그런 다음 이 결과를 사용하여v필요한 가치를 제공합니다.

이것이 최선의 접근법이 될 것 같지 않다.만약 그렇다면, 이는 MySQL의 옵티마이저가 이러한 종류의 문제에 대처할 수 있는 능력을 효과적으로 고려한 것이다.

하지만 모든 엔진에는 약점이 있습니다.그래서 개인적으로 RDBMS가 어떻게 동작하고 있는지 이해하고 선택을 할 수 있을 때까지 모든 것을 시도합니다.

편집

사용 예ROW_NUMBER(). (Oracle, SQL Server, PostGreSQL 등)

SELECT
  *
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY g ORDER BY a DESC, b DESC) AS sequence_id,
    *
  FROM
    yourTable
)
  AS data
WHERE
  sequence_id = 1

이 문제는 연관된 쿼리를 사용하여 해결할 수 있습니다.

SELECT g, v
FROM tab t
WHERE NOT EXISTS (
    SELECT 1
    FROM tab
    WHERE g = t.g
        AND a > t.a
        OR (a = t.a AND b > t.b)
    )

언급URL : https://stackoverflow.com/questions/12726549/select-one-value-from-a-group-based-on-order-from-other-columns