时间戳

首页 > 解决方案 > 如何聚合序列中具有相同值的行数？

sql - 如何聚合序列中具有相同值的行数？

问题描述

我有一个查询返回以下示例中的数据：

SELECT timestamp, atm_id FROM TRANSACTIONS ORDER BY TIMESTAMP ASC;

输出

TIMESTAMP  | ATM_ID | 
--------------------
2010-01-01 | EP02   |
2010-01-01 | EP02   |
2010-01-28 | EP02   |
2010-02-07 | EP02   |
2010-02-09 | EP11   |
2010-03-19 | EP11   |
2010-03-19 | EP02   |
2010-04-03 | EP05   |
2010-04-30 | EP02   |

我知道如何按 ATM_ID 分组并将计数放在每个前面

SELECT
   ATM_ID,
   COUNT(*) CNT
FROM
   TRANSACTIONS
GROUP BY
   ATM_ID;

根据上面的示例数据，这将产生类似

 ATM_ID |  CNT  
---------------
 EP02   |  6
 EP11   |  2
 EP05   |  1

但是，我对不同级别的分组感兴趣。如果某个 ATM_ID 在连续行中重复，则输出中应包含按顺序具有相同 ATM_ID 的行数，即使相同的 ATM_ID 稍后出现在不同的 ATM_ID 之后

期望的输出

 ATM_ID |  CNT  
---------------
 EP02   |  4         --Four rows of ATM_ID EP02
 EP11   |  2         --Followed by 2 rows of ATM_ID EP11
 EP02   |  1         --Followed by 1 row of ATM_ID EP02
 EP05   |  1         --Followed by 1 row of ATM_ID EP05
 EP02   |  1         --Followed by 1 row of ATM_ID EP02

忽略右边的评论，这些只是为了澄清，不是输出的一部分。那可能吗？

PS：Syed Aladeen 下面的答案给出了正确计数的输出，但顺序错误。为了方便起见，我创建了一个 SQL 小提琴：

SQL小提琴

标签： sqloraclegroup-by

解决方案

试试这个

select atm_id , count(*) 
from (select TRANSACTIONS.*,
     (row_number() over (order by id) -
      row_number() over (partition by atm_id order by id)
     ) as grp
from TRANSACTIONS
) TRANSACTIONS
group by grp, atm_id order by max(id)

推荐阅读