mysql - 최대값 - 오라클 where 절 max




SQL은 열에 최대 값을 가진 행만 선택합니다. (20)

나는이 테이블 문서를 가지고있다.

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

ID마다 한 행을 선택하고 가장 큰 레브 만 선택하려면 어떻게해야합니까?
위의 데이터를 사용하면 결과에 [1, 3, ...][2, 1, ..] 두 행이 포함되어야합니다. MySQL을 사용하고 있습니다.

현재는 while 루프의 검사를 사용하여 결과 세트에서 오래된 rev를 감지하고 덮어 씁니다. 그러나 이것이 결과를 얻는 유일한 방법입니까? SQL 솔루션이 없습니까?

최신 정보
답변에서 알 수 있듯이 SQL 솔루션과 여기 sqlfiddle 데모가 있습니다.

업데이트 2
나는 위의 sqlfiddle 을 추가 한 후, 질문이 upvoted 된 비율이 응답의 upvote 비율을 능가하는 것을 알아 차렸다. 그건 의도가 아니야! 바이올린은 답변, 특히 허용 된 답변을 기반으로합니다.


첫눈에...

MAX 집계 함수가있는 GROUP BY 절만 있으면됩니다.

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

그렇게 간단하지가 않습니까?

방금 content 열이 필요한 것으로 나타났습니다.

이것은 SQL에서 매우 일반적인 질문입니다. 일부 그룹 식별자 당 열에 최대 값이있는 행의 전체 데이터를 찾습니다. 내 경력 중에 많이 들었다. 실제로, 그것은 현재의 기술 면접에서 내가 대답 한 질문 중 하나였습니다.

실제로 커뮤니티는 greatest-n-per-group 질문을 처리하기 위해 단일 태그를 만들었습니다.

기본적으로이 문제를 해결하기위한 두 가지 방법이 있습니다.

간단한 group-identifier, max-value-in-group 하위 쿼리로 조인

이 접근법에서는 먼저 하위 쿼리에서 group-identifier, max-value-in-group (위에서 이미 해결 된) group-identifier, max-value-in-group 찾습니다. 그런 다음 group-identifiermax-value-in-group 모두에 대해 동등한 하위 쿼리에 테이블을 가입시킵니다.

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

왼쪽으로 셀프 조인하기, 조인 조건과 필터 조조하기

이 접근 방식에서는 테이블 자체를 그대로 두었습니다. 물론 평등은 group-identifier 됩니다. 다음, 2 개의 똑똑한 움직임 :

  1. 두 번째 조인 조건은 왼쪽 값이 오른쪽 값보다 작습니다.
  2. 1 단계를 수행 할 때 실제로 최대 값을 갖는 행은 오른쪽에 NULL 을 갖습니다 ( LEFT JOIN , 기억합니까?). 그런 다음 조인 된 결과를 필터링하여 오른쪽이 NULL 행만 표시 NULL .

그래서 당신은 결국 :

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

결론

두 접근법 모두 똑같은 결과를 가져온다.

group-identifier 대해 max-value-in-group 을 가진 두 개의 행이있는 경우 두 행 모두 두 방식 모두에서 결과에있게됩니다.

두 방법 모두 SQL ANSI와 호환되므로 "맛"에 관계없이 원하는 RDBMS에서 작동합니다.

두 접근법 모두 성능이 우수하지만 마일리지는 다를 수 있습니다 (RDBMS, DB 구조, 색인 등). 그래서 다른 벤치 마크 보다 한 가지 방법을 선택하면됩니다. 그리고 당신에게 가장 이해하기 쉬운 것을 골라야합니다.


SELECT * FROM Employee 여기서 Employee.Salary (Employe_id로 Employee 그룹에서 최대 (salary) 선택) ORDER BY Employee.Salary


select 문에 많은 필드가 있고 최적화 된 코드를 통해 해당 필드의 최신 값을 원할 경우 :

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

나는 내 자신의 문제를 해결하기 위해 아래를 사용했다. 먼저 임시 테이블을 만들고 고유 ID 당 최대 rev 값을 삽입했습니다.

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

그런 다음이 최대 값 (# temp1)을 가능한 모든 id / content 조합에 연결했습니다. 이렇게함으로써 필자는 비 - 최대 id / content 조합을 자연스럽게 필터링하고 각각에 대한 유일한 최대 rev 값을 남겨 둡니다.

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

나는 어떤 칼럼으로 기록을 순위 매김함으로써 이것을하고 싶어한다. 이 경우 id 별로 그룹화 된 순위 값을 순위 지정합니다. 더 높은 rev 사람들은 더 낮은 순위를 가질 것입니다. 따라서 최고 rev 순위는 1입니다.

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

변수를 도입하면 모든 것이 느려지는지 확실하지 않습니다. 하지만 적어도 나는 YOURTABLE 두 번 질문하지 않습니다.


나는 이것을 사용할 것이다 :

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

하위 쿼리 SELECT 너무 어쩌면 어쩌면 아니지만 JOIN 절을 사용할 수있는 것 같습니다. 나는 쿼리를 최적화하는 전문가는 아니지만 MySQL, PostgreSQL, FireBird에서 시도했으며 매우 효과적입니다.

이 스키마는 여러 조인과 WHERE 절에서 사용할 수 있습니다. 그것은 내 실례이다 (당신의 문제를 "딱딱한"표와 동일하게 해결한다) :

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

그것은 십대의 기록을 가진 테이블에서 요구되며, 실제로는 너무 강한 기계에서는 0.01 초보다 적게 걸립니다.

IN 절을 사용하지 않을 것입니다 (위의 어딘가에서 언급했듯이). IN은 짧은 목록의 constans와 함께 사용되며 하위 쿼리를 기반으로 작성된 쿼리 필터가 아닙니다. 이것은 IN의 하위 쿼리가 매우 오랜 시간이 걸리는 쿼리를 만들 수있는 모든 스캔 레코드에 대해 수행되기 때문입니다.


내 취향은 가능한 한 적은 코드로 사용하는 것입니다 ...

IN 을 사용하여이 작업을 수행 할 수 있습니다.

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

내 마음에 덜 복잡하고 ... 읽기 쉽고 유지하기가 쉽습니다.


누군가 Linq verson을 찾고 있다면, 이것은 나를 위해 작동하는 것 같습니다 :

public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
    var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
        .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );    

    return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}

또 다른 해결책은 상관 관계가있는 하위 쿼리를 사용하는 것입니다.

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

(id, rev)에 색인을 붙이면 간단한 검색처럼 하위 쿼리가 렌더링됩니다.

다음은 ~ 1million 레코드의 InnoDB 테이블에서 MySQL 측정을 기반으로 한 @ AdrianCarneiro의 대답 (하위 쿼리, 왼쪽 결합)의 솔루션과의 비교입니다. 그룹 크기는 1-3입니다.

전체 테이블 스캔의 경우 하위 쿼리 / 왼쪽 결합 / 상관 된 타이밍은 6/8/9와 서로 관련이 있지만 직접 조회 또는 배치 ( id in (1,2,3) )에서는 서브 쿼리가 훨씬 느립니다. 하위 쿼리를 다시 실행하기 때문에). 그러나 나는 속도의 leftjoin과 상관 솔루션을 구별 할 수 없었다.

마지막으로 leftjoin이 그룹에 n * (n + 1) / 2 조인을 생성하므로 그룹의 크기에 따라 그 성능이 크게 영향을받을 수 있습니다.


성능을 보증 할 수는 없지만 Microsoft Excel의 한계에 영감을받은 트릭입니다. 그것은 좋은 특징들을 가지고있다.

좋은 물건

  • 동점이 있더라도 (때로는 유용 할 수 있음) 단 하나의 "최대 레코드"만 반환해야합니다.
  • 조인이 필요하지 않습니다.

접근

조금 추한 데다 rev 열의 유효한 값 범위에 대해 알고 있어야합니다. 회전 열이 소수 자릿수를 포함하여 0.00에서 999 사이의 숫자이지만 소수점 오른쪽에 두 자리 만있을 것이라고 가정합니다 (예 : 34.17이 유효한 값).

가장 중요한 사실은 기본 비교 필드를 원하는 데이터와 연결 / 포장하여 문자열로 단일 합성 열을 만드는 것입니다. 이 방법으로 SQL의 MAX () 집계 함수가 모든 데이터를 반환하도록 할 수 있습니다 (단일 열로 묶여 있기 때문에). 그런 다음 데이터 압축을 풀어야합니다.

위 예제를 SQL로 작성한 방법은 다음과 같습니다.

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

패킹은 rev 값에 관계없이 rev 열을 알려진 문자 길이의 수만큼 강제로 시작하므로 예를 들어

  • 3.2는 1003.201이됩니다.
  • 57은 1057.001이된다.
  • 923.88은 1923.881이됩니다.

그렇게하면 두 숫자의 문자열 비교가 두 숫자의 숫자 비교와 동일한 "최대"를 가져오고 하위 문자열 함수를 사용하여 원래 숫자로 쉽게 변환 할 수 있습니다 (한 형식 또는 다른 형식으로 사용할 수 있음). 어디에나).


이 같은?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

이 답변들 중 어느 것도 나를 위해 일하지 못했습니다.

이것이 나를 위해 일한 것입니다.

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

이 솔루션은 YourTable에서 단 하나의 항목 만 선택하므로 더 빠릅니다. sqlfiddle.com의 테스트에 따라 MySQL 및 SQLite (SQLite의 경우 DESC 제거)에서만 작동합니다. 어쩌면 익숙하지 않은 다른 언어에서도 작동하도록 조정할 수 있습니다.

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id

이것은 나를 위해 sqlite3 작동합니다 :

SELECT *, MAX(rev) FROM t1 GROUP BY id

*를 사용하면 중복 된 열을 얻을 수 있지만 그다지 문제는 아닙니다.


이것이이 문제와 관련하여 가장 인기있는 질문이기 때문에 여기에 또 다른 답을 다시 올릴 것입니다.

이 작업을 수행하는 더 간단한 방법이있는 것처럼 보입니다 ( 단, MySQL에서만 ) :

select *
from (select * from mytable order by id, rev desc ) x
group by id

이 문제에 대한 간결하고 우아한 답변을 제공 한이 질문 에서 사용자 보헤미안의 신용 응답을 부탁드립니다 .

편집 : MySQL은 GROUP BY 문이 GROUP BY 목록에없는 열에 대해 의미있는 값을 반환한다는 것을 보장하지 않기 때문에 많은 사람들이이 솔루션을 사용할 수 있지만 장기적으로 안정적이지 않을 수 있습니다. 따라서이 솔루션을 사용하는 데 따른 모든 책임은 사용자에게 있습니다.


작업을 수행하는 다른 방법은 OVER PARTITION 절에서 MAX () 분석 함수를 사용하는 것입니다.

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

이 게시물에 이미 문서화 된 다른 OVER PARTITION 솔루션은 다음과 같습니다.

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
      FROM YourTable
    ) t
  WHERE t.rank = 1 

이 2 SELECT는 Oracle 10g에서 잘 작동합니다.


해당 필드의 최대 값을 가진 필드 만 사용하여 레코드를 검색하는 또 다른 해결책이 있습니다. 이 작업은 SQL400에서 작동하는 플랫폼입니다. 이 예에서 FIELD5 필드의 최대 값을 갖는 레코드는 다음 SQL 문에 의해 검색됩니다.

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)

revidMAX() 대한 하나의 maxRevId 값으로 결합한 다음 조인하지 않고 선택을 수행 한 다음 원래 값으로 다시 분할 할 수 있습니다.

SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
      FROM YourTable
      GROUP BY id) x;

단일 테이블 대신 복잡한 조인이있는 경우 특히 빠릅니다. 전통적인 방식을 사용하면 복잡한 결합이 두 번 수행됩니다.

위의 조합은 revidINT UNSIGNED (32 비트)이고 BIGINT UNSIGNED (64 비트)로 결합 된 값이 비트 함수 인 경우 간단합니다. id & rev 가 32 비트 값보다 크거나 여러 열로 구성된 경우 값을 MAX() 적합한 채우기가있는 이진 값에 결합해야합니다.


SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

select * from yourtable
group by id
having rev=max(rev);




greatest-n-per-group