sql - 在 Oracle SQL 中计算每月确认的收入

Question

我有一张订单行表，其中显示了预订金额和预订日期，但收入是在 3 个月内确认的（因此在预订月份中确认为 1/3，在接下来的两个月中分别确认 1/3）。

我需要创建一个查询来显示每个月确认的总收入。

有没有可以解决这个问题的分析函数？目前我已经拼凑了 3 个连接查询，这些查询给出了数字，但在 3 个单独的列中，我需要在一列中：

select     TRUNC(OM.BOOKING_DATE, 'MONTH') as Month
          , SUM(OM.BOOKED_VALUE)/3 as Month_1 
          , M2.Month_2
          , M3.Month_3

from      ORDERS.OM,
          (select    TRUNC(ADD_MONTHS(OM.BOOKING_DATE,1), 'MONTH') as Month
                   , SUM(OM.BOOKED_VALUE)/3 as Month_2

           from      ORDERS.OM

           GROUP By TRUNC(ADD_MONTHS(OM.BOOKING_DATE,1), 'MONTH')) M2,
          (select    TRUNC(ADD_MONTHS(OM.BOOKING_DATE,2), 'MONTH') as Month
                   , SUM(OM.BOOKED_VALUE)/3 as Month_3

           from      ORDERS.OM
           GROUP By TRUNC(ADD_MONTHS(OM.BOOKING_DATE,2), 'MONTH')) M3

WHERE     TRUNC(OM.BOOKING_DATE, 'MONTH') = M2.MONTH
AND       TRUNC(OM.BOOKING_DATE, 'MONTH') = M3.MONTH
 
GROUP By TRUNC(OM.BOOKING_DATE, 'MONTH'), M2.Month_2, M3.Month_3

Order by 1 DESC

标签： sql

Answer 1

每行三倍并求和

select   t.Month, SUM(t.Val) as Value
from      ORDERS.OM
cross join lateral (select TRUNC(OM.BOOKING_DATE, 'MONTH') as Month, OM.BOOKED_VALUE/3.0 as Val from dual union all
      select TRUNC(ADD_MONTHS(OM.BOOKING_DATE,1), 'MONTH'), OM.BOOKED_VALUE/3.0  from dual union all 
      select TRUNC(ADD_MONTHS(OM.BOOKING_DATE,2), 'MONTH'), OM.BOOKED_VALUE/3.0  from dual ) t
group by t.Month