Prev: paypal wholesale UGG shoes(paypal payment)(www.globlepurchase.com)
Next: Why does adding a where clause make query 100 times slower?
From: HumanJHawkins on 9 Apr 2010 12:17
There is a good article at ASPFAQ on how to calculate median in a SQL
table. (http://databases.aspfaq.com/database/how-do-i-calculate-the-
median-in-a-table.html). I would like to either turn that into a
stored procedure, or ideally turn it into a custom aggregate function
do I could use it with a GROUP BY clause.

There are details and caveats in the article, but the gist of the SQL
is:
SELECT AVG(splunge) FROM
(
SELECT splunge FROM (
SELECT TOP 1 splunge = splunge * 1.0 FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge
) sub_a
ORDER BY 1 DESC
) sub_1
UNION ALL
SELECT splunge FROM (
SELECT TOP 1 splunge = splunge * 1.0 FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge DESC
) sub_b
ORDER BY 1
) sub_2
) median

Is it possible to create custom aggregate functions in SQL? If not, is
it possible to pass an array into an SP so the SP could return the
median? I haven't found any direct way of doing either of these.

Thanks in advance for any tips, links, or anything that points me in
the right direction.
From: HumanJHawkins on 9 Apr 2010 15:22
On Apr 9, 9:43 am, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> You can create custom aggregate with CLR:http://msdn.microsoft.com/en-us/library/ms131057.aspx
>
> But if you are using SQL Server 2008 you can pass table-valued parameters to functions and stored procedures and
> calculate median that way.http://msdn.microsoft.com/en-us/library/bb510489.aspx
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Thanks Plamen,

After posting, I found a note where you helped me on a similar issue a
few years ago. Unfortunately after reading through those articles, it
looks like this is harder than I hoped, and probably outside the scope
of what I should do for this project. I'll go back to what you helped
me with earlier, and work it out without a custom aggregate.
From: Erland Sommarskog on 18 Apr 2010 17:25
HumanJHawkins (JHawkins(a)Locutius.Com) writes:
> There is a good article at ASPFAQ on how to calculate median in a SQL
> table. (http://databases.aspfaq.com/database/how-do-i-calculate-the-
> median-in-a-table.html). I would like to either turn that into a
> stored procedure, or ideally turn it into a custom aggregate function
> do I could use it with a GROUP BY clause.
>
> There are details and caveats in the article, but the gist of the SQL
> is:
> SELECT AVG(splunge) FROM
> (
> SELECT splunge FROM (
> SELECT TOP 1 splunge = splunge * 1.0 FROM
> (
> SELECT TOP 50 PERCENT splunge
> FROM blat ORDER BY splunge
> ) sub_a
> ORDER BY 1 DESC
> ) sub_1
> UNION ALL
> SELECT splunge FROM (
> SELECT TOP 1 splunge = splunge * 1.0 FROM
> (
> SELECT TOP 50 PERCENT splunge
> FROM blat ORDER BY splunge DESC
> ) sub_b
> ORDER BY 1
> ) sub_2
> ) median
>
> Is it possible to create custom aggregate functions in SQL? If not, is
> it possible to pass an array into an SP so the SP could return the
> median? I haven't found any direct way of doing either of these.

You can use the row_number function to compute the median:

; WITH numbered AS (
SELECT a, b, c, d, val,
rowno = row_number() OVER (PARTITION BY a, b, c, d
ORDER BY val),
cnt = COUNT(*) OVER(PARITION BY a, b, c, d)
FROM tbl
)
SELECT a, b, c, d, AVG(val)
FROM numbered
WHERE rowno IN (cnt/2 + 1, (cnt + 1)/2)
GROUP BY a, b, c, d

The CTE numbers the rows and counts them per your grouping columns.
The CTE is then filtered so that only the middle row(s) is retained,
and if there are two middle rows, we take the average of that. It's
important that your GROUP BY clause matches your PARTITION BY clause.


As for "arrays", see http://www.sommarskog.se/arrays-in-sql.html on my
web site for a discussion.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: paypal wholesale UGG shoes(paypal payment)(www.globlepurchase.com)
Next: Why does adding a where clause make query 100 times slower?