Oracle SQL组由单个字段组成,并计算分组的行数

考虑这个表some_table：

+--------+----------+---------------------+-------+
| id     | other_id | date_value          | value |
+--------+----------+---------------------+-------+
| 1      | 1        | 2011-04-20 21:03:05 | 104   |
| 2      | 1        | 2011-04-20 21:03:04 | 229   |
| 3      | 3        | 2011-04-20 21:03:03 | 130   |
| 4      | 1        | 2011-04-20 21:02:09 | 97    |
| 5      | 2        | 2011-04-20 21:02:08 | 65    |
| 6      | 3        | 2011-04-20 21:02:07 | 101   |
| ...    | ...      | ...                 | ...   |
+--------+----------+---------------------+-------+

我想通过other_id选择和分组,以便我只获得唯一的other_ids.此查询有效(信用@MichaelPakhantsov)：

select id,other_id,date_value,value from
 (
   SELECT id,value,ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r
   FROM some_table 
 )
 where r = 1

如何为每个other_id获得相同的结果,但是计算了分组的行数.期望的结果如下：

+--------+----------+---------------------+-------+-------+
| id     | other_id | date_value          | value | count |
+--------+----------+---------------------+-------+-------+
| 1      | 1        | 2011-04-20 21:03:05 | 104   | 3     |
| 5      | 2        | 2011-04-20 21:02:08 | 65    | 2     |
| 3      | 3        | 2011-04-20 21:03:03 | 130   | 2     |
+--------+----------+---------------------+-------+-------+

我已尝试在内部和外部选择中使用COUNT(other_id),但它会产生此错误：

ORA-00937: not a single-group group function

注意：类似于this question(示例表和从那里获取的答案),但该问题没有给出折叠行的计数.

解决方法

添加一个

count(*) OVER (partition by other_id) cnt

到内部查询

select id,cnt from
 (
   SELECT id,ROW_NUMBER() OVER (partition by other_id order BY Date_Value desc) r,count(*) OVER (partition by other_id) cnt
   FROM some_table 
 )
 where r = 1

Oracle SQL组由单个字段组成,并计算分组的行数

解决方法

猜你在找的Oracle相关文章