I'm trying to calculate a median on a column in a table. Does anyone know a workable hql median query?
I'm using SQL Server(2000 something, not 2012, so I can't use PERCENTILE_CONT). I've found a number of solutions online for straight sql, but when I try to port it to HQL I get all sorts of problems(including not being able to use a subquery in a from clause, queries requires a from clause, unable to figure how to add 'over(order by x)' to SQLServerDialect which means that row_number for sql server is pretty useless).
I'm thankful for any suggestions.
Here's some possibilities I found from the web
Code:
SELECT AVG(1.0E * x)
FROM (
SELECT x,
2 * ROW_NUMBER() OVER (ORDER BY x) - COUNT(*) OVER () AS y
FROM @Foo
) AS d
WHERE y BETWEEN 0 AND 2
Code:
SELECT
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
+
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median
Code:
SELECT
CustomerId,
AVG(TotalDue)
FROM
(
SELECT
CustomerId,
TotalDue,
ROW_NUMBER() OVER (
PARTITION BY CustomerId
ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc,
ROW_NUMBER() OVER (
PARTITION BY CustomerId
ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc
FROM Sales.SalesOrderHeader SOH
) x
WHERE
RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY CustomerId
ORDER BY CustomerId;
Login
FAQ
Search