Проблема с трудным соединением TSQL

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

Мой естественный (и процедурный) подход таков: «для каждого пользователя в таблице T выберите 10 лучших результатов, упорядоченных по дате».

Каждый раз, когда я пытаюсь сформулировать вопрос в уме, используя подход, основанный на множестве, я продолжаю сталкиваться с термином "foreach".

Можно ли сделать что-то вроде этого:

SELECT *
FROM table AS t1
INNER JOIN (
    SELECT TOP 10 *
    FROM table AS t2
    WHERE t2.id = t1.id
    ORDER BY date DESC
)

Или даже

SELECT (    SELECT TOP 10 *
             FROM table AS t2
             WHERE t2.id = t1.id
             ORDER BY date    )
FROM table AS t1

Или есть другое решение с использованием временных таблиц, о котором я должен подумать?

ИЗМЕНИТЬ:

Просто чтобы быть совершенно ясным - мне нужно 10 лучших результатов для каждого пользователя в таблице, например. 10 * N, где N = количество пользователей.

ИЗМЕНИТЬ:

В ответ на предложение RBarryYoung у меня возникла проблема, которую лучше всего продемонстрировать с помощью кода:

CREATE TABLE #temp (id INT, date DATETIME)

INSERT INTO #temp (id, date) VALUES (1, GETDATE())
INSERT INTO #temp (id, date) VALUES (1, GETDATE())

SELECT *
FROM #temp AS t1
CROSS APPLY (
 SELECT TOP 1 *
 FROM #temp AS t2
 WHERE t2.id = t1.id
 ORDER BY t2.date DESC
) AS t2

DROP TABLE #temp

Запустив это, вы можете видеть, что это не ограничивает результаты ТОП 1 ... Я делаю что-то не так?

ИЗМЕНИТЬ:

Кажется, мой последний пример вызвал некоторую путаницу. Вот пример, показывающий, что я хочу сделать:

CREATE TABLE #temp (id INT, date DATETIME)
INSERT INTO #temp (id, date) VALUES (1, GETDATE())
INSERT INTO #temp (id, date) VALUES (1, GETDATE())
INSERT INTO #temp (id, date) VALUES (1, GETDATE())
INSERT INTO #temp (id, date) VALUES (2, GETDATE())

SELECT *
FROM #temp AS t1
CROSS APPLY
(
    SELECT TOP 2 *
 FROM #temp AS t2
    WHERE t2.id = t1.id
    ORDER BY t2.date DESC
) AS t2

DROP TABLE #temp

Это выводит:

1 2009-08-26 09:05:56.570 1 2009-08-26 09:05:56.583
1 2009-08-26 09:05:56.570 1 2009-08-26 09:05:56.583
1 2009-08-26 09:05:56.583 1 2009-08-26 09:05:56.583
1 2009-08-26 09:05:56.583 1 2009-08-26 09:05:56.583
1 2009-08-26 09:05:56.583 1 2009-08-26 09:05:56.583
1 2009-08-26 09:05:56.583 1 2009-08-26 09:05:56.583
2 2009-08-26 09:05:56.583 2 2009-08-26 09:05:56.583

Если я использую отдельные:

SELECT DISTINCT t1.id
FROM #temp AS t1
CROSS APPLY
(
    SELECT TOP 2 *
 FROM #temp AS t2
    WHERE t2.id = t1.id
    ORDER BY t2.date DESC
) AS t2

я получил

1
2

я нуждаюсь

1
1
2

Кто-нибудь знает возможно ли это?

ИЗМЕНИТЬ:

Следующий код сделает это

WITH RowTable AS
(
SELECT 
 id, date, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RowNum
FROM #temp 
)
SELECT *
FROM RowTable
WHERE RowNum <= 2;

Писал в комментариях, но форматирования кода нет, поэтому выглядит не очень красиво.


tsql join
person Khanzor    schedule 25.08.2009    source источник
comment
Вы просили об этом не для того, чтобы ограничить результаты ТОП-1, не так ли? Вы просили, чтобы он имитировал результаты для пользователя TOP 1 foreach, который отличается. Но я прав, что мой первоначальный ответ не учитывал, что пользователь (идентификатор) был продублирован в исходной таблице: теперь исправлено.   -  person RBarryYoung    schedule 25.08.2009
comment
@RBarryYoung - Извини, приятель. Я добавил пример, чтобы продемонстрировать именно то, что я хочу. Это было немного упрощенно с моей стороны :(   -  person Khanzor    schedule 26.08.2009

Ответы (5)


arrow_upward
4
arrow_downward

Да, есть несколько разных хороших способов сделать это в 2005 и 2008 годах. Наиболее похожим на то, что вы уже пробовали, является CROSS APPLY:

SELECT T2.*
FROM (
    SELECT DISTINCT ID FROM table
) AS t1
CROSS APPLY (
    SELECT TOP 10 *
    FROM table AS t2
    WHERE t2.id = t1.id
    ORDER BY date DESC
) AS t2
ORDER BY T2.id, date DESC

Затем это возвращает десять самых последних записей в [table] (или столько, сколько существует, до 10) для каждого отдельного [id]. Предполагая, что [id] соответствует пользователю, это должно быть именно то, что вы просите.

(редактирование: небольшие изменения, потому что я не учел, что T1 и T2 были одними и теми же таблицами, и поэтому будет несколько повторяющихся идентификаторов t1.ID, соответствующих нескольким повторяющимся идентификаторам T2.)

person RBarryYoung    schedule 25.08.2009
comment
+1 Отличное предложение! Я никогда раньше не видел CROSS APPLY. Однако это не ограничивает результаты до 10? См. последнее редактирование для примера. - person Khanzor; 25.08.2009
comment
Сверху выберите * поставьте select n * 10, если вы хотите, чтобы 3 пользователя использовали select top 30, но в этом случае у каждого пользователя должно быть не менее 10 результатов... - person THEn; 25.08.2009
comment
@THEn - это не совсем правильно, это просто ограничит общее количество результатов, оставив при этом те, которые имеют более 10 в наборе запросов. - person Khanzor; 25.08.2009
comment
Ханзор: Если я понимаю ваши опасения, то (DISTINCT) подзапрос, который я добавил, должен это исправить (извините, я не могу тестировать отсюда, поэтому пишу вслепую). - person RBarryYoung; 25.08.2009

arrow_upward
0
arrow_downward

Это возможно, однако использование вложенных запросов будет медленнее.

Следующее также найдет результаты, которые вы ищете:

SELECT TOP 10 * 
FROM table as t1
INNER JOIN table as t2 
  ON t1.id = t2.id
ORDER BY date DESC
person Russell    schedule 25.08.2009
comment
Это вернет только 10 результатов, мне нужно выбрать 10 для каждого пользователя. - person Khanzor; 25.08.2009
comment
Ах хорошо, извините за это! Ваш первый запрос будет работать быстрее, чем второй. - person Russell; 25.08.2009

arrow_upward
0
arrow_downward

Я считаю, что это SO вопрос ответит на ваш вопрос. Это не ответ на тот же самый вопрос, но я думаю, что решение подойдет и вам.

person Jeff Leonard    schedule 25.08.2009
comment
Не совсем - я хочу просто выбрать 10 лучших результатов или меньше, этот ответ, кажется, выбирает только тогда, когда ранг ниже этого числа. Это похоже на фразу HAVING COUNT(*)? - person Khanzor; 25.08.2009

arrow_upward
0
arrow_downward

Вот трюк, который я использую, чтобы выполнить этот тип запроса «top-N-per-group»:

SELECT t1.id
FROM table t1 LEFT OUTER JOIN table t2 
 ON (t1.user_id = t2.user_id AND (t1.date > t2.date
     OR t1.date = t2.date AND t1.id > t2.id))
GROUP BY t1.id
HAVING COUNT(*) < 10
ORDER BY t1.user_id, COALESCE(COUNT(*), 0);
person Bill Karwin    schedule 25.08.2009
comment
Это возвращает только отдельные T1.ids? - person RBarryYoung; 25.08.2009
comment
Если вам нужно больше столбцов, вы должны включить их в предложение GROUP BY. Или же вы можете использовать приведенное выше как подзапрос в предикате IN(), чтобы вы могли получить остальные пользовательские данные. - person Bill Karwin; 25.08.2009

arrow_upward

arrow_downward

person    schedule
comment
Ну, по какой-то причине это не работает с таблицей примеров, которую я дал, например. SELECT id, date, ROW_NUMBER() OVER (РАЗДЕЛЕНИЕ ПО id ORDER BY date DESC) AS RowNum FROM #temp WHERE RowNum ‹= 2; Но если он изменен для использования инструкции WITH blah AS: WITH RowTable AS ( SELECT id, date, ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS RowNum FROM #temp ) SELECT * FROM RowTable WHERE RowNum ‹= 2; Это работает нормально. - person Khanzor; 26.08.2009
comment
Я возился с подобной проблемой в течение нескольких часов, пытаясь решить ее любым способом - с помощью метода ROW_NUMBER() только что 3-минутный запрос сократился до 10 секунд. - person DanB; 23.02.2011