在sql查询中计算治疗天数

Question

我正试着从甲骨文数据库中按月计算治疗天数。(大大简化的)数据如下：

 Therapies                          
+-----------+-----------+----------+
| Rx Number | StartDate | StopDate |
|-----------+-----------+----------|
| 1         | 12-29-14  | 1-10-15  |
| 2         | 1-2-15    | 1-14-15  |
| 3         | 1-29-15   | 2-15-15  |
+-----------+-----------+----------+

就本例而言，所有时间都假定为午夜。治疗总天数为(10-1 + 32-29) + (14-2) + (15-1 + 32-29) = 41。1月份治疗总天数为(10-1) + (14-2) + (32-29) = 24。

如果我想计算1月份的治疗天数，我最大的努力是以下查询：

SELECT SUM(stopdate - startdate) 
FROM therapies 
WHERE startdate > to_date('01-JAN-15') 
  AND stopdate < to_date ('01-FEB-15');

然而，rx的1和3根本没有被捕获。我可以尝试以下几种方法：

SELECT SUM(stopdate - startdate) 
FROM therapies 
WHERE stopdate > to_date('01-JAN-15') 
  AND startdate < to_date ('01-FEB-15');

但这将包括第一次和第三次治疗的全部持续时间，而不仅仅是一月份的部分。为了使问题更加复杂，我需要这些为期两年的每月摘要。所以我的问题是：

1. 如何包括悬垂疗法，以便只包括目标时间段内的部分，以及
2. 我如何在两年期间自动生成这些每月摘要？

Answer 1

如何将悬垂疗法包括在内，以便只包括目标时间段内的部分？

select sum(
    greatest(least(stopdate, date '2015-01-31' + 1) 
      - greatest(startdate, date '2015-01-01'), 0)) suma
  from therapies

我如何在两年期间自动生成这些每月摘要？

with period as (select date '2014-01-01' d1, date '2015-12-31' d2 from dual),
months as (select trunc(add_months(d1, level-1), 'Month') dt 
    from period connect by add_months(d1, level-1)<d2)
select to_char(dt, 'yyyy-mm') mth, 
    sum(greatest(least(stopdate, add_months(dt, 1)) - greatest(startdate, dt), 0)) suma
  from therapies, months
  group by to_char(dt, 'yyyy-mm') order by mth

上面的查询产生了所需的输出。请将您的日期插入适当的位置，以更改分析期间。在第二个SQL子查询中，months给出了24个日期，每个月一个。剩下的只是使用函数greatest()、least()和一些数学。

Answer 2

使用case语句设置开始日期和停止日期。如下所示：

select sum( Stopdate - (case Startdate when startdate < to_date(@YourBeginingDate) then To_date(@YourBeginingDate) else startdate end) FROM therapies WHERE stopdate > to_date(@YourBeginingDate) AND StartDate < to_date(@YourEndingDate)

Answer 3

我要做的事情如下：

WITH t1 AS (
  SELECT 1 AS rx, DATE'2014-12-29' AS start_date
       , DATE'2015-01-10' AS stop_date
    FROM dual
   UNION ALL
  SELECT 2, DATE'2015-01-02', DATE'2015-01-14'
    FROM dual
   UNION ALL
  SELECT 3, DATE'2015-01-29', DATE'2015-02-15'
    FROM dual
)
SELECT TRUNC(rx_dt, 'MONTH') AS rx_month, SUM(rx_cnt) AS rx_day_cnt
  FROM (
    SELECT rx_dt, COUNT(*) AS rx_cnt
      FROM (
        SELECT rx, start_date + LEVEL - 1 AS rx_dt
          FROM t1
       CONNECT BY start_date + LEVEL - 1 < stop_date
           AND PRIOR rx = rx
           AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
    ) GROUP BY rx_dt
) GROUP BY TRUNC(rx_dt, 'MONTH')
 ORDER BY rx_month

结果：

12/1/2014 12:00:00 AM   2
1/1/2015 12:00:00 AM    24
2/1/2015 12:00:00 AM    15

See SQL Fiddle here.

我所做的是使用LEVEL和CONNECT BY来获得所有基于start_date和stop_date的治疗(不包括在内)。然后，我GROUP BY治疗日期(rx_dt)，以处理重叠疗法。然后我使用GROUP BY函数来确定治疗的月份。

这应该可以在两年(或更长时间)内正常工作；只需在上一次GROUP BY之前添加该过滤器即可。

WHERE rx_dt >= DATE'2014-01-01'
  AND rx_dt < DATE'2016-01-01'
GROUP BY TRUNC(rx_dt, 'MONTH')

注意，如果您的主键是复合的，则应该在CONNECT BY子句中包含所有列：

CONNECT BY start_date + LEVEL - 1 < stop_date
    AND PRIOR rx = rx
    AND PRIOR patient_id = patient_id
    --etc.