mysql - 按月对日志进行分组总计

Question

我有一个日志表，按 act_datetime、app_id、location_id、media_id 和 media_type_id 存储媒体请求。我想要的是每个结果集行包含每个月的类型总计。例如，日志记录包含:

我尝试使用临时表按 app_id 提取记录并按月分组，但每个总计都得到多行。我可以使用子查询，但如何获取每个月按类型划分的总行数？

非常感谢任何帮助。

谢谢

布兰登

编辑

以下代码结合了共同的想法:

此查询大约需要 13 秒来解析大约 810 万行。这是可以接受的吗？最后，如何将 2018 年 1 月的日期显示为一列？由于日期也用在组和排序依据子句中，因此在转换为字符串时出现错误。

我还想尝试代码构造 sum( case when media_type_id = 1 then 1 else 0 end )...以查看是否获得相同的结果和速度。

感谢大家的帮助!

Answer 1

假设这是 SQL Server，而不是 MySQL:

SELECT DATEPART(MONTH, act_datetime) AS [Month],
       COUNT(CASE WHEN app_id = 14 AND media_type_id = 1 AND location_id = 1 THEN act_datetime END) AS MP3_Messages_MO,
       COUNT(CASE WHEN app_id = 14 AND media_type_id = 1 AND location_id = 2 THEN act_datetime END) AS MP3_Messages_FL,
       COUNT(CASE WHEN app_id = 14 AND media_type_id = 3 AND location_id = 1 THEN act_datetime END) AS MP3_Messages_MO,
       COUNT(CASE WHEN app_id = 14 AND media_type_id = 3 AND location_id = 2 THEN act_datetime END) AS MP3_Messages_FL,
       COUNT(CASE WHEN app_id = 55 AND media_type_id = 1 THEN act_datetime END) AS MP3_Music,
       COUNT(CASE WHEN app_id = 55 AND media_type_id = 9 THEN act_datetime END) AS ZIP_Music
FROM YourTable
GROUP BY DATEPART(MONTH, act_datetime);

请注意，您没有包含不同年份的逻辑，无论年份如何，每个月的数据都会进行计数。

由于缺乏可用数据，这也完全未经测试。