日期的连续性在数据库中（找到缺失数据的差距） [英] Date continuity in a database (find missing date gaps)

问题描述

我有用户谁支付每月支付的数据库。我需要检查是否有这些款项的连续性。 例如下表中：

I have a database of users who pay monthly payment. I need to check if there is continuity in these payments. For example in the table below:

+---------+------------+
| user_id |    date    |
+---------+------------+
|       1 | 2015-02-01 |
|       2 | 2015-02-01 |
|       3 | 2015-02-01 |
|       1 | 2015-03-01 |
|       2 | 2015-03-01 |
|       3 | 2015-03-01 |
|       4 | 2015-03-01 |
|       1 | 2015-04-01 |
|       2 | 2015-04-01 |
|       3 | 2015-04-01 |
|       4 | 2015-04-01 |
|       5 | 2015-04-01 |
|       1 | 2015-05-01 |
|       2 | 2015-05-01 |
|       3 | 2015-05-01 |
|       4 | 2015-05-01 |
|       5 | 2015-05-01 |
|       1 | 2015-06-01 |
|       2 | 2015-06-01 |
|       3 | 2015-06-01 |
|       5 | 2015-06-01 |
|       3 | 2015-07-01 |
|       4 | 2015-07-01 |
|       5 | 2015-07-01 |
+---------+------------+

直到5月一切正常，但6月4用户没有支付他虽然在接下来的一个月（7月）支付。 七月用户1和2没有支付，但这是正常的，因为他们可以辞去服务。 因此，在这种情况下，我需要有信息的用户4没有支付六月。 是否有可能做到这一点使用SQL？

Until May everything was ok, but in June user 4 didn't pay although he paid in the next month (July). In July users 1 and 2 didn't pay, but this is ok, because they could resign from the service. So in this case I need to have information "User 4 didn't pay in June". Is it possible to do that using SQL?

我使用的MS Access，如果有必要的信息。

I use MS Access if it's necessary information.

推荐答案

我已经写了一个简单的查询，但我知道，这不是最好的解决办法。其他的解决方案仍然是值得欢迎的。

I have written a simple query for this but I realize that it's not the best solution. Other solutions are still welcome.

SELECT user_id,
       MIN(date) AS min_date,
       MAX(date) AS max_date,
       COUNT(*) AS no_of_records,
       round((MAX(date)-MIN(date))/30.4+1,0) AS months,
       (months-no_of_records) AS diff
FROM test
GROUP BY user_id
HAVING (round((MAX(date)-MIN(date))/30.4+1,0)-COUNT(*)) > 0
ORDER BY 6 DESC;

现在我们可以来看看列no_of_records和月。如果它们不相等，有此用户的间隙。

Now we can take a look at columns "no_of_records" and "months". If they are not equal, there was a gap for this user.

这篇关于日期的连续性在数据库中（找到缺失数据的差距）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！