CODEWITHSUNDEEP
mysqlsqlmariadbwindow-functions
Edamame
Edamame

Reputation: 25376

Error Code: 1235. This version of MariaDB doesn't yet support 'COUNT(DISTINCT) aggregate as window function'

I have the following table on a MariaDB:

date              name

2018-01-02        John
2018-01-03        Mary
2018-01-05        Peter
2018-01-06        John
2018-01-07        Ann
2018-01-08        Ann
2018-01-09        John
2018-01-10        Peter

And I want to compute the cumulated_name_count on each day. i.e. I am expecting my results like:

date        cumulated_name_count

2018-01-02        1
2018-01-03        2
2018-01-04        2
2018-01-05        3
2018-01-06        3
2018-01-07        4
2018-01-08        4
2018-01-09        4
2018-01-10        4

To achieve this, I am using the following code with window function:

select date, 
  count(distinct name) over (order by time rows unbounded preceding) 
  as cumulated_name_count from myDB.myTable

However, I got the following error:

Error Code: 1235. This version of MariaDB doesn't yet support 'COUNT(DISTINCT) aggregate as window function'

Is there a way to avoid such error and get the cumulated count over a window function? Thanks!

Upvotes: 2

Views: 1909

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

You can do this with nested window functions:

select date, 
       sum( seqnum = 1 ) over (order by time rows unbounded preceding)
              as cumulated_name_count
    from (select  t.*,
                  row_number() over (partition by name order by time)
                        as seqnum
              from myDB.myTable t
         ) t2;

The subquery enumerates each name value by time. The outer query then counts the number of "1" up to each record.

Upvotes: 1

Related Questions