我目前正在尝试比较今天和正好7天前的汇总数字(不是今天和7天前,而是简单地比较这两个离散日期(。我已经有了一种使用大量子查询的方法,但性能很差,我现在正在尝试优化。
这就是我到目前为止所想到的(示例查询,由于保密性,没有使用真实的表名和列(:
Select current_date, previous_date, current_sum, previous_sum, percentage
From (Select date as current_date, sum(numbers) as current_sum,
lag (sum(numbers)) over (partition by date order by date) as previous_sum,
(Select max(date)-7 From t1 ) as previous_date,
(current_sum - previous_sum)*100/current_sum as percentage
From t1 where date>=sysdate-7 group by date,previous_date)
但我肯定做错了什么,因为在输出中,previous_sum显示为空,当然百分比也显示为空。
你知道我做错了什么吗?我以前没有用过LAG,所以肯定有什么东西。谢谢
使用预聚合子查询的Join。
with agg as (
select sum(numbers) as sum_numbers, date from t1 group by date
)
select curr.sum_numbers as current_sum,
prev.sum_numbers as prev_sum,
curr.date as curr_date,
prev.date as prev_date
from agg curr
left join agg prev on curr.date-7=prev.date
使用滞后:
with agg as (
select sum(numbers) as sum_numbers, date from t1 group by date
)
select sum_numbers as current_sum,
lag(sum_numbers, 7) over(order by date) as prev_sum,
a.date as curr_date,
lag(a.date,7) over(order by date) as prev_date
from agg a
如果你只想要两个日期(今天和今天-7(,那么使用条件聚合和过滤器可以简单得多:
select sum(case when date = trunc(sysdate) then numbers else null end) as current_sum,
sum(case when date = trunc(sysdate-7) then numbers else null end) as previous_sum,
trunc(sysdate) as curr_date,
trunc(sysdate-7) as prev_date,
(current_sum - previous_sum)*100/current_sum as percentage
from t1 where date = trunc(sysdate) or date = trunc(sysdate-7)
您可以使用窗口(分析(函数来实现这一点,这应该是最快的方法。您的实际聚合查询有点不清楚,但我认为它是:
select date as current_date, sum(numbers) as current_sum
from t1
group by date;
如果您有所有日期的值,则使用:
select date as current_date, sum(numbers) as current_sum,
lag(sum(numbers), 7) over (order by date) as prev_7_sum
from t1
group by date;
如果你没有整天的数据,那么使用一个窗口框架:
select date as current_date, sum(numbers) as current_sum,
max(sum(numbers), 7) over (order by date range between '7' day preceding and '7' day preceding) as prev_7_sum
from t1
group by date;