选择缺失的行并按日期分组(对数据库具有只读访问权限) [英] Selecting missing rows and grouping by date (with read-only access to DB)

问题描述

我有一个数据集，我需要从中计算每天分组的所有行出现次数，并将它们汇总为以下格式的数据集:

I have a dataset from which I need to count all row occurrences grouping by each day and sum them into a dataset of following format:

|     date     |  count  |
|  2001-01-01  |    11   |
|  2001-01-02  |    0    |
|  2001-01-03  |    4    |

问题是，某些时间段缺少某些数据，因此应创建新日期以使其计数为零.考虑到同样的问题，我搜索了各种主题，从他们那里我了解到可以通过创建一个临时日历表来保存所有日期并将结果数据集与日期表连接来解决.

The problem is, that some of the data is missing from certain periods of time and new dates should be created to have the count of zero. I have searched various topics considering this same issue and from them I've learned that it's possible to solve by creating a temporary calendar table to hold all the dates and join the result dataset with the date table.

不过，我对正在使用的数据库只有读取权限，因此我无法创建单独的日历表.那么这是否可以仅在单个查询中解决?如果没有，我总是可以在 PHP 中做到这一点，但我更喜欢更直接的方式来做到这一点.

Though, I have only a read access to the database I'm using, so it's not possible for me to create a separate calendar table. So could this be possible to solve in a single query only? If not, I could always do this in PHP but I would prefer a more straighforward way to do this.

只是根据评论中提出的问题进行澄清:对于特定的用户给定的时间范围，需要缺少的日期.例如.查询可以是:

Just to clarify based on the questions asked in the comments: The missing dates are required for a spesific, user given time frame. E.g. the query could be:

SELECT date(timestamp), count(distinct(id)) 
FROM 'table' 
WHERE date(timestamp) BETWEEN date("2001-01-01") AND date("2001-12-31")
GROUP BY date(timestamp)

推荐答案

SQL 真的不是为这种工作制作的 :/

这是可能的，但真的很麻烦，我强烈不鼓励您这样做.

That's possible but really really messy and I strongly discourage you from doing it.

最简单的方法是拥有一个单独的日历表，但正如您所说，您只有对数据库的读取权限.

The easiest way was to have a separate calendar table but as you said you only have a read access to your database.

另一种是使用这种技巧生成序列:

The other one is to generate the sequence using this kind of trick:

SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, ("yourquery") t;

我不会进入它，正如我已经告诉过你的那样，它真的很丑:(

I won't get into it, as I already told you, it's really ugly :(

这篇关于选择缺失的行并按日期分组(对数据库具有只读访问权限)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！