sql - 有什么办法可以在 postgreSQL 中将假期与工作日分开吗？

Question

我住在伊朗，伊朗的假期与日历不同。我需要从工作日中切断周末。我的日历上有伊朗假期。我的 table 是这样的:

start_date           finish_date         datediff(day)
---------------------------------------------------
03/03/2018           03/09/2018          6
03/10/2018           03/21/2018          11

我知道伊朗的假期包括这些日子:

03/05/2018
03/11/2018
03/19/2018

所以我需要减少任何时间间隔的伊朗假期。例如，时间 03/05/2018 在第一个间隔中，应该从这个间隔中减少。关键是我不能加入假期表和时间间隔表，因为它们在两个不同的数据库上。

决赛 table 是这样的:

start_date           finish_date         datediff(day)   Difference_of_time_ 
                                                         except_the_holidays
---------------------------------------------------------------------------
03/03/2018           03/09/2018          6                       5
03/10/2018           03/21/2018          11                      9

Answer 1

我在过去的两家公司都处理过这个确切的问题。在每种情况下，一个非常有效的解决方案是维护一个工作日日历，其中包含所有日期:工作日、周末、假期，并对每个日期进行分类。它还有一个运行索引来指示工作日是否已经过去。这是一个示例:

cal_date   is_workday  day_type     workday_index   workday_index_back
  7/1/2018 0           WE                    4635                 4635
  7/2/2018 1           WD                    4636                 4635
  7/3/2018 1           WD                    4637                 4636
  7/4/2018 0           HD                    4637                 4637
  7/5/2018 1           WD                    4638                 4637
  7/6/2018 1           WD                    4639                 4638
  7/7/2018 0           WE                    4639                 4639
  7/8/2018 0           WE                    4639                 4639
  7/9/2018 1           WD                    4640                 4639
 7/10/2018 1           WD                    4641                 4640
 7/11/2018 1           WD                    4642                 4641
 7/12/2018 1           WD                    4643                 4642
 7/13/2018 1           WD                    4644                 4643
 7/14/2018 0           WE                    4644                 4644
 7/15/2018 0           WE                    4644                 4644

(7 月 4 日是美国的假期)。

workday_index 字段在每个工作日递增，在非工作日不递增。一旦您定义了假期日历，就可以很容易地构建该表。

数字本身没有任何意义，它们只是一种计算整个日历工作日数的机制。

从这里开始，计算日期之间的差异就像:

select
  t.start_date, t.finish_date,
  t.finish_date - t.start_date as calendar_days,
  wd2.workday_index - wd1.workday_index as work_days
from
  table t
  join work_days wd1 on t.start_date = wd1.cal_date
  join work_days wd2 on t.finish_date = wd2.cal_date

您还可以执行一些操作，例如计算某件事情的工作天数。例如，如果您有客户需求日期和提前期(制造天数)，那么您可以按如下方式计算最晚的可能开始日期:

select
  c.need_date, c.lead_time_days, 
  wd2.cal_date as earliest_possible_start_date
from
  customer_data c
  join util.work_days wd1 on c.need_date = wd1.cal_date
  join util.work_days wd2 on wd1.workday_index - c.lead_time_days = wd2.workday_index

作为额外的好处，如果您要应用前滚规则与后滚规则，该表的工作方式也不同——这就是为什么有两个工作日索引——前向和后向。这是适用的，因为您处理非工作日的方式可能与上下文相关。

例如，如果客户在星期六之前想要他的产品，那么他的需求日期实际上是星期五(回滚)。但是，如果供应商 promise 周六送货，则日期需要向前推进——您要到周一才能真正收到。