sql - Oracle SQL 按单个字段分组并计算分组的行数

Question

考虑这张表 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_id s。此查询有效(信用@MichaelPakhantsov):

select id, other_id, date_value, value from
 (
   SELECT id, other_id, date_value, 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 (示例表格和从那里获取的答案)但该问题没有给出折叠行的计数。

Answer 1

添加一个

count(*) OVER (partition by other_id) cnt

到内部查询

select id, other_id, date_value, value, cnt from
 (
   SELECT id, other_id, date_value, value, 
   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