sql - 带条件获取表的前 N ​​行

Question

这是我们处理的一些调度数据的人为示例。

想法就是天真地问问题

Given that a project has N remaining unscheduled hours, a head_count (the number of people working on it), and the assumption that each worker will work 8 days; what non-weekend days AFTER the last scheduled day will this project require?

我有一个简化的 dim_dates 表:

CREATE TABLE dim_dates (
  date_actual DATE NOT NULL,
  is_weekend BOOLEAN NOT NULL
);

和项目表:

CREATE TABLE projects (
  id SERIAL PRIMARY KEY NOT NULL,
  last_scheduled_date DATE NOT NULL,
  remaining_hours_required INT NOT NULL,
  head_count INTEGER NOT NULL
);

所以给定这个项目:

INSERT INTO projects (last_scheduled_date, remaining_hours_required, head_count) 
VALUES ('2018-01-04', 21, 1);

通过 CEIL(remaining_hours_required::decimal/(8 * head_count)) 我们最终需要 3 天的工作。日期 2018-01-04 是星期四，因此我们应该结束的日期是接下来的星期五、星期一和星期二。

要获得以下非周末的日子，我们可以轻松加入 dim_dates:

SELECT p.*, d.* 
FROM projects p
INNER JOIN dim_dates d ON (
  d.date_actual > p.last_scheduled_date
  AND
  d.is_weekend IS FALSE
);

这会给我们所有 之后不是周末的日期。我的难题是如何将这个集合过滤到 3 行。

所以(必要的)任务描述是:

1. 计算完成项目所需的剩余天数
2. 获取所有 dim_dates，其中 is_weekend 为 false 且 date_actual 高于项目的最后预定日期

这是一个数据 fiddle :http://sqlfiddle.com/#!17/f6a90/3

Answer 1

使用运行总和来保留每个项目的剩余时间。并将这些限制在限制范围内。

在子查询中，它限制了计算的最大日期。
这样它就不会计算 dim_dates 中的每个下一个日期。

SELECT 
  id as projectId
, date_actual as weekDay
, row_number()
     over (partition by id order by date_actual desc)-1 as daysRemaining
FROM
(
SELECT
 p.id, d.date_actual, p.head_count
, p.last_scheduled_date, p.remaining_hours_required 
,(p.remaining_hours_required 
   - sum(8*head_count) over 
        (partition by p.id order by date_actual)
  ) as remaining_hours
FROM projects p
JOIN dim_dates d 
  ON (d.date_actual > p.last_scheduled_date
  AND d.date_actual <= p.last_scheduled_date + cast(ceil(((p.remaining_hours_required/5.0)*7.0)/(8*p.head_count))+1 as int)
  AND d.is_weekend IS FALSE)
) q
WHERE remaining_hours > (-8*head_count)
ORDER BY id, date_actual;

在 SQL fiddle 上测试 here