SQL: la tâche du temps de travail

Bonjour, Radio SQL est à nouveau diffusée! Pétrir les ganglions, propager des pseudopodes (ou vice versa?) Et syntoniser notre onde gravitationnelle!


La dernière fois, j'ai été presque ostracisé pour analyser ( https://habr.com/en/post/359064/ ) le problème de l'Olympiade SQL, soi-disant il n'était pas assez proche de la vie. Comme si les balises "programmation anormale" et "Jeux olympiques" ne parlaient pas d'elles-mêmes. Mais évidemment, personne ne lit les balises! Néanmoins, je continuerai le sujet de l'analyse des tâches dans le merveilleux langage de programmation SQL. Parce que les pattes (démangeaisons).

Aujourd'hui, nous sommes confrontés à une tâche exclusivement mortelle, voire pratique. Je suis tombé dessus, essayant de calculer les performances du SLA à la demande d'utilisateurs aimants. L'essence du problème initial est la suivante: il fallait calculer la durée du travail pour chaque candidature et comparer avec ce que nous avions promis. Tout irait bien, mais le temps dans les obligations a été déclaré fonctionnel, et à partir des changements de statut dans les applications, je n'ai pu obtenir que le calendrier. Et voici une pensée! La voici, tâche! Pas trop compliqué, mais pas tout à fait banal non plus. Juste pour étirer les parties centrales de vos systèmes nerveux autonomes, les rendant plus sympathiques!

Donc, j'énonce la condition.

Il y a plusieurs intervalles de temps spécifiés par la date-heure de son début et de sa fin (un exemple dans la syntaxe 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) ) 

Il est requis dans une requête SQL (c) pour calculer la durée de chaque intervalle en heures de travail. Nous pensons que nous travaillons en semaine du lundi au vendredi, les heures de travail sont toujours de 10h00 à 19h00. En outre, conformément au calendrier de production de la Fédération de Russie, il existe un certain nombre de jours fériés officiels qui ne sont pas des jours ouvrables, et certains jours de congé, au contraire, sont des jours ouvrables en raison du transfert de ces mêmes jours fériés. Le raccourcissement des jours de pré-vacances n'est pas nécessaire, nous les considérons comme complets. Étant donné que les vacances varient d'une année à l'autre, c'est-à-dire qu'elles sont fixées par une liste explicite, nous nous limiterons aux dates uniquement de 2018 et 2019. Je suis sûr que, si nécessaire, la solution peut être facilement complétée.

Il est nécessaire d'ajouter une colonne avec la durée en heures de travail aux périodes initiales des périodes . Voici le résultat:

  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 

Nous ne vérifions pas l'exactitude des données initiales; nous considérons toujours start_time <= stop_time .

Les constructions spéciales de dialecte SQL de PostgreSQL peuvent être utilisées, mais pas abusées. Pour une exactitude complète des conditions, j'ajoute que la requête doit être exécutée sur PostgreSQL version 10 ou ultérieure.

Dans un mois, il y aura une analyse de la tâche. Je ne prends aucune décision de sorte qu'il y ait une incitation à résoudre par moi-même. Nous vous prions de bien vouloir - placer le code dans les commentaires sous les spoilers!

Dernier point mais non le moindre . Si j'ai déjà réussi à publier cet article sur le blog d'entreprise de Postgres Professional, nous utiliserons des goodies d'entreprise: pour la solution la plus intéressante à ce problème, nous jouerons un voyage gratuit à PGConf.Russia 2020 . Les critères d'intérêt seront personnellement les miens, ainsi que ceux des collègues avec lesquels j'estime nécessaire de consulter. Bonne chance

MISE À JOUR! Quelque chose que je vois que les heures de travail sont perçues pour une raison exclusivement sans minutes de travail. N'oubliez pas les minutes! J'ai ajusté les intervalles initiaux et la réponse attendue pour souligner la disponibilité des minutes.

Résumé UPDATE2 : https://habr.com/en/company/postgrespro/blog/457722/ .