查询数据以创建统计图表

Question

我有一个表，其中包含这些字段id、title、d细目、date_created、type、website_id。我希望在给定的时间内查询数据，并获得以下情况(我的意见)：

• 如果月份和年份处于起始时间，则结束时间相等，我将查询该月份的每周警告。(例如，01/07/2019-31/07/2019)
• 如果两者是同一年，我将查询每月警告，这意味着我将计数的警告数量从一个月到月底。(例如，1/2019-9/2019)
• 最后一种情况几乎与上述情况相似，但时间较长。(例如，12/2018-3/2019)

以上案例的例子：

• 时间:01/07/2019-31/07/2019，结果： 01/07/2019-07/07/2019:3警告08/07/2019-14/07/2019:0警告15/07/2019 - 21/07/2019: 1警告22/07/2019-28/07/2019:2警告29/07/2019 - 31/07/2019: 0
• 判例2，时间:1/2019-6/2019 1/2019: 1警告2/2019: 3警告3/2019: 0警告4/2019: 1警告5/2019: 2警告6/2019: 0警告

这是我的解决方案，但我不能编写SQL查询。我需要3个SQL查询3的情况和修复我的解决方案，如果可能的话。

Answer 1

首先，我们知道我们需要选择警告，然后根据不同的时间对它们进行分组。首先，我要按日历周分组。

一个月内所有的警告：

SELECT `id` from warnings WHERE date_created >= 2019-07-01 AND date_created <= 2019-07-31;

要得到一个月中有多少警告，几乎是一样的：

SELECT count(`id`) from warnings WHERE date_created >= 2019-07-01 AND date_created <= 2019-07-31;

它将返回一行，其中包含一个值。还不是很有趣。要了解每个(日历)周发生了多少次警告，可以按周将结果分组。

SELECT count(`id`) as num_warnings, WEEK(date_created) as weeknum
FROM warning
WHERE `date_created` >= 2019-07-01 AND date_created <= 2019-07-31
GROUP BY weeknum;

这将给出日历周中的警告数量。如果这个月从星期五开始，那么第一周的人数就会很低。

从一个月的第一天开始，每隔七天查询一次，事情就变得复杂多了。(而且，很明显，最后一周不会是整整七天。)

为了提供帮助，我首先提到了SELECT / GROUP BY - segments of time (10 seconds, 30 seconds, etc)，它讨论了几秒钟的分组问题。一周是60*60*24*7秒，所以答案可以很容易地转换--但是我们会遇到一个陷阱。

SELECT count(`id`) as num_warnings as weeknum
FROM warning
WHERE `date_created` >= 2019-07-01 AND date_created <= 2019-07-31
GROUP BY UNIX_TIMESTAMP(date_created) DIV 604800

这将获取警告的时间戳，并将其除以一周内的秒数，并从小数点中删除。所以每隔604800秒，除法就会增加1。就快到了，但问题是:这会告诉你自1973年1月1日以来已经过了多少周了，你想知道从这个月的第一个月开始已经过了多少个星期了。换句话说，你希望零位出现在月初，而不是1973年。

SELECT count(`id`) as num_warnings
FROM warnings
WHERE `date_created` >= 2019-07-01 AND date_created <= 2019-07-31
GROUP BY (UNIX_TIMESTAMP(date_created) - UNIX_TIMESTAMP('2019-07-01')) DIV 604800

差不多就是把一个月除以几个星期了。我对Django几乎一无所知，所以我无法帮助您编写生成查询的代码。

但是把一年除以几个月怎么样？起初，这似乎是一个类似的问题，但有一个问题:一个月有多少秒？

一年中按月分组的答案实际上更像上面按周划分的最初解决方案。它之所以有效，是因为一年总是从一个月初开始：

SELECT count(`id`) as num_warnings, MONTH(date_created) as monthNum
FROM warnings
WHERE `date_created` >= 2019-01-01 AND date_created <= 2019-12-31
GROUP BY monthNum;

应该能让你接近你想去的地方。

这两个查询非常不同，因此您需要识别Django代码中的不同情况，并构建适当的查询。