得到每日平均公关月时，有些天是少了？

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');