Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 помогите с запросом (с)  [new]
любитель запросов
Guest
Добрый вечер!
есть запрос :
+
if object_id('tempdb..#inserted') is not null
 drop table #inserted

create table #inserted
(id int identity(1,1)
, data datetime
, idA1 int
, idA2 int)

insert into #inserted ([data], idA1, idA2) 
select '20080901', 1, 1 union all
select '20080901', 1, 2 union all
select '20080901', 1, 3 union all
select '20080902', 1, 1 union all
select '20080902', 1, 2 union all
select '20080902', 1, 3 union all
select '20080903', 1, 1 union all
select '20080903', 1, 2 union all
select '20080903', 1, 3 union all
select '20080904', 1, 1 union all
select '20080904', 1, 2 union all
select '20080904', 1, 3 union all
select '20080904', 1, 1 union all
select '20080904', 1, 1 union all
select '20080904', 1, 1 union all
select '20080905', 3, 1;

-- select * from #inserted;
with SS as 
( select max(id) id, data, idA1, idA2 from #inserted group by idA1, idA2, data
)

select i.id from #inserted i inner join 
SS on i.id = SS.id
where i.[data] = (select max(data) from SS where i.idA1 = SS.idA1 and i.idA2 = SS.idA2 group by idA1, idA2)

Работает, выдаёт что надо. Вопрос простой - можно ли сделать лучше с точки зрения производительности
Смысл запроса - для каждой пары idA1, idA2 выбрать id с максимальной датой data.
13 авг 09, 18:08    [7535578]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (с)  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
select top 1 with ties idA1, idA2 from #inserted order by row_number() over (partition by idA1, idA2 order by data desc)

для спящего время бодрствования равносильно сну
13 авг 09, 18:15    [7535617]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (с)  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
select top 1 with ties id, idA1, idA2 from #inserted order by row_number() over (partition by idA1, idA2 order by data desc)
ид забыл..

для спящего время бодрствования равносильно сну
13 авг 09, 18:15    [7535625]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (с)  [new]
любитель запросов
Guest
Алексей2003
select top 1 with ties id, idA1, idA2 from #inserted order by row_number() over (partition by idA1, idA2 order by data desc)
ид забыл..

для спящего время бодрствования равносильно сну

Поправил Вас немного
select top 1 with ties id, idA1, idA2 from #inserted 
order by row_number() over 
(partition by idA1, idA2 order by data desc, id desc)
Вроде как то, что надо. Судя по плану дело гораздо лучше.
Алексей2003, спасибо!
13 авг 09, 18:27    [7535678]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (с)  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
А так?
WITH CTE AS
(
 SELECT ROW_NUMBER()OVER(PARTITION BY idA1, idA2 ORDER BY data DESC,id DESC)N,id,idA1,idA2
 FROM #inserted
)
SELECT id,idA1,idA2
FROM CTE
WHERE N=1;
Если максимальных дат несколько, то
WITH CTE AS
(
 SELECT RANK()OVER(PARTITION BY idA1, idA2 ORDER BY data DESC,id DESC)N,id,idA1,idA2
 FROM #inserted
)
SELECT id,idA1,idA2
FROM CTE
WHERE N=1;
13 авг 09, 19:41    [7535877]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (с)  [new]
любитель запросов
Guest
iap
А так?
Спасибо iap. Стало ещё лучше - сортировка с выбором top1 сменилась на фильтрацию.
Планы прилагаю.
;with SS as 
( select max(id) id, data, idA1, idA2 from #inserted group by idA1, idA2, data
)

select i.id from #inserted i inner join 
SS on i.id = SS.id
where i.[data] = (select max(data) from SS where i.idA1 = SS.idA1 and i.idA2 = SS.idA2 group by idA1, idA2)
Rows                 Executes             StmtText                                                                                                                                                                                                                                                                                                                                                                                                                                                       StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                                                                                                                                                                              DefinedValues                                                                                                                             EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                                                Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
4679 1 with SS as
( select max(id) id, data, idA1, idA2 from #inserted group by idA1, idA2, data
)

select i.id from #inserted i inner join
SS on i.id = SS.id
where i.[data] = (select max(data) from SS where i.idA1 = SS.idA1 and i.idA2 = SS.idA2 group by idA1, idA2) 3 1 0 NULL NULL NULL NULL 23846.3 NULL NULL NULL 61.50001 NULL NULL SELECT 0 NULL
4679 1 |--Filter(WHERE:([tempdb].[dbo].[#inserted].[data] as [i].[data]=[Expr1016])) 3 2 1 Filter Filter WHERE:([tempdb].[dbo].[#inserted].[data] as [i].[data]=[Expr1016]) NULL 23846.3 0 0.1144622 11 61.50001 [i].[id] NULL PLAN_ROW 0 1
214021 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([i].[idA1], [i].[idA2])) 3 3 2 Nested Loops Inner Join OUTER REFERENCES:([i].[idA1], [i].[idA2]) NULL 238463 0 0.9967753 27 61.38554 [i].[id], [i].[data], [Expr1016] NULL PLAN_ROW 0 1
214021 1 |--Sort(ORDER BY:([i].[idA1] ASC, [i].[idA2] ASC)) 3 4 3 Sort Sort ORDER BY:([i].[idA1] ASC, [i].[idA2] ASC) NULL 238463 0.01126126 19.5524 27 35.37015 [i].[id], [i].[data], [i].[idA1], [i].[idA2] NULL PLAN_ROW 0 1
214021 1 | |--Hash Match(Inner Join, HASH:([Expr1007])=([i].[id]), RESIDUAL:([Expr1007]=[tempdb].[dbo].[#inserted].[id] as [i].[id])) 3 5 4 Hash Match Inner Join HASH:([Expr1007])=([i].[id]), RESIDUAL:([Expr1007]=[tempdb].[dbo].[#inserted].[id] as [i].[id]) NULL 238463 0 5.158672 27 15.80648 [i].[id], [i].[data], [i].[idA1], [i].[idA2] NULL PLAN_ROW 0 1
214021 1 | |--Hash Match(Aggregate, HASH:([tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2], [tempdb].[dbo].[#inserted].[data]), RESIDUAL:([tempdb].[dbo].[#inserted].[idA1] = [tempdb].[dbo].[#inserted].[idA1] AND [tempdb].[dbo].[#inserted].[idA2] = [tempdb].[dbo].[#inserted].[idA2] AND [tempdb].[dbo].[#inserted].[data] = [tempdb].[dbo].[#inserted].[data]) DEFINE:([Expr1007]=MAX([tempdb].[dbo].[#inserted].[id]))) 3 6 5 Hash Match Aggregate HASH:([tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2], [tempdb].[dbo].[#inserted].[data]), RESIDUAL:([tempdb].[dbo].[#inserted].[idA1] = [tempdb].[dbo].[#inserted].[idA1] AND [tempdb].[dbo].[#inserted].[idA2] = [tempdb].[dbo].[#inserted].[idA2] AND [tempdb].[dbo].[#inserted].[data] = [tempdb].[dbo].[#inserted].[data]) [Expr1007]=MAX([tempdb].[dbo].[#inserted].[id]) 238463 0 8.851441 11 9.749625 [Expr1007] NULL PLAN_ROW 0 1
238463 1 | | |--Table Scan(OBJECT:([tempdb].[dbo].[#inserted])) 3 7 6 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#inserted]) [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] 238463 0.6357176 0.2624663 27 0.8981839 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] NULL PLAN_ROW 0 1
238463 1 | |--Table Scan(OBJECT:([tempdb].[dbo].[#inserted] AS [i])) 3 11 5 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#inserted] AS [i]) [i].[id], [i].[data], [i].[idA1], [i].[idA2] 238463 0.6357176 0.2624663 27 0.8981839 [i].[id], [i].[data], [i].[idA1], [i].[idA2] NULL PLAN_ROW 0 1
214021 214021 |--Table Spool 3 13 3 Table Spool Lazy Spool NULL NULL 1 0.01 0.00010028 15 25.01862 [Expr1016] NULL PLAN_ROW 0 238463
4679 4679 |--Assert(WHERE:(CASE WHEN [Expr1015]>(1) THEN (0) ELSE NULL END)) 3 14 13 Assert Assert WHERE:(CASE WHEN [Expr1015]>(1) THEN (0) ELSE NULL END) NULL 1 0 1.8E-07 15 1.119395 [Expr1016] NULL PLAN_ROW 0 1
4679 4679 |--Stream Aggregate(DEFINE:([Expr1015]=Count(*), [Expr1016]=ANY([Expr1013]))) 3 15 14 Stream Aggregate Aggregate NULL [Expr1015]=Count(*), [Expr1016]=ANY([Expr1013]) 1 0 1.1E-06 23 1.119395 [Expr1015], [Expr1016] NULL PLAN_ROW 0 1
4679 4679 |--Stream Aggregate(DEFINE:([Expr1013]=MAX([tempdb].[dbo].[#inserted].[data]))) 3 16 15 Stream Aggregate Aggregate NULL [Expr1013]=MAX([tempdb].[dbo].[#inserted].[data]) 1 0 1.1E-06 15 1.119394 [Expr1013] NULL PLAN_ROW 0 1
214021 4679 |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#inserted].[data] ASC)) 3 17 16 Sort Distinct Sort DISTINCT ORDER BY:([tempdb].[dbo].[#inserted].[data] ASC) NULL 1 0.01126126 0.000100015 15 1.119393 [tempdb].[dbo].[#inserted].[data] NULL PLAN_ROW 0 1
238463 4679 |--Table Scan(OBJECT:([tempdb].[dbo].[#inserted]), WHERE:([tempdb].[dbo].[#inserted].[idA1] as [i].[idA1]=[tempdb].[dbo].[#inserted].[idA1] AND [tempdb].[dbo].[#inserted].[idA2] as [i].[idA2]=[tempdb].[dbo].[#inserted].[idA2])) 3 18 17 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#inserted]), WHERE:([tempdb].[dbo].[#inserted].[idA1] as [i].[idA1]=[tempdb].[dbo].[#inserted].[idA1] AND [tempdb].[dbo].[#inserted].[idA2] as [i].[idA2]=[tempdb].[dbo].[#inserted].[idA2]) [tempdb].[dbo].[#inserted].[data] 1 0.6357176 0.2624663 23 0.8981839 [tempdb].[dbo].[#inserted].[data] NULL PLAN_ROW 0 1
14 авг 09, 10:34    [7537545]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (с)  [new]
любитель запросов
Guest
select top 1 
with ties 
id, idA1, idA2 from #inserted 
order by row_number() over 
(partition by idA1, idA2 order by data desc, id desc);
Rows                 Executes             StmtText                                                                                                                                                                                                   StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                               DefinedValues                                                                                                                             EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                                                                           Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
4679 1 select top 1
with ties
id, idA1, idA2 from #inserted
order by row_number() over
(partition by idA1, idA2 order by data desc, id desc); 5 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 40.04936 NULL NULL SELECT 0 NULL
4679 1 |--Top(TOP EXPRESSION:((1))) 5 2 1 Top Top TOP EXPRESSION:((1)), TIE COLUMNS:([Expr1004]) NULL 1 0 1E-07 19 40.04936 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] NULL PLAN_ROW 0 1
4680 1 |--Sort(ORDER BY:([Expr1004] ASC)) 5 3 2 Sort Sort ORDER BY:([Expr1004] ASC) NULL 1 0.01126126 19.5524 27 40.04936 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2], [Expr1004] NULL PLAN_ROW 0 1
238463 1 |--Sequence Project(DEFINE:([Expr1004]=row_number)) 5 4 3 Sequence Project Compute Scalar DEFINE:([Expr1004]=row_number) [Expr1004]=row_number 238463 0 0.0238463 27 20.4857 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2], [Expr1004] NULL PLAN_ROW 0 1
0 0 |--Compute Scalar(DEFINE:([Expr1006]=(1))) 5 5 4 Compute Scalar Compute Scalar DEFINE:([Expr1006]=(1)) [Expr1006]=(1) 238463 0 0.00476926 27 20.49047 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2], [Segment1005], [Expr1006] NULL PLAN_ROW 0 1
238463 1 |--Segment 5 6 5 Segment Segment [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] NULL 238463 0 0.00476926 27 20.49047 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2], [Segment1005] NULL PLAN_ROW 0 1
238463 1 |--Sort(ORDER BY:([tempdb].[dbo].[#inserted].[idA1] ASC, [tempdb].[dbo].[#inserted].[idA2] ASC, [tempdb].[dbo].[#inserted].[data] DESC, [tempdb].[dbo].[#inserted].[id] DESC)) 5 7 6 Sort Sort ORDER BY:([tempdb].[dbo].[#inserted].[idA1] ASC, [tempdb].[dbo].[#inserted].[idA2] ASC, [tempdb].[dbo].[#inserted].[data] DESC, [tempdb].[dbo].[#inserted].[id] DESC) NULL 238463 0.01126126 19.5524 27 20.46185 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] NULL PLAN_ROW 0 1
238463 1 |--Table Scan(OBJECT:([tempdb].[dbo].[#inserted])) 5 8 7 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#inserted]) [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] 238463 0.6357176 0.2624663 27 0.8981839 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] NULL PLAN_ROW 0 1
14 авг 09, 10:35    [7537554]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом (с)  [new]
любитель запросов
Guest
WITH CTE AS
(
 SELECT ROW_NUMBER()OVER(PARTITION BY idA1, idA2 ORDER BY data DESC,id DESC)N,id,idA1,idA2, data
 FROM #inserted
)
SELECT id,idA1,idA2, data
FROM CTE
WHERE N=1
Rows                 Executes             StmtText                                                                                                                                                                                              StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                               DefinedValues                                                                                                                             EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                                                                                                                                                           Warnings Type                                                             Parallel EstimateExecutions
-------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
4679 1 WITH CTE AS
(
SELECT ROW_NUMBER()OVER(PARTITION BY idA1, idA2 ORDER BY data DESC,id DESC)N,id,idA1,idA2, data
FROM #inserted
)
SELECT id,idA1,idA2, data
FROM CTE
WHERE N=1 7 1 0 NULL NULL NULL NULL 10791.1 NULL NULL NULL 20.60016 NULL NULL SELECT 0 NULL
4679 1 |--Filter(WHERE:([Expr1004]=(1))) 7 2 1 Filter Filter WHERE:([Expr1004]=(1)) NULL 10791.1 0 0.1144622 27 20.60016 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] NULL PLAN_ROW 0 1
238463 1 |--Sequence Project(DEFINE:([Expr1004]=row_number)) 7 3 2 Sequence Project Compute Scalar DEFINE:([Expr1004]=row_number) [Expr1004]=row_number 238463 0 0.0238463 35 20.4857 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2], [Expr1004] NULL PLAN_ROW 0 1
0 0 |--Compute Scalar(DEFINE:([Expr1006]=(1))) 7 4 3 Compute Scalar Compute Scalar DEFINE:([Expr1006]=(1)) [Expr1006]=(1) 238463 0 0.00476926 35 20.49047 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2], [Segment1005], [Expr1006] NULL PLAN_ROW 0 1
238463 1 |--Segment 7 5 4 Segment Segment [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] NULL 238463 0 0.00476926 35 20.49047 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2], [Segment1005] NULL PLAN_ROW 0 1
238463 1 |--Sort(ORDER BY:([tempdb].[dbo].[#inserted].[idA1] ASC, [tempdb].[dbo].[#inserted].[idA2] ASC, [tempdb].[dbo].[#inserted].[data] DESC, [tempdb].[dbo].[#inserted].[id] DESC)) 7 6 5 Sort Sort ORDER BY:([tempdb].[dbo].[#inserted].[idA1] ASC, [tempdb].[dbo].[#inserted].[idA2] ASC, [tempdb].[dbo].[#inserted].[data] DESC, [tempdb].[dbo].[#inserted].[id] DESC) NULL 238463 0.01126126 19.5524 27 20.46185 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] NULL PLAN_ROW 0 1
238463 1 |--Table Scan(OBJECT:([tempdb].[dbo].[#inserted])) 7 7 6 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#inserted]) [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] 238463 0.6357176 0.2624663 27 0.8981839 [tempdb].[dbo].[#inserted].[id], [tempdb].[dbo].[#inserted].[data], [tempdb].[dbo].[#inserted].[idA1], [tempdb].[dbo].[#inserted].[idA2] NULL PLAN_ROW 0 1
14 авг 09, 10:35    [7537559]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить