您好，Radio SQL再次播出！ 今天，我们有一个解决方案，可以解决在以前的广播中发送的问题 ，并承诺下次解决。 而下一次来了。
这项任务在银河系的人形生物中引起了热烈的反响（毫不奇怪，他们的劳动奴役仍然尊重文明的利益）。 不幸的是，在第三颗行星上，Spektr-RG太空天文台的发射被推迟到2019年7月底，即RC（当地年表），并计划借助它广播该节目。 我不得不寻找替代的传输路径，这导致信号略有延迟。 但是一切顺利，一切顺利。
我必须马上说，在任务分析中不会有任何魔术，也不需要在这里寻找启示或等待某些特别有效的（或特别是其他任何意义上的）实施。 这只是一个解析任务。 在其中，那些不知道如何解决此类问题的人将能够看到如何解决这些问题。 而且，这里没有什么可怕的。
让我提醒您情况。它的开始和结束的日期时间指定了几个时间间隔（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) )
一个SQL查询（c）中需要计算每个时间间隔的持续时间（以工作时间为单位）。 我们认为我们在周一至周五的工作日工作，工作时间始终为10:00至19:00。 此外，根据俄罗斯联邦的生产日历，有一些法定假日不是工作日，相反，由于某些假日的推迟，某些假期是工作日。 无需缩短节假日，我们认为它们已经完成。 由于假期每年不同，也就是说，是由明确列出来设置的，因此我们将自己限制在2018年和2019年之内。 我确信，如有必要，可以轻松补充该解决方案。
必须从工期开始到初始工期在工作时间中增加一列。 结果如下：
id | start_time | stop_time | work_hrs
我们不检查初始数据的正确性；我们始终考虑start_time <= stop_time 。
条件的结尾，原始位置在这里： https : //habr.com/en/company/postgrespro/blog/448368/ 。
这项任务使我有些微不足道，因为我有意识地以描述性形式给出了很好的一半条件（通常在现实生活中会发生），而技术实施则应自行决定如何设置时间表。 一方面，这需要一些建筑思维技巧。 另一方面，此计划表的现成格式将促使它使用一些模板。 如果您省略，那么思想和幻想将更加充分地发挥作用。 招待会完全得到了回报，使我也可以在已发布的解决方案中找到有趣的方法。
因此，以这种方式解决原始问题，将需要解决两个子任务：
- 确定如何最紧凑地设置工作计划，甚至可以方便地用于解决方案。
- 实际上根据上一个子任务的工作计划，按工作时间计算每个源期间的持续时间。
最好从第二个开始，以了解我们需要以哪种形式解决第一个。 然后解决第一个问题，然后再次返回第二个问题，以获得最终结果。
我们将使用CTE语法逐步收集结果，该语法允许我们将所有必要的数据样本放入单独的命名子查询中，然后将所有内容链接在一起。
好吧，走吧。
计算工时的持续时间
要计算额头上每个工作时段的持续时间，您需要将初始时段（图上的绿色）与描述工作时间的间隔（橙色）相交。 工作时间间隔为星期一的10:00至19:00，星期二的10:00至19:00，依此类推。 结果显示为蓝色：
顺便说一句，为了减少混乱，我将继续将初始时间段称为初始时间段，并称为工作时间间隔。
在每个初始阶段都应重复该过程。 我们的初始时间段已在“ 时间段”选项卡（start_time，stop_time）中设置 ，我们将以表格的形式表示工作时间，例如时间表（strat_time，stop_time） ，其中存在每个工作日。 结果是所有初始时间段和工作时间间隔的完整笛卡尔积。
考虑到相交区间的所有可能选项后，可以采用经典方式对相交进行计数-我们将绿色与橙色相交，结果为蓝色：
并在每种情况下为结果的开始和结束取所需的值：
select s.start_time, s.stop_time
因为对于每个路口，我们只能有四个选项之一，所以使用union all将所有选项组合为一个请求。
您可以通过使用PostgreSQL中可用的tsrange范围类型以及已经可用的相交操作来进行其他操作：
select tsrange(s.start_time, s.stop_time) * tsrange(s.start_time, s.stop_time) from periods p, schedule s
如此同意-嗯-容易一点。 通常，PostgreSQL中有很多这样方便的小东西，因此在上面编写查询非常好。
产生日历
现在返回带有工作时间安排的子任务。
我们需要以每个工作日从10:00到19:00的工作时间间隔的形式获取工作时间表，例如schedule（start_time，stop_time） 。 据我们了解，解决我们的问题将很方便。 在现实生活中，这样的时间表应该设置表，两年中只有大约500条记录，出于实际目的，甚至需要设置10年-这是几千个记录，对现代数据库而言确实是垃圾。 但是我们有一个将在一个请求中解决的问题，并且在其中列出整个此类表不是很实际。 让我们尝试更紧凑地实现它。
无论如何，我们都需要放假以将其从基本时间表中删除，这里仅列出合适的内容：
dates_exclude(d) as ( values('2018-01-01'::date),
以及要添加的其他工作日：
dates_include(d) as ( values
可以通过一个特殊且非常合适的generate_series（）函数来生成两年的工作日序列，该函数立即将周六和周日抛出：
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)
通过将所有内容连接在一起来获得工作日：我们生成两年中所有工作日的序列，从date_include添加其他工作日，并从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)
现在我们得到了所需的时间间隔：
schedule(start_time, stop_time) as ( select d + '10:00:00'::time, d + '19:00:00'::time from schedule_base )
所以，我们有了时间表。
全部放在一起
现在我们将得到交点：
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)
注意ON连接条件，它不匹配连接表中的两个对应记录，没有这样的对应关系，但是引入了一些优化措施，以切断我们的初始周期不相交的工作时间间隔。 这是使用&&运算符完成的，该运算符检查tsrange间隔的交集。 这样可以消除许多空的交叉点，以免妨碍视线，但另一方面，也可以消除有关那些完全不在工作时间内的初始时段的信息。 因此，我们很佩服我们的方法可行，并按如下所示重写请求：
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
在periods_wrk中，我们将每个源周期分解为工作间隔，然后考虑它们的总持续时间。 结果是所有周期和间隔的笛卡尔积，但没有一个周期丢失。
一切，结果被接收。 我不喜欢空间隔为NULL值，让查询更好地显示零长度间隔。 将金额合并为（） ：
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
一起得出最终结果：
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),
万岁！..可以完成，但是为了完整起见，我们将考虑一些其他相关主题。
主题的进一步发展
节假日缩短，午休时间，一周中不同日期的时间表有所不同...原则上，一切都明确了，您需要确定时间表的定义，仅举几个例子。
您可以通过以下方式为工作日设置不同的开始和结束时间，具体取决于一周中的星期几：
select d + case extract(dow from d) when 1 then '10:00:00'::time
如果您需要考虑从13:00到14:00的午休时间，则可以选择两个，而不是每天一个时间间隔：
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
等等。
性能表现
关于性能，我会说几句话，因为总是有关于它的问题。 我不会咀嚼太多，这是一个带有星号的部分。
通常，过早的优化是有害的。 根据我多年的观察，代码的可读性是其最重要的优势。 如果代码读得很好，那么维护和开发就更容易了。 可读性强的代码隐含地要求良好的解决方案体系结构，正确的注释以及良好的变量名，紧凑性而又不牺牲可读性等，也就是说，所有代码均被称为“良好”。
因此，请求始终以尽可能可读的方式编写，并且当且仅当事实证明性能不足时，我们才开始进行优化。 此外，我们将在性能不足的地方精确地对其进行优化，并在达到充分程度的范围内对其进行优化。 如果您当然珍惜自己的时间，那您就有事要做。
但是不要在请求中做不必要的工作是正确的；您应该始终尝试考虑这一点。
基于此，我们将立即在查询中包括一个优化-让每个源周期仅与具有公共点的那些工作时间间隔相交（而不是范围边界上的长期经典条件，对于tsrange类型使用内置的&&运算符更方便）。 该优化已经出现在请求中，但是导致了一个事实，即完全超出工作时间的初始时间段从结果中消失了。
重新进行优化。 为此，请使用LEFT JOIN ，它将保存Periods表中的所有记录。 现在， periods_wrk子查询将如下所示：
, 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))
对请求的分析表明，测试数据上的时间减少了大约一半。 由于运行时取决于服务器同时执行的操作，因此我进行了一些测量，并给出了一些“典型”的结果，而不是中间的最大或最小。
旧查询：
explain (analyse) with periods(id, start_time, stop_time) as ( ... QUERY PLAN
新功能：
explain (analyse) with periods(id, start_time, stop_time) as ( ... QUERY PLAN
但是最重要的是，这样的请求也将更好地扩展，需要更少的服务器资源，因为完整的笛卡尔积快速增长。
在这一点上，我将停止优化。 当我自己解决此问题时，即使以这种要求的更糟糕的形式，我也具有足够的性能，但实际上并不需要进行优化。 要每季度获取一次有关我的数据的报告，我可以再等十秒钟。 在这种情况下，在优化上花费的额外时间将永远不会得到回报。
但是事实证明这并不有趣；让我们仍然考虑一下，如果确实需要执行时间方面的优化，那么事件将如何发展。 例如，我们要为数据库中的每个记录实时监控此参数，也就是说，每次打喷嚏都会调用此请求。 好吧，或者提出您自己的理由，为什么需要进行优化。
首先想到的是一次计数，并将一个包含工作间隔的表放入数据库中。 可能有禁忌：如果无法更改数据库，或者在此类表中的相关数据支持下可能会遇到困难。 然后，您将不得不在请求本身中“实时”生成工作时间，因为这不是一个很繁重的子查询。
下一个最强大（但并非总是适用）的方法是算法优化。 其中一些方法已在问题的情况下出现在文章的注释中。
我最喜欢这个。 如果您用日历中的所有（不仅是工作日）制作一张表，并计算从某个“世界创造”中经过的每天工作时间的累计总数，那么您可以通过一次减法运算得出两个日期之间的工作时间数。 仅需要正确考虑第一天和最后一天的工作时间，您就可以完成工作。 这是我采用这种方法的结果：
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
我将简要解释这里发生的事情。 在schedule_base子查询中，我们将生成日历的两年中的所有日期，并每天通过该符号确定工作日（= 1）与否（= 0）。 此外，在schedule子查询中，我们将窗口函数视为从2018年1月1日起的累计工作小时数。 可以在一个子查询中完成所有操作，但结果却很麻烦，这会损害可读性。 然后，在主要要求中，我们考虑了期末和期初工作时间之间的差异，并且在某种程度上很花哨地考虑了该期第一天和最后一天的工作时间。 Floridity与将工作日开始之前的时间移至其开始，以及将工作日结束之后的时间移至其结束相关。 此外，如果将带有shedule_base和schedule的请求的一部分删除到单独的预先计算的表中（如前所述），该请求将变成一个完全琐碎的请求。
让我们比较一个较大的样本的执行情况，以便更好地显示已完成的优化，从任务条件开始的四个时间段内，将更多时间用于生成工作计划。
我花了大约三千个时期。 我将仅在EXPLAIN中给出最上面的摘要行，典型值如下。
原始选项：
GroupAggregate (cost=265790.95..296098.23 rows=144320 width=36) (actual time=656.654..894.383 rows=2898 loops=1) ...
优化：
Hash Join (cost=45.01..127.52 rows=70 width=36) (actual time=1.620..5.385 rows=2898 loops=1) ...
时间增加了几个数量级。 随着期限的增加和期限的延长，差距只会越来越大。
一切似乎都很好，但是为什么进行了这样的优化后，我却将请求的第一个版本留给自己，直到它的性能足够了？ 是的，因为优化的版本无疑是更快的，但是它需要更多的时间来了解它的工作原理，也就是说，可读性已经变差。 也就是说，下次我需要在更改后的条件下重写请求时，我（或不是我）将不得不花费更多的时间来了解请求的工作方式。
今天就这些，保持触角的温暖，我要告别您，直到下一个Radio SQL版本。