중복이 SQL과 테이블에 위에 각 그룹에서 3 값을 선택하는 방법 [중복]

우리는 두 개의 열이있는 테이블이 있다고 가정 한 열은 어떤 사람의 이름을 포함하는 다른 열은 각 사람에 관한 몇 가지 값이 포함되어 있습니다. 한 사람이 두 개 이상의 값을 가질 수 있습니다. 각 값은 숫자 유형이 있습니다. 문제는 우리가 테이블에서 각 사람에 대한 상위 3 개 값을 선택 할 수 있습니다. 한 사람이 3 개보다 작은 값이있는 경우, 우리는 그 사람에 대한 모든 값을 선택합니다.

SQL로 테이블에 3 값을 각 그룹에서이 문서를 선택 상단에 제공되는 요청에 의한 테이블에 중복이없는 경우이 문제는 해결 될 수있다. 중복이있는 경우에, 해결책은 무엇인가?

예를 들어, 하나의 이름 존에 대해, 그는 그와 관련된 5 개 값이 있습니다. 그들은 20,7,7,7,4이다. 나는 각각의 이름에 대한 값으로 하강하기 위해 다음과 같이 이름 / 값 쌍을 반환해야합니다

-----------+-------+
| name     | value |
-----------+-------+
| John     |    20 |
| John     |     7 |
| John     |     7 |
-----------+-------+

만 3 행은 존 세 7S가 비록 존 반환해야합니다.

해결법

==============================

1.많은 현대 DBMS (예를 들어 포스트 그레스, 오라클, SQL-서버, DB2 및 많은 다른 사람)에, 다음은 잘 작동합니다. 그것은 CTE를 최신 SQL 표준의 일부 순위 함수 ROW_NUMBER ()를 사용합니다 :

많은 현대 DBMS (예를 들어 포스트 그레스, 오라클, SQL-서버, DB2 및 많은 다른 사람)에, 다음은 잘 작동합니다. 그것은 CTE를 최신 SQL 표준의 일부 순위 함수 ROW_NUMBER ()를 사용합니다 :

 WITH cte AS
  ( SELECT name, value,
           ROW_NUMBER() OVER (PARTITION BY name
                              ORDER BY value DESC
                             )
             AS rn
    FROM t
  )
SELECT name, value, rn
FROM cte
WHERE rn <= 3
ORDER BY name, rn ;

CTE 만 ROW_NUMBER () 없음 :

SELECT name, value, rn
FROM 
  ( SELECT name, value,
           ROW_NUMBER() OVER (PARTITION BY name
                              ORDER BY value DESC
                             )
             AS rn
    FROM t
  ) tmp 
WHERE rn <= 3
ORDER BY name, rn ;

에서 테스트 :

순위 기능이없는 MySQL과 다른 DBMS에서, 하나는 중 파생 테이블, 상관 관계 서브 쿼리 또는 GROUP BY와 함께 자체 조인을 사용할 수 있습니다.

제 (TID)는 테이블의 기본 키 것으로 가정된다 :

SELECT t.tid, t.name, t.value,              -- self join and GROUP BY
       COUNT(*) AS rn
FROM t
  JOIN t AS t2
    ON  t2.name = t.name
    AND ( t2.value > t.value
        OR  t2.value = t.value
        AND t2.tid <= t.tid
        )
GROUP BY t.tid, t.name, t.value
HAVING COUNT(*) <= 3
ORDER BY name, rn ;


SELECT t.tid, t.name, t.value, rn
FROM
  ( SELECT t.tid, t.name, t.value,
           ( SELECT COUNT(*)                -- inline, correlated subquery
             FROM t AS t2
             WHERE t2.name = t.name
              AND ( t2.value > t.value
                 OR  t2.value = t.value
                 AND t2.tid <= t.tid
                  )
           ) AS rn
    FROM t
  ) AS t
WHERE rn <= 3
ORDER BY name, rn ;

MySQL의에서 테스트

==============================
2.나는 질문을 downvote하려고 했어요. 그러나, 나는 정말 데이터베이스 간 솔루션을 요구 할 수 있음을 깨달았다.

나는 질문을 downvote하려고 했어요. 그러나, 나는 정말 데이터베이스 간 솔루션을 요구 할 수 있음을 깨달았다.

당신이 할 수있는 데이터베이스 독립적 인 방법을 찾고 가정, 내가 사용 생각할 수있는 유일한 방법은 하위 쿼리 (또는 비 결 합을) 상관 관계. 다음은 그 예이다 :
```
select distinct t.personid, val, rank
from (select t.*,
             (select COUNT(distinct val) from t t2 where t2.personid = t.personid and t2.val >= t.val
             ) as rank
      from t
     ) t
where rank in (1, 2, 3)
```
그러나, 당신이 언급하는 각 데이터베이스 (내가주의는 하둡은 데이터베이스 아니다)이 일을 더 나은 방법이있다. 불행하게도, 그들 중 누구도 표준 SQL 없다.

여기가 SQL 서버에서 작업의 예입니다 :
```
with t as (
      select 1 as personid, 5 as val union all
      select 1 as personid, 6 as val union all
      select 1 as personid, 6 as val union all
      select 1 as personid, 7 as val union all
      select 1 as personid, 8 as val
     )
select distinct t.personid, val, rank
from (select t.*,
             (select COUNT(distinct val) from t t2 where t2.personid = t.personid and t2.val >= t.val
             ) as rank
      from t
     ) t
where rank in (1, 2, 3);
```

==============================

3.GROUP_CONCAT를 사용하고 FIND_IN_SET 당신은 that.Check SQLFIDDLE 할 수 있습니다.

GROUP_CONCAT를 사용하고 FIND_IN_SET 당신은 that.Check SQLFIDDLE 할 수 있습니다.

SELECT *
FROM tbl t
WHERE FIND_IN_SET(t.value,(SELECT
                             SUBSTRING_INDEX(GROUP_CONCAT(t1.value ORDER BY VALUE DESC),',',3)
                           FROM tbl t1
                           WHERE t1.name = t.name
                           GROUP BY t1.name)) > 0
ORDER BY t.name,t.value desc

==============================
4.당신의 결과 세트가 너무 무거운없는 경우 결과 집합을 반복 처리하는 간단한 비교 알고리즘에 의해 bigges 세를 발견 한 문제에 대한 저장 프로 시저 (또는 익명 PL / SQL 블록)를 작성할 수 있습니다.

당신의 결과 세트가 너무 무거운없는 경우 결과 집합을 반복 처리하는 간단한 비교 알고리즘에 의해 bigges 세를 발견 한 문제에 대한 저장 프로 시저 (또는 익명 PL / SQL 블록)를 작성할 수 있습니다.

==============================

5.이 시도 -

이 시도 -

CREATE TABLE #list ([name] [varchar](100) NOT NULL, [value] [int] NOT NULL)
INSERT INTO #list VALUES ('John', 20), ('John', 7), ('John', 7), ('John', 7), ('John', 4);

WITH cte
AS (
SELECT NAME
    ,value
    ,ROW_NUMBER() OVER (
        PARTITION BY NAME ORDER BY (value) DESC
        ) RN
FROM #list
)
SELECT NAME
,value
FROM cte
WHERE RN < 4
ORDER BY value DESC

==============================

6.이것은 MS SQL 작동합니다. 또는 절 (또는 equivelant)을 통해 그룹에 할당 행 번호에 대한 능력을 가지고 다른 SQL 언어에서 실행할 수 있어야한다

이것은 MS SQL 작동합니다. 또는 절 (또는 equivelant)을 통해 그룹에 할당 행 번호에 대한 능력을 가지고 다른 SQL 언어에서 실행할 수 있어야한다

if object_id('tempdb..#Data') is not null drop table #Data;
GO

create table #data (name varchar(25), value integer);
GO
set nocount on;
insert into #data values ('John', 20);
insert into #data values ('John', 7);
insert into #data values ('John', 7);
insert into #data values ('John', 7);
insert into #data values ('John', 5);
insert into #data values ('Jack', 5);
insert into #data values ('Jane', 30);
insert into #data values ('Jane', 21);
insert into #data values ('John', 5);
insert into #data values ('John', -1);
insert into #data values ('John', -1);
insert into #data values ('Jane', 18);
set nocount off;
GO

with D as (
SELECT
     name
    ,Value
    ,row_number() over (partition by name order by value desc) rn
From
    #Data
)
SELECT Name, Value
FROM D
WHERE RN <= 3
order by Name, Value Desc

Name    Value
Jack    5
Jane    30
Jane    21
Jane    18
John    20
John    7
John    7

from https://stackoverflow.com/questions/16720525/how-to-select-top-3-values-from-each-group-in-a-table-with-sql-which-have-duplic by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 대 'PATINDEX' '와 같은'SQL 문자열 비교 속도 (0)	2020.07.02
[SQL] 어떻게 분할 쉼표는 MySQL을 저장 프로 시저의 텍스트를 분리 (0)	2020.07.01
[SQL] SQL 쿼리에서 역 중복 제거 (0)	2020.07.01
[SQL] SQL 서버 : 어떻게 저장 프로 시저의 매개 변수로 데이터베이스 이름을 얻을 수 있습니다 (0)	2020.07.01
[SQL] 다른 열 그룹화 다른 칼럼의 최대 값에 기초 얻기 [중복] (0)	2020.07.01

복붙노트

[SQL] 중복이 SQL과 테이블에 위에 각 그룹에서 3 값을 선택하는 방법 [중복]

중복이 SQL과 테이블에 위에 각 그룹에서 3 값을 선택하는 방법 [중복]

해결법

1.많은 현대 DBMS (예를 들어 포스트 그레스, 오라클, SQL-서버, DB2 및 많은 다른 사람)에, 다음은 잘 작동합니다. 그것은 CTE를 최신 SQL 표준의 일부 순위 함수 ROW_NUMBER ()를 사용합니다 :

2.나는 질문을 downvote하려고 했어요. 그러나, 나는 정말 데이터베이스 간 솔루션을 요구 할 수 있음을 깨달았다.

3.GROUP_CONCAT를 사용하고 FIND_IN_SET 당신은 that.Check SQLFIDDLE 할 수 있습니다.

4.당신의 결과 세트가 너무 무거운없는 경우 결과 집합을 반복 처리하는 간단한 비교 알고리즘에 의해 bigges 세를 발견 한 문제에 대한 저장 프로 시저 (또는 익명 PL / SQL 블록)를 작성할 수 있습니다.

5.이 시도 -

6.이것은 MS SQL 작동합니다. 또는 절 (또는 equivelant)을 통해 그룹에 할당 행 번호에 대한 능력을 가지고 다른 SQL 언어에서 실행할 수 있어야한다

'SQL' 카테고리의 다른 글

티스토리툴바