SQL query to find the number of numbers in the running total

Suppose the table has 1 column ID and the values are as below:

ID
5
5
5
6
5
5
6
6

the output should be

ID  count
5    3
6    1
5    2
6    2

How can we do that in a single SQL query.

In relational databases data in the table has no any order, see this: https://en.wikipedia.org/wiki/Table_(database)

the database system does not guarantee any ordering of the rows unless an ORDER BY clause is specified in the SELECT statement that queries the table.

therefore, in order to get desired results, you must have an additional colum in the table that defines an order of rows (and can by used in ORDER BY
clause).

In the below examle cn
column defines such an order:

select * from tab123 ORDER BY rn;

        RN ID
---------- -------
         1 5
         2 5
         3 5
         4 6
         5 5
         6 5
         7 6
         8 6

Starting from Oracle version 12c new MATCH_REGOGNIZE
clause can be used:

select * from tab123
match_recognize(
   order by rn
   measures
     strt.id as id,
     count(*) as cnt
   one row per match
   after match skip past last row
   pattern( strt ss* )
   define ss as ss.id = prev( ss.id )
);

On earlier versions that support windows function (Oracle 10 and above) you can use two windows functions: LAG ... over
and SUM ... over
, in this way

select max( id ) as id, count(*) as cnt
FROM (
    select id, sum( xxx ) over (order by rn ) as yyy
    from (
        select t.*,
               case lag( id ) over (order by rn )
               when id then 0 else 1 end as xxx
        from tab123 t
    )
)
GROUP BY yyy
ORDER BY yyy;
Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » SQL query to find the number of numbers in the running total

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录