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

[vanjayhsu]

测试数据如下：
<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>
求高手帮忙看看。谢谢！

[vanjayhsu]

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

[yf520gn]

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 

是分太少啦~~~

[Divina]

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

  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 怎么加怎么减了

希望对你有用。

[vanjayhsu]

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

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

[dawugui]

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

[dawugui]

--楼主给的测试数据错了一个.
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.
*/

[sleepzzzzz]

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

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

[vanjayhsu]

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

[yf520gn]

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

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

[sleepzzzzz]

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

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

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

[sleepzzzzz]

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

[vanjayhsu]

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