代码之家 › 专栏 › 技术社区 › Edamame

错误代码:1235。此版本的MariaDB尚不支持“COUNT(DISTINCT)aggregate as window function”

window-functions mariadb sql mysql

Edamame · 技术社区 · 6 年前

我在马里亚行有一张桌子:

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

我想计算每天累积的名字数。i、 e.我期望我的结果如下:

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

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

但是,我得到了以下错误:

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

有没有一种方法可以避免这种错误,并通过窗口函数获得累积计数?谢谢!

1 回复 | 直到 6 年前

Rick James diyism 5 年前

可以使用嵌套窗口函数执行此操作:

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;

子查询枚举每个 name