ITPub博客

首页 > 应用开发 > IT综合 > AskTom 关于first_value() last_value()的解释

AskTom 关于first_value() last_value()的解释

原创 IT综合 作者:liang573728 时间:2019-05-27 19:21:05 0 删除 编辑

Hi Tom, a Short question

In this query, I expect column "a" and "b" to have the same value

select table_name,
column_id,
column_name,
first_Value(column_name) over (partition by table_name order by column_id) a,
last_value(column_name) over (partition by table_name order by column_id desc) b from all_tab_columns
where table_name='ALL_ALL_TABLES';


TABLE_NAME COLUMN_ID COLUMN_NAME A B
ALL_ALL_TABLES 1 OWNER OWNER OWNER
ALL_ALL_TABLES 2 TABLE_NAME OWNER TABLE_NAME
...
ALL_ALL_TABLES 51 COMPRESSION OWNER COMPRESSION
ALL_ALL_TABLES 52 DROPPED OWNER DROPPED


but Last_Value (b) seems to be the same than "Column_name". What am I doing wrong?

Thanks in advance.

[@more@]

and we said...
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.

ops$tkyte%ORA10GR2> select table_name,
2 column_id,
3 column_name,
4 first_Value(column_name) over (partition by table_name order by column_id) a,
5 last_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
7 where table_name='ALL_ALL_TABLES'
8 /

TABLE_NAME COLUMN_ID COLUMN_NAME A
B
------------------------------ ---------- ------------------------------
------------------------------ ------------------------------
ALL_ALL_TABLES 52 DROPPED OWNER
OWNER
ALL_ALL_TABLES 51 COMPRESSION OWNER
OWNER
ALL_ALL_TABLES 50 DEPENDENCIES OWNER
OWNER
ALL_ALL_TABLES 49 CLUSTER_OWNER OWNER
OWNER
ALL_ALL_TABLES 48 MONITORING OWNER
OWNER
ALL_ALL_TABLES 47 SKIP_CORRUPT OWNER
OWNER

ref:

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/640706/viewspace-1025241/,如需转载,请注明出处,否则将追究法律责任。

下一篇: to_char上的fm意思
请登录后发表评论 登录
全部评论

注册时间:2005-10-15

  • 博文量
    152
  • 访问量
    120704