mysql - 不能在 Lag() 中使用 Count(*)

Question

我有一个简单的查询，但它一直抛出“无效使用组函数”。当我删除“count(*)”时它工作正常。如何在不延迟使用的情况下获得计数？

select CreateDate as date, count(*) as count,
       lag(count(*), 1) over(order by CreateDate) as previous 
from contacts
group by createdate

标签： mysqlsql

Answer 1

嗯。. . MySQL 应该允许将聚合函数与窗口函数一起使用。也许解析器中有一个错误。

我认为这会起作用：

select d.*, lag(cnt) over (order by cnt) as previous 
from (select CreateDate as date, count(*) as cnt
      from contacts
      group by CreateDate
     ) d;