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

Question

我有以下内容，存储在 Postgres 9.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。

谢谢。

Answer 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;