oracle:实现滑动平均效果

最新推荐文章于 2022-07-31 23:29:32 发布

「已注销」

最新推荐文章于 2022-07-31 23:29:32 发布

阅读量718

点赞数

本文链接：https://blog.csdn.net/qq_40584718/article/details/105157808

版权

本文介绍了在Oracle数据库中实现滑动平均的方法，包括处理时间序列连续、不连续和存在重复样例的情况。通过数据准备、查询语句的编写以及使用开窗函数，详细阐述了如何在不同场景下找到正确的时间区间间隔，并强调了全连接和分组统计的重要性。文章还探讨了rows和range子句在限定窗口范围中的应用，以及unbounded和current now的含义。

摘要由CSDN通过智能技术生成

文章目录

问题背景

1321. 餐馆营业额变化增长 - 力扣（LeetCode）

问题背景
实现思路:
(1) 首先找到对应的7天之末的终点,分别是2019-01-07,2019-01-08,2019-01-09,2019-01-10.
在Oracle中可以通过lead函数实现上述效果.但是需要考虑的两种情况:

时间序列连续与不连续
时间序列的重复与不重复
注:这里的重复是完全相同

(2) 假定在上述(1)已经完全解决,那么只需要从表customer筛选出customer_id在7天终结结点序列的样例,组成新表tmp,然后将其与customer表全连接即可,达到初步想要的结果.但事实上,这样的样例并非所有的都满足要求,所以需要进一步筛选.而其对应的筛选条件就是:customer的visited_on与tmp的visited_on的差值在[0,6]之间即可.
(3) 然后按照tmp的visited_on分组统计即可.
注:当7天终结结点序列中的时间戳在customer中存在重复,在分组统计之前,需要对关联表进行去重.然后该节点会重复num次计算(num:表示时间戳重复的次数).
这里的7天终结结点序列就是滑动平均的时间各时间节点.
期望的结果是:sum=each_sum avg=each_avg
而事实上是:sum2=num*each_sum,avg2=sum2/7
在本例中,2019-01-10重复,

不去重,会出现重复计算

select cu.*,tmp.visited_on t_visited_on
        from customer cu,
            (select * from customer 
            where visited_on in (
                select lead(visited_on,6 ) over(order by visited_on ) 
                    from (select distinct visited_on from customer)) 
            and visited_on is not null ) tmp
        where tmp.visited_on-cu.visited_on between 0 and 6
order by tmp.visited_on;

去重后(下面的结果才是期望得到的):

select distinct tmp2.* 
    from (
        select cu.*,tmp.visited_on t_visited_on
        from customer cu,
            (select * from customer 
            where visited_on in (
                select lead(visited_on,6 ) over(order by visited_on ) 
                    from (select distinct visited_on from customer)) 
            and visited_on is not null ) tmp
        where tmp.visited_on-cu.visited_on between 0 and 6
        ) tmp2 
order by t_visited_on;

时间序列连续

数据准备

表customer,特点:时间序列基本连续

create table customer(
    customer_id number,
    name varchar2(10),
    visited_on date,
    amount number
);

insert into customer values(1,'John',to_date('2019-01-01','yyyy-mm-dd'),100);
insert into customer values(2,'Daniel',to_date('2019-01-02','yyyy-mm-dd'),110);
insert into customer values(3,'Jade',to_date('2019-01-03','yyyy-mm-dd'),120);
insert into customer values(4,'Khaled',to_date('2019-01-04','yyyy-mm-dd'),130);
insert into customer values(5,'Winston',to_date('2019-01-05','yyyy-mm-dd'),110);
insert into customer values(6,'Elvis',to_date('2019-01-06','yyyy-mm-dd'),140);
insert into customer values(7,'Anna',to_date('2019-01-07','yyyy-mm-dd'),150);
insert into customer values(8,'Maria',to_date('2019-01-08','yyyy-mm-dd'),80);
insert into customer values(9,'Jaze',to_date('2019-01-09','yyyy-mm-dd'),110);
insert into customer values(1,'Jhon',to_date('2019-01-10','yyyy-mm-dd'),130);
insert into customer values(3,'Jade',to_date