sql - 是否有一种好的 SQL 方法来汇总以日期范围 block 作为输入的表？

Question

有没有人有好的方法从包含开始和停止日期给定的时间间隔的表格中生成每日摘要(其中空停止意味着无限期地继续)？

描述问题的最佳方式是举个例子。想象一下餐厅员工的数据库跟踪时间表，员工经常被雇用和解雇。有时，员工可能会回来并被重新雇用。

给定一个表，每个员工一行，如下所示:

position,name,start,end
bottle washer,Fred,1/2/2013,1/5/2013
bottle washer,Barney,1/4/2013,1/7/2013
bottle washer,Betty,1/10/2013,
bottle washer,Wilma,1/12/2013,1/13/2013
cook,Bilbo,1/1/2013,1/3/2013
cook,Frodo,1/5/2013,1/8/2013
cook,Bilbo,1/7/2013

我正在寻找一个查询，该查询为每一天/职位生成一行，给出当天的人员配备水平，如:

position,date,staffing
bottle washer,1/1/2013,0
bottle washer,1/2/2013,1
bottle washer,1/3/2013,1
bottle washer,1/4/2013,2
bottle washer,1/5/2013,2
bottle washer,1/6/2013,1
bottle washer,1/7/2013,1
bottle washer,1/8/2013,0
bottle washer,1/9/2013,0
bottle washer,1/10/2013,1
bottle washer,1/11/2013,1
bottle washer,1/12/2013,2
bottle washer,1/13/2013,2
bottle washer,1/14/2013,1
bottle washer,1/15/2013,1
cook,1/1/2013,1
cook,1/2/2013,1
cook,1/3/2013,1
cook,1/4/2013,0
cook,1/5/2013,1
cook,1/6/2013,1
cook,1/7/2013,2
cook,1/8/2013,2
cook,1/9/2013,1
cook,1/10/2013,1
cook,1/11/2013,1
cook,1/12/2013,1
cook,1/13/2013,1
cook,1/14/2013,1
cook,1/15/2013,1

我意识到我可以通过在客户端编写过程代码或在服务器端编写存储过程来解决这个问题，但是使用单个 SQL 查询会简化很多事情。如果它是一个复杂的查询，或者如果它使用窗口或分析函数，那就没问题了。目前，如果需要的话，我们正在使用 Oracle。

有什么好的方法建议吗？

Answer 1

分区外部连接可以帮助填充日历数据。它类似于交叉连接，但在非平凡的表大小下可能表现得更好。

交叉连接会将集合 A 中的每一行与集合 B 中的每一行连接起来。

分区外连接会将集合 A 中的每一行与集合 B 中的每个定义的子集连接起来。

以下示例仅根据您提供的计划数据生成日期范围。因此第 14 和第 15 号不会出现，但很容易修复。

with schedules as
(
    --Schedule data.
    select 'bottle washer' position, 'Fred'   name, date '2013-01-02' startdate, date '2013-01-05' enddate from dual union all
    select 'bottle washer' position, 'Barney' name, date '2013-01-04' startdate, date '2013-01-07' enddate from dual union all
    select 'bottle washer' position, 'Betty'  name, date '2013-01-10' startdate, null              enddate from dual union all
    select 'bottle washer' position, 'Wilma'  name, date '2013-01-12' startdate, date '2013-01-13' enddate from dual union all
    select 'cook'          position, 'Bilbo'  name, date '2013-01-01' startdate, date '2013-01-03' enddate from dual union all
    select 'cook'          position, 'Frodo'  name, date '2013-01-05' startdate, date '2013-01-08' enddate from dual union all
    select 'cook'          position, 'Bilbo'  name, date '2013-01-07' startdate, null              enddate from dual
),
dates as
(
    --Date range, based on schedule data.
    select first_startdate + level - 1 the_date
    from
    (
        select min(startdate) first_startdate
            ,(max(enddate) - min(startdate))+1 date_diff
        from schedules
    )
    connect by level <= date_diff
)
--Count of staff working per position, per day.
select position, the_date, count(schedules.startdate) staffing
from dates
left join schedules partition by (position)
    on dates.the_date between schedules.startdate
        and nvl(schedules.enddate, date '9999-12-31')
group by position, the_date
order by position, the_date;