我有查询
SELECT FROM_UNIXTIME(1800 * FLOOR(date/1800)) AS period_start,
COUNT(*) AS count
FROM readed_messages
GROUP BY period_start
ORDER BY period_start ASC
如果记录不存在,如何添加子句if/else
。我想得到命令返回0和命令返回计数。我不知道该怎么做
生成丢失的数据是一个恼人的问题。通常最好留给应用程序级别。
做不到这一点,如果你有在MySQL中这样做,丢失的数据必须来自某个地方。这可以是由用户变量和union交叉连接构建的动态数字序列,或者如果我们知道我们的域值(就像我们在这种情况下所做的),我们可以预先构建一些表来处理它。
在这种情况下,您正在操作日期,因此我们需要一些表来帮助我们生成您感兴趣的所有值。我建议这些表格:
-
calendar_years(y integer)
-
calendar_months(m integer)
-
calendar_days(d integer)
-
calendar_hours(h integer)
-
calendar_minutes(m integer)
这些表都是单列,包含您正在查看的值的范围。calendar_minutes
可以只把0
和30
作为值,因为我们只看半小时的增量。
这是一个实例,cross join
实际上是我们想要的。
select unix_timestamp(concat(y, '-', lpad(m, 2, '0'), '-', lpad(d, 2, '0'), ' ', lpad(h, 2, '0'), ':', lpad(mm, 2, '0'), ':', '00')) tt
from calendar_years
cross join calendar_months
cross join calendar_days
cross join calendar_hours
cross join calendar_minutes
现在将为我们提供表值的所有可能组合,或者本质上-我们感兴趣的每个时期。
我们使用concat
和lpad
将这些列转换为有效的datetime
字符串,然后使用unix_timestamp
将其转换为时间戳。
这给了我们一组左连接到readed_messages
的值,这将给我们最终的答案:
select from_unixtime(tt), count(id)
from (
select unix_timestamp(concat(y, '-', lpad(m, 2, '0'), '-', lpad(d, 2, '0'), ' ', lpad(h, 2, '0'), ':', lpad(mm, 2, '0'), ':', '00')) tt
from calendar_years
cross join calendar_months
cross join calendar_days
cross join calendar_hours
cross join calendar_minutes
) q
left join readed_messages rm
on (1800 * floor(`date` / 1800)) = q.tt
where tt <> 0 -- this method is brute force, generates some invalid dates
group by tt
显然,这将为我们提供许多值,可能在一段时间内远远超过您感兴趣的时间,如果您愿意,您可以在where
子句中进一步过滤它。
这里有一个演示