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

Откуда:
Сообщений: 420

Есть таблица
CREATE TABLE [dbo].[Test1] (
  [Id] int IDENTITY(1, 1) NOT NULL,
  [Tovar] int NULL,
  [Type1] int NULL,
  [Type2] int NULL,
  [Price] numeric(9, 2) NULL,
  [Amount] int NULL
)
Например данные:
Id	Tovar	Type1	Type2	Price	Amount
  1	  1	  1	  1	20	  2
  2	  1	  1	  1	20	  3
  3	  1	  1	  1	18	  2
  4	  1	  1	  1	18	  3
  5	  1	  1	  1	18	  5
  6	  1	  1	  1	17	  5
  7	  1	  1	  1	15	  15
  8	  1	  1	  1	15	  3
  9	  1	  1	  1	12	  10
  10	  2	  1	  1	30	  2
  11	  2	  1	  1	30	  5
  12	  2	  1	  1	25	  2
  13	  2	  1	  1	22	  5
  14	  2	  1	  1	21	  5
  15	  2	  1	  1	20	  3


Нужно сгруппировать товары по ценам и количествам для данной цены

Select Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price order By Price

  1	  1	  1	12	  10
  1	  1	  1	15	  18
  1	  1	  1	17	  5
  1	  1	  1	18	  10
  1	  1	  1	20	  5
  2	  1	  1	20	  3
  2	  1	  1	21	  5
  2	  1	  1	22	  5
  2	  1	  1	25	  2
  2	  1	  1	30	  7

A вот как получить только первые несколько записей для каждого товара?
например типа:

Select Top 3 Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price order By Price

  1	  1	  1	12	  10
  1	  1	  1	15	  18
  1	  1	  1	17	  5
Возвращает первые несколько записей, а нужно для всех различных товаров,
т.е. 

  1	  1	  1	12	  10
  1	  1	  1	15	  18
  1	  1	  1	17	  5
  2	  1	  1	20	  3
  2	  1	  1	21	  5
  2	  1	  1	22	  5

Конечно можно в цикле, но было бы интереснее одним запросом...

29 сен 09, 22:57    [7722559]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
PaulYoung
Member

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

есть идея для >= 2005
Declare @N int

SET @N = 3

Select z.*
from
(
Select
  x.Tovar, x.Type1, x.Type2, x.Price, x.Amount,
  row_number() over(partition BY x.Tovar ORDER BY x.Tovar, x.Type1, x.Type2, x.Price, x.Amount) num
from
(
Select Tovar, Type1, Type2, Price, Sum(Amount) Amount From Test1
Group By Tovar, Type1, Type2, Price order By Price
) x
ORDER BY x.Tovar, x.Type1, x.Type2, x.Price, x.Amount
) z where z.num <= @N
которую не проверял
29 сен 09, 23:42    [7722625]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
i2akai1
Member

Откуда: Петербург
Сообщений: 217
Multy,

Если 2000 то:

SELECT a.Tovar,a.Type1,a.Type2,a.Price, a.Amount
FROM 
  (Select 
    Tovar,Type1,Type2,Price, Sum(Amount) Amount 
  From Test1
  Group By Tovar,Type1,Type2,Price) a
  JOIN 
    (Select 
      Tovar,Type1,Type2,Price, Sum(Amount) Amount 
    From Test1
    Group By Tovar,Type1,Type2,Price) b
  ON a.Tovar = b.Tovar
    AND a.Type1 >= b.Type1 
    AND a.Type2 >= b.Type2
    AND a.Price >= b.Price 
GROUP BY a.Tovar,a.Type1,a.Type2,a.Price, a.Amount
HAVING COUNT(ALL b.Tovar) <= 3
30 сен 09, 00:16    [7722668]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
Multy
Member [заблокирован]

Откуда:
Сообщений: 420
Ругается:
Сообщение 1033, уровень 15, состояние 1, строка 15
Предложение ORDER BY не допускается в представлениях, встроенных функциях, производных таблицах, вложенных запросах и обобщенных табличных выражениях, если не указано TOP или FOR XML.
30 сен 09, 00:17    [7722670]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
Multy
Member [заблокирован]

Откуда:
Сообщений: 420
i2akai1,
СПАСИБО
Работает :-)
30 сен 09, 00:19    [7722675]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
GlebZ
Member

Откуда: USA
Сообщений: 284
Что-то типа:

Select Top 3 WITH TIES  Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price 
ORDER BY row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price)



Водку? Водку - буду!
30 сен 09, 00:21    [7722678]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
i2akai1
Member

Откуда: Петербург
Сообщений: 217
Multy,

:)
30 сен 09, 00:24    [7722680]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
Multy
Member [заблокирован]

Откуда:
Сообщений: 420
GlebZ
Что-то типа:

Select Top 3 WITH TIES  Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price 
ORDER BY row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price)



Водку? Водку - буду!


В некотором смысле работает, только нужно указывать точное число записей которые должны получится в результате.
Т.е. в данном примере для группы по 3 будет 

Select Top 6 WITH TIES  Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price 
ORDER BY row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price)
Причём если добавить ещё строчку с одним значением товара, например:

 16	  3	  1	  1	20	  3

то:

Select Top 7 WITH TIES  Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price 
ORDER BY row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price)
То, что надо

Но не Select Top 8 :-)

30 сен 09, 01:29    [7722746]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Multy
GlebZ
Что-то типа:

Select Top 3 WITH TIES  Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price 
ORDER BY row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price)



Водку? Водку - буду!


В некотором смысле работает, только нужно указывать точное число записей которые должны получится в результате.
Т.е. в данном примере для группы по 3 будет 

Select Top 6 WITH TIES  Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price 
ORDER BY row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price)
Причём если добавить ещё строчку с одним значением товара, например:

 16	  3	  1	  1	20	  3

то:

Select Top 7 WITH TIES  Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price 
ORDER BY row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price)
То, что надо

Но не Select Top 8 :-)

Только и исключительно TOP(1)
Для трёх из каждой группы:
Select Top(1) WITH TIES  Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price
ORDER BY row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price)/(3+1);
30 сен 09, 10:06    [7723201]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
Multy
Member [заблокирован]

Откуда:
Сообщений: 420
Select Top 1
Я Сразу заметил возвращает по одной записи для каждого уникального значения, но интререс именно в получении 2-х и более...
30 сен 09, 23:36    [7727617]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Multy
Select Top 1
Я Сразу заметил возвращает по одной записи для каждого уникального значения, но интререс именно в получении 2-х и более...
Обратите внимание на WITH TIES, а не на TOP!
Главное - обеспечить правильное значение в ORDER BY

Но в любом случае, по-видимому, вариант с ограничением на ROW_NUMBER()OVER(PARTITION BY ... ORDER BY ...) эффективнее будет...
1 окт 09, 20:36    [7731874]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
Multy
Member [заблокирован]

Откуда:
Сообщений: 420
iap
Multy
Select Top 1
Я Сразу заметил возвращает по одной записи для каждого уникального значения, но интререс именно в получении 2-х и более...
Обратите внимание на WITH TIES, а не на TOP!
Главное - обеспечить правильное значение в ORDER BY

Но в любом случае, по-видимому, вариант с ограничением на ROW_NUMBER()OVER(PARTITION BY ... ORDER BY ...) эффективнее будет...


Только вот в данном контексте Select Top N действует на обшее количество записей. Выбирает гарантированно по одной из каждого набора, Надо как-то указать, что бы Select Top N действовал внутри каждого набора.

То, что эффективнее будет это наверняка.
7 окт 09, 02:56    [7751009]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Multy
iap
Multy
Select Top 1
Я Сразу заметил возвращает по одной записи для каждого уникального значения, но интререс именно в получении 2-х и более...
Обратите внимание на WITH TIES, а не на TOP!
Главное - обеспечить правильное значение в ORDER BY

Но в любом случае, по-видимому, вариант с ограничением на ROW_NUMBER()OVER(PARTITION BY ... ORDER BY ...) эффективнее будет...


Только вот в данном контексте Select Top N действует на обшее количество записей. Выбирает гарантированно по одной из каждого набора, Надо как-то указать, что бы Select Top N действовал внутри каждого набора.

То, что эффективнее будет это наверняка.
Если добиться, чтобы для каждой группы некая функция возвращала определённое количество
равных значений, равных минимуму по всем записям, то TOP(1) WITH TIES ... ORDER BY <минимальное значение функции>
обеспечит присутствие всех вышеуказанных записей, т.е. из каждой группы по N.
В качестве такой функции я предлагал целочисленное деление номера записи внутри группы (ROW_NUMBER()OVER(PARTITION BY ... ORDER BY ...))
на (N+1), что обеспечивает значение 0 для первых N номеров внутри каждой группы, а для следующих номеров - значение >0.
После этого останется только выбрать ВСЕ записи (уже не глядя на группы) с нулевым значением функции (WITH TIES это и делает).

Однако, повторюсь, это не самое эффективное решение!
7 окт 09, 09:24    [7751352]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
Multy
Member [заблокирован]

Откуда:
Сообщений: 420

Select row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price) Num,
Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price
Таким вот запросом можно получить:
Num
  1	1	  1	  1	12	  10
  2	1	  1	  1	15	  18
  3	1	  1	  1	17	  5
  4	1	  1	  1	18	  10
  5	1	  1	  1	20	  5
  1	2	  1	  1	20	  3
  2	2	  1	  1	21	  5
  3	2	  1	  1	22	  5
  4	2	  1	  1	25	  2
  5	2	  1	  1	30	  7
теперь если обрезать строки, где номер больше заданного, то всё в порядке.
Однако если добавить:

Select row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price) Num,
Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Where Num<=N
Group By Tovar,Type1,Type2,Price
Ругается. 

Пока тока так смог придумать.
Select a.Tovar,a.Type1,a.Type2,a.Price,a.Amount from 
(Select row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price) Num,
Tovar,Type1,Type2,Price, Sum(Amount) Amount From Test1
Group By Tovar,Type1,Type2,Price) a
Where a.Num<=N

Однако это опять 2 селекта, к тому же индекс на представление не создаёт.
Как-нибудь по другому можно ограничить?


10 окт 09, 09:03    [7768517]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
Multy
Member [заблокирован]

Откуда:
Сообщений: 420
Неужели никто таки не знает как это сделать одним запросом?
13 окт 09, 23:46    [7781794]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Multy
Неужели никто таки не знает как это сделать одним запросом?
Скорее всего знают, но хотят, чтобы вы и сами подумали. :)
select
  Tovar, Type1, Type2, Price, sum(Amount)
from (
  Select 
    row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price) Num,
    Tovar, Type1, Type2, Price, Amount 
  From Test1
) A
Where Num<=N
Group By Tovar, Type1, Type2, Price
13 окт 09, 23:53    [7781807]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
Multy
Member [заблокирован]

Откуда:
Сообщений: 420
Senya_L
Multy
Неужели никто таки не знает как это сделать одним запросом?
Скорее всего знают, но хотят, чтобы вы и сами подумали. :)

select
  Tovar, Type1, Type2, Price, sum(Amount)
from (
  Select 
    row_number() OVER (PARTITION BY Tovar,Type1,Type2 order By Price) Num,
    Tovar, Type1, Type2, Price, Amount 
  From Test1
) A
Where Num<=N
Group By Tovar, Type1, Type2, Price


Похоже думай-не думай, а не получится...
17 окт 09, 09:37    [7799939]     Ответить | Цитировать Сообщить модератору
 Re: Как выбрать определённое количество записей для разных значений одного поля?  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
автор
думай-не думай, а не получится...
что не получится ? тут представили несколько вариантов, вот еще один, почти как у Senya L:
1) test data:
+
declare @t table(id int identity not null, art int, t1 int, t2 int, price numeric(9,2), amt int)
insert into @t
select 1,  1,  1,    20,   2  union all
select 1,  1,  1,    20,   3  union all
select 1,  1,  1,    18,   2  union all
select 1,  1,  1,    18,   3  union all
select 1,  1,  1,    18,   5  union all
select 1,  1,  1,    17,   5  union all
select 1,  1,  1,    15,   15 union all
select 1,  1,  1,    15,   3  union all
select 1,  1,  1,    12,   10 union all
select 2,  1,  1,    30,   2  union all
select 2,  1,  1,    30,   5  union all
select 2,  1,  1,    25,   2  union all
select 2,  1,  1,    22,   5  union all
select 2,  1,  1,    21,   5  union all
select 2,  1,  1,    20,   3
/*
-- some more:
insert into @t
select 3,  1,  1,    61,   2  union all
select 3,  1,  1,    61,   3  union all
select 3,  1,  1,    61,   22 union all
select 3,  1,  1,    61,   33 union all
select 3,  1,  1,    61,   44 union all
select 3,  1,  1,    62,   55 union all
select 3,  1,  1,    62,   10 union all
select 3,  1,  1,    62,   20 union all
select 3,  1,  1,    62,   30 union all
select 3,  1,  1,    62,   40 union all
select 3,  1,  1,    63,   50 union all
select 3,  1,  1,    64,   60 union all 
select 3,  1,  1,    65,   70
*/
2) query:
select art,t1,t2,price,amt_sum
from(
  select art,t1,t2,price,amt_sum=sum(amt),r=row_number()over(partition by art order by price)
  from @t
  group by art,t1,t2,price
)t 
where r<=3
3) result:
+
artt1t2priceamt_sum
11112.0010
11115.0018
11117.005
21120.003
21121.005
21122.005

Что неверно ?

2 topic starter
Кстати, ВОПРОС.
В стартовом посте Вы сказали, что первые N записей нужно
автор
нужно для всех различных товаров
То есть, не для сочетания "товар+тип1+тип2", а именно для одного поля "товар". Поэтому при нумерации строки в моем варианте делятся на группы НЕ по ключу "art,t1,t2", а только по "art".
В ваших данных для одного и того же товара в полях "тип1" и "тип2" указаны одни и те же значения, а что делать, если они будут разными ?
17 окт 09, 11:21    [7799999]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить