Calculating median

praj227 · **Joined:** Tue Aug 09, 2005 6:02 pm **Posts:** 5

Hi,

I have a table with persormance latencies. I want to calculate the median latency. The rows in the table are such that the latencies are unsorted. How can I calculate the median if I'm using Hibernate?

Thanks

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

sven · **Posted:** Tue Aug 30, 2005 8:06 pm

Which database are you using?
Maybe your database does already support a median function. Oracle does, for example.

Code:

SELECT MEDIAN(column_name) FROM table;

In this case you could use a native SQL query or extend the OracleDialect (org.hibernate.dialect) in order to support this function.

If you're using a database like MySQL, which does not support such a function, you'll probably have to do it with several selects or within Java.

Best regards
Sven
[/list]

sven · **Posted:** Tue Aug 30, 2005 8:09 pm

Just an addition.
The median function in Oracle requires at least version 10g.

Best regards
Sven

praj227 · **Joined:** Tue Aug 09, 2005 6:02 pm **Posts:** 5

I am using mysql so it doesn't support median function. Another thing is that I may have thousands of rows so reading them using a select statement into a java List and then going to the middle element in the list seemed a memory intensive solution to me.

sven · **Posted:** Wed Aug 31, 2005 5:32 pm

www.google.com

There are many articles about this topic.

For example:
http://dev.mysql.com/doc/mysql/en/group ... tions.html

Best regards
Sven

praj227 · **Joined:** Tue Aug 09, 2005 6:02 pm **Posts:** 5

I tried that with an example set but didn't get any results. Please correct me if I am doing something wrong here.

Table temp(x int)
values in the table - 13 13 13 13 14 14 16 18 21

query =
select t.x from temp t,temp s group by t.x having SUM(SIGN(1-SIGN(s.x-t.x)))=(COUNT(*)+1)/2;
result =
Empty set (0.00 sec)

getting the breakup:

select sum(sign(1-sign(b.x-a.x))),(count(*)+1)/2, b.x, a.x from temp a, temp b group by a.x;

result =

|sum(sign(1-sign(b.x-a.x))) | (count(*)+1)/2 | x | x |
| 16 | 18.50 | 13 | 13 |
| 12 | 9.50 | 13 | 14 |
| 7 | 5.00 | 13 | 16 |
| 8 | 5.00 | 13 | 18 |
| 9 | 5.00 | 13 | 21 |

Am I doing something wrong here or this doesn't work as its supposed to?

sven · **Posted:** Wed Aug 31, 2005 6:31 pm

You should took the first example in the comments, right?

Reading further on, you'll see:

Quote:

The SELECT statement listed in a comment above does not work for all lists of values (especially when the median value is not unique in the list, or when the median should be the average of the two middle-most values in an even-sized list).

Try another take on the median. :)

Best regads
Sven

praj227 · **Joined:** Tue Aug 09, 2005 6:02 pm **Posts:** 5

Awesome!!! Sorry I got really impatient with the problem and didn't scroll all the way to the bottom ;)

Thanks a lot.