sql - 缺少某些日子时，获取每日平均公关月？

Question

我有此查询查找每月的每日平均值：

select avg(d.dayilyCount)
from (
select count(*) as dayilyCount, timestamp as dt, strftime('%d-%m-%Y', timestamp / 1000, 'unixepoch', 'localtime') as dmy 
from T group by dmy
) d 
group by strftime('%m-%Y', d.dt / 1000, 'unixepoch', 'localtime')

问题在于，它会占空的天数。我该如何解决？

例如：如果一月份有28天，而只有20天有数据。它只会计算并平均20天，而缺少8天。

Answer 1

SELECT
  SUM(d.dayilyCount) / (SELECT
    julianday('now', 'start of month', '+1 month', '-1 day') - julianday('now', 'start of month') + 1) as mon_avg
FROM (SELECT
  COUNT(*) AS dayilyCount,
  timestamp AS dt,
  strftime('%d-%m-%Y', timestamp / 1000, 'unixepoch', 'localtime') AS dmy
FROM T
GROUP BY dmy) d
GROUP BY strftime('%m-%Y', d.dt / 1000, 'unixepoch', 'localtime');