Joined: Sun Nov 18, 2007 4:42 am Posts: 1
|
Good afternoon! If who н..у.. collided{faced} with such problem{task} or knows as her{it} to decide help to solve her{it}! Very much it is necessary!
The problem{task} would consist in that what to calculate a median (under the prices a column price) in the huge table where more than 5000000 records
As a matter of fact, this median is at even ко.т.естве numbers - average arithmetic between two extreme of half (the greater in the bottom half and smaller in top) or number on сре..не of some numbers at their odd quantity{amount}
SQL
As I have understood it is necessary to insert an index column
// We sort
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 the Median from mytable
where k1 between @rows/2 and (@rows/2) +SIGN (@rows+1%2)
I should write inquiry HQL and to receive a median as it to make??
Business втом that records is added each hour how to optimize inquiry
Beforehand thanks!
|
|