Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Приджойнить поле с максимальным значением  [new]
Dmitry.Dennisov
Member

Откуда:
Сообщений: 129
Народ, всем привет!

Есть вопрос - есть ответ, но хотелось бы более изящное решение, если такое есть:

Две таблички t1(id, ff /*просто поле*/) и t2(id, t1_id, f1 /*Дата*/ , f2 /*Описание*/)
Надо выбрать left join c t2 поля f2 где f1 максимальное ...как это проще реализовать?

Я сделал так:
select tt.t1_id, mDate, t2.f2, ff from
(select * from t1
left join (select t1_id, max(f1) as mDate from t2 group by t1_id) as t on t.t1_id = t1.id) as tt
left join t2 on tt.t1_id = t2.id and tt.mDate = t2.f1


Надеюсь несильно замориочил...постоянно приходиться так объединять,
думаю может можно как-то проще?


Заранее спасибо!
25 июн 14, 11:16    [16215769]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8308
Список лидеров групп можно выбрать при помощи:
select top (1) with ties id ... order by row_numer() over (partition by id order by id desc)

не используя дополнительный запрос с агрегированием.
25 июн 14, 11:44    [16216072]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
Добрый Э - Эх
Guest
Dmitry.Dennisov,

Способы выбрать максимум указаны тут.
Джойн со второй таблицей сам допилишь...
25 июн 14, 11:46    [16216096]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Добрый Э - Эх
Dmitry.Dennisov,

Способы выбрать максимум указаны тут.
Джойн со второй таблицей сам допилишь...


Рекомендую способ номер 6 в этой ссылке через cross apply или outer apply. Когда я решал задачу, аналогичную вашей, то из перечисленных способов он оказался самым быстрым. Естественно вам надо попробовать на вашей конкретной БД, что лучше.
25 июн 14, 12:00    [16216270]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
Dmitry.Dennisov
Member

Откуда:
Сообщений: 129
Владислав Колосов; Добрый Э - Эх; a_voronin - огромное спасибо за подсказки, то что надо!
25 июн 14, 14:01    [16217346]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Dmitry.Dennisov,


Мои коллеги подсказывают, что на 2012 вот такой вариант оказывается самым крутым. distinct - FIRST_VALUE

8) -- Для версии сервера 2012 и выше
select t1.*
from [Таблица] t1 left join
(
select distinct t2.[Клиент], FIRST_VALUE(t2.[Дата] OVER ORDER BY [Дата] DESC) as [Дата]
from [Таблица] t2
where t2.[Клиент]=t1.[Клиент]
group by t2.[Клиент]
) v
where t2.[Дата]=t1.[Дата];
25 июн 14, 14:24    [16217517]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
гость 18
Guest
a_voronin
Dmitry.Dennisov,


Мои коллеги подсказывают, что на 2012 вот такой вариант оказывается самым крутым. distinct - FIRST_VALUE

8) -- Для версии сервера 2012 и выше
select t1.*
from [Таблица] t1 left join
(
select distinct t2.[Клиент], FIRST_VALUE(t2.[Дата] OVER ORDER BY [Дата] DESC) as [Дата]
from [Таблица] t2
where t2.[Клиент]=t1.[Клиент]
group by t2.[Клиент]
) v
where t2.[Дата]=t1.[Дата];


ошибка в запросе
25 июн 14, 15:53    [16218155]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
a_voronin
Мои коллеги подсказывают, что на 2012 вот такой вариант оказывается самым крутым. distinct - FIRST_VALUE
А ваши коллеги или вы сами доказательства этой крутости сможете предоставить?
Особо впечатлило distinct + group by, не говоря уже о том, что сей опус даже не скомпилируется...
25 июн 14, 16:20    [16218392]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
invm
a_voronin
Мои коллеги подсказывают, что на 2012 вот такой вариант оказывается самым крутым. distinct - FIRST_VALUE
А ваши коллеги или вы сами доказательства этой крутости сможете предоставить?
Особо впечатлило distinct + group by, не говоря уже о том, что сей опус даже не скомпилируется...


Group by не нужен. А возбухать не надо. Попробуйте сами. На том запросе который оптимизировали

SELECT DISTINCT FIRST_VALUE 1 минута против 6 минут на OUTER APPLY SELECT TOP 1

Запросы кидать сюда не буду. Может под вечер напишу суррогатный тест.
25 июн 14, 16:27    [16218449]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
Mikle83
Member

Откуда: Санкт-Петербург
Сообщений: 653
[quot a_voronin]
invm
пропущено...
SELECT DISTINCT FIRST_VALUE 1 минута против 6 минут на OUTER APPLY SELECT TOP 1
Запросы кидать сюда не буду. Может под вечер напишу суррогатный тест.


Довольно занятная метрика... Состояние кэша данных, иные процессы на СУБД, загруженность процессора при этом идентичны?
Есть ли возможность статистику io вывести на обоих запросах?
25 июн 14, 16:41    [16218544]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
[quot Mikle83]
a_voronin
пропущено...


Довольно занятная метрика... Состояние кэша данных, иные процессы на СУБД, загруженность процессора при этом идентичны?
Есть ли возможность статистику io вывести на обоих запросах?


Ситуация такая, боевой запрос кинуть не могу. Написать отдельный скрипт надо время. В общем, если кто-то хочет проверить. То вот моя вводная.

Если требуется выбирать для записи первой таблицы по одной из нескольких записей из второй таблицы, то на SQL 2012 предположительно оптимальное решение делается через LEFT JOIN на SELECT DISTINCT <ключ джойна> FIRST_VALUE(<выбираемое значение>) OVER (PARTITION BY <ключ джойна> ORDER BY <порядок приоритета выбирания>).

Я делаю это заявление с оговоркой ПРЕДПОЛОЖИТЕЛЬНО, желающим предлагаю проверить. Провести независимые тесты. Ваше мнение лично мне будет интересно. Я сам до cих пол полагался на OUTER APPLY.

Mikle83
Состояние кэша данных, иные процессы на СУБД, загруженность процессора, статистику
предлагаю промониторить

Грубый критерий -- это вывести планы обоих запросов и посмотреть как соотноситься их трудоемкость в процентах.
25 июн 14, 16:57    [16218662]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
a_voronin
Попробуйте сами.
С чего вдруг я должен доказывать ваши утверждения? Да еще и исправлять ваши же синтаксически неверные запросы?
a_voronin
Может под вечер напишу суррогатный тест.
Ждем с нетерпением.
25 июн 14, 17:00    [16218689]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
invm
a_voronin
Попробуйте сами.
С чего вдруг я должен доказывать ваши утверждения? Да еще и исправлять ваши же синтаксически неверные запросы?


Вы можете на SQL 2000 работать и решать эту задачу через GROUP BY
25 июн 14, 17:58    [16219134]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Вот суррогатный тест. Сравнения OUTER APPLY и LEFT JOIN SELECT DISTINCT FIRST_VALUE. Но что-то у меня результаты противоречивые. План показывает преимущества второго, профайлер преимущества первого. Предлагаю остальным попробовать.

В любом случае существует ещё один способ решения обсуждавшейся задачи через LEFT JOIN SELECT DISTINCT FIRST_VALUE. На нашем боевом кейсе получился значительный выигрыш.



IF OBJECT_ID('tempdb..#T') IS NOT NULL 
DROP TABLE #T
IF OBJECT_ID('tempdb..#TT') IS NOT NULL 
DROP TABLE #TT

CREATE TABLE #T (N BIGINT NOT NULL, N1 AS N % 47629 PERSISTED, N2 AS N % 63949 PERSISTED);
CREATE CLUSTERED INDEX IX_#T ON #T(N, N1, N2);
CREATE TABLE #TT (N1 BIGINT NOT NULL);
CREATE CLUSTERED INDEX IX_#TT ON #TT(N1);

WITH N100 AS 
( -- цифры от 0 до 99
	SELECT 0 AS N
	UNION ALL 
	SELECT N100.N + 1 FROM N100
	WHERE N100.N < 99
) 
INSERT INTO #T(N)
SELECT N = N1.N + N2.N * CAST(100 AS BIGINT)+ N3.N * CAST(10000 AS BIGINT) + N4.N * CAST(100000 AS BIGINT) FROM N100 AS N1, N100 AS N2, N100 AS N3, N100 AS N4
WHERE N4.N < 2; -- эту цифру можно менять в диапазоне от 1 до 100 для увеличения объема данных 

INSERT INTO #TT(N1)
SELECT DISTINCT N1 FROM #T;

IF OBJECT_ID('tempdb..#Result1') IS NOT NULL 
DROP TABLE #Result1
IF OBJECT_ID('tempdb..#Result2') IS NOT NULL 
DROP TABLE #Result2

PRINT CONVERT(CHAR(25), GETDATE(), 121)

SELECT T1.N1, T2.N 
INTO #Result1
FROM #TT T1
OUTER APPLY 
(
	SELECT TOP 1 N FROM #T T2
	WHERE T2.N1 = T1.N1
	ORDER BY N2 DESC 
) T2

PRINT CONVERT(CHAR(25), GETDATE(), 121)

SELECT T1.N1, T2.N 
INTO #Result2
FROM #TT T1
LEFT JOIN 
(
	SELECT DISTINCT T2.N1,  
		N = FIRST_VALUE(N) OVER (PARTITION BY T2.N1 ORDER BY N2 DESC)
	FROM #T T2
) T2 ON T2.N1 = T1.N1

PRINT CONVERT(CHAR(25), GETDATE(), 121)
25 июн 14, 19:21    [16219544]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
a_voronin,

В вашем тесте нет никакого смысла, ибо нет индекса по #T(N1, N2)

+
use tempdb;
go

create table dbo.t1 (id int identity primary key, v1 int null);
create table dbo.t2 (id int identity primary key, t1_id int not null, v2 int null);
go

insert into dbo.t1
 (v1)
 select top (10000)
  rand(checksum(newid())) * 10000
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

insert into dbo.t2
 (t1_id, v2)
 select top (1000000)
  rand(checksum(newid())) * 10000,
  rand(checksum(newid())) * 10000
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b cross join
  master.dbo.spt_values c;

create index IX_t2__t1_id__v2 on dbo.t2 (t1_id, v2);
go

set statistics xml, io, time on;

select
 count(t1.v1), count(x.v2)
from
 dbo.t1 outer apply
 (select top (1) v2 from dbo.t2 where t1_id = t1.id order by v2 desc) x;

with x as
(
 select distinct
  t1_id, first_value(v2) over (partition by t1_id order by v2 desc) as v2
 from
  dbo.t2
)
select
 count(t1.v1), count(x.v2)
from
 dbo.t1 left join
 x on x.t1_id = t1.id;

set statistics xml, io, time off;
go

drop table dbo.t2, dbo.t1;
go
25 июн 14, 20:16    [16219703]     Ответить | Цитировать Сообщить модератору
 Re: Приджойнить поле с максимальным значением  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Я поменял порядок полей

CREATE CLUSTERED INDEX IX_#T ON #T(N1, N2, N);


OUTER APPLY стал крутым.

В общем при правильном индексе OUTER APPLY оказывается круче.
25 июн 14, 20:50    [16219816]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить