mysql - rango - hacer una busqueda por fechas en php

Buscar coincidencias de rango de fechas dentro de la misma tabla, para usuarios específicos MySQL

Question

No soy un experto en MySQL, por lo que estoy buscando ayuda sobre este tema.

Necesito realizar una prueba simple (en principio), tengo esta tabla (simplificada):

tableid | userid | car | From | To
--------------------------------------------------------
1 | 1 | Fiesta | 2015-01-01 | 2015-01-31
2 | 1 | MX5 | 2015-02-01 | 2015-02-28
3 | 1 | Navara | 2015-03-01 | 2015-03-31
4 | 1 | GTR | 2015-03-28 | 2015-04-30
5 | 2 | Focus | 2015-01-01 | 2015-01-31
6 | 2 | i5 | 2015-02-01 | 2015-02-28
7 | 2 | Aygo | 2015-03-01 | 2015-03-31
8 | 2 | 206 | 2015-03-29 | 2015-04-30
9 | 1 | Skyline | 2015-04-29 | 2015-05-31
10 | 2 | Skyline | 2015-04-29 | 2015-05-31

Necesito encontrar dos cosas aquí:

1. Si algún usuario tiene una solaparación de fechas en sus asignaciones de automóviles de más de un día (el final de la tarea puede ser el mismo día que comienza la nueva asignación).
2. ¿Alguno de los dos usuarios intentó obtener el mismo automóvil asignado en la misma fecha, o los intervalos de fechas se superponen para ellos en el mismo automóvil?

Entonces, la consulta (o consultas) que estoy buscando debe devolver esas filas:

tableid | userid | car | From | To
--------------------------------------------------------
3 | 1 | Navara | 2015-03-01 | 2015-03-31
4 | 1 | GTR | 2015-03-28 | 2015-04-30
7 | 2 | Aygo | 2015-03-01 | 2015-03-31
8 | 2 | 206 | 2015-03-29 | 2015-04-30
9 | 1 | Skyline | 2015-04-29 | 2015-05-31
10 | 2 | Skyline | 2015-04-29 | 2015-05-31 

Siento que estoy golpeando mi cabeza contra la pared aquí, me gustaría poder hacer estas comparaciones en consultas separadas. Necesito mostrarlos en una tabla, pero siempre podría unirme a los resultados.

He investigado y algunas horas de prueba, pero no puedo llegar al resultado que quiero.

SQLFiddle con los datos de prueba anteriores

He intentado estas publicaciones por cierto (no eran exactamente lo que necesitaba pero estaban lo suficientemente cerca, o al menos eso pensé):

Comparando dos rangos de fechas dentro de la misma tabla

Cómo comparar valores de columnas de texto de la misma tabla

Esta fue la solución más cercana que pude encontrar, pero cuando probé en una sola mesa (uniendo la mesa a sí misma) estaba obteniendo resultados extravagantes : verificando una tabla para la superposición de tiempo?

EDITAR

Como solución temporal, he adaptado un enfoque diferente, similar a los mensajes que he encontrado durante mi investigación (arriba). Ahora verificaré si la fecha de alquiler / asignación de automóviles nuevos se superpone con cualquier intervalo de fechas dentro de la tabla. De ser así, guardaré los id. De las filas con las que la fecha se superpone. De esta manera, al menos, podré marcar superposiciones y permitir que un usuario mire las filas marcadas y resuelva cualquier solapamiento manual.

Gracias a todos los que ofrecieron su ayuda con esto, marcaré la respuesta philipxy como la elegida (en las próximas 24 horas) a menos que alguien tenga una mejor manera de lograr esto. No tengo dudas de que después de su respuesta, eventualmente podré alcanzar los resultados que necesito. Por el momento, tengo que adoptar cualquier solución que funcione como necesito para terminar mi proyecto en los próximos días, de ahí el cambio de enfoque.

Editar # 2

Las dos respuestas son geniales y para cualquiera que encuentre que esta publicación tiene el mismo problema que yo, ¡léalas y mire los violines! :) ¡Una gran cantidad de trabajo cerebral increíble entró en ellos! Temporalmente tuve que ir con la solución que mencioné en la Edición número 1 de la mía, pero adaptaré mis consultas para ir con @Ryan Vincent approach + @philipxy ediciones / comentarios sobre ignorar la superposición inicial de un día.

Answer 1

Aquí está la primera parte: Autos superpuestos por usuario ...

SQLFiddle: Consulta correlacionada y Join Query

Segunda parte: más de un usuario en un carro al mismo tiempo: SQLFiddle - Consulta correlacionada y Join Query . Consulta debajo ...

Yo uso las consultas correlacionadas:

Es probable que necesite índices en userid y ''car''. Sin embargo, compruebe el ''plan de explicación'' para ver cómo está mysql accediendo a los datos. Y solo pruébalo :)

Coches superpuestos por usuario

La consulta:

SELECT `allCars`.`userid` AS `allCars_userid`,
`allCars`.`car` AS `allCars_car`,
`allCars`.`From` AS `allCars_From`,
`allCars`.`To` AS `allCars_To`,
`allCars`.`tableid` AS `allCars_id`
FROM
`cars` AS `allCars`
WHERE
EXISTS
(SELECT 1
FROM `cars` AS `overlapCar`
WHERE
`allCars`.`userid` = `overlapCar`.`userid`
AND `allCars`.`tableid` <> `overlapCar`.`tableid`
AND NOT ( `allCars`.`From` >= `overlapCar`.`To` /* starts after outer ends */
OR `allCars`.`To` <= `overlapCar`.`From`)) /* ends before outer starts */
ORDER BY
`allCars`.`userid`,
`allCars`.`From`,
`allCars`.`car`; 

Los resultados:

allCars_userid allCars_car allCars_From allCars_To allCars_id
-------------- ----------- ------------ ---------- ------------
1 Navara 2015-03-01 2015-03-31 3
1 GTR 2015-03-28 2015-04-30 4
1 Skyline 2015-04-29 2015-05-31 9
2 Aygo 2015-03-01 2015-03-31 7
2 206 2015-03-29 2015-04-30 8
2 Skyline 2015-04-29 2015-05-31 10

Por qué funciona? o cómo lo pienso:

Utilizo la consulta correlacionada, por lo que no tengo duplicados para tratar y es probablemente la más fácil de entender para mí. Hay otras formas de expresar la consulta. Cada uno tiene ventajas y desventajas. Quiero algo que pueda entender fácilmente.

Requisito: para cada usuario, asegúrese de que no tengan dos o más automóviles al mismo tiempo.

Por lo tanto, para cada registro de usuario (AllCars) compruebe la tabla completa (overlapCar) para ver si puede encontrar un registro diferente que se superpone para el momento del registro actual. Si encontramos uno, seleccione el registro actual que estamos verificando (en todos los autos).

Por lo tanto, el control de superposición es:

• el allCars userid allCars y el overLap userid overLap deben ser iguales
• el allCars auto allCars y el récord del auto overlap deben ser diferentes

• el allCars tiempo allCars y el overLap tiempo overLap deben superponerse.

  La verificación del rango de tiempo:

  En lugar de verificar los tiempos de superposición, use pruebas positivas. El enfoque más fácil es comprobar que no se superpone y aplicar un NOT a él.

Un automóvil con más de un usuario al mismo tiempo ...

La consulta:

SELECT `allCars`.`car` AS `allCars_car`,
`allCars`.`userid` AS `allCars_userid`,
`allCars`.`From` AS `allCars_From`,
`allCars`.`To` AS `allCars_To`,
`allCars`.`tableid` AS `allCars_id`
FROM
`cars` AS `allCars`
WHERE
EXISTS
(SELECT 1
FROM `cars` AS `overlapUser`
WHERE
`allCars`.`car` = `overlapUser`.`car`
AND `allCars`.`tableid` <> `overlapUser`.`tableid`
AND NOT ( `allCars`.`From` >= `overlapUser`.`To` /* starts after outer ends */
OR `allCars`.`To` <= `overlapUser`.`From`)) /* ends before outer starts */
ORDER BY
`allCars`.`car`,
`allCars`.`userid`,
`allCars`.`From`;

Los resultados:

allCars_car allCars_userid allCars_From allCars_To allCars_id
----------- -------------- ------------ ---------- ------------
Skyline 1 2015-04-29 2015-05-31 9
Skyline 2 2015-04-29 2015-05-31 10

Editar:

En vista de los comentarios, por parte de @philipxy, acerca de los rangos de tiempo que necesitan verificaciones "mayores que o iguales a", he actualizado el código aquí. No he cambiado los SQLFiddles .

Answer 2

Para cada tabla de entrada y salida, encuentre su significado . Es decir, una plantilla de declaración parametrizada por nombres de columna, también conocido como predicado , que una fila convierte en una declaración verdadera o falsa, también conocida como proposición . Una tabla contiene las filas que hacen que su predicado sea una proposición verdadera. Es decir, las filas que hacen que una proposición verdadera entre en una tabla y filas que hacen que una proposición falsa permanezca fuera. Por ejemplo, para su tabla de entrada:

rental [tableid] was user [userid] renting car [car] from [from] to [to]

Luego, pronuncie el predicado de la tabla de salida en términos del predicado de la tabla de entrada. No use descripciones como su 1 y 2:

1. Si algún usuario tiene una solaparación de fechas en sus asignaciones de automóviles de más de un día (el final de la tarea puede ser el mismo día que comienza la nueva asignación).

En su lugar, encuentre el predicado que indica una fila arbitraria cuando está en la tabla:

rental [tableid] was user [user] renting car [car] from [from] to [to]
in self-conflict with some other rental

Para que el DBMS calcule las filas que lo hacen verdadero, debemos expresar esto en términos de nuestro predicado (s) dado más literales y condiciones:

-- query result holds the rows where
FOR SOME t2.tableid, t2.userid, ...:
rental [t1.tableid] was user [t1.userid] renting car [t1.car] from [t1.from] to [t1.to]
AND rental [t2.tableid] was user [t2.userid] renting car [t2.car] from [t2.from] to [t2.to]
AND [t1.userid] = [t2.userid] -- userids id the same users
AND [t1.to] > [t2.from] AND ... -- tos/froms id intervals with overlap more than one day
...

(Dentro de una SELECT SQL SELECT , el producto cruzado de las tablas JOIN ed tiene los nombres de columna de la column alias del formulario. Piense en . Como otro carácter permitido en los nombres de las columnas. Finalmente, la cláusula SELECT descarta el alias .)

Convertimos un predicado de consulta en una consulta SQL que calcula las filas que lo hacen verdadero:

• El predicado de una tabla es reemplazado por el alias de la tabla.
• Para usar el mismo predicado / tabla varias veces, haga alias.
• Cambiar la columna de old a new en un predicado agrega AND old = new .
• AND de los predicados es reemplazado por JOIN .
• OR de los predicados es reemplazado por UNION .
• AND NOT de predicados se reemplaza por EXCEPT , MINUS o la MINUS LEFT JOIN correspondiente.
• AND condition se reemplaza por WHERE o ON .
• Para un predicado verdadero FOR SOME columns to drop o THERE EXISTS columns to drop , SELECT DISTINCT columns to keep SELECT DISTINCT columns to keep .
• Etc. (Ver esto )

Por lo tanto (completando las elipsis):

SELECT DISTINCT t1.*
FROM t t1 JOIN t t2
ON t1.userid = t1.userid -- userids id the same users
WHERE t1.to > t2.from AND t2.to > t1.from -- tos/froms id intervals with overlap more than one day
AND t1.tableid <> t2.tableid -- tableids id different rentals

2. ¿Alguno de los dos usuarios intentó obtener el mismo auto asignado en la misma fecha, o los intervalos de fechas se superponen para ellos en el mismo automóvil?

Encontrar el predicado que una fila arbitraria indica cuando está en la tabla:

rental [tableid] was user [user] renting car [car] from [from] to [to]
in conflict with some other user''s rental

En términos de nuestro predicado (s) dado más literales y condiciones:

-- query result holds the rows where
FOR SOME t2.*
rental [t1.tableid] was user [t1.userid] renting car [t1.car] from [t1.from] to [t1.to]
AND rental [t2.tableid] was user [t2.userid] renting car [t2.car] from [t2.from] to [t2.to]
AND [t1.userid] <> [t2.userid] -- userids id different users
AND [t1.car] = [t2.car] -- .cars id the same car
AND [t1.to] >= [t2.from] AND [t2.to] >= [t1.from] -- tos/froms id intervals with any overlap
AND [t1.tableid] <> [t2.tableid] -- tableids id different rentals

La UNION de consultas para los predicados 1 y 2 devuelve las filas para las cuales predicate 1 OR predicate 2 .

Intente aprender a expresar predicados, qué filas indican cuándo en las tablas, si solo como el objetivo para la (sub) consulta intuitiva.

PD: es bueno tener siempre un control de datos en casos de borde y sin borde para que una condición sea verdadera y falsa. Por ejemplo, intente la consulta 1 con GTR a partir del 31, una superposición de solo un día, lo que no debería ser un conflicto autónomo.

La consulta de PPS que involucra filas duplicadas, como con NULL, tiene significados de consulta bastante complejos. Es difícil decir cuándo una tupla entra o se queda fuera de una mesa y cuántas veces. Para que las consultas tengan los significados intuitivos simples por mis correspondencias, no pueden tener duplicados. Aquí SQL desafortunadamente difiere del modelo relacional. En la práctica, las personas confían en las expresiones idiomáticas cuando permiten filas no diferenciadas y dependen de que las filas sean distintas debido a las limitaciones. Por ejemplo, unirse a columnas ÚNICAS por UNIQUE, PK y FK. Por ejemplo: un paso DISTINCT final solo funciona en un momento diferente al de una versión que no lo necesita; el tiempo puede o no ser un problema de implementación importante que afecte el fraseo elegido para un predicado / resultado dado.