SQL: solution de tâche de temps de travail

Bonjour, Radio SQL est à nouveau diffusée! Aujourd'hui, nous avons une solution au problème que nous avions transmis lors de notre précédente émission et que nous avions promis de distinguer la prochaine fois. Et cette prochaine fois est venue.

La tâche a suscité une réponse vive parmi les humanoïdes de la galaxie de la Voie lactée (et sans surprise, avec leur esclavage de travail, qu'ils respectent toujours au profit de la civilisation). Malheureusement, sur la troisième planète, le lancement de l'observatoire spatial Spektr-RG a été reporté fin juillet 2019, la RC (chronologie locale), à ​​l'aide de laquelle il était prévu de diffuser cette émission. J'ai dû chercher des voies de transmission alternatives, ce qui a entraîné un léger retard dans le signal. Mais tout va bien qui se termine bien.

Je dois dire tout de suite qu'il n'y aura pas de magie dans l'analyse de la tâche, il n'est pas nécessaire de chercher des révélations ici ou d'attendre une mise en œuvre particulièrement efficace (ou surtout certaines dans un autre sens). Ce n'est qu'une tâche d'analyse. Ceux qui ne savent pas comment aborder la solution de ces problèmes pourront voir comment les résoudre. De plus, il n'y a rien de terrible ici.

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) ) 

  id | start_time | stop_time | work_hrs ----+---------------------+---------------------+---------- 1 | 2019-03-29 07:00:00 | 2019-04-08 14:00:00 | 58:00:00 2 | 2019-04-10 07:00:00 | 2019-04-10 20:00:00 | 09:00:00 3 | 2019-04-11 12:00:00 | 2019-04-12 16:07:12 | 13:07:12 4 | 2018-12-28 12:00:00 | 2019-01-16 16:00:00 | 67:00:00 

La tâche donne un léger piquant au fait que j'ai donné consciemment une bonne moitié de la condition sous une forme descriptive (comme c'est généralement le cas dans la vie réelle), laissant à la discrétion de la mise en œuvre technique la façon dont le calendrier de travail doit être fixé. D'une part, cela nécessite certaines compétences de réflexion architecturale. Et d'autre part, le format prêt à l'emploi de ce calendrier aurait incité une certaine utilisation de modèle de celui-ci. Et si vous omettez, alors la pensée et la fantaisie fonctionneront plus pleinement. L'accueil a porté ses fruits, ce qui m'a permis de trouver également des approches intéressantes dans les solutions publiées.

Ainsi, pour résoudre le problème d'origine de cette manière, deux sous-tâches devront être résolues:

1. Déterminez comment définir le plus rapidement possible un horaire de travail, et même de manière à ce qu'il soit pratique à utiliser pour une solution.
2. Calculez réellement la durée de chaque période source en heures de travail en fonction de l'horaire de travail de la sous-tâche précédente.

Et il vaut mieux commencer par le second, afin de comprendre sous quelle forme il faut résoudre le premier. Résolvez ensuite le premier et revenez de nouveau au second afin d'obtenir le résultat final.
Nous collecterons le résultat progressivement, en utilisant la syntaxe CTE, qui nous permet de mettre tous les échantillons de données nécessaires dans des sous-requêtes nommées distinctes, puis de tout lier ensemble.

Eh bien, allons-y.

Calculez la durée en heures de travail

Pour calculer la durée de chacune des périodes en heures de travail sur le front, vous devez croiser la période initiale (couleur verte sur le diagramme) avec les intervalles qui décrivent le temps de travail (orange). Les intervalles d'heures de travail sont les lundis de 10h00 à 19h00, les mardis de 10h00 à 19h00 et ainsi de suite. Le résultat est affiché en bleu:

Soit dit en passant, afin de devenir moins confus, je continuerai à appeler les périodes initiales des périodes initiales, et j'appellerai des intervalles d'heures de travail.

La procédure doit être répétée pour chaque période initiale. Les périodes initiales pour nous sont déjà définies dans l' onglet périodes (start_time, stop_time) , nous représenterons les heures de travail sous la forme d'un tableau, disons, horaire (strat_time, stop_time) , où chaque jour ouvrable est présent. Le résultat est un produit cartésien complet de toutes les périodes et intervalles initiaux du temps de travail.

Les intersections peuvent être comptées de manière classique, après avoir considéré toutes les options possibles pour les intervalles d'intersection - nous intersectons le vert avec l'orange, le résultat est le bleu:

et en prenant dans chaque cas la valeur souhaitée pour le début et la fin du résultat:

  select s.start_time, s.stop_time -- case #1 from periods p, schedule s where p.start_time <= s.start_time and p.stop_time > s.stop_time union all select p.start_time, s.stop_time -- case #2 from periods p, schedule s where p.start_time >= s.start_time and p.stop_time > s.stop_time and p.start_time < s.stop_time union all select s.start_time, p.stop_time -- case #3 from periods p, schedule s where p.start_time <= s.start_time and p.stop_time < s.stop_time and p.stop_time > s.start_time union all select p.start_time, p.stop_time -- case #4 from periods p, schedule s where p.start_time >= s.start_time and p.stop_time < s.stop_time 

Étant donné que pour chaque intersection, nous ne pouvons avoir qu'une seule des quatre options, toutes sont combinées en une seule demande en utilisant union all .

Vous pouvez faire autrement en utilisant le type de plage tsrange disponible dans PostgreSQL et l'opération d'intersection déjà disponible pour lui:

  select tsrange(s.start_time, s.stop_time) * tsrange(s.start_time, s.stop_time) from periods p, schedule s 

D'accord, alors - euh - un peu plus facile. En général, il y a beaucoup de petites choses aussi pratiques dans PostgreSQL, donc écrire des requêtes dessus est très agréable.

Générer un calendrier

Revenons maintenant à la sous-tâche avec le calendrier des heures de travail.

Nous devons obtenir l'horaire de travail sous forme d'intervalles de temps de travail de 10h00 à 19h00 pour chaque jour ouvrable, quelque chose comme l' horaire (start_time, stop_time) . Comme nous l'avons compris, il sera commode de résoudre notre problème. Dans la vraie vie, un tel calendrier devrait être établi, pendant deux ans, il ne s'agit que d'environ 500 enregistrements, pour des raisons pratiques, il sera nécessaire de définir même dix ans - c'est un couple et demi mille enregistrements, de vrais déchets pour les bases de données modernes. Mais nous avons un problème qui sera résolu en une seule demande, et lister l'ensemble de ce tableau n'est pas très pratique. Essayons de l'implémenter de manière plus compacte.

Dans tous les cas, nous avons besoin de jours fériés pour les supprimer de l'horaire de base, et ici seule la liste convient:

  dates_exclude(d) as ( values('2018-01-01'::date), -- 2018 ('2018-01-02'::date), ('2018-01-03'::date), ('2018-01-04'::date), ('2018-01-05'::date), ('2018-01-08'::date), ('2018-02-23'::date), ('2018-03-08'::date), ('2018-03-09'::date), ('2018-05-01'::date), ('2018-05-02'::date), ('2018-05-09'::date), ('2018-06-11'::date), ('2018-06-12'::date), ('2018-11-05'::date), ('2018-12-31'::date), ('2019-01-01'::date), -- 2019 ('2019-01-02'::date), ('2019-01-03'::date), ('2019-01-04'::date), ('2019-01-07'::date), ('2019-01-08'::date), ('2019-03-08'::date), ('2019-05-01'::date), ('2019-05-02'::date), ('2019-05-03'::date), ('2019-05-09'::date), ('2019-05-10'::date), ('2019-06-12'::date), ('2019-11-04'::date) ) 

et jours ouvrables supplémentaires à ajouter:

  dates_include(d) as ( values --  2018,  2019  ('2018-04-28'::date), ('2018-06-09'::date), ('2018-12-29'::date) ) 

La séquence de jours ouvrables pendant deux ans peut être générée par une fonction spéciale et très appropriée generate_series () , lançant immédiatement les samedis et dimanches en cours de route:

  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) --     

Nous obtenons les jours ouvrables en connectant tout ensemble: nous générons une séquence de tous les jours ouvrables sur deux ans, ajoutons des jours ouvrables supplémentaires à dates_include et supprimons tous les jours supplémentaires à dates_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) --     union select d from dates_include --     except select d from dates_exclude --     ) 

Et maintenant, nous obtenons les intervalles de temps dont nous avons besoin:

  schedule(start_time, stop_time) as ( select d + '10:00:00'::time, d + '19:00:00'::time from schedule_base ) 

Donc, nous avons le calendrier.

Tout mettre ensemble

Nous allons maintenant obtenir les intersections:

  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) 

Faites attention à la condition de jointure ON , elle ne correspond pas à deux enregistrements correspondants des tables jointes, il n'y a pas une telle correspondance, mais une optimisation est introduite qui coupe les intervalles de temps de travail avec lesquels notre période initiale ne se coupe pas. Cela se fait à l'aide de l'opérateur && , qui vérifie l'intersection des intervalles tsrange . Cela supprime beaucoup d'intersections vides afin de ne pas gêner les yeux, mais, d'autre part, supprime les informations sur les périodes initiales qui tombent entièrement en dehors des heures. Nous admirons donc que notre approche fonctionne et réécrivons la demande comme suit:

  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 

Dans périodes_wrk, nous décomposons chaque période source en intervalles de travail, puis nous considérons leur durée totale. Le résultat a été un produit cartésien complet de toutes les périodes et intervalles, mais pas une seule période n'a été perdue.

Tout, le résultat est reçu. Je n'aimais pas les valeurs NULL pour les intervalles vides, laissez la requête afficher un intervalle de longueur nulle mieux. Enveloppez le montant en fusion () :

 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 

Tous ensemble donne le résultat 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), -- 2018 ('2018-01-02'::date), ('2018-01-03'::date), ('2018-01-04'::date), ('2018-01-05'::date), ('2018-01-08'::date), ('2018-02-23'::date), ('2018-03-08'::date), ('2018-03-09'::date), ('2018-05-01'::date), ('2018-05-02'::date), ('2018-05-09'::date), ('2018-06-11'::date), ('2018-06-12'::date), ('2018-11-05'::date), ('2018-12-31'::date), ('2019-01-01'::date), -- 2019 ('2019-01-02'::date), ('2019-01-03'::date), ('2019-01-04'::date), ('2019-01-07'::date), ('2019-01-08'::date), ('2019-03-08'::date), ('2019-05-01'::date), ('2019-05-02'::date), ('2019-05-03'::date), ('2019-05-09'::date), ('2019-05-10'::date), ('2019-06-12'::date), ('2019-11-04'::date) ), dates_include(start_time, stop_time) as ( values --  2018,  2019  ('2018-04-28 10:00:00'::timestamp, '2018-04-28 19:00:00'::timestamp), ('2018-06-09 10:00:00'::timestamp, '2018-06-09 19:00:00'::timestamp), ('2018-12-29 10:00:00'::timestamp, '2018-12-29 19:00:00'::timestamp) ) ), schedule_base(start_time, stop_time) as ( select d::timestamp + '10:00:00', d::timestamp + '19:00:00' from generate_series( (select min(start_time) from periods)::date::timestamp , (select max(stop_time) from periods)::date::timestamp , '1 day'::interval ) as days(d) where extract(dow from d) not in (0,6) ), schedule as ( select * from schedule_base where start_time::date not in (select d from dates_exclude) union select * from dates_include ), 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(coalesce(upper(wrkh)-lower(wrkh), '0 sec'::interval)) from periods_wrk group by id, start_time, stop_time 

Hourra! .. Cela pourrait être terminé, mais pour être complet, nous examinerons quelques sujets plus connexes.

Poursuite du développement du sujet

Jours de pré-vacances raccourcis, pauses déjeuner, horaires différents pour différents jours de la semaine ... En principe, tout est clair, vous devez fixer la définition de l' horaire , donnez juste quelques exemples.

Voici comment définir des heures de début et de fin différentes pour une journée de travail, selon le jour de la semaine:

  select d + case extract(dow from d) when 1 then '10:00:00'::time --  when 2 then '11:00:00'::time --  when 3 then '11:00:00'::time --  --        else '10:00:00'::time end , d + case extract(dow from d) --   19   when 5 then '14:00:00'::time --  else '19:00:00'::time end from schedule_base 

Si vous devez prendre en compte les pauses déjeuner de 13h00 à 14h00, alors au lieu d'un intervalle par jour, faites deux:

  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 

Eh bien et ainsi de suite.

Performances

Je vais dire quelques mots sur les performances, car il y a toujours des questions à ce sujet. Je ne vais pas déjà mâcher beaucoup, c'est une section avec un astérisque.

En général, l'optimisation prématurée est mauvaise. D'après mes nombreuses années d'observation, la lisibilité du code est son avantage le plus important. Si le code est bien lu, il est plus facile à maintenir et à développer. Un code bien lisible nécessite implicitement à la fois une bonne architecture de solution, des commentaires appropriés et de bons noms de variables, une compacité sans sacrifier la lisibilité, etc., c'est-à-dire tout ce pour quoi le code est appelé bon.

Par conséquent, la demande est toujours écrite aussi lisible que possible, et nous commençons à l'optimiser si et seulement s'il s'avère que les performances sont insuffisantes. De plus, nous l'optimiserons précisément là où les performances sont insuffisantes et exactement dans la mesure où elles deviennent suffisantes. Si vous appréciez certainement votre temps et que vous avez quelque chose à faire.

Mais ne pas faire de travail inutile dans la demande est juste; vous devez toujours essayer de prendre cela en compte.

Sur cette base, nous inclurons immédiatement une optimisation dans la requête - laissez chaque période source ne croiser qu'avec les intervalles de temps de travail avec lesquels elle a des points communs (au lieu d'une longue condition classique sur les limites de la plage, il est plus pratique d'utiliser l'opérateur && intégré pour le type tsrange ). Cette optimisation est déjà apparue dans la demande, mais a conduit au fait que les périodes initiales totalement tombées en dehors des heures de travail ont disparu des résultats.

Ramenez cette optimisation. Pour ce faire, utilisez LEFT JOIN , qui enregistrera tous les enregistrements de la table des périodes . Maintenant, la sous-requête period_wrk ressemblera à ceci:

 , 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)) 

L'analyse de la demande montre que le temps consacré aux données de test a diminué d'environ la moitié. Étant donné que le temps d'exécution dépend de ce que le serveur faisait en même temps, j'ai pris quelques mesures et donné un résultat «typique», pas le plus grand, pas le plus petit, du milieu.

Ancienne requête:

 explain (analyse) with periods(id, start_time, stop_time) as ( ... QUERY PLAN ------------------------------------------------------------------------------------ HashAggregate (cost=334.42..338.39 rows=397 width=36) (actual time=10.724..10.731 rows=4 loops=1) ... 

Nouveau:

 explain (analyse) with periods(id, start_time, stop_time) as ( ... QUERY PLAN ------------------------------------------------------------------------------------ HashAggregate (cost=186.37..186.57 rows=20 width=36) (actual time=5.431..5.440 rows=4 loops=1) ... 

Mais le plus important est qu'une telle demande évoluera également mieux, nécessitant moins de ressources serveur, car le produit cartésien complet se développe très rapidement.

Et là-dessus, je m'arrêterais avec des optimisations. Lorsque j'ai résolu ce problème par moi-même, j'avais suffisamment de performances même dans une forme beaucoup plus terrible de cette demande, mais il n'y avait vraiment pas besoin d'optimiser. Pour obtenir un rapport sur mes données une fois par trimestre, je peux attendre dix secondes supplémentaires. L'heure supplémentaire consacrée à l'optimisation dans de telles conditions ne sera jamais payante.

Mais cela s'avère sans intérêt; réfléchissons encore à la façon dont les événements pourraient se développer si l'optimisation en termes de temps d'exécution était vraiment nécessaire. Par exemple, nous voulons surveiller ce paramètre en temps réel pour chacun de nos enregistrements dans la base de données, c'est-à-dire que pour chaque éternuement, une telle demande sera appelée. Eh bien, ou venez avec votre propre raison, pourquoi auriez-vous besoin d'optimiser.

La première chose qui me vient à l'esprit est de compter une fois et de mettre dans la base de données une table avec des intervalles de travail. Il peut y avoir des contre-indications: si la base de données ne peut pas être modifiée, ou si des difficultés sont attendues avec la prise en charge des données pertinentes dans un tel tableau. Ensuite, vous devrez laisser la génération de temps de travail «à la volée» dans la demande elle-même, car il ne s'agit pas d'une sous-requête très lourde.

L'approche suivante et la plus puissante (mais pas toujours applicable) est l'optimisation algorithmique. Certaines de ces approches ont déjà été présentées dans les commentaires de l'article avec l'état du problème.

J'aime celui-ci surtout. Si vous créez un tableau avec tous les jours (non seulement de travail) du calendrier et calculez le total cumulé du nombre d'heures de travail chaque jour d'une certaine «création du monde», vous pouvez obtenir le nombre d'heures de travail entre deux dates avec une opération de soustraction. Il ne reste plus qu'à prendre correctement en compte les heures de travail du premier et du dernier jour - et vous avez terminé. Voici ce que j'ai obtenu dans cette approche:

  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 

Je vais expliquer brièvement ce qui se passe ici. Dans la sous-requête schedule_base , nous générons tous les jours du calendrier pendant deux ans et nous déterminons chaque jour le signe si le jour ouvrable (= 1) ou non (= 0). De plus, dans la sous-requête de planification , nous considérons la fonction de fenêtre comme le nombre total cumulé d'heures de travail à partir du 2018-01-01. Il serait possible de tout faire dans une seule sous-requête, mais cela s'avérerait plus lourd, ce qui nuirait à la lisibilité. Ensuite, dans la demande principale, nous considérons la différence entre le nombre d'heures de travail à la fin et au début de la période et, quelque peu fleuri, prenons en compte les heures de travail du premier et du dernier jour de la période. La floridité est associée au décalage de l'heure avant le début de la journée de travail vers son début et de l'heure après la fin de la journée de travail à sa fin. De plus, si la partie de la demande avec shedule_base et planning est supprimée dans une table pré-calculée distincte (comme suggéré précédemment), cette demande deviendra complètement triviale.

Comparons l'exécution sur un échantillon plus grand afin de mieux montrer l'optimisation effectuée, pour quatre périodes à partir de la condition de tâche, plus de temps est consacré à la génération d'un horaire de travail.

J'ai pris environ 3 000 règles. Je ne donnerai que la ligne de résumé supérieure dans EXPLAIN, les valeurs typiques sont les suivantes.

Option d'origine:

 GroupAggregate (cost=265790.95..296098.23 rows=144320 width=36) (actual time=656.654..894.383 rows=2898 loops=1) ... 

Optimisé:

 Hash Join (cost=45.01..127.52 rows=70 width=36) (actual time=1.620..5.385 rows=2898 loops=1) ... 

Le gain de temps était de quelques ordres de grandeur. Avec l'augmentation du nombre de périodes et leur durée en années, l'écart ne fera que s'élargir.

Tout semblait aller bien, mais pourquoi, après avoir fait une telle optimisation, j'ai laissé la première version de la demande pour moi jusqu'à ce que ses performances soient suffisantes? Oui, car la version optimisée est sans aucun doute plus rapide, mais elle nécessite beaucoup plus de temps pour comprendre comment elle fonctionne, c'est-à-dire que la lisibilité s'est détériorée. Autrement dit, la prochaine fois que je devrai réécrire la demande dans mes conditions modifiées, je devrai (ou pas moi) passer beaucoup plus de temps à comprendre comment fonctionne la demande.

C'est tout pour aujourd'hui, gardez les tentacules au chaud, et je vous dis au revoir jusqu'à la prochaine version de Radio SQL.