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

Откуда:
Сообщений: 51
Помогите сделать группировку. Уверен, что есть просто и элегантное решение.

Допустим у меня есть таблица:

PK Name Price FK
1 tour1 357 55
2 tour1 569 56
3 tour1 369 57

Мне нужно выбрать из всех этих туров тур с минимальной ценой. Я пишу
select min(name), min(price)
from table
where name = 'tour1'
group by Name

Но еще нужно выбрать FK. При чем тот, который находится в одной строке с минимальной ценой. Конечно я не могу написать на нем какую-нибуд функцию типа min или max. Но должен же быть способ выбрать именно связанный с этой ценой FK.
10 окт 09, 02:35    [7768404]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Тестовые данные:
+
declare @t table(pk int, name varchar(10),price int,fk int)
insert into @t 
select 1,'tour1',357,55 union all
select 2,'tour1',569,56 union all
select 3,'tour1',369,57

insert into @t 
select 4,'tour2',341,58 union all
select 5,'tour2',319,59 union all
select 6,'tour2',339,60

insert into @t 
select 7,'tour3',348,61 union all
select 8,'tour3',345,62 union all
select 9,'tour3',343,63
Вариант для SS 2005:
select top 1 with ties
      pk,name,price,fk 
from @t
order by case when 1=row_number()over(partition by name order by price) then 1 else 2 end
Вариант для SS 2000:
select 
       pk=cast(substring(tmpConcatField,13,12) as int)
      ,name
      ,price=cast(left(tmpConcatField,12) as int)
      ,fk=cast(substring(tmpConcatField,25,12) as int)
from(
  select
         name
        ,tmpConcatField=
          min(
            space(12-len(cast(price as varchar)))+cast(price as varchar)+
            space(12-len(cast(pk as varchar)))+cast(pk as varchar)+
            space(12-len(cast(fk as varchar)))+cast(fk as varchar)
          )
  from @t
  group by name
)t
Result:
pknamepricefk
1tour135755
5tour231959
9tour334363
10 окт 09, 08:54    [7768515]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
aleks2
Guest
Ozzy-Osbourne,

Я, канешно, понимаю, что просто - это не наш путь... однако

select *
FROM 
[Допустим у меня есть таблица] T
WHERE T.PK=(select TOP 1 X.PK FROM [Допустим у меня есть таблица] X WHERE X.Name=T.NAME ORDER BY X.Price ASC)

ну или ежели надо все минимальные туры

select *
FROM 
[Допустим у меня есть таблица] T
WHERE T.Price=(select min(Price) FROM [Допустим у меня есть таблица] X WHERE X.Name=T.NAME)
или
select *
FROM 
[Допустим у меня есть таблица] T
WHERE T.PK in (select TOP 1with ties X.PK FROM [Допустим у меня есть таблица] X WHERE X.Name=T.NAME ORDER BY X.Price ASC)
10 окт 09, 15:26    [7768917]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
aleks2,

если приведенные варианты не шутка и price - не индексированное поле, то надо бы их проверить на большом числе строк. Особливо второй, где агрегат внутри where-условия :-)
А вообще, в стартовом сообщении все туры имеют почему-то одно и то же имя (`tour1`) и я не совсем уверен, правильно ли понял то, что нужно автору. Полноценного скрипта с данными нет.
10 окт 09, 18:14    [7769108]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
Mr. Anderson
Member

Откуда:
Сообщений: 51
Ozzy-Osbourne
aleks2,

если приведенные варианты не шутка и price - не индексированное поле, то надо бы их проверить на большом числе строк. Особливо второй, где агрегат внутри where-условия :-)
А вообще, в стартовом сообщении все туры имеют почему-то одно и то же имя (`tour1`) и я не совсем уверен, правильно ли понял то, что нужно автору. Полноценного скрипта с данными нет.


Ты все правильно понял. У меня именно так. Только я упорно не могу понять как все это работает.

Можно подробнее? Я прочитал про все функции, которые ты использовал, но се равно не понимаю. Я о варианте для SQL 2005.


top 1 with ties
Почему именно with ties?

order by case when 1=row_number()over(partition by name order by price) then 1 else 2 end

Тут я тоже не понимаю. Если в области с tour1 1 строка, то сортируем по первому столбцу, а если нет, то по второму?
14 окт 09, 23:52    [7788055]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
Robb
Member

Откуда: там и тут
Сообщений: 949
Mr. Anderson,

а если несколько туров с минимальной ценой? тады как?

select * from (select *,MIN(price)over()mn from @t)x where x.mn=x.price 
15 окт 09, 03:19    [7788245]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
Robb
Member

Откуда: там и тут
Сообщений: 949
у Ozzy-Osbourne тест данные..
15 окт 09, 03:21    [7788246]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Mr. Anderson
Почему именно with ties? <skipped> Если в области с tour1 1 строка, то сортируем по первому столбцу, а если нет, то по второму?
with ties отрабатывает после всего остального, в частности - после получения значений row_number(). Сами значения row_number() в select-секции не указаны, т.к. они не нужны в исходном требовании выводимых колонок.
Теперь выведем эти row_number и отменим инструкцию 'top 1 with ties':
select --top 1 with ties
      pk,name,price,fk 
     ,rowNum=row_number()over(partition by name order by price)
from @t
order by case when 1=row_number()over(partition by name order by price) then 1 else 2 end
Result:
pknamepricefkrowNum
1tour1357551
5tour2319591
9tour3343631
8tour3345622
7tour3348613
6tour2339602
4tour2341583
3tour1369572
2tour1569563

Инструкция order by, как видно по результату, приводит к тому, что строки с rowNum=1 выводятся первыми, а прочие строки - ниже их.Так вот: добавление 'top 1' отберёт одну строку, у которой значения ключа сортировки самые наименьшие (т.е. rowNum=1). А добавление к этому 'with ties' ("с хвостами") добавит к этой одной строке все "такие же" строки, имеющие значение в ключе сортировки, равное наименьшему. В итоге, будут отобраны первые три строки от тестового набора.
15 окт 09, 07:58    [7788380]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Ozzy-Osbourne,

во-первых, не понимаю, зачем там у Вас CASE?
Во-вторых, это решение, как уже давно тут выясняли, проигрывает такому:
with CTE as(select *,rowNum=row_number()over(partition by name order by price)from @t)
select pk,name,price,fk
from CTE
where rowNum=1;
15 окт 09, 08:53    [7788481]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
Mr. Anderson
Member

Откуда:
Сообщений: 51
Ozzy-Osbourne
Mr. Anderson
Почему именно with ties? <skipped> Если в области с tour1 1 строка, то сортируем по первому столбцу, а если нет, то по второму?
with ties отрабатывает после всего остального, в частности - после получения значений row_number(). Сами значения row_number() в select-секции не указаны, т.к. они не нужны в исходном требовании выводимых колонок.
Теперь выведем эти row_number и отменим инструкцию 'top 1 with ties':
select --top 1 with ties
      pk,name,price,fk 
     ,rowNum=row_number()over(partition by name order by price)
from @t
order by case when 1=row_number()over(partition by name order by price) then 1 else 2 end
Result:
pktnametpricetfktrowNum
1ttour1t357t55t1
5ttour2t319t59t1
9ttour3t343t63t1
8ttour3t345t62t2
7ttour3t348t61t3
6ttour2t339t60t2
4ttour2t341t58t3
3ttour1t369t57t2
2ttour1t569t56t3

Инструкция order by, как видно по результату, приводит к тому, что строки с rowNum=1 выводятся первыми, а прочие строки - ниже их.Так вот: добавление 'top 1' отберёт одну строку, у которой значения ключа сортировки самые наименьшие (т.е. rowNum=1). А добавление к этому 'with ties' ("с хвостами") добавит к этой одной строке все "такие же" строки, имеющие значение в ключе сортировки, равное наименьшему. В итоге, будут отобраны первые три строки от тестового набора.


Спасибо, кажется все понял. У меня еще один вопрос. Как можно отсортировать все это по вычисляемой колонке после всех этих манипуляций. Например к Вашим тестовым данным прибавим еще колонку pt_tourdate - дата начала тура.

С помощью этого скрипта я считаю, какой это будет день недели

case
when datepart(dw, min(pt_tourdate))+ @@datefirst - 1 > 7 then datepart (dw, min(pt_tourdate)) + @@datefirst - 1 - 7
else datepart(dw, min(pt_tourdate)) + @@datefirst - 1
end as dw

Как теперь все это упорядочить по dw?
15 окт 09, 12:14    [7790035]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
iljy
Member

Откуда:
Сообщений: 8711
Mr. Anderson,

с top 1 with ties - никак. А в варианте jap - order by dw
15 окт 09, 12:21    [7790098]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iljy
Mr. Anderson,

с top 1 with ties - никак. А в варианте jap - order by dw
Во-первых, день недели можно посчитать и проще
SELECT 1+DATEDIFF(DAY,0, GETDATE())%7;
Во-вторых, и с TOP(1) WITH TIES отсортировать не проблема:
WITH CTE AS
(
 SELECT TOP(1) WITH TIES *
 FROM @t
 ORDER BY ROW_NUMBER()OVER(PARTITION BY name ORDER BY price)
)
SELECT pk,name,price,fk
FROM CTE
ORDER BY DATEDIFF(DAY,0,pt_tourdate)%7;
Если надо по-другому отсортировать - тоже ничего трудного.
15 окт 09, 13:35    [7790817]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
iap
Ozzy-Osbourne,

во-первых, не понимаю, зачем там у Вас CASE?
Во-вторых, это решение, как уже давно тут выясняли, проигрывает такому:
with CTE as(select *,rowNum=row_number()over(partition by name order by price)from @t)
select pk,name,price,fk
from CTE
where rowNum=1;

1) 'case' сам не знаю, зачем :-) переклинило :-/
2) спорить не буду, но проверил сейчас примерно на 100 тыс строках - проигрыш менее чем 1 сек.
Скрипт наполнения случайными данными:
+
if object_id('tempdb..#t')>0 drop table #t
create table #t (pk int identity, name varchar(10),price int,fk int)
declare @i int,@i_lim int,@j int,@j_lim int,@x_lim int
set @i=1
set @j_lim=10
set @i_lim=10000
while @i<=@i_lim begin
  set @j=1
  set @x_lim=5+ceiling( rand()*@j_lim )
  while @j<=@x_lim begin
    insert into #t select 'tour'+cast(@i as varchar), 300+rand()*200, rand()*100
    set @j=@j+1
  end
  set @i=@i+1
end


Статистика для CTE-варианта:
+
with CTE as(select *,rowNum=row_number()over(partition by name order by price)from #t)
select pk,name,price,fk
from CTE
where rowNum=1
(10000 row(s) affected)
Table '#t'. Scan count 1, logical reads 448 CPU time = 1109 ms, elapsed time = 1978 ms.

Статистика для top 1 with ties-варианта:
+
select top 1 with ties
      pk,name,price,fk 
from #t
order by row_number()over(partition by name order by price)
Table '#t'. Scan count 1, logical reads 448 CPU time = 1640 ms, elapsed time = 2524 ms

Статистика для варианта под SS 2000:
+
select 
       pk=cast(substring(tmpConcatField,13,12) as int)
      ,name
      ,price=cast(left(tmpConcatField,12) as int)
      ,fk=cast(substring(tmpConcatField,25,12) as int)
from(
  select
         name
        ,tmpConcatField=
          min(
            space(12-len(cast(price as varchar)))+cast(price as varchar)+
            space(12-len(cast(pk as varchar)))+cast(pk as varchar)+
            space(12-len(cast(fk as varchar)))+cast(fk as varchar)
          )
  from #t
  group by name
)t
Table '#t'. Scan count 1, logical reads 448 CPU time = 1313 ms, elapsed time = 1700 ms.
15 окт 09, 13:46    [7790953]     Ответить | Цитировать Сообщить модератору
 Re: Группировка  [new]
iljy
Member

Откуда:
Сообщений: 8711
iap,

а, ну да, простите, не подумал о сортировке внутри подзапроса
15 окт 09, 14:03    [7791142]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить