¡Hola, Radio SQL está en el aire otra vez! Hoy tenemos una solución al problema que transmitimos en nuestra transmisión anterior, y prometimos hacerlo la próxima vez. Y la próxima vez ha llegado.
La tarea despertó una respuesta animada entre los humanoides de la galaxia de la Vía Láctea (y no es sorprendente, con su esclavitud laboral, que todavía respetan en beneficio de la civilización). Desafortunadamente, en el tercer planeta, el lanzamiento del observatorio espacial Spektr-RG se pospuso a fines de julio de 2019, el RC (cronología local), con la ayuda de la cual se planeó transmitir este programa. Tuve que buscar rutas de transmisión alternativas, lo que provocó un ligero retraso en la señal. Pero todo está bien, eso termina bien.
Debo decir de inmediato que no habrá magia en el análisis de la tarea, no hay necesidad de buscar revelaciones aquí o esperar una implementación particularmente efectiva (o especialmente alguna en cualquier otro sentido). Esta es solo una tarea de análisis. En él, aquellos que no saben cómo abordar la solución de tales problemas podrán ver cómo resolverlos. Además, no hay nada terrible aquí.
Déjame recordarte 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 un listado explícito, 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 .
Al final de la condición, el original está aquí: https://habr.com/en/company/postgrespro/blog/448368/ .
La tarea da un ligero picante al hecho de que conscientemente he dado una buena mitad de la condición en forma descriptiva (como suele ser el caso en la vida real), dejando a la discreción de la implementación técnica cómo se debe establecer el horario de trabajo. Por un lado, esto requiere algunas habilidades de pensamiento arquitectónico. Y, por otro lado, el formato listo para usar de esta programación habría provocado el uso de alguna plantilla. Y si omites, entonces el pensamiento y la fantasía funcionarán más plenamente. La recepción valió la pena por completo, permitiéndome también encontrar enfoques interesantes en las soluciones publicadas.
Entonces, para resolver el problema original de esta manera, será necesario resolver dos subtareas:
- Determine cómo establecer de manera más compacta un horario de trabajo, y aun así, es conveniente usarlo como solución.
- Realmente calcule la duración de cada período fuente en horas de trabajo de acuerdo con el cronograma de trabajo de la subtarea anterior.
Y es mejor comenzar con el segundo, para comprender de qué forma necesitamos resolver el primero. Luego resuelva el primero y regrese nuevamente al segundo para obtener el resultado final.
Recopilaremos el resultado gradualmente, utilizando la sintaxis CTE, que nos permite poner todas las muestras de datos necesarias en subconsultas con nombre separadas y luego vincular todo.
Pues vamos.
Calcule la duración en horas de trabajo
Para calcular la duración de cada uno de los períodos en horas de trabajo en la frente, debe cruzar el período inicial (color verde en el diagrama) con los intervalos que describen el tiempo de trabajo (naranja). Los intervalos de horas de trabajo son los lunes de 10:00 a 19:00, los martes de 10:00 a 19:00 y así sucesivamente. El resultado se muestra en azul:
Por cierto, para confundirme menos, continuaré refiriéndome a los períodos iniciales como períodos iniciales, y llamaré a intervalos de horas de trabajo.
El procedimiento debe repetirse para cada período inicial. Los períodos iniciales para nosotros ya están establecidos en la pestaña de períodos (start_time, stop_time) , representaremos las horas de trabajo en forma de tabla, por ejemplo, horario (strat_time, stop_time) , donde cada día laborable está presente. El resultado es un producto cartesiano completo de todos los períodos e intervalos iniciales de tiempo de trabajo.
Las intersecciones se pueden contar de la manera clásica, teniendo en cuenta todas las opciones posibles para los intervalos de intersección: intersectamos el verde con el naranja, el resultado es azul:
y tomando en cada caso el valor deseado para el comienzo y el final del resultado:
select s.start_time, s.stop_time
Dado que para cada intersección solo podemos tener una de cuatro opciones, todas ellas se combinan en una sola solicitud utilizando union all .
Puede hacer lo contrario utilizando el tipo de rango tsrange disponible en PostgreSQL y la operación de intersección ya disponible para ello:
select tsrange(s.start_time, s.stop_time) * tsrange(s.start_time, s.stop_time) from periods p, schedule s
De acuerdo en eso, uh, un poco más fácil. En general, hay bastantes cosas pequeñas tan convenientes en PostgreSQL, por lo que escribir consultas sobre él es muy bueno.
Generar calendario
Ahora volvamos a la subtarea con el horario de horas de trabajo.
Necesitamos obtener el horario de trabajo en forma de intervalos de tiempo de trabajo de 10:00 a 19:00 para cada día laboral, algo así como el horario (hora de inicio, hora de finalización) . Como entendimos, será conveniente resolver nuestro problema. En la vida real, dicho calendario debe establecerse en una tabla, durante dos años solo son unos 500 registros, para fines prácticos será necesario establecer incluso diez años: esto es un par y medio mil registros, basura real para bases de datos modernas. Pero tenemos un problema que se resolverá en una solicitud, y enumerar toda la tabla no es muy práctico. Intentemos implementarlo de manera más compacta.
En cualquier caso, necesitamos días festivos para eliminarlos del horario base, y aquí solo la lista es adecuada:
dates_exclude(d) as ( values('2018-01-01'::date),
y días hábiles adicionales para agregar:
dates_include(d) as ( values
La secuencia de días hábiles durante dos años puede generarse mediante una función especial y muy adecuada generate_series () , que arroja inmediatamente sábados y domingos en el camino:
select d from generate_series( '2018-01-01'::timestamp , '2020-01-01'::timestamp , '1 day'::interval ) as d where extract(dow from d) not in (0,6)
Obtenemos los días hábiles conectando todo juntos: generamos una secuencia de todos los días hábiles en dos años, agregamos días hábiles adicionales de date_include y eliminamos todos los días adicionales de date_exclude :
schedule_base as ( select d from generate_series( '2018-01-01'::timestamp , '2020-01-01'::timestamp , '1 day'::interval ) as d where extract(dow from d) not in (0,6)
Y ahora tenemos los intervalos de tiempo que necesitamos:
schedule(start_time, stop_time) as ( select d + '10:00:00'::time, d + '19:00:00'::time from schedule_base )
Entonces, tenemos el horario.
Poniendo todo junto
Ahora obtendremos las intersecciones:
select p.* , tsrange(p.start_time, p.stop_time) * tsrange(s.start_time, s.stop_time) as wrkh from periods p join schedule s on tsrange(p.start_time, p.stop_time) && tsrange(s.start_time, s.stop_time)
Preste atención a la condición de unión ON , no coincide con dos registros correspondientes de las tablas unidas, no existe tal correspondencia, pero se introduce alguna optimización que corta los intervalos de tiempo de trabajo con los que nuestro período inicial no se cruza. Esto se hace usando el operador && , que verifica la intersección de los intervalos de rango . Esto elimina muchas intersecciones vacías para no interferir con los ojos, pero, por otro lado, elimina la información sobre esos períodos iniciales que caen completamente fuera de las horas. Entonces, admiramos que nuestro enfoque funcione y reescribimos la solicitud de la siguiente manera:
periods_wrk as ( select p.* , tsrange(p.start_time, p.stop_time) * tsrange(s.start_time, s.stop_time) as wrkh from periods p , schedule s ) select id, start_time, stop_time , sum(upper(wrkh)-lower(wrkh)) from periods_wrk group by id, start_time, stop_time
En los períodos_wrk descomponemos cada período de origen en intervalos de trabajo, y luego consideramos su duración total. El resultado fue un producto cartesiano completo de todos los períodos e intervalos, pero no se perdió un solo período.
Todo, el resultado es recibido. No me gustaron los valores NULL para intervalos vacíos, deje que la consulta muestre un intervalo de longitud cero mejor. Envuelva la cantidad en fusión () :
select id, start_time, stop_time , coalesce(sum(upper(wrkh)-lower(wrkh)), '0 sec'::interval) from periods_wrk group by id, start_time, stop_time
Todos juntos dan el resultado final:
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:00:00'::timestamp) , (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp) ), dates_exclude(d) as ( values('2018-01-01'::date),
¡Hurra! .. Esto podría estar terminado, pero en aras de la exhaustividad consideraremos algunos temas más relacionados.
Mayor desarrollo del tema.
Días más cortos antes de las vacaciones, descansos para almorzar, diferentes horarios para diferentes días de la semana ... En principio, todo está claro, debe fijar la definición de horario , solo dé un par de ejemplos.
Así es como puede establecer diferentes horas de inicio y finalización para un día hábil, según el día de la semana:
select d + case extract(dow from d) when 1 then '10:00:00'::time
Si necesita tener en cuenta las pausas para el almuerzo de 13:00 a 14:00, en lugar de un intervalo por día, haga dos:
select d + '10:00:00'::time , d + '13:00:00'::time from schedule_base union all select d + '14:00:00'::time , d + '19:00:00'::time from schedule_base
Bueno y así sucesivamente.
Rendimiento
Diré algunas palabras sobre el rendimiento, ya que siempre hay preguntas al respecto. No voy a masticar mucho, esta es una sección con un asterisco.
En general, la optimización prematura es malvada. Según mis muchos años de observación, la legibilidad del código es su ventaja más importante. Si el código se lee bien, entonces es más fácil de mantener y desarrollar. El código legible requiere implícitamente una buena arquitectura de solución, comentarios adecuados y buenos nombres de variables, compacidad sin sacrificar la legibilidad, etc., es decir, todo para lo que se llama el código.
Por lo tanto, la solicitud siempre se escribe de la manera más legible posible, y comenzamos a optimizar si y solo si resulta que el rendimiento es insuficiente. Además, lo optimizaremos con precisión cuando el rendimiento sea insuficiente y exactamente en la medida en que sea suficiente. Si ciertamente valoras tu propio tiempo y tienes algo que hacer.
Pero no es correcto realizar un trabajo innecesario en la solicitud; siempre debe intentar tener esto en cuenta.
En base a esto, incluiremos una optimización en la consulta de inmediato: deje que cada período de origen se entrecruce solo con aquellos intervalos de tiempo de trabajo con los que tiene puntos comunes (en lugar de una condición clásica larga en los límites del rango, es más conveniente usar el operador && incorporado para el tipo de rango ). Esta optimización ya apareció en la solicitud, pero ha llevado al hecho de que los períodos iniciales que cayeron por completo de las horas de trabajo desaparecieron de los resultados.
Traiga de vuelta esta optimización. Para hacer esto, use LEFT JOIN , que guardará todos los registros de la tabla de períodos . Ahora la subconsulta period_wrk se verá así:
, periods_wrk as ( select p.* , tsrange(p.start_time, p.stop_time) * tsrange(s.start_time, s.stop_time) as wrkh from periods p left join schedule s on tsrange(p.start_time, p.stop_time) && tsrange(s.start_time, s.stop_time))
El análisis de la solicitud muestra que el tiempo en los datos de prueba ha disminuido aproximadamente a la mitad. Dado que el tiempo de ejecución depende de lo que el servidor estaba haciendo al mismo tiempo, tomé algunas medidas y di un resultado "típico", no el más grande, ni el más pequeño, del medio.
Consulta anterior:
explain (analyse) with periods(id, start_time, stop_time) as ( ... QUERY PLAN
Nuevo:
explain (analyse) with periods(id, start_time, stop_time) as ( ... QUERY PLAN
Pero lo más importante es que dicha solicitud también se escalará mejor, lo que requiere menos recursos del servidor, ya que el producto cartesiano completo crece muy rápidamente.
Y en esto me detendría con optimizaciones. Cuando resolví este problema por mí mismo, tuve suficiente rendimiento incluso en una forma mucho más terrible de esta solicitud, pero realmente no había necesidad de optimizar. Para obtener un informe sobre mis datos una vez por trimestre, puedo esperar diez segundos adicionales. La hora extra que se dedica a la optimización en tales condiciones nunca valdrá la pena.
Pero resulta poco interesante; sigamos pensando en cómo podrían desarrollarse los eventos si realmente se necesitara una optimización en términos de tiempo de ejecución. Por ejemplo, queremos monitorear este parámetro en tiempo real para cada uno de nuestros registros en la base de datos, es decir, para cada estornudo se llamará a dicha solicitud. Bueno, o invente su propia razón, ¿por qué necesitaría optimizar?
Lo primero que viene a la mente es contar una vez y poner en la base de datos una tabla con intervalos de trabajo. Puede haber contraindicaciones: si la base de datos no se puede cambiar, o se esperan dificultades con el apoyo de los datos relevantes en dicha tabla. Luego tendrá que dejar la generación de tiempo de trabajo "sobre la marcha" en la solicitud, ya que esta no es una subconsulta muy pesada.
El siguiente y más poderoso enfoque (pero no siempre aplicable) es la optimización algorítmica. Algunos de estos enfoques ya se han presentado en los comentarios al artículo con la condición del problema.
Este me gusta más que nada. Si hace una tabla con todos los días (no solo de trabajo) del calendario y calcula el total acumulado de cuántas horas de trabajo cada día de una determinada "creación del mundo" han pasado, entonces puede obtener el número de horas de trabajo entre dos fechas con una operación de resta. Solo queda tener en cuenta correctamente las horas de trabajo para el primer y último día, y ya está. Esto es lo que obtuve en este enfoque:
schedule_base(d, is_working) as ( select '2018-01-01'::date, 0 union all select d+1, case when extract(dow from d+1) not in (0,6) and d+1 <> all('{2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-07,2019-01-08,2019-03-08,2019-05-01,2019-05-02,2019-05-03,2019-05-09,2019-05-10,2019-06-12,2019-11-04,2018-01-01,2018-01-02,2018-01-03,2018-01-04,2018-01-05,2018-01-08,2018-02-23,2018-03-08,2018-03-09,2018-04-30,2018-05-01,2018-05-02,2018-05-09,2018-06-11,2018-06-12,2018-11-05,2018-12-31}') or d+1 = any('{2018-04-28,2018-06-09,2018-12-29}') then 1 else 0 end from schedule_base where d < '2020-01-01' ), schedule(d, is_working, work_hours) as ( select d, is_working , sum(is_working*'9 hours'::interval) over (order by d range between unbounded preceding and current row) from schedule_base ) select p.* , s2.work_hours - s1.work_hours + ('19:00:00'::time - least(greatest(p.start_time::time, '10:00:00'::time), '19:00:00'::time)) * s1.is_working - ('19:00:00'::time - least(greatest(p.stop_time::time, '10:00:00'::time), '19:00:00'::time)) * s2.is_working as wrk from periods p, schedule s1, schedule s2 where s1.d = p.start_time::date and s2.d = p.stop_time::date
Explicaré brevemente lo que está sucediendo aquí. En la subconsulta schedule_base , generamos todos los días del calendario durante dos años y cada día determinamos el signo si el día hábil (= 1) o no (= 0). Además, en la subconsulta de programación , consideramos la función de ventana como el número total acumulado de horas de trabajo desde 2018-01-01. Sería posible hacer todo en una subconsulta, pero resultaría más engorroso, lo que perjudicaría la legibilidad. Luego, en la solicitud principal, consideramos la diferencia entre el número de horas de trabajo al final y al comienzo del período y, de manera algo florida, tomamos en cuenta las horas de trabajo para el primer y último día del período. La floridez se asocia con el cambio del tiempo antes del comienzo de la jornada laboral a su comienzo, y el tiempo posterior al final de la jornada laboral a su finalización. Además, si la parte de la solicitud con shedule_base y horario se elimina en una tabla precalculada separada (como se sugirió anteriormente), la solicitud se convertirá en una completamente trivial.
Comparemos la ejecución en una muestra más grande para mostrar mejor la optimización realizada, durante cuatro períodos desde la condición de la tarea se dedica más tiempo a generar un cronograma de trabajo.
Tomé alrededor de 3 mil períodos. Daré solo la línea de resumen superior en EXPLAIN, los valores típicos son los siguientes.
Opción original:
GroupAggregate (cost=265790.95..296098.23 rows=144320 width=36) (actual time=656.654..894.383 rows=2898 loops=1) ...
Optimizado:
Hash Join (cost=45.01..127.52 rows=70 width=36) (actual time=1.620..5.385 rows=2898 loops=1) ...
La ganancia de tiempo fue un par de órdenes de magnitud. Con el aumento en el número de períodos y su duración en años, la brecha solo se ampliará.
Todo parecía estar bien, pero ¿por qué, habiendo hecho tal optimización, dejé la primera versión de la solicitud para mí hasta que su rendimiento fue suficiente? Sí, porque la versión optimizada es indudablemente más rápida, pero requiere mucho más tiempo para entender cómo funciona, es decir, la legibilidad ha empeorado. Es decir, la próxima vez que necesite reescribir la solicitud en mis condiciones cambiadas, yo (o no yo) tendré que dedicar mucho más tiempo a comprender cómo funciona la solicitud.
Eso es todo por hoy, mantén los tentáculos calientes y te digo adiós hasta la próxima versión de Radio SQL.