计算列日期范围内每个日期发生的行数 [英] count number of rows that occur for each date in column date range

问题描述

我有一张表，上面有如下数据

I have a table with data such as below

Group       Start Date        End Date
A        01/01/01       01/03/01
A       01/01/01        01/02/01
A       01/03/01        01/04/01
B       01/01/01        01/01/01
ETC

我希望产生一个这样的视图，每天可以计数一次

I am looking to produce a view that gives a count for each day, like this

Group       Date        Count
A       01/01/01            2
A       01/02/01            2
A       01/03/01            2
A       01/04/01         1
B       01/01/01            1

我正在使用Oracle 9，对如何处理此问题一无所知，并且正在寻找使我入门的任何想法.
注意:生成用于保存日期的表是不切实际的，因为最终产品必须细分为分钟.

I am using Oracle 9 and am at a total loss on what how to handle this and am looking for any idea to get me started.
Note: Generating a table to hold the dates is not practical because I final product has to break down to the minute.

推荐答案

我通常用数字表解决这种问题:

Typically I solve this kind of problem with a numbers table:

WITH Dates AS (
    SELECT DateAdd(d, Numbers.Number - 1, '1/1/2001') AS Date
    FROM Numbers
    WHERE Numbers.Number BETWEEN 1 AND 100000 -- Arbitrary date range
)
SELECT GroupData.Group, Dates.Date, COUNT(*)
FROM Dates
LEFT JOIN GroupData
    ON Dates.Date BETWEEN GroupData.StartDate AND GroupData.EndDate
GROUP BY GroupData.Group, Dates.Date
ORDER BY GroupData.Group, Dates.Date

这篇关于计算列日期范围内每个日期发生的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！