如何计算两个日期范围(Partition by和Partition range )

Question

我想用下面的公式来计算和比较实际的季度成本和以前的季度成本：(实际年度平均季度成本)/(去年平均季度成本)

我已经完成了实际的季度：

avg ("TOTAL_MONTHLY_COST_IN_USD") OVER (PARTITION BY START_YEARS, 
START_QUARTERS ORDER BY "START_QUARTERS" desc) as "PAST_Q"

但最难的是每季度获得实际的第一年平均成本。在最终结果中，我希望我的表看起来像这样：

ACT_Q | PAST_Q | DIVIDE
567.2 | 456.6  |  1.24

为了获得PAST_Q，我尝试使用partition by and PRECEDING。我有以下用于计算的支持列：

START_DATE , START_MONTH, START_QUARTER , START_YEAR .

此外，我使用add_month(x，-12)创建了它们，并将它们命名为LAST_YEAR，等等。

我的第一次尝试:我在一个内部select中分离了sum_cost计算和count_rows，然后将它们相除，但在结果中我只得到了0个值。

case when nvl(quarter_count, 0)<>0 then
sum_totalcost/quarter_count
end as "PAST_AVG_COST_QUARTERLY"

(avg ("TOTAL_MONTHLY_COST_IN_USD") OVER (PARTITION BY START_YEARS, START_QUARTERS ORDER BY ACTUAL_YEAR_QUARTER RANGE BETWEEN 450 PRECEDING AND 0 FOLLOWING) - avg ("TOTAL_MONTHLY_COST_IN_USD") OVER (PARTITION BY START_YEARS, START_QUARTERS ORDER BY ACTUAL_YEAR_QUARTER RANGE BETWEEN 360 PRECEDING AND 0 FOLLOWING)) as sum_cost

(count (*) OVER (PARTITION BY START_YEARS, 
START_QUARTERS ORDER BY 
ACTUAL_YEAR_QUARTER RANGE BETWEEN 450 PRECEDING AND 0 FOLLOWING) - count (*) OVER
 (PARTITION BY START_YEARS, START_QUARTERS 
ORDER BY ACTUAL_YEAR_QUARTER RANGE BETWEEN 360 PRECEDING AND 0 FOLLOWING)) as count_rows

有人知道如何计算这个范围的最佳实践吗？这将是非常有帮助的！提前谢谢。

Answer 1

幸运的是，我用" with“语句创建了两个表，解决了这个问题。该表包含实际平均成本/季度。这两个表都有这些数据: act_YEAR | act_QUARTER | past_YEAR | past_QUARTER |COUNTRY | MONTHLY_COST，然后我加入了过去一年和季度的第一个表，第二个表是act year & quarter。