Удаление Дубликатов Без CTE С использованием Пользовательского порядка сортировки SQL Server

ProgramBox

Удаление Дубликатов Без CTE С использованием Пользовательского порядка сортировки SQL Server

Post author:admin
Запись опубликована:13 января, 2022
Post category:Вопросы по программированию

#sql #sql-server #tsql

Вопрос:

У меня есть следующая таблица:

 UserName | Score | Position | Class
Larry    | 0.12   | 1       | 2
Larry    | 0.13   | 2       | 2
Larry    | 0.12   | 3       | 2
Gale     | 0.112  | 4       | 2
Smith    | 0.1    | 5       | 2
Gale     | 0.21   | 6       | 2
Smith    | 0.42   | 7       | 2
Gale     | 0.112  | 8       | 2
Smith    | 0.42   | 9       | 2

Что мне нужно, так это получить уникальное имя пользователя, принадлежащее классу 2, вместе с наивысшим баллом и лучшей (самой низкой) позицией, т. Е. Из дубликатов выберите тот, у которого самый высокий балл и самая низкая позиция. Наконец, отсортируйте итоговый набор результатов, используя наивысший балл и самую низкую позицию. Например, приведенная выше таблица должна возвращать:

 Smith| 0.42 | 7 | 2
Gale | 0.21 | 6 | 2
Larry| 0.13 | 2 | 2

Запрос, который я задал, выглядит следующим образом:

 ;WITH Filtered AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY [UserName] ORDER BY [Score]  DESC, [Position] --get unique UserNames getting the row with highest score and lowest position
            ) AS RowN
    FROM [dbo].[MyTable]
    WHERE [Class] = 2
)
SELECT *
FROM Filtered
WHERE RowN = 1
ORDER BY [Score] DESC, [Position] -- sort final set based on Score and Position

Вопрос в том, существует ли способ, при котором то же самое можно сделать без CTE (или подзапроса, или временной таблицы, или любого держателя результата) и, скорее всего, в одном операторе select?

Для указанного запроса используется T-SQL.

1. Какова мотивация для нежелания использовать CTE? У вас, вероятно, неверное представление о них.

2. Точка с запятой является терминатором оператора, а не клуджем, чтобы избежать исправления отсутствия такового в предыдущем операторе (и в вашем операторе его тоже нет).

3. @Tayyab почему вы считаете, что решение без CTE было бы лучше? Какую реальную проблему вы хотите решить? Использование ROW_NUMBER() либо в CTE, либо в подзапросе потребует только сканирования одной таблицы. На самом деле, поскольку и ROW_NUMBER внешний, и внешний ORDER BY используют один и тот же порядок сортировки, сервер может выполнять только одну сортировку

4. Я бы повторил беспокойство по поводу того, чтобы просить сделать это без CTE. Понимание того, что SQL является декларативным языком и что он, по сути, скомпилирован в соответствии с планом выполнения, вероятно, будет полезным знанием для вас. Еще лучше-осознать, что вы просто предполагаете, что один шаблон имеет преимущества в ресурсах или производительности по сравнению с другим, что является как ложным предположением, так и явно непроверенным. Наконец, рассмотрение этих вопросов до того, как они материализуются как реальные/конкретные проблемы, является самой сутью преждевременной оптимизации.

5. Я подозреваю, что мы не собираемся выяснять, почему ОП выступает против CTEs; к сожалению, скорее всего, ответ на этот вопрос скажет нам, в чем заключается реальный задаваемый вопрос. Это фактически похоже на намеренное требование другой отвертки, когда у вас есть отвертка правильной формы и размера для винта, который у вас есть, и замена, которую вы получаете, имеет только правильный размер или правильную форму (но не то и другое).

Ответ №1:

Использование MAX() и FIRST_VALUE() оконные функции:

 SELECT DISTINCT 
       Username,
       MAX(Score) OVER (PARTITION BY Username) Score,
       FIRST_VALUE(Position) OVER (PARTITION BY Username ORDER BY Score DESC, Position) Position,
       Class
FROM MyTable       
WHERE Class = 2
ORDER BY Score DESC, Position

Смотрите демонстрацию.

Ответ №2:

Вы могли бы сформулировать это так:

 select top (1) with ties username, score, min(position) as position, class
from t
where class = 2
group by username, score, class
order by row_number() over (partition by username order by score desc);

Обычно это происходит медленнее, чем альтернативные методы, использующие CTE или подзапросы, но, похоже, это удовлетворяет ваше желание только одного SELECT .

1. Хотя возвращаемый результирующий набор точен, все же окончательные данные не отсортированы в указанном порядке, вероятно, вы пропустили сортировку по позициям.

2. Добавление position ORDER BY пункта нарушило бы это решение, @Tayyab , так как тогда нет никаких связей.

Ответ №3:

Моя идея состояла бы в том, чтобы самостоятельно присоединиться к столу. По крайней мере, он отлично работает с тестовыми данными

 select t1.username, max(t1.score) score, min(t2.position) position, t1.class
  from test t1
  join test t2
    on t1.username = t2.username 
 where t1.class = 2
 group by t1.username, t2.score, t1.class
having t2.score = max(t1.score) 
 order by score desc, position

1. Работает как заклинание. Спасибо.

2. @Tayyab делает это? Вы сравнивали планы выполнения? Для этого потребуется два сканирования таблиц и буферизация сгруппированных результатов перед их фильтрацией having . С другой стороны, использование ROW_NUMBER() либо в CTE, либо в подзапросе (они эквивалентны) потребует только одного сканирования

3. @PanagiotisKanavos Да, это работает, однако производительность едва ли сопоставима с тем, что указано в вопросе. Спасибо, что указали.

4. @Tayyab что это barely comparable значит? Каковы индексы? Планы выполнения? Если бы все задействованные поля были проиндексированы, самостоятельное объединение не могло бы быть таким быстрым именно потому, что оно должно вычислять промежуточные результаты группы.

Метки: Удаление Дубликатов Без CTE С использованием Пользовательского порядка сортировки SQL Server