关于分析函数的例子

今天在群里看到有人问了一个sql问题。表结构如下

wsx@ORCL> select * from t_client;

A   B
--- -------------------
a   2013-08-16:16:25:21
a   2013-08-16:16:25:24
a   2013-08-16:16:25:24
b   2013-08-16:16:25:28
b   2013-08-16:16:25:29
b   2013-08-16:16:25:30

a列代表客户名，b代表客户的更新时间。

他要的结果是显示客户A和客户B的最新时间的记录。有人马上就提示用分析函数，各种大牛啊。根据他们提示我也动手敲了一下

以下是我的的sql语句，带执行计划

wsx@ORCL> select * from (select t_client.b,t_client.a, row_number() over(partition by a order by b desc) rn from t_client) t where t.rn=1;

B                   A           RN
------------------- --- ----------
2013-08-16:16:25:24 a            1
2013-08-16:16:25:30 b            1


Execution Plan
----------------------------------------------------------
Plan hash value: 4076986829

-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |     6 |   150 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |          |     6 |   150 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|          |     6 |    72 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_CLIENT |     6 |    72 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T"."RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "A" ORDER BY
              INTERNAL_FUNCTION("B") DESC )<=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
        234  recursive calls
          0  db block gets
         39  consistent gets
          0  physical reads
          0  redo size
        720  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          2  rows processed

wsx@ORCL> select distinct  first_value(b) over(partition by a order by b desc) from t_client;  ---first_value 返回组中的第一个值  last_value反之. 使用DISTINCT与不使用DISTINCT，对FIRST_VALUE函数是有区别的，使用DISTINCT的FIRST_VALUE函数效果等类似于SQL Server中的first()，取得所有分组中的第一条数据。

FIRST_VALUE(B)OVER(
-------------------
2013-08-16:16:25:24
2013-08-16:16:25:30


Execution Plan
----------------------------------------------------------
Plan hash value: 79666223

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     6 |    72 |     5  (40)| 00:00:01 |
|   1 |  HASH UNIQUE        |          |     6 |    72 |     5  (40)| 00:00:01 |
|   2 |   WINDOW SORT       |          |     6 |    72 |     5  (40)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T_CLIENT |     6 |    72 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        626  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed