如何为median创建聚合函数？

Question

我需要在Advantage-Database中创建一个聚合函数来计算中间值。

SELECT 
    group_field
  , MEDIAN(value_field) 
FROM 
  table_name
GROUP BY 
  group_field

似乎我正在寻找的解决方案非常特定于所使用的sql引擎。

Answer 1

正如您在帮助文件中看到的那样，ADS中没有内置的中值聚合函数：

http://devzone.advantagedatabase.com/dz/webhelp/Advantage10.1/index.html

恐怕您必须编写自己的存储过程或sql脚本来解决此问题。

以下问题的可接受答案可能是您的解决方案：Simple way to calculate median with MySQL

Answer 2

我已经用一个解决方案更新了这个答案，这个解决方案避免了连接，而是将一些数据存储在json对象中。

解决方案#1 (两个selects和一个join，一个获取计数，一个获取排名)

这有点冗长，但它确实有效，而且速度相当快。

SELECT  x.group_field, 
        avg(
            if( 
                x.rank - y.vol/2 BETWEEN 0 AND 1, 
                value_field, 
                null
            )
        ) as median
FROM (
    SELECT  group_field, value_field, 
            @r:= IF(@current=group_field, @r+1, 1) as rank, 
            @current:=group_field
    FROM (
        SELECT group_field, value_field
        FROM table_name
        ORDER BY group_field, value_field
    ) z, (SELECT @r:=0, @current:='') v
) x, (
    SELECT group_field, count(*) as vol 
    FROM table_name
    GROUP BY group_field
) y WHERE x.group_field = y.group_field
GROUP BY x.group_field

解决方案#2 (使用json对象存储计数并避免联接)

SELECT group_field, 
    avg(
        if(
            rank - json_extract(@vols, path)/2 BETWEEN 0 AND 1,
            value_field,
            null
        )
    ) as median
FROM (
    SELECT group_field, value_field, path, 
        @rnk := if(@curr = group_field, @rnk+1, 1) as rank,
        @vols := json_set(
            @vols, 
            path, 
            coalesce(json_extract(@vols, path), 0) + 1
        ) as vols,
        @curr := group_field
    FROM (
        SELECT p.group_field, p.value_field, concat('$.', p.group_field) as path
        FROM table_name
        JOIN (SELECT @curr:='', @rnk:=1, @vols:=json_object()) v
        ORDER BY group_field, value_field DESC
    ) z
) y GROUP BY group_field;