计算日期列中的日期是否在开始日期和结束日期之间[Oracle SQL]

This is my first post, so I hope I've posted this one correctly.

这是我的第一篇文章，所以我希望我已经正确发布了这个帖子。

My problem:

I want to count the number of active customers per day, the last 30 days.

我想计算过去30天每天活跃客户的数量。

What I have so far:

In the first column I want to print today, and the last 29 days. This I have done with

在第一栏，我想今天打印，最后29天。我已经完成了

select distinct trunc(sysdate-dayincrement, 'DD') AS DATES
from   (
  select level as dayincrement
  from   dual
  connect by level <= 30
)

I've picked it up here at *, and it works perfectly. I can even extend the number of days returned to ex. 365 days. Perfect!

我已经在*上找到了它，它完美无缺。我甚至可以延长返回前的天数。 365天。完善！

I also have a table that looks like this

我也有一张看起来像这样的桌子

|Cust# | Start date | End date  |
| 1000 | 01.01.2015 | 31.12.2015|
| 1001 | 02.01.2015 | 31.12.2016|
| 1002 | 02.01.2015 | 31.03.2015|
| 1003 | 03.01.2015 | 31.08.2015|

This is where I feel the problem starts

这是我觉得问题开始的地方

I would like to get this result:

我想得到这个结果：

| Dates    | # of cust |
|04.01.2015|     4     |
|03.01.2015|     4     |
|02.01.2015|     3     |
|01.01.2015|     1     |

Here the query would count 1 if:

如果出现以下情况，查询将计为1：

• Start date <= DATES
  
• 开始日期<=日期
• End date >= DATES
• 结束日期> =日期

Else count 0.

其他数量为0。

I just don't know how to structure the query.

我只是不知道如何构造查询。

I tried this, but it didn't work.

我尝试过这个，但它没有用。

count(
  IF ENDDATE <= DATES THEN
    IF STARTDATE >= DATES THEN 1 ELSE 0 END IF
  ELSE
    0
  END IF
) AS CUST

Any ideas?

有任何想法吗？

2 个解决方案

#1

The following produces the results you're looking for. I had change the date generator to start on 04-JAN-2015 instead of SYSDATE (which is, of course, in the year 2016), and to use LEVEL-1 to include 'current' day:

以下产生您正在寻找的结果。我已将日期生成器更改为在2015年1月4日开始而不是SYSDATE（当然，在2016年），并使用LEVEL-1包含“当前”日：

WITH CUSTS AS (SELECT 1000 AS CUST_NO, TO_DATE('01-JAN-2015', 'DD-MON-YYYY') AS START_DATE, TO_DATE('31-DEC-2015', 'DD-MON-YYYY') AS END_DATE FROM DUAL UNION ALL
               SELECT 1001 AS CUST_NO, TO_DATE('02-JAN-2015', 'DD-MON-YYYY') AS START_DATE, TO_DATE('31-DEC-2016', 'DD-MON-YYYY') AS END_DATE FROM DUAL UNION ALL
               SELECT 1002 AS CUST_NO, TO_DATE('02-JAN-2015', 'DD-MON-YYYY') AS START_DATE, TO_DATE('31-MAR-2015', 'DD-MON-YYYY') AS END_DATE FROM DUAL UNION ALL
               SELECT 1003 AS CUST_NO, TO_DATE('03-JAN-2015', 'DD-MON-YYYY') AS START_DATE, TO_DATE('31-AUG-2015', 'DD-MON-YYYY') AS END_DATE FROM DUAL ),
     DATES AS (SELECT DISTINCT TRUNC(TO_DATE('04-JAN-2015', 'DD-MON-YYYY') - DAYINCREMENT, 'DD') AS DT
                FROM (SELECT LEVEL-1 AS DAYINCREMENT
                        FROM DUAL
                        CONNECT BY LEVEL <= 30))
SELECT d.DT, COUNT(*)
  FROM CUSTS c
  CROSS JOIN DATES d
  WHERE d.DT BETWEEN c.START_DATE AND c.END_DATE
  GROUP BY d.DT
  ORDER BY DT DESC

Best of luck.

祝你好运。

#2

You could write a CASE expression equivalent to your IF-ELSE construct.

您可以编写一个等同于IF-ELSE构造的CASE表达式。

For example,

例如，

SQL> SELECT COUNT(
  2    CASE
  3      WHEN hiredate <= sysdate
  4      THEN 1
  5      ELSE 0
  6    END ) AS CUST
  7  FROM emp;

      CUST
----------
        14

SQL>

However, looking at your desired output, it seems, you just need to use COUNT and GROUP BY. The date conditions should be in the filter predicate.

但是，看看你想要的输出，你似乎只需要使用COUNT和GROUP BY。日期条件应该在过滤器谓词中。

For example,

例如，

SELECT dates, COUNT(*) 
FROM   table_name
WHERE  dates BETWEEN start_date AND end_date
GROUP BY dates;