¡Hola, Radio SQL está en el aire otra vez! Amasar los ganglios, extender pseudopodios (¿o viceversa?) ¡Y sintonizar nuestra onda gravitacional!
La última vez, estuve casi condenado al ostracismo por analizar (
https://habr.com/en/post/359064/ ) la tarea Olympiad SQL, supuestamente no estaba lo suficientemente cerca de la vida. Como si las etiquetas "programación anormal" y "Juegos Olímpicos" no hablen por sí mismas. ¡Pero obviamente nadie lee las etiquetas! Sin embargo, continuaré con el tema de analizar las tareas en el maravilloso lenguaje de programación SQL. Porque las patas (picazón).
Hoy nos enfrentamos a una tarea que pone en peligro la vida exclusivamente, e incluso una práctica. Me encontré con él, tratando de calcular el rendimiento del SLA a pedido de usuarios amantes. La esencia del problema inicial es la siguiente: era necesario calcular la duración del trabajo para cada aplicación y compararla con lo que prometimos. Todo estaría bien, pero el tiempo en las obligaciones se declaró funcionando, y de los cambios de estado en las aplicaciones solo pude obtener el calendario. Y aquí hay un pensamiento! ¡Aquí está ella, tarea! No es demasiado complicado, pero tampoco del todo trivial. ¡Solo para estirar las partes centrales de sus sistemas nerviosos autónomos, haciéndolos más comprensivos!
Entonces, declaro la condición.
Hay varios intervalos de tiempo especificados por la fecha y hora de su inicio y finalización (un ejemplo en la sintaxis de PostgreSQL):
with periods(id, start_time, stop_time) as ( values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp), (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp) )
Se requiere en una consulta SQL (c) para calcular la duración de cada intervalo en horas de trabajo. Creemos que trabajamos de lunes a viernes de lunes a viernes, las horas de trabajo son siempre de 10:00 a 19:00. Además, de acuerdo con el calendario de producción de la Federación de Rusia, hay varios días festivos oficiales que no son días hábiles, y algunos de los días libres, por el contrario, son días hábiles debido al aplazamiento de esos mismos días festivos. No es necesario acortar los días previos a las vacaciones, los consideramos completos. Dado que las vacaciones varían de un año a otro, es decir, se establecen mediante una lista explícita, nos limitaremos a fechas solo de 2018 y 2019. Estoy seguro de que, si es necesario, la solución se puede complementar fácilmente.
Es necesario agregar una columna con la duración en horas de trabajo a los períodos iniciales de los
períodos . Aquí está el resultado:
id | start_time | stop_time | work_hrs
No verificamos que los datos iniciales sean correctos; siempre consideramos
start_time <= stop_time .
Las construcciones especiales de dialecto SQL de PostgreSQL se pueden usar, pero no se deben abusar de ellas. Para una corrección completa de las condiciones, agrego que la consulta debe ejecutarse en PostgreSQL versión 10 o posterior.
En un mes habrá un análisis de la tarea. No tomo ninguna decisión para que haya un incentivo para resolver por mi cuenta. Solicitamos amablemente: coloque el código en los comentarios debajo de los spoilers.
Por último pero no menos importante . Si ya he logrado publicar este artículo en el blog corporativo de Postgres Professional, utilizaremos algunas ventajas corporativas: para la solución más interesante a este problema, haremos un viaje gratis a
PGConf.Russia 2020 . Los criterios de interés serán personalmente míos, más los de los colegas con quienes considero necesario consultar. Buena suerte
ACTUALIZAR! Algo que veo que las horas de trabajo se perciben por alguna razón exclusivamente sin minutos de trabajo. ¡No olvides los minutos! Ajusté los intervalos iniciales y la respuesta esperada para enfatizar la disponibilidad de minutos.
Resumen de
ACTUALIZACIÓN2 :
https://habr.com/en/company/postgrespro/blog/457722/ .