是否有一个很好的SQL方法来汇总带有日期范围块作为输入的表？(Is there a good SQL approach for summarizing a table with blocks of date ranges as input?)
有没有人有一个很好的方法来从包含开始和停止日期给出的时间间隔的表中生成每日摘要（其中空止点意味着无限期地继续）？
描述问题的最佳方式是一个例子。 想象一下，餐馆工人的数据库跟踪时间表，雇员经常被雇用和解雇。 有时员工可能会回来重新雇用。
给出每个员工一行的表格，如下所示：
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。
关于好方法的任何建议？
Does anyone have a good approach for generating a daily summary from a table containing intervals of times given by start and stop dates (where a null stop means continues indefinitely)?
The best way to describe the problem is with an example. Imagine a database tracking schedules for workers at a restaurant, where employees are hired and fired frequently. Sometimes employees might come back and get rehired.
Given a table with one row for each employee like the following:
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/2013I'm looking for a query that produces one row for each day/position giving the staffing level for that day, as in:
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,1I realize that I could solve this problem by writing procedural code on the client side or a stored procedure on the server side, but it would simplify alot of things to use a single SQL query. It would be fine if it were a complicated query or if it used windowing or analytic functions. At the moment, we're using Oracle if that matters.
Any suggestions on good approaches?
原文：https://stackoverflow.com/questions/21567199