本帖最后由 瘾上你了 于 2012-2-26 17:07 编辑
一：代码如下：
建表：
create table test(
id number,
mc number,
sl number
)
插数：
insert into test values(2,110,1);
insert into test values(1,111,1);
insert into test values(2,555,2);
insert into test values(1,222,1);
insert into test values(2,333,2);
insert into test values(1,666,3);
insert into test values(1,333,2);
insert into test values(2,222,1);
insert into test values(1,555,3);
commit;
查表：
select * from test；
idmc sl
2 110 1
1 111 1
2 555 2
1 222 1
2 333 2
1 666 3
1 333 2
2 222 1
1 555 3
二：问题来了：
关于以下SQL语句：
select id,mc,sl,
first_value(mc) over (partition by id order by sl) fv,
last_value(mc) over (partition by id order by sl) ls
from test；
得到的结果是：
idmc slfvls
1 111 1 111 222
1 222 1 111 222
1 333 2 111 333
1 555 3 111 666
1 666 3 111 666
2 110 1 110 222
2 222 1 110 222
2 333 2 110 555
2 555 2 110 555
为什么得到的ls列是上面这样的，为什么不是下面这样的？
idmc slfvls
1 111 1 111 111
1 222 1 111 222
1 333 2 111 333
1 555 3 111 555
1 666 3 111 666
2 110 1 110 110
2 222 1 110 222
2 333 2 110 333
2 555 2 110 555
我知道last_value默认的windowing方式是current row 到 unbounded preceeding. 问题就在于现在同一个分区里面order by的列名的值一样的，为什么last_value是取的相同order by 的列名值的最后一个值呢？比如order by sl 的前面两行sl 的值都是1，为什么第一行的last_value函数的值不取current row（第一行的当前行就是第一行）的mc的值而取第二行的呢？第2行的值取第2行这是理所当然。请大家帮我解答下，非常感谢。
二：下面是关于first_value 和 last_value 分析函数的总结，不是问题，算是我的个人笔记吧。
摘自：askTom
first_Value(column_name) over (partition by table_name order by column_id) a,
that will take the data, break it up by table_name
then, within each TABLE_NAME, sort by column_id
then, return the FIRST_VALUE of column name in the window which is by default (since you didn't say otherwise) going from the CURRENT ROW to UNBOUNDED PRECEDING.
So, the first_value is the first row in the window - always.
last_value(column_name) over (partition by table_name order by column_id desc) b from all_tab_columns
that will
a) break up by table_name
b) sort by column_id
c) return last row from current window - which by default is the CURRENT ROW and UNBOUNDED PRECEDING....
The last row of the window is always changing - as you progress through the partition.
you either want:
a) the first_value after sorting by column_id - wait you already have that.
b) the last_value with rows between current row and unbounded following.
So like this:
1 select table_name,
2column_id,
3column_name,
4first_Value(column_name) over (partition by table_name order by column_id) a,
5last_value(column_name) over (partition by table_name order by column_id desc
rows between current row and unbounded following) b
6 from all_tab_columns
7where table_name='ALL_ALL_TABLES'
In this way,both the value of column a and b are the same.
|
