SQL은 열에 최대 값이있는 행만 선택합니다. [중복]
이 질문에 이미 답변이 있습니다.
- 각 그룹의 마지막 레코드 검색-MySQL 24 답변
문서 용 테이블이 있습니다 (여기에 단순화 된 버전).
+------+-------+--------------------------------------+
| id | rev | content |
+------+-------+--------------------------------------+
| 1 | 1 | ... |
| 2 | 1 | ... |
| 1 | 2 | ... |
| 1 | 3 | ... |
+------+-------+--------------------------------------+
ID 당 하나의 행과 가장 큰 수익 만 선택하려면 어떻게해야합니까?
위의 데이터를 사용하면 결과에 [1, 3, ...]및 [2, 1, ..]. MySQL을 사용하고 있습니다.
현재 while루프 에서 검사를 사용 하여 결과 집합에서 이전 rev를 감지하고 덮어 씁니다. 그러나 이것이 결과를 얻는 유일한 방법입니까? 이없는 SQL의 해결책은?
업데이트
에 대한 답변을 제안,가 이다 는 SQL 솔루션, 그리고 여기에 sqlfiddle 데모 .
업데이트 2
위의 sqlfiddle 을 추가 한 후 질문이 찬성 되는 비율이 답변의 찬성 비율을 능가하는 것으로 나타났습니다 . 그것은 의도가 아닙니다! 바이올린은 답변, 특히 받아 들여지는 답변을 기반으로합니다.
첫눈에...
집계 함수 가있는 GROUP BY절만 있으면 MAX됩니다.
SELECT id, MAX(rev)
FROM YourTable
GROUP BY id
그렇게 간단하지 않습니까?
content칼럼 이 필요하다는 것을 방금 깨달았 습니다.
이것은 SQL에서 매우 일반적인 질문입니다. 일부 그룹 식별자 당 열에서 최대 값이있는 행의 전체 데이터를 찾습니다. 제 커리어 동안 많이 들었습니다. 사실, 현재 직장의 기술 인터뷰에서 제가 대답 한 질문 중 하나였습니다.
실제로 StackOverflow 커뮤니티가 다음과 같은 질문을 처리하기 위해 하나의 태그를 생성 한 것은 매우 일반적입니다 : maximum-n-per-group .
기본적으로이 문제를 해결하기위한 두 가지 접근 방식이 있습니다.
간단한 group-identifier, max-value-in-group하위 쿼리로 결합
이 접근 방식에서는 먼저 group-identifier, max-value-in-group하위 쿼리에서 (이미 위에서 해결 한)를 찾습니다 . 그런 다음 group-identifier및 둘 모두에 대해 동일하게 하위 쿼리에 테이블을 조인합니다 max-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. 그런 다음 두 가지 현명한 움직임 :
- 두 번째 조인 조건은 왼쪽 값이 오른쪽 값보다 작습니다.
- 1 단계를 수행하면 실제로 최대 값이있는 행
NULL이 오른쪽에 있습니다 (LEFT JOIN, 기억하십니까?). 그런 다음 결합 된 결과를 필터링하여 오른쪽이 인 행만 표시합니다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;
결론
두 방법 모두 똑같은 결과를 가져옵니다.
max-value-in-groupfor 와 함께 두 개의 행이있는 경우 두 group-identifier가지 방법 모두에서 두 행이 모두 결과가됩니다.
두 방법 모두 SQL ANSI와 호환되므로 "기종"에 관계없이 좋아하는 RDBMS와 함께 작동합니다.
두 가지 방법 모두 성능에 친숙하지만 마일리지는 다를 수 있습니다 (RDBMS, DB 구조, 인덱스 등). 따라서 하나의 접근 방식을 다른 것보다 선택할 때 벤치 마크 . 그리고 당신에게 가장 의미있는 것을 선택하십시오.
내 선호는 가능한 한 적은 코드를 사용하는 것입니다 ...
이것을 IN시도 하여 할 수 있습니다 .
SELECT *
FROM t1 WHERE (id,rev) IN
( SELECT id, MAX(rev)
FROM t1
GROUP BY id
)
제 생각에는 덜 복잡합니다. 읽고 유지하기가 더 쉽습니다.
또 다른 해결책은 상관 하위 쿼리를 사용하는 것입니다.
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)에 인덱스가 있으면 하위 쿼리가 거의 간단한 조회처럼 렌더링됩니다.
다음은 ~ 1 백만 레코드의 InnoDB 테이블을 사용한 MySQL 측정을 기반으로 한 @AdrianCarneiro의 답변 (하위 쿼리, leftjoin)의 솔루션과 비교 한 것입니다. 그룹 크기는 1-3입니다.
전체 테이블 스캔의 경우 하위 쿼리 / leftjoin / 상관 타이밍은 서로 관련되어 6/8/9로 관련되지만 직접 조회 또는 일괄 처리 ( id in (1,2,3))의 경우 하위 쿼리가 다른 쿼리보다 훨씬 느립니다 (서브 쿼리를 다시 실행하기 때문에). 그러나 나는 leftjoin과 상관 솔루션을 속도면에서 구별 할 수 없었다.
마지막으로 leftjoin은 그룹에서 n * (n + 1) / 2 개의 조인을 생성하므로 성능은 그룹의 크기에 따라 크게 영향을받을 수 있습니다.
SQL 창 기능 솔루션을 제공하는 답변이 없다는 것에 놀랐습니다.
SELECT a.id, a.rev, a.contents
FROM (SELECT id, rev, contents,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
FROM YourTable) a
WHERE a.rank = 1
SQL 표준 ANSI / ISO 표준 SQL : 2003 및 이후에 ANSI / ISO 표준 SQL : 2008로 확장 된 추가, 창 (또는 창) 기능은 현재 모든 주요 공급 업체에서 사용할 수 있습니다. 동점 문제를 처리하는 데 사용할 수있는 더 많은 유형의 순위 함수가 있습니다 RANK, DENSE_RANK, PERSENT_RANK.
성능을 보증 할 수는 없지만 Microsoft Excel의 한계에서 영감을받은 트릭이 있습니다. 좋은 기능이 있습니다
좋은 물건
- 동점이 있더라도 하나의 "최대 레코드"만 강제로 반환해야합니다 (때로는 유용함).
- 조인이 필요하지 않습니다.
접근하다
약간 추악하며 rev 열의 유효한 값 범위에 대해 알아야합니다 . 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 예 있도록
- 3.2는 1003.201이됩니다.
- 57은 1057.001이됩니다.
- 923.88은 1923.881이됩니다.
올바르게 수행하면 두 숫자의 문자열 비교는 두 숫자의 숫자 비교와 동일한 "최대 값"을 산출해야하며 하위 문자열 함수를 사용하여 원래 숫자로 쉽게 다시 변환 할 수 있습니다 (한 형식 또는 다른 형식으로 사용 가능) 어디에나).
이것이 가장 쉬운 해결책이라고 생각합니다.
SELECT *
FROM
(SELECT *
FROM Employee
ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
SELECT *: 모든 필드를 반환합니다.FROM Employee: 테이블을 검색했습니다.(SELECT *...)subquery : 급여별로 정렬 된 모든 사람을 반환합니다.GROUP BY employeesub.Salary: 각 직원의 최상위 정렬 된 급여 행이 반환 된 결과가되도록합니다.
한 행만 필요한 경우 훨씬 더 쉽습니다.
SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1
또한 다른 목적으로 분해, 이해 및 수정하는 것이 가장 쉬운 방법이라고 생각합니다.
ORDER BY Employee.Salary DESC: 가장 높은 급여부터 급여별로 결과를 정렬합니다.LIMIT 1: 하나의 결과 만 반환합니다.
이러한 유사한 문제의 해결이 방법을 이해하는 것은 사소한된다 : 가장 낮은 급여 (변경으로 직원 수 DESC에를 ASC), 최고 열 소득 직원 (변경 얻을 LIMIT 1에이 LIMIT 10종류의 다른 필드 (변경에 의해,) ORDER BY Employee.Salary로 ORDER BY Employee.Commission), 등
이 같은?
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)
NOT EXIST이 문제에 대해 기반 솔루션 을 사용하고 싶습니다 .
SELECT
id,
rev
-- you can select other columns here
FROM YourTable t
WHERE NOT EXISTS (
SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)
이렇게하면 그룹 내에서 최대 값을 가진 모든 레코드가 선택되고 다른 열을 선택할 수 있습니다.
작업을 수행하는 또 다른 방법 MAX()은 OVER PARTITION 절에서 분석 함수를 사용하는 것입니다.
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
ROW_NUMBER()이 게시물에 이미 문서화 된 다른 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에서 잘 작동합니다.
MAX () 솔루션은 확실히 더 빨리 실행 ROW_NUMBER()하기 때문에 솔루션 MAX()의 복잡성이 O(n)동시에 ROW_NUMBER()복잡성이 최소가 테이블 레코드의 수를 나타냅니다!O(n.log(n))n
이 문제와 관련하여 가장 인기있는 질문이므로 여기에 또 다른 답변을 다시 게시하겠습니다.
이 작업을 수행하는 더 간단한 방법이있는 것 같습니다 (그러나 MySQL에서만 ).
select *
from (select * from mytable order by id, rev desc ) x
group by id
사용자 보헤미안의하십시오 신용 대답 에서 이 질문 이 문제에 등의 간결하고 우아한 답을 제공합니다.
편집 : 이 솔루션은 많은 사람들에게 작동하지만 MySQL은 GROUP BY 문이 GROUP BY 목록에없는 열에 대해 의미있는 값을 반환한다는 것을 보장하지 않기 때문에 장기적으로 안정적이지 않을 수 있습니다. 따라서이 솔루션을 사용하여 위험을 감수하십시오!
내가 거의 언급하지 않은 세 번째 솔루션은 MySQL 전용이며 다음과 같습니다.
SELECT id, MAX(rev) AS rev
, 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id
예, 끔찍해 보이지만 (문자열 및 뒤로 변환 등) 내 경험상 일반적으로 다른 솔루션보다 빠릅니다. 아마도 그것은 내 사용 사례에만 해당되지만 수백만 개의 레코드와 많은 고유 ID가있는 테이블에서 사용했습니다. 아마도 MySQL이 다른 솔루션을 최적화하는 데 상당히 나쁘기 때문일 수 있습니다 (적어도이 솔루션을 생각 해낸 5.0 일 동안).
한 가지 중요한 것은 GROUP_CONCAT가 구축 할 수있는 문자열의 최대 길이를 갖는다는 것입니다. group_concat_max_len변수 를 설정하여이 한계를 높이고 싶을 것입니다 . 행이 많은 경우 이는 확장에 제한이됩니다.
어쨌든 콘텐츠 필드가 이미 텍스트 인 경우 위의 내용이 직접 작동하지 않습니다. 이 경우 아마도 \ 0과 같은 다른 구분 기호를 사용하고 싶을 것입니다. 또한 group_concat_max_len한계에 더 빨리 도달 할 수 있습니다 .
mySQL 은 아니지만이 질문을 찾고 SQL을 사용하는 다른 사람들을 위해 가장 큰 n-per-group 문제 를 해결하는 또 다른 방법 Cross Apply은 MS SQL에서 사용하는 것 입니다.
WITH DocIds AS (SELECT DISTINCT id FROM docs)
SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
SELECT Top 1 * FROM docs d
WHERE d.id = d1.id
ORDER BY rev DESC
) d2
다음은 SqlFiddle의 예입니다.
select 문에 많은 필드가 있고 최적화 된 코드를 통해 이러한 모든 필드에 대한 최신 값을 원하는 경우 :
select * from
(select * from table_name
order by id,rev desc) temp
group by id
내 생각 엔 이걸 원해?
select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)
SQL Fiddle : 여기 확인
SELECT *
FROM Employee
where Employee.Salary in (select max(salary) from Employee group by Employe_id)
ORDER BY Employee.Salary
이 솔루션은 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
나는 이것을 사용할 것이다 :
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 절과 함께 사용할 수 있습니다. 내 작업 예제입니다 ( "firmy"테이블에 대한 문제와 동일하게 해결).
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은 하위 쿼리에 빌드 된 쿼리 필터가 아니라 짧은 상수 목록과 함께 사용하도록 제공됩니다. 스캔 된 모든 레코드에 대해 IN의 서브 쿼리가 수행되기 때문에 쿼리가 매우 오래 걸릴 수 있습니다.
이것은 어떤가요:
SELECT all_fields.*
FROM (SELECT id, MAX(rev) FROM yourtable GROUP BY id) AS max_recs
LEFT OUTER JOIN yourtable AS all_fields
ON max_recs.id = all_fields.id
여기에 좋은 방법이 있습니다.
다음 코드를 사용하십시오.
with temp as (
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)
나는 어떤 열로 레코드의 순위를 매기는 것을 좋아합니다. 이 경우으로 rev그룹화 된 순위 값 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두 번 쿼리하지 않습니다 .
rev 필드를 역순으로 정렬 한 다음 가장 높은 rev 값을 가진 각 그룹의 첫 번째 행을 제공하는 id로 그룹화했습니다.
SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;
Tested in http://sqlfiddle.com/ with the following data
CREATE TABLE table1
(`id` int, `rev` int, `content` varchar(11));
INSERT INTO table1
(`id`, `rev`, `content`)
VALUES
(1, 1, 'One-One'),
(1, 2, 'One-Two'),
(2, 1, 'Two-One'),
(2, 2, 'Two-Two'),
(3, 2, 'Three-Two'),
(3, 1, 'Three-One'),
(3, 3, 'Three-Three')
;
This gave the following result in MySql 5.5 and 5.6
id rev content
1 2 One-Two
2 2 Two-Two
3 3 Three-Two
here is another solution hope it will help someone
Select a.id , a.rev, a.content from Table1 a
inner join
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev
None of these answers have worked for me.
This is what worked for me.
with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max
Here's another solution to retrieving the records only with a field that has the maximum value for that field. This works for SQL400 which is the platform I work on. In this example, the records with the maximum value in field FIELD5 will be retrieved by the following SQL statement.
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)
Explanation
This is not pure SQL. This will use the SQLAlchemy ORM.
I came here looking for SQLAlchemy help, so I will duplicate Adrian Carneiro's answer with the python/SQLAlchemy version, specifically the outer join part.
This query answers the question of:
"Can you return me the records in this group of records (based on same id) that have the highest version number".
This allows me to duplicate the record, update it, increment its version number, and have the copy of the old version in such a way that I can show change over time.
Code
MyTableAlias = aliased(MyTable)
newest_records = appdb.session.query(MyTable).select_from(join(
MyTable,
MyTableAlias,
onclause=and_(
MyTable.id == MyTableAlias.id,
MyTable.version_int < MyTableAlias.version_int
),
isouter=True
)
).filter(
MyTableAlias.id == None,
).all()
Tested on a PostgreSQL database.
I used the below to solve a problem of my own. I first created a temp table and inserted the max rev value per unique id.
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
I then joined these max values (#temp1) to all of the possible id/content combinations. By doing this, I naturally filter out the non-maximum id/content combinations, and am left with the only max rev values for each.
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
You can make the select without a join when you combine the rev and id into one maxRevId value for MAX() and then split it back to original values:
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;
단일 테이블 대신 복잡한 조인이있을 때 특히 빠릅니다. 전통적인 접근 방식을 사용하면 복잡한 조인이 두 번 수행됩니다.
상기 비트의 조합은 기능을 간단 언제 rev하고 id있다 INT UNSIGNED(32 비트) 및 값에 맞는 결합 BIGINT UNSIGNED(64 비트). 때 id및 rev32 비트 값보다 크거나 여러 열을 만들어, 당신은 적합한 패딩 이진 값을 예로 값을 결합 필요 MAX().
참고 URL : https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column
'Programing' 카테고리의 다른 글
| 비동기 실행과 동기 실행, 이것이 실제로 무엇을 의미합니까? (0) | 2020.09.27 |
|---|---|
| PHP를 사용한“주의 : 정의되지 않은 변수”,“주의 : 정의되지 않은 인덱스”,“주의 : 정의되지 않은 오프셋” (0) | 2020.09.27 |
| Git과 Dropbox를 효과적으로 함께 사용하십니까? (0) | 2020.09.27 |
| PHP에서 클라이언트 IP 주소를 얻는 방법 (0) | 2020.09.27 |
| 다른 분기에서 하나의 파일 만 가져 오는 방법 (0) | 2020.09.27 |