偏移量分析函数lead、lag

语法：

lead (expression ，[offset,[default]])
over ([query_partition_clause] order_by_clause)

expression :可以包含除了分析函数意外的任何oracle内置函数。

offset :可选项，表里偏移当前行的值，默认值为1。

default :可选项，如果offset值超出了当前表范围，就返回此值，默认值为null.

举例：

创建视图v_products 记录个月商品

create or replace view v_products as(select 1 as id, '茄子' as product,to_date('2023-01','yyyy-mm') as month,255 as money from DUAL
              union
              select 2 as id, '黄瓜' as product,to_date('2023-01','yyyy-mm') as month,188 as money from DUAL
              union
              select 3 as id, '西瓜' as product,to_date('2023-01','yyyy-mm') as month ,145 as money from DUAL
              union
              select 4 as id, '苹果' as product,to_date('2023-01','yyyy-mm') as month ,500 as money from DUAL
              union
              select 5 as id, '茄子' as product,to_date('2023-02','yyyy-mm') as month,260 as money from DUAL
              union
              select 6 as id, '黄瓜' as product,to_date('2023-02','yyyy-mm') as month,244 as money from DUAL
              union
               select 7 as id, '西瓜' as product,to_date('2023-02','yyyy-mm') as month,111 as money from DUAL
              union
              select 8 as id, '苹果' as product,to_date('2023-02','yyyy-mm') as month,455 as money from DUAL);

统计各类商品二月较一月的营业额升降情况

select product, last_money - money
from (select id,
             product,
             month,
             money,
             lead(money, 1, null) over (partition by product order by month) as
                 last_money
      from v_products)
where last_money is not null;