怎样用一条SQL获取同期值和前期值

2008-12-10 02:22:10 652 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

LAG()函数实战避坑指南：从原理到生产级SQL优化 LAG()是SQL窗口函数中最基础也最易误用的核心工具，本质是在有序序列中实现‘向前/向后取值’的确定性操作。其执行依赖ORDER BY排序与PARTITION BY逻辑分片两大机制，底层通过扫描+缓存+回填完成O(1)取值，性能90%由索引和数据治理决定。技术价值在于支撑同比、环比、趋势识别等关键分析场景，广泛应用于零售销量预警、用户行为路径、金融时序风控等业务系统。本文聚焦LAG()在真实ETL脚本中的高频陷阱——如NULL排序歧义、分区粒度错配、OFFSET硬编码漂移、DEFAULT语义失真等，并提供阅读详情

测试数据如下：
<code=SQL>
create table test(time_id varchar2(6),value number);
insert into test
select '200801',1 from dual union all
select '200802',2 from dual union all
select '200701',3 from dual union all
select '200802',4 from dual union all
</code>
要求用一条SQL语句查询出同期值和前期值，预计得到的数据应如下：
<code=SQL>
time_id value lastmonth_value lastyear_value
200701 3
200702 4 3
200801 1 3
200802 2 1 4
</code>
求高手帮忙看看。谢谢！

...全文

树莓派4在Bookworm Raspbian系统下无法进入桌面模式（启动后出现彩色画面，然后一直黑屏，但是可以正常启动和ssh）本文记录了这段比较坎坷的探索之路，由于你的问题不一定是我最终解决方案的，可能是前面探索路上试过的，所以建议按顺序看排除前置问题。双十一又买了个树莓派 4B，插上之前树莓派 4B 的 TF 卡直接就能使用（毕竟是一样规格的）。但是由于用来连接硬盘的 USB 线是坏的，我以为是系统问题就进行了重装（所以坏掉的线还是直接扔了好）。重新安装系统就装了最新的 2023-10-10 版本的 64 位的 Raspberry Pi OS，但是出现了一个问题：启动后出现彩色画面，然后一直黑屏。阅读详情

vanjayhsu

基础和管理 17,379 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

vanjayhsu 2008-12-10

打赏
举报

OK了。。谢谢各位的参与，给了我很多好建议。其实SQL的功能还是很强大的。
尤其大乌龟和bw555给了一种比较通用的SQL。
yf520gn对oracle的掌握很好，函数用得很到位。谢谢各位！

sleepzzzzz 2008-12-10

打赏
举报

我没权利评论，供参考，各位高人，我说错了不要怪我，呵呵．

sleepzzzzz 2008-12-10

打赏
举报

[Quote=引用 8 楼 vanjayhsu 的回复:]
回复楼上的，bw555的算法用测试数据检验不正确，很奇怪。。。
[/Quote]

是你的数据错了,这一条'200802',4按你的意思应该是'200702',4　才对．

bw555的算法考虑到了月份不连续或者年份不连续的情况．

yf520gn 2008-12-10

打赏
举报

[Quote=引用 4 楼 vanjayhsu 的回复:]
回复2楼的，谢谢你的SQL。初步测试正确。请问下lag(value,1)中的1是写死的吗？

回复3楼，谢谢你提供的思路。我一开始也这么试过，但是可能出现的情况是，初期的数据在数据库表里面是没有同期值的，因此这样表连接起来可能出现全部空值。但是我想要的是当期的值可以取到，但同期和前期的如果没有就为空。
[/Quote]
lag(value,1)表示取得前一条数据的值，如果写成lag(value,2)表示取得是前一条的前一条的值

vanjayhsu 2008-12-10

打赏
举报

回复楼上的，bw555的算法用测试数据检验不正确，很奇怪。。。

sleepzzzzz 2008-12-10

打赏
举报

上面几个语句有待验证哦．

另外一个帖中的bw555算法应该是比较严密的．

dawugui 2008-12-10

打赏
举报

--楼主给的测试数据错了一个.

create table tb(time_id varchar2(6),value number); 

insert into tb

  select '200801',1 from dual union all 

  select '200802',2 from dual union all 

  select '200701',3 from dual union all 

  select '200702',4 from dual 



select t.* , 

       (select value from tb where time_id = (select max(time_id) from tb where time_id < t.time_id))  lastmonth_value,  

       (select value from tb where time_id = (select time_id from tb where to_number(substr(time_id,1,4)) = to_number(substr(t.time_id,1,4)) - 1 and substr(time_id,5,2) = substr(t.time_id,5,2))) lastyear_value  

from tb t

order by t.time_id



drop table tb 



/*

TIME_I      VALUE LASTMONTH_VALUE LASTYEAR_VALUE

------ ---------- --------------- --------------

200701          3                               

200702          4               3               

200801          1               4              3

200802          2               1              4



4 rows selected.

*/

dawugui 2008-12-10

打赏
举报

select t.* , 

       (select value from tb where time_id = (select max(time_id) from tb where time_id < t.time_id))  lastmonth_value,  

       (select value from tb where time_id = (select time_id from tb where to_number(substr(time_id,1,4)) = to_number(substr(t.time_id,1,4)) - 1 and substr(time_id,5,2) = substr(t.time_id,5,2))) lastyear_value  

from tb t

order by t.time_id

vanjayhsu 2008-12-10

打赏
举报

回复2楼的，谢谢你的SQL。初步测试正确。请问下lag(value,1)中的1是写死的吗？

回复3楼，谢谢你提供的思路。我一开始也这么试过，但是可能出现的情况是，初期的数据在数据库表里面是没有同期值的，因此这样表连接起来可能出现全部空值。但是我想要的是当期的值可以取到，但同期和前期的如果没有就为空。

Divina 2008-12-10

打赏
举报

提供个想法
把一张表当三张用。



  select time_id，a.value as value , b.value as lastmonth_value ,c.value as lastyear_value

  from test a , test b , test c

  where a.time_id = '你指定的'

  and   b.time_id = a.time_id - 1个月

  and   c.time_id = a.time_id + 1个月

这样能达到你要的值，但是你要自己去判断怎么搞定中间的 time_id 怎么加怎么减了

希望对你有用。

yf520gn 2008-12-10

打赏
举报



select time_id, value,lag(value,1) over(partition by substr(time_id,1,4)order by time_id)

 as lastmonth_value,

lag(value,1) over(partition by substr(time_id,5,2) order by substr(time_id,1,4) ) as lastyear_value 

from test

是分太少啦~~~

vanjayhsu 2008-12-10

打赏
举报

这种问题太常见还是太难？

怎样用一条SQL获取同期值和前期值

相关推荐