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

Откуда:
Сообщений: 37
Добрый день ! Если кто нибудь сталквался с такой задачей или знает как ее решить помогите решить ее! Очень надо!
Задача заключается в том что бы вычислить медиану(по ценам столбец price) в огромной таблице где более 5 000 000 записей
По сути, эта медиана есть при четном колтчестве чисел - среднее арифметическое между двумя крайними из половин (большее в нижней половине и меньшее в верхней) или число по средине ряда чисел при их нечетном количестве
SQL
как я понял надо вставить индексный столбец
// сортируем
create clusterred index price on myteble(price)
ALTER mytable add k1 int identity
DROP index mytable.price
create clustered index k1 on mytable.price

Select top 1@rows=rows from sysindexes where id=OBJECT_ID('mytable')
order by inid

Select avg(price) as Медиана from mytable
where k1 between @rows/2 and (@rows/2)+SIGN(@rows+1%2)


Мне надо написать запрос HQL и получить медиану как это сделать??
дело втом что записи добавляется каждый час как оптимизировать запрос
Заранее спасибо!
18 ноя 07, 12:34    [4930411]     Ответить | Цитировать Сообщить модератору
 Re: HQL и медиана  [new]
aleks2
Guest
Ну в 2000-м я родил только такого ублюдка

declare @t table(price money PRIMARY KEY CLUSTERED)

insert @t(price) values(1)
insert @t(price) values(2)
insert @t(price) values(3)
insert @t(price) values(4)

declare @rc int, @rc05 int, @maxprice money, @maxprice2 money
set @rc=(select  count(*) FROM @t)
set @rc05=@rc/2
if @rc05*2=@rc begin
  set rowcount @rc05
  select @maxprice=price FROM @t ORDER BY price
  set @rc05=@rc05+1
  set rowcount @rc05
  select @maxprice2=price FROM @t ORDER BY price
  select (@maxprice+@maxprice2)/2
end else begin
  set @rc05=@rc05+1
  set rowcount @rc05
  select @maxprice=price FROM @t ORDER BY price
  select @maxprice
end

это его "оптимизированный вариант"

declare @t table(price money PRIMARY KEY CLUSTERED)

insert @t(price) values(1)
insert @t(price) values(2)
insert @t(price) values(3)
insert @t(price) values(4)

declare @rc int, @rc05 int, @maxprice money, @maxprice2 money
set @rc=(select  count(*) FROM @t)
set @rc05=@rc/2
if @rc05*2=@rc begin
  set @rc05=@rc05+1
  set rowcount @rc05
  select @maxprice2=@maxprice, @maxprice=price FROM @t ORDER BY price
  select (@maxprice+@maxprice2)/2
end else begin
  set @rc05=@rc05+1
  set rowcount @rc05
  select @maxprice=price FROM @t ORDER BY price
  select @maxprice
end
18 ноя 07, 15:58    [4930682]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: HQL и медиана  [new]
proof666
Member

Откуда:
Сообщений: 16
Прошло уже много времени, но вдруг кому пригодится.
Сам столкнулся с такой же задачей, получилась такая функция:
IF OBJECT_ID('dbo.НАЗВАНИЕ ФУНКЦИИ') IS NOT NULL
  DROP FUNCTION dbo.'НАЗВАНИЕ ФУНКЦИИ';
go

--'СТОЛБЕЦ' столбец таблицы по которому нужно получить медиану
--'@ПАРАМЕТР1' передаваемый параметр, по сути группировка
--'@ПАРАМЕТР2' передаваемый параметр, по сути группировка
/*
для рассчета медианы по столбцу, к нему необходимо применить ROW_Number() over (Order by столбец), 
в моем случае он называется ROW
*/


CREATE FUNCTION dbo.'НАЗВАНИЕ ФУНКЦИИ' ('ПАРАМЕТР1' AS int,'@ПАРАМЕТР2' AS int) --указываем тип данных переменных
RETURNS NUMERIC(11, 1) -- тип выводимых данных
AS
BEGIN
  RETURN
(
	(select	max('СТОЛБЕЦ') as  'Имя'
from	(select top 50 PERCENT 'СТОЛБЕЦ' from 'Таблица' where 'ПАРАМЕТР1'='@ПАРАМЕТР1' 
						and 'ПАРАМЕТР2'='@ПАРАМЕТР2' order by ROW) a1)
+
(select	min('СТОЛБЕЦ') as  'Имя'
from	(select top 50 PERCENT 'СТОЛБЕЦ' from 'Таблица' where 'ПАРАМЕТР1'='@ПАРАМЕТР1' 
						and 'ПАРАМЕТР2'='@ПАРАМЕТР2' order by ROW desc) a2)

)/2
END
GO


-- далее применяем функцию к таблице:
select	distinct 'ПАРАМЕТР1','ПАРАМЕТР2', dbo.'НАЗВАНИЕ ФУНКЦИИ'('ПАРАМЕТР1','ПАРАМЕТР2') as 'Имя'
from	'Таблица'
17 июл 12, 15:59    [12878618]     Ответить | Цитировать Сообщить модератору
 Re: HQL и медиана  [new]
proof666
Member

Откуда:
Сообщений: 16
Кстати, в моем примере все-равно, четное или не четное количество строк в сортированном диапазоне, т.к. в случае нечетного мин и макс значения - будет одно и тоже число... (число + число)/2 = число...
17 июл 12, 16:02    [12878646]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: HQL и медиана  [new]
RouR
Member

Откуда:
Сообщений: 174
PERCENTILE_CONT(0.5)
MSSQL2012+
31 июл 15, 12:25    [17959448]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить