Return small weighted sample - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Return small weighted sample

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-03 : 15:09:31

Here is what I want to accomplish.

In a table of 100,000 records there is a column called [RELEVANCE].

This column is a weight calculation for a specific item in that table.

I want to return a set # of results (for the sample lets say 10) that brings back a higher quantity of the more popular items (RELEVANCE) In this case I want 80% of my results to be from the top 30 PERCENTILE of highest relevance.

Currently I am thinking of just doing this query below, but it's SOOO Slow due to the newid() being used to randomize the results, any better methods to randomize and get these results so the total results will be a representation of the table.


SELECT *,newid() as id
FROM
(
	select top 8 *
	from
	(
		select top 30 percent *
		from
			mytable
		order by relevance desc
	) a
	order by newid()
) A
union 
SELECT *,newid() as id
FROM
(
	select top 2 *
	from
	(
	select top 70 percent *
	from
		mytable
	order by relevance asc
) a
	order by newid()
) aa
order by id

Thanks!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-03 : 23:49:00

see if this will help
Limiting Result Sets by Using TABLESAMPLE

KH
[spoiler]Time is always against us[/spoiler]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-04 : 00:51:03

Although that is a really cool feature I was unaware of, since my sample I need is not as much random as it is sectioning the table and shuffling it, I do not think I can get that to work. Thanks for showing me that though, I was completely unaware of that feature! Time for me to do a refresher course on BOL:)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-04 : 22:53:27

I tested the tablesample feature by creating non-derived tables, the performance was not a drastic improvement, so I instead just added a couple filters to my TOP clauses to get the amount of row data down a little more and that appears to have resolved it.

Thanks again!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources