¿Cómo agrupar por semana en MySQL?

El servidor de tablas de Oracle ofrece una función TRUNC(timestamp,'DY') , TRUNC(timestamp,'DY') . Esta función convierte cualquier marca de tiempo a la medianoche del domingo anterior. ¿Cuál es la mejor manera de hacer esto en MySQL?

Oracle también ofrece TRUNC(timestamp,'MM') para convertir una marca de tiempo a la medianoche del primer día del mes en que ocurre. En MySQL, este es sencillo:

 TIMESTAMP(DATE_FORMAT(timestamp, '%Y-%m-01')) 

Pero este truco de DATE_FORMAT no funcionará durante semanas. WEEK(timestamp) función WEEK(timestamp) , pero realmente no quiero el número de semana dentro del año; esto es para trabajos de varios años.

Puede usar tanto YEAR(timestamp) y YEAR(timestamp) como WEEK(timestamp) y WEEK(timestamp) , y utilizar estas dos expresiones en las cláusulas SELECT y GROUP BY .

No demasiado elegante, pero funcional …

Y, por supuesto, puede combinar estas dos partes de fecha en una sola expresión, es decir, algo así como

 SELECT CONCAT(YEAR(timestamp), '/', WEEK(timestamp)), etc... FROM ... WHERE .. GROUP BY CONCAT(YEAR(timestamp), '/', WEEK(timestamp)) 

Editar : Como señala Martin xx, también puede usar la función YEARWEEK(mysqldatefield) , aunque su salida no es tan amigable como la fórmula anterior.

Edite 2 [3 1/2 años después!]:
YEARWEEK(mysqldatefield) con el segundo argumento opcional ( mode ) establecido en 0 o 2 es probablemente la mejor manera de agregar por semanas completas (es decir, incluidas las semanas que se distribuyen a partir del 1 de enero), si eso es lo que se desea. El enfoque YEAR() / WEEK() propuesto inicialmente en esta respuesta tiene el efecto de dividir los datos agregados para esas semanas “transzonales” en dos: una con el año anterior, una con el año nuevo.
Un corte limpio cada año, a costa de tener hasta dos semanas parciales, uno en cada extremo, a menudo se desea en la contabilidad, etc. y para eso el enfoque YEAR() / WEEK() es mejor.

La respuesta aceptada anteriormente no funcionó para mí, porque ordenó las semanas por orden alfabético, no por orden cronológico:

 2012/1 2012/10 2012/11 ... 2012/19 2012/2 

Aquí está mi solución para contar y agrupar por semana:

 SELECT CONCAT(YEAR(date), '/', WEEK(date)) AS week_name, YEAR(date), WEEK(date), COUNT(*) FROM column_name GROUP BY week_name ORDER BY YEAR(DATE) ASC, WEEK(date) ASC 

Genera:

 YEAR/WEEK YEAR WEEK COUNT 2011/51 2011 51 15 2011/52 2011 52 14 2012/1 2012 1 20 2012/2 2012 2 14 2012/3 2012 3 19 2012/4 2012 4 19 

Puede obtener el número de año y semana concatenados (200945) utilizando la función YEARWEEK () . Si entiendo su objective correctamente, eso debería permitirle agrupar sus datos de varios años.

Si necesita la marca de tiempo real para el comienzo de la semana, es menos agradable:

 DATE_SUB( field, INTERVAL DAYOFWEEK( field ) - 1 DAY ) 

Para realizar un pedido mensual, puede considerar la función LAST_DAY () : la clasificación sería el último día del mes, pero eso debería ser equivalente a la clasificación para el primer día del mes … ¿no es así?

Lo descubrí … es un poco engorroso, pero aquí está.

 FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -1, 7)) 

Y, si las reglas de su negocio dicen que sus semanas comienzan los lunes, cambie el -1 a -2 .


Editar

Han pasado los años y finalmente he terminado de escribir esto. http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

Solo agregue esto en la selección:

 DATE_FORMAT($yourDate, \'%X %V\') as week 

Y

 group_by(week); 

Si necesita la fecha de “fin de semana”, esto también funcionará. Esto contará la cantidad de registros para cada semana. Ejemplo: Si se crearon tres órdenes de trabajo entre (inclusive) 1/2/2010 y 1/8/2010 y 5 se crearon entre (inclusive) 1/9/2010 y 1/16/2010, esto devolverá:

3 1/8/2010
5 1/16/2010

Tuve que usar la función DATE () extra para truncar mi campo de fecha y hora.

SELECT COUNT(*), DATE_ADD( DATE(wo.date_created), INTERVAL (7 - DAYOFWEEK( wo.date_created )) DAY) week_ending FROM work_order wo GROUP BY week_ending;

    Intereting Posts