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

Откуда:
Сообщений: 2
Помогите разобраться.

Есть некая система, которая работает с таблицей discounts: client_id, product_id, period(date), discount. В ней лежат данные по клиентским скидкам на товары. Скидки могут меняться - тогда в таблицу записывается новая запись, и система использует последнюю по дате запись.

У меня стоит задача создать запрос, который будет показывать текущие актуальные скидки всех клиентов для всех товаров.
Размер таблицы ~ 60 000 записей. "Нужных" мне записей с актуальными скидками - порядка 30 000.
Бьюсь с запросом, никак не могу получить нужные данные, или отдаются все 60 000, или получаю ошибки.

Что-то делаю не так, но что? Ткните плиз носом.

select
  client_id,
  product_id,
  discount,
  max(period) as mperiod
from discounts
group by 
  client_id, 
  product_id,
  period
having 
  period=max(period)


p.s. mssql 2008
и еще, в таблице нет первичного ключа, такое вот чудо.
13 июн 11, 14:59    [10806172]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
invm
Member

Откуда: Москва
Сообщений: 9827
select top (1) with ties
  client_id,
  product_id,
  discount,
  period,
from discounts
order by
 row_number() over (partition by client_id, product_id order by period desc)
13 июн 11, 15:29    [10806339]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
sergo_s,

SELECT  *
FROM    ( SELECT    client_id ,
                    product_id ,
                    discount ,
                    period ,
                    MAX(period) OVER ( PARTITION BY client_id, product_id ) AS mperiod
          FROM      discounts
        ) t
WHERE   t.mperiod = t.period
?
13 июн 11, 15:33    [10806356]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
sergo_s
Member

Откуда:
Сообщений: 2
Интересный подход, не знал таких штуковин. Проверил оба запроса, работают. Однако выдают разное количество записей.

Кроме того, сам родил еще 2 варианта, и результат их работы совпадает с приведенными выше запросами: 1) отдает точно такое же число записей что и запрос @invm, второй - как запрос @kDnZP.

Как бы понять что логически более правильно? (оптимизацию пока опускаем, главное - получить все требуемые записи) Я то и над своими запросами задумался, а над вашими - так вовсе =)
Привожу свои варианты:

1) Отдает 27662 записей
select
  _Fld15523_RRRef as client_id,
  _Fld15518RRef as product_id,
  _Fld15524 as discount,
  max(_Period) as period
from _InfoRg15517 as d
where 
  _Period=(select max(_Period) from _InfoRg15517 as i where d._Fld15518RRef=i._Fld15518RRef and   d._Fld15523_RRRef = i._Fld15523_RRRef)
group by _Fld15523_RRRef,_Fld15518RRef,_Fld15524;
2) Отдает 27742 записей
SELECT
			d._Fld15523_RRRef                 AS client_id,
			d._Fld15518RRef                   AS product_id,
			d._Fld15524                       AS discount,
			d._Period                         AS period 
		FROM
			_InfoRg15517 AS d 
			INNER JOIN (SELECT
							MAX(_Period) AS mperiod,
							_Fld15518RRef,
							_Fld15523_RRRef 
						FROM
							_InfoRg15517 
						GROUP BY
							_Fld15523_RRRef,
							_Fld15518RRef 
			)
			AS M 
			ON 
			d._Fld15523_RRRef=M._Fld15523_RRRef AND
			d._Fld15518RRef=M._Fld15518RRef AND
                        d._Period=M.mperiod

p.s.
Да, на самом деле поля и таблицы такие страшные =)
14 июн 11, 17:55    [10812074]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
monsenior
Member

Откуда: Москва
Сообщений: 969
примерно так:
14 июн 11, 23:03    [10813333]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
monsenior
Member

Откуда: Москва
Сообщений: 969
monsenior
примерно так:

и почему тут нельзя редактировать свои сообщения?(
примерно так:
create table discounts
(
	client_id int,
	product_id int,
	discount int,
	period datetime
);

insert into discounts values(1, 1, 5, CONVERT(datetime, '01.01.2011', 104));
insert into discounts values(1, 2, 5, CONVERT(datetime, '01.01.2011', 104));
insert into discounts values(1, 3, 5, CONVERT(datetime, '01.01.2011', 104));
insert into discounts values(2, 1, 6, CONVERT(datetime, '01.01.2011', 104));
insert into discounts values(2, 3, 6, CONVERT(datetime, '01.01.2011', 104));

insert into discounts values(1, 2, 4, CONVERT(datetime, '01.02.2011', 104));
insert into discounts values(2, 1, 8, CONVERT(datetime, '01.02.2011', 104));
insert into discounts values(2, 3, 5, CONVERT(datetime, '01.02.2011', 104));

select
	client_id,
	product_id,
	max(period) as period
into #t
from discounts
group by
	client_id,
	product_id;
	
select
	a.client_id,
	a.product_id,
	a.discount
from discounts as a
inner join #t as t
	on a.client_id = t.client_id and 
	a.product_id = t.product_id and
	a.period = t.period
	
drop table #t;
drop table discounts;
можете выбросить временную таблицу если любите всё пихать в один запрос)
14 июн 11, 23:05    [10813340]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
invm
Member

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

declare @t table (client_id int, product_id int, discount int, period date)
insert into @t
values
 (1, 1, 1, '20110101'),
 (1, 1, 2, '20110101'),
 (2, 2, 1, '20110101'),
 (2, 2, 2, '20110201'),
 (2, 2, 3, '20110301')

select top (1) with ties
  client_id,
  product_id,
  discount,
  period
from @t
order by
 row_number() over (partition by client_id, product_id order by period desc)

SELECT  *
FROM    ( SELECT    client_id ,
                    product_id ,
                    discount ,
                    period ,
                    MAX(period) OVER ( PARTITION BY client_id, product_id ) AS mperiod
          FROM      @t
        ) t
WHERE   t.mperiod = t.period 
 

ИМХО, иметь две скидки на одну дату неправильно.
15 июн 11, 00:44    [10813652]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
order by row_number() over (partition by client_id, product_id order by period desc)
     |--Top(TOP EXPRESSION:((1)))
44% |--Sort(ORDER BY:([Expr1004] ASC))
|--Sequence Project(DEFINE:([Expr1004]=row_number))
|--Segment
44% |--Sort(ORDER BY:([client_id] ASC, [product_id] ASC, [period] DESC))
13% |--Table Scan(OBJECT:(@t))


Тупит скуль. Ну когда будет Top(N)OVER(<Parttion> <Order>)
15 июн 11, 15:21    [10816627]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
invm
Member

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

Значит плохо просим...
15 июн 11, 15:38    [10816788]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
invm
Значит плохо просим...
Плохо?
Оно в топе (Most Voted, места: 1,2,3,6,9), это раз.
Они обещали что уже будет в Denali, это два.
И где?!
15 июн 11, 22:29    [10818950]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
invm
Member

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

Ну если до сих пор просим, значит плохо просим. Либо, как обычно, наши просьбы веса не имеют.
15 июн 11, 22:35    [10818963]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Mnior
Тупит скуль. Ну когда будет Top(N)OVER(<Parttion> <Order>)
Вам не кажется, что такая конструкция не может существовать,
ибо может возвращать много значений там, где функция должна вернуть одно?
Я бы ещё понял TOP(1).
Могу ошибаться, но в стандарте вроде есть что-то наподобие FIRST(), LAST() и т.п. ?
16 июн 11, 09:45    [10819807]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Или имеется в виду не оконная функция (OVER()), а усложнение нынешнего TOP?
16 июн 11, 09:48    [10819835]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
invm
Member

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

Именно усложнение TOP.
16 июн 11, 10:27    [10820114]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
iap
Или имеется в виду не оконная функция
Может просвятите в понятиях.
Что есть философия оконных функций? Почему они не могут менять набор?
16 июн 11, 12:20    [10821014]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Mnior
iap
Или имеется в виду не оконная функция
Может просвятите в понятиях.
Что есть философия оконных функций? Почему они не могут менять набор?
Они возвращают одно значение для каждой строки, а не N значений, как в TOP(N).
Я исходил из смысла OVER(), который принят сейчас.
А Вы, по-видимому, предлагете его расширить. Правильно?

P.S. Что такое "менять набор"?
16 июн 11, 14:30    [10822123]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
iap
Они возвращают одно значение для каждой строки, а не N значений, как в TOP(N).
Несуразица. Не делает оно "N значени"й для "каждой строки". А просто фильтрует выходной поток.
Это не функция, а оператор. Оконный.

OVER не определяет что это функция. Он определяет диапазон и порядок применения к тому к чему дописан. Дописан к функции Sum(), значит она стала оконной. Дописали к оператору Top, значит он стал оконным.

Согласен что синтаксис плохой (не я выбирал). Правильно было бы указать применимость:
TOP (expression) [ PERCENT ] [ WITH TIES ] [ BY GROUP ]

iap
А Вы, по-видимому, предлагете его расширить.
Не я один. Голосов за это немало.
16 июн 11, 15:58    [10822905]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Mnior
синтаксис
И семантика.
16 июн 11, 16:00    [10822912]     Ответить | Цитировать Сообщить модератору
 Re: Группировка записей и максимальное значение  [new]
uaggster
Member

Откуда:
Сообщений: 1058
kDnZP, ужасы какие то говорите :-)

Select a.client_id,
  a.product_id,
  a.discount,
  a.period
From discounts a
Where not Exists (Select * from discounts b
Where a.client_id=b.client_id and a.product_id=b.product_id and a.period<b.period)
16 июн 11, 22:55    [10825056]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить