datos agregados (por mediana) en mysql por trimestre y por tipo_cliente

Estoy usando MySQL, y mi tabla SQL se ve así:

sales_year (INT), sales_month (INT), sales_day (INT), price (float), customer_type (TEXT) 

Me gustaría saber qué sql_query los datos de precios por trimestre (calcule el precio mediano de cada trimestre y cuántas observaciones se usaron para calcular la mediana) y los agrupe por tipo de cliente.

Estoy luchando con dos pasos importantes: Median no parece ser compatible con mySQL, y también cómo agregar datos por trimestre: parece que agrupar por tipo de cliente es muy fácil una vez que esos dos se resuelven.

LUCHA – Computing the median ….

Yo, por ejemplo, intenté crear un cuarto de columna y funciona, pero calcula el AVG en lugar de la mediana:

  select avg(price) as avg_price, floor(sales_month/3.0+1) as sales_quarter, count(*) as n_transactions, sales_year, customer_type from mydb.mytable group by sales_quarter, sales_year, customer_type; 

Este command funciona perfectamente bien. Pero, idealmente, podría cambiar la media por MEDIAN pero mySQL no tiene dicho soporte, ¿alguna sugerencia sobre cómo cambiar este código para que funcione con fines medianos?

Nota: También intenté instalar mi propia function mediana a partir de funciones definidas por el usuario en este sitio, pero el código C no se compiló en mi mac os X.

Entonces la salida se vería así:

 sales_quarter (INT) sales_year (INT) median_price (FLOAT) number_users_used_to_compute_median (INT) customer_type (TEXT) 

Oh, solo llama al promedio la mediana. Las personas con las que hablas generalmente no sabrán la diferencia (;).

De acuerdo, en serio, puedes hacer esto en MySQL. Existe un método que utiliza group_concat() y substring_index() , pero que corre el riesgo de desbordar los valores intermedios de la cadena. En su lugar, enumere los valores y haga aritmética simple. Para esto, necesitas una enumeración y un total. La enumeración es:

  select t.*, @rn := if(@q = quarter and @y = @year and @ct = customer_type, @rn + 1, if(@q := quarter, if(@y := @year, if(@ct := customer_type, 1, 1), 1), 1) ) as rn from mydb.mytable t cross join (select @q := '', @y := '', @ct := '', @rn := 0) vars order by sales_quarter, sales_year, customer_type, price; 

Esto está cuidadosamente formulado. El order by columnas corresponde a las variables definidas. Solo hay una statement que asigna variables en la select . Las sentencias if() anidadas aseguran que cada variable se establece (utilizando an and or or podría provocar un cortocircuito). Es importante recordar que MySQL no garantiza el order de evaluación de las expresiones en la select , por lo que tener solo una statement establecer las variables es importante para garantizar la corrección.

Ahora, get la mediana es bastante fácil. Necesita el recuento total, el valor secuencial ( rn ) y cierta aritmética para manejar el caso donde hay un número par de valores:

 select trn.sales_quarter, trn.sales_year, trn.customer_type, avg(price) as median from (select t.*, @rn := if(@q = quarter and @y = @year and @ct = customer_type, @rn + 1, if(@q := quarter, if(@y := @year, if(@ct := customer_type, 1, 1), 1), 1) ) as rn from mydb.mytable t cross join (select @q := '', @y := '', @ct := '', @rn := 0) vars order by sales_quarter, sales_year, customer_type, price ) trn join (select sales_quarter, sales_year, customer_type, count(*) as numrows from mydb.mytable t group by sales_quarter, sales_year, customer_type ) s on trn.sales_quarter = s.sales_quarter and trn.sales_year = s.sales_year and trn.customer_type = s.customer_type where 2*rn in (numrows, numrows - 1, numrows + 1) group by trn.sales_quarter, trn.sales_year, trn.customer_type; 

Solo para enfatizar que el promedio final no está haciendo un cálculo promedio. Está calculando la mediana. La definición normal es que para un número par de valores, la mediana es el promedio de los dos en el medio. La cláusula where maneja tanto los casos pares como los impares.

Para get la mediana, podrías intentar algo como,

 SELECT * FROM table LIMIT COUNT(*)/2, 1 

Esto básicamente dice: "Dame 1 artículo que comienza en el n / 2º elemento, donde n es el tamaño del set".

Entonces, si lo hicieras por trimestre, sería lo mismo con algunas cosas del tipo de cuarto de GROUP BY arrojadas. Avísame si quieres que amplíe esto más.

Referencia a la respuesta de velcrow y publicar SqlFiddle aquí .

 select quarter, group_concat(val order by row_number) ValSortString, floor((max(row_number) - min(row_number))/2)+1 as FirstPosition, ceil((max(row_number) - min(row_number))/2) +1 as SecondPosition, split_str(group_concat(val order by row_number),',',floor((max(row_number) - min(row_number))/2)+1) as FirstVal, split_str(group_concat(val order by row_number),',',ceil((max(row_number) - min(row_number))/2)+1) as SecondVal, (split_str(group_concat(val order by row_number),',',floor((max(row_number) - min(row_number))/2)+1) + split_str(group_concat(val order by row_number),',',ceil((max(row_number) - min(row_number))/2)+1) )/2 as Median from ( SELECT data.quarter,@rownum:=@rownum+1 as row_number, data.val,total_rows FROM data , (select quarter,count(*) as total_rows from data group by quarter) as t, (SELECT @rownum:=0) r where t.quarter = data.quarter order by data.quarter,val ) as b group by quarter 

Este código solo grupo por trimestre, fácil de expandir otro grupo por columnas.

Yo uso group_concat y split_str para simplificarlo solo usando una subconsulta.

Entonces debes crear la function split_str:

 CREATE FUNCTION SPLIT_STR( x VARCHAR(255), delim VARCHAR(12), pos INT ) RETURNS VARCHAR(255) RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos), LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1), delim, ''); 

El problema es group_concat y split_str tiene límite. Pero esta versión puede resolver el problema solo con subconsulta y fácil de entender.

ACTUALIZAR
De acuerdo con el indicador de Gordon Linoff, agrego otra solución sin group_concat.

 select quarter, floor((total_rows + 1)/2) as FirstPosition, ceil((total_rows + 1)/2) as SecondPosition, avg(val) as median from ( SELECT data.quarter, @rownum:= if (@q = data.quarter ,@rownum+1,if(@q := data.quarter, 1, 1) )as row_number, data.val, total_rows FROM data , (select quarter,count(*) as total_rows from data group by quarter) as t, (SELECT @q := '', @rownum:=0) r where t.quarter = data.quarter order by data.quarter,val ) as b where row_number in (floor((total_rows + 1)/2), ceil((total_rows + 1)/2)) group by quarter 

Y nuevo Sql Fiddle aquí .

Soy un novato en mysql, esta pregunta es fácil de realizar por MSSql, DB2 u Oracle, todas tienen Row_number()(Partition by ...) .

No tengo la reputación suficiente para comentar la respuesta de Gordon Linoff. Tengo que agradecerle que haya aprendido a implementar la function row_number()(Partition by ...) .