Как выбрать верхние 3 значения из каждой группы в таблице с SQL, которые имеют дубликаты

Ответ 1

Во многих современных СУБД (например, Postgres, Oracle, SQL-Server, DB2 и многих других) следующее будет работать нормально. Он использует CTE и функцию ранжирования ROW_NUMBER(), которая является частью последнего стандарта SQL:

 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 ; 

Протестировано:

• Postgres
• Oracle
• SQL-сервер

В MySQL и других СУБД, которые не имеют ранжирующих функций, нужно использовать либо производные таблицы, коррелированные подзапросы, либо самосоединиться с 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

Я собирался уменьшить вопрос. Однако я понял, что действительно может потребоваться решение для кросс-баз данных.

Предполагая, что вы ищете независимый от базы данных способ сделать это, единственный способ, которым я могу думать, - использовать коррелированные подзапросы (или не equijoins). Вот пример:

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)

Однако каждая база данных, которую вы упоминаете (и я отмечаю, Hadoop не является базой данных), имеет лучший способ сделать это. К сожалению, ни один из них не является стандартным SQL.

Вот пример его работы в SQL Server:

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, вы можете это сделать. 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

Если ваш результирующий набор не настолько тяжелый, вы можете написать хранимую процедуру (или анонимный 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. Должен быть работоспособным на любом другом диалекте 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