postgresql - 两个工作日之间的统计数据(周一至周五)

我有以下内容，存储在Postgres9.5.4数据库中。

CREATE TABLE activity (
    id int primary key,
    cat_id smallint,
    start_date date,
    end_date date
);

INSERT INTO activity VALUES(5822, 1, '2016-07-01', '2016-07-01');
INSERT INTO activity VALUES(5824, 5, '2016-07-01', '2016-07-01');
INSERT INTO activity VALUES(5825, 4, '2016-07-01', '2016-07-01');
INSERT INTO activity VALUES(5840, 5, '2016-07-01', '2016-07-01');
INSERT INTO activity VALUES(5873, 5, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5875, 3, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5876, 5, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5882, 5, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5883, 5, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5888, 4, '2016-07-05', '2016-07-05');
INSERT INTO activity VALUES(5905, 4, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5908, 5, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5911, 5, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5912, 3, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5913, 4, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5915, 4, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5920, 4, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5923, 4, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5928, 5, '2016-07-06', '2016-07-06');
INSERT INTO activity VALUES(5949, 4, '2016-07-07', '2016-07-07');
INSERT INTO activity VALUES(5955, 5, '2016-07-07', '2016-07-07');
INSERT INTO activity VALUES(5959, 4, '2016-07-07', '2016-07-07');
INSERT INTO activity VALUES(5960, 2, '2016-07-07', '2016-07-07');
INSERT INTO activity VALUES(5965, 4, '2016-07-07', '2016-07-07');
INSERT INTO activity VALUES(5998, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6000, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6001, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6003, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6005, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6008, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6012, 4, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6016, 5, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6018, 5, '2016-07-11', '2016-07-11');
INSERT INTO activity VALUES(6032, 4, '2016-07-12', '2016-07-12');
INSERT INTO activity VALUES(6035, 5, '2016-07-12', '2016-07-12');
INSERT INTO activity VALUES(6052, 4, '2016-07-12', '2016-07-12');
INSERT INTO activity VALUES(6060, 4, '2016-07-12', '2016-07-12');
INSERT INTO activity VALUES(6070, 5, '2016-07-13', '2016-07-13');
INSERT INTO activity VALUES(6075, 5, '2016-07-13', '2016-07-11');
INSERT INTO activity VALUES(6076, 3, '2016-07-13', '2016-07-11');
INSERT INTO activity VALUES(6077, 5, '2016-07-13', '2016-07-11');
INSERT INTO activity VALUES(6078, 5, '2016-07-13', '2016-07-13');

我想收集一些关于两个工作日（仅限周一至周五）之间差异的统计数据。
这个问题可能有助于我努力实现的目标。

SELECT
    SUM(CASE WHEN cat_id = 5 THEN 1 ELSE 0 END)  as "# Cat 5",
    AVG(CASE WHEN cat_id = 5 THEN start_date - end_date END)  as "AVG Cat 5",
    MIN(CASE WHEN cat_id = 5 THEN start_date - end_date END)  as "MIN Cat 5",
    MAX(CASE WHEN cat_id = 5 THEN start_date - end_date END)  as "MAX Cat 5"
FROM activity

我还创建了一个SQL Fiddle，以帮助进行测试。
为了进一步描述这一点，假设一个活动在星期六创建，星期一完成，持续时间应该是0天（因为我们不包括星期六和星期天）。如果某个活动是在周五创建并在周一完成的，则此活动的持续时间为1。
谢谢您。

最佳答案

您在这里要做的事情可以使用CTE在纯SQL中完成。请参见this answer以计算离某个日期一定天数的工作日；您需要逆操作，但您将得到要点。
不过，使用此功能更容易、更快，可以根据上述链接的答案重新计算两个日期之间的工作日数：

CREATE FUNCTION business_days_between(from_date date, to_date date) RETURNS int AS $$
-- This function assumes Mon-Fri business days. This returns an "open" range of days,
-- i.e. from Monday to Tuesday = 1. For a "closed" range of days (e.g. rentals), you
-- should add 1 to the result.
DECLARE
  alldays   int;   -- All calendar days in the period, counting down to 0
  days      int;   -- The working days to calculate, counting up from 0
  weeks     int;
  start_dow int;
  temp_date date;
BEGIN
  -- If from_date = to_date simply return 0.
  IF from_date = to_date THEN
    RETURN 0;
  END IF;

  -- If dates are reversed, flip them
  IF from_date > to_date THEN
    temp_date := from_date;
    from_date = to_date;
    to_date := temp_date;
  END IF;

  -- Set up variables, remove initial weekend days
  alldays := to_date - from_date;
  start_dow := extract(dow from from_date);
  IF start_dow = 0 THEN -- Don't count initial Sunday
    alldays := alldays - 1;
  END IF;
  IF start_dow = 6 THEN -- Fudge initial Saturday to the following Sunday
    start_dow := 0;
    alldays := alldays - 2;
  END IF;
  IF alldays <= 0 -- from_date is Saturday, to_date is next Sunday or Monday
    RETURN 0;
  END IF;

  -- Process the current week
  IF start_dow + alldays <= 5 THEN -- Stay in this week
    RETURN alldays;
  ELSE                             -- Count towards Friday
    days := 5 - start_dow;
    alldays := alldays - days;
  END IF;

  -- Move by full weeks
  weeks := alldays / 7;
  alldays := alldays - weeks * 7;
  days := days + weeks * 5;

  -- Add any remaining days, after the weekend
  IF alldays > 2 THEN
    days := days + alldays - 2;
  END IF;

  RETURN days;
END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;

您的查询现在变成：

SELECT
    count(*) as "# Cat 5",
    avg(business_days) AS "AVG Cat 5",
    min(business_days) AS "MIN Cat 5",
    max(business_days) AS "MAX Cat 5"
FROM (
  SELECT business_days_between(start_date, end_date) AS business_days
  FROM activity
  WHERE cat_id = 5) sub;

关于postgresql - 两个工作日之间的统计数据(周一至周五)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39135644/