Oracle Group By Issue

Question

我正在努力解决一个看似容易解决的问题(至少对于我在MySQL /SqlServer中是这样！)

我会简化这个问题。假设我有下表：

表决票

ID  ID_IDEA DATE_VOTE   with ID_IDEA FK(IDEA.ID)
1   3       10/10/10
2   0       09/09/10
3   3       08/08/10
4   3       11/11/10
5   0       06/06/10
6   1       05/05/10

我正在尝试查找每个想法的最新投票，这意味着我只想返回ID为4、2和6的行。

在Oracle中，如果不使用SUM()、AVG等函数，就不能使用GROUP BY。我对它的工作原理有点困惑。

请指教，

谢谢。

Answer 1

SELECT id,
       id_idea,
       date_vote
FROM   (SELECT id,
               id_idea,
               date_vote,
               Row_number() over (PARTITION BY id_idea 
                                      ORDER BY date_vote DESC NULLS LAST) AS rn
        FROM   VOTE) AS t
WHERE  rn = 1  

Answer 2

您不应该使用分析函数来处理这样的查询，如果您可以通过简单的聚合来实现：

SQL> create table vote(id,id_idea,date_vote)
  2  as
  3  select 1, 3, date '2010-10-10' from dual union all
  4  select 2, 0, date '2010-09-09' from dual union all
  5  select 3, 3, date '2010-08-08' from dual union all
  6  select 4, 3, date '2010-11-11' from dual union all
  7  select 5, 0, date '2010-06-06' from dual union all
  8  select 6, 1, date '2010-05-05' from dual
  9  /

Table created.

SQL> select max(id) keep (dense_rank last order by date_vote) id
  2       , id_idea
  3       , max(date_vote) date_vote
  4    from vote
  5   group by id_idea
  6  /

        ID    ID_IDEA DATE_VOTE
---------- ---------- -------------------
         2          0 09-09-2010 00:00:00
         6          1 05-05-2010 00:00:00
         4          3 11-11-2010 00:00:00

3 rows selected.

与分析变体相比：

1)它起作用了(如果你去掉了'AS‘中的'AS’，那么分析的那个也能起作用)

2)更短

3)更清晰(好吧，这是主观的)

4)它的性能稍微好一点，请看：

以下是聚合查询的计划：

Execution Plan
----------------------------------------------------------
Plan hash value: 2103353780

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    39 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |     3 |    39 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| VOTE |     6 |    78 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

这是分析查询的计划：

Execution Plan
----------------------------------------------------------
Plan hash value: 781916126

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

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

   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "ID_IDEA" ORDER BY
              INTERNAL_FUNCTION("DATE_VOTE") DESC  NULLS LAST)<=1)

问候你，罗伯。

Answer 3

我通常使用first or last function来做这件事。它有一种奇怪的结构，这可能解释了为什么它不经常被使用。请注意，只要order by子句是确定性的，那么max/min就不重要(但这是必需的，因为这是构造函数的方式。

select 
  max(id) keep (dense_rank last order by date_vote) as id,
  id_idea,
  max(date_vote)
  from vote
group by id_idea