On Thu, Jun 19, 2008 at 09:54:12AM -0700, Rt Ibmer wrote:
> Hello,
>
> I am trying to write a subquery that can serve as a poor man's median
> function. However I am running into issues every angle I try and cannot find
> any details about this in the docs or list archives.
>
> Question 1:
>
> Currently I can calculate the median manually as two steps:
>
> sql> select count(*)/2 from mytable where myIntVal is not null;
> +----------------+
> | sql_div_count_ |
> +================+
> | 10 |
> +----------------+
> sql> select myIntVal as median from mytable order by myIntVal limit 1 offset
> 10;
> +--------+
> | median |
> +========+
> | 1172 |
> +--------+
>
> However I cannot figure out how to combine these two queries into one query
> so that the count/2 can be done together with the query, so that I don't have
> to manually first get the count and then hard code it into the second query
> that does the median. How can these be combined?
>
> For instance I tried this which does not work:
>
> sql> select myIntVal as median from mytable order by myIntVal limit 1 offset
> (select count(*)/2 from mytable where myIntVal is not null);
>
> but this gives me:
> !syntax error, unexpected '(', expecting IDENT or sqlINT
>
> So I assume you can not dynamically substitute a subquery for an OFFSET
> value. But is there then another way to combine these statements into one
> statement?
>
> Question 2:
>
> Once I get the above resolved, I need to be able to get the median as an
> aggregate function, so that I can get medians in a group by query for each
> element in the group. For instance, if there really was a median() aggregate
> function I would do this:
>
> select median(myIntval), customerid from mytable group by customerid;
>
> However of course there is no median function. So I understand the work
> around for not having a median function is to get the total rows, sort the
> rows and then take the row in the middle.
>
> But how can I do that if it is in a group by statement like the above?
>
> What I am trying desperately to avoid is having to make a query to get all
> the group by results, and then for each result having to do a separate
> individual query to get its median separately. Please tell me this is
> possible! :) And if so, how?
>
> Thank you!!
>
I have a different solution (some googling), try the following
CREATE TABLE sampleData ( groupID int, numValue int );
INSERT INTO sampleData VALUES ( 1, 1 );
INSERT INTO sampleData VALUES ( 1, 2 );
INSERT INTO sampleData VALUES ( 1, 6 );
INSERT INTO sampleData VALUES ( 1, 16 );
INSERT INTO sampleData VALUES ( 1, 7 );
INSERT INTO sampleData VALUES ( 2, 5 );
INSERT INTO sampleData VALUES ( 2, 5 );
INSERT INTO sampleData VALUES ( 2, 5 );
INSERT INTO sampleData VALUES ( 2, 11 );
INSERT INTO sampleData VALUES ( 3, 10 );
INSERT INTO sampleData VALUES ( 3, 17 );
INSERT INTO sampleData VALUES ( 3, 52 );
INSERT INTO sampleData VALUES ( 3, 66 );
INSERT INTO sampleData VALUES ( 4, 18 );
INSERT INTO sampleData VALUES ( 5, 0 );
INSERT INTO sampleData VALUES ( 5, 0 );
SELECT R.groupID, AVG(1.0*R.numValue) AS medianValue
FROM
( SELECT GroupID, numValue, ROW_NUMBER() OVER(PARTITION BY groupID
ORDER BY NumValue) AS rowno
FROM sampleData
) R
INNER JOIN
( SELECT GroupID, 1+count(*) as N
FROM sampleData
GROUP BY GroupID
) G
ON R.GroupID = G.GroupID AND R.rowNo BETWEEN N/2 AND N/2+N%2
GROUP BY R.groupID;
Niels
>
>
>
>
> -------------------------------------------------------------------------
> Check out the new SourceForge.net Marketplace.
> It's the best place to buy or sell services for
> just about anything Open Source.
> http://sourceforge.net/services/buy/index.php
> _______________________________________________
> Monetdb-developers mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/monetdb-developers
--
Niels Nes, Centre for Mathematics and Computer Science (CWI)
Kruislaan 413, 1098 SJ Amsterdam, The Netherlands
room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312
url: http://www.cwi.nl/~niels e-mail: [EMAIL PROTECTED]
-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://sourceforge.net/services/buy/index.php
_______________________________________________
Monetdb-developers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-developers
