Consulta de sorting SQL para calcular ranges y mediana en subgrupos

Quiero calcular la mediana de y en subgrupos de esta simple xy_table :

  x | y --groups--> gid | x | y --medians--> gid | x | y ------- ------------- ------------- 0.1 | 4 0.0 | 0.1 | 4 0.0 | 0.1 | 4 0.2 | 3 0.0 | 0.2 | 3 | | 0.7 | 5 1.0 | 0.7 | 5 1.0 | 0.7 | 5 1.5 | 1 2.0 | 1.5 | 1 | | 1.9 | 6 2.0 | 1.9 | 6 | | 2.1 | 5 2.0 | 2.1 | 5 2.0 | 2.1 | 5 2.7 | 1 3.0 | 2.7 | 1 3.0 | 2.7 | 1 

En este ejemplo, cada x es único y la tabla ya está orderada por x . Ahora quiero GROUP BY round(x) y get la tupla que contiene la mediana de y en cada grupo.

Ya puedo calcular la mediana de toda la tabla con esta consulta de sorting :

 SELECT ax, ay FROM xy_table a,xy_table b WHERE ay >= by GROUP BY ax, ay HAVING count(*) = (SELECT round((count(*)+1)/2) FROM xy_table) 

Salida: 0.1, 4.0

Pero todavía no tuve éxito escribiendo una consulta para calcular la mediana de los subgrupos.

Atención: no tengo una function de agregación median() disponible. Tampoco proponga soluciones con instrucciones PARTITION , RANK o QUANTILE (como las que se encuentran en las preguntas SO similares pero también en el proveedor). Necesito SQL simple (es decir, compatible con SQLite sin la function median() )

Edición: en realidad estaba buscando el Medoid y no el Median .

Sugiero hacer la informática en tu lenguaje de progtwigción:

 for each group: for each record_in_group: append y to array median of array 

Pero si está atrapado con SQLite, puede orderar cada grupo por y y seleccionar los loggings en el medio como este http://sqlfiddle.com/#!5/d4c68/55/0 :

ACTUALIZACIÓN : solo importa un valor "mediano" mayor, incluso para nr. de filas, por lo que no se necesita avg() :

 select groups.gid, ids.y median from ( -- get middle row number in each group (bigger number if even nr. of rows) -- note the integer divisions and modulo operator select round(x) gid, count(*) / 2 + 1 mid_row_right from xy_table group by round(x) ) groups join ( -- for each record get equivalent of -- row_number() over(partition by gid order by y) select round(ax) gid, ax, ay, count(*) rownr_by_y from xy_table a left join xy_table b on round(ax) = round (bx) and ay >= by group by ax ) ids on ids.gid = groups.gid where ids.rownr_by_y = groups.mid_row_right 

OK, esto se basa en una tabla temporal:

 create temporary table tmp (x float, y float); insert into tmp select * from xy_table order by round(x), y 

Pero podría crear esto para un range de datos que le interesan. Otra forma sería asegurar que xy_table este order de sorting, en lugar de orderar solo en x . La razón de esto es la falta de capacidad de numeración de filas de SQLite.

Entonces:

 select tmp4.x as gid, t.* from ( select tmp1.x, round((tmp2.y + coalesce(tmp3.y, tmp2.y)) / 2) as y -- <- for larger of the two, change to: (case when tmp2.y > coalesce(tmp3.y, 0) then tmp2.y else tmp3.y end) from ( select round(x) as x, min(rowid) + (count(*) / 2) as id1, (case when count(*) % 2 = 0 then min(rowid) + (count(*) / 2) - 1 else 0 end) as id2 from ( select *, rowid from tmp ) t group by round(x) ) tmp1 join tmp tmp2 on tmp1.id1 = tmp2.rowid left join tmp tmp3 on tmp1.id2 = tmp3.rowid ) tmp4 join xy_table t on tmp4.x = round(tx) and tmp4.y = ty 

Si quisiera tratar la mediana como el mayor de los dos valores medios, que no se ajusta a la definición como @Aprillion ya se señaló, entonces simplemente tomaría el mayor de los dos valores y , en lugar de su promedio, en el tercera línea de la consulta.