Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Timmed Mean
Message
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Autre
Titre:
Divers
Thread ID:
00764927
Message ID:
00766128
Vues:
36
Would this work for you?
select avg(x.pageViews) as avgPageViewsPerDay 
	FROM (select top 95 percent count(*) as pageViews from hits
		group by CONVERT(char(8), viewTime,112) AS ViewDate
		order by pageviews asc ) x
	JOIN (select top 95 percent count(*) as pageViews from hits
			group by CONVERT(char(8), viewTime,112) AS ViewDate
			order by pageviews desc ) y
		ON x.ViewDate = y.ViewDate
>Is there an easy way on SQL Server to find the trimmed mean vs a regular mean "avg()" of something?
>
>I came up with a way that involves selecting the top 95 percent of the results ordered ascending and then ordered descending. But I think it has a flaw, becuase the second time it would select, less records would be trimmed.
>
>Here is the code I have so far...
>
>select avg(pageViews) as avgPageViewsPerDay from (
>select top 95 percent pageViews from (
>select top 95 percent count(*) as pageViews from hits
>group by datepart(day, viewTime), datepart(month, viewTime), datepart(year, viewTime)
>order by pageviews asc ) x
>order by pageviews desc ) y
>
>
>To illustrate the flaw I forsee Ill use an example, your main result has a 100 records and you want a 10% trimmed mean. So you need to cut off the top 5 and bottom 5 leaving 90 results left. When you run the first select top 95% ordered ascending it will trim the bottom 5 rows, but when you run the descending trim, it will take 95% of the remaining 95 records, meaning only 4.75 records will be trimmed, now depeding on how sql server rounds the value, this maybe or maybe not be accurate.
>
>I hope Im explaining this clearly enough.. any ideas.
>
>One idea I did have was to calculate the amount of records to trim prior, and use it in a "select top " instead of a percentage, but you would have to use a dynamicaly formed query then because you cant use a variable in place of the number, and I really dont want to do that.
--sb--
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform