mysql - 如何创建日期序列？

我想计算数据集中每天的操作次数。

date         action_id
2010-01-01   id00
2010-01-03   id01
2010-01-05   id02

这只是一个示例，但要点是我的数据不包括每天的操作，而我想包括结果中有零个操作的天。

我的计划是这样做。

with dates as (
select [sequence of dates from 2010-01-01 to 2010-02-01] as day)

select day, coalesce(count(distinct action_id), 0) as actions
from dates
left join my_table
on dates.date = my_table.date

如何创建日期序列？

最佳答案

您的示例显示了CTE。因此，您可以使用递归CTE：

with recursive dates as (
      select date('2010-01-01') as day
      union all
      select day + interval 1 day
      from dates
      where day < '2010-02-01'
     )
select d.day, count(distinct t.action_id) as actions
from dates d left join
     my_table t
     on d.day = my_table.date
group by d.day;

请注意，COUNT()从不返回NULL，因此COALESCE()是不必要的。

在旧版本中，您可以使用日历表或即时生成数据。假设您的表有足够的行：

select d.day, count(distinct t.action_id) as actions
from (select date('2010-01-01') + interval (@rn := @rn + 1) - 1 day as day
  from my_table cross join
       (select @rn := 0) params
  limit 31
 ) d left join
     my_table t
     on d.day = my_table.date
group by d.day;

关于mysql - 如何创建日期序列？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55799000/