Oracle SQL如何根据日期范围按月分解收入

Question

基于表A中的数据，试图找到一种有效的方法来实现下表B中的结果。表A中的这些记录？请注意，ID 1的结束日期为12/31/2199（不是错别字），我们仅在09/2016至12/2016的月份列出每个ID的收入。另请注意，ID 3在11/2016月份有两个收入，其中600个代表11月收入（因为这是ID在2016年11月底的收入）。至于2016年11月开始的ID，他们的行将在9月16日和10月16日缺失，因为它们在11月之前不存在。

表A：

ID  INCOME  EFFECTIVE_DATE  END_DATE
1   700     07/01/2016      12/31/2199
2   500     08/20/2016      12/31/2017
3   600     11/11/2016      02/28/2017
3   100     09/01/2016      11/10/2016
4   400     11/21/2016      12/31/2016

表B（预期结果）：

         
ID  INCOME  MONTH
1   700     12/2016
1   700     11/2016
1   700     10/2016
1   700     09/2016
2   500     12/2016
2   500     11/2016
2   500     10/2016
2   500     09/2016
3   600     12/2016
3   600     11/2016
3   100     10/2016
3   100     09/2016
4   400     12/2016
4   400     11/2016

解决方案我使用了下面@mathguy提供的答案，它的工作方式就像魅力 - 在此过程中学习了一些新知识：枢轴和Unpivot。还要感谢@MTO（和其他所有人）抽出宝贵的时间来提供帮助。

Answer 1

这是一个解决方案，仅一次从基表中使用每一行，并且不需要加入，组等。它使用unpivot操作，自Oracle 11.1以来可用，不是一个昂贵的操作。

with
     table_a ( id, income, effective_date, end_date ) as (
       select 1, 700, date '2016-07-01', date '2199-12-31' from dual union all
       select 2, 500, date '2016-08-20', date '2017-12-31' from dual union all
       select 3, 600, date '2016-11-11', date '2017-02-28' from dual union all
       select 3, 100, date '2016-09-01', date '2016-11-10' from dual union all
       select 4, 400, date '2016-11-21', date '2016-12-31' from dual
     )
-- end of test data (not part of the solution): SQL query begins BELOW THIS LINE
select id, income, mth
from (
       select id,
              case when date '2016-09-30' between effective_date and end_date
                   then income end as sep16,
              case when date '2016-10-31' between effective_date and end_date
                   then income end as oct16,
              case when date '2016-11-30' between effective_date and end_date
                   then income end as nov16,
              case when date '2016-12-31' between effective_date and end_date
                   then income end as dec16
       from   table_a
     )
unpivot ( income for mth in ( sep16 as '09/2016', oct16 as '10/2016', nov16 as '11/2016',
                              dec16 as '12/2016' )
        )
order by id, mth desc
;

输出：

ID INCOME MTH
-- ------ -------
 1    700 12/2016
 1    700 11/2016
 1    700 10/2016
 1    700 09/2016
 2    500 12/2016
 2    500 11/2016
 2    500 10/2016
 2    500 09/2016
 3    600 12/2016
 3    600 11/2016
 3    100 10/2016
 3    100 09/2016
 4    400 12/2016
 4    400 11/2016
14 rows selected.

Answer 2

使用递归子查询子句的解决方案。这并不依赖于将界限用于查询中的界限，因为它们可以作为绑定变量:lower_bound和:upper_bound传递。在下面的示例中，它们分别设置为DATE '2016-09-01'和DATE '2016-12-31'。

查询：

WITH months ( id, income, month, end_dt ) AS (
  SELECT id,
         income,
         CAST( TRUNC( GREATEST( a.effective_date, :lower_bound ), 'MM' ) AS DATE ),
         LEAST( a.end_date, :upper_bound )
  FROM   TableA a
  WHERE  :lower_bound <= a.end_date
  AND    a.effective_date <= :upper_bound
UNION ALL
  SELECT id,
         income,
         CAST( ADD_MONTHS( month, 1 ) AS DATE ),
         end_dt
  FROM   months
  WHERE  ADD_MONTHS( month, 1 ) <= end_dt
)
SELECT id,
       income,
       LAST_DAY( month ) AS month
FROM   months
WHERE  LAST_DAY( month ) <= end_dt
ORDER BY id, month;

输出：

ID INCOME MONTH
-- ------ ----------
 1    700 2016-09-30
 1    700 2016-10-31
 1    700 2016-11-30
 1    700 2016-12-31
 2    500 2016-09-30
 2    500 2016-10-31
 2    500 2016-11-30
 2    500 2016-12-31
 3    100 2016-09-30
 3    100 2016-10-31
 3    600 2016-11-30
 3    600 2016-12-31
 4    400 2016-11-30
 4    400 2016-12-31