获取每天的总观看次数,包括离开“0视图”一天没有记录 [英] Get total views per day including leaving "0 views" for a day that has no record
问题描述
我正在创建一个图形,每天可以获得一定范围的总体视图,或者只要返回。
我遇到的问题是填写默认值 0
当某天没有任何视图时,有一天可能绝对没有任何视图,所以我需要MySQL返回默认值 0
当没有找到 - 我不知道该怎么做。
这是我使用的查询获取每天的总浏览量:
SELECT DATE(FROM_UNIXTIME(v.date))AS date_views,
COUNT v.view_id)AS total_views
FROM
(
视图v
)
GROUP BY date_views
ORDER BY v.date DESC
我的结果返回:
+ ------------ + ------------- +
| date_views | total_views |
+ ------------ + ------------- +
| 2012-10-17 | 2 |
| 2012-10-15 | 5 |
| 2012-10-14 | 1 |
| 2012-10-10 | 7 |
+ ------------ + ------------- +
但是,我想要返回 0
的缺少日子,因为 2012-10-16
, 2012-10-11
, 2012-10-12
, 2012-10-13
不包括在内。
所以,例如:
+ ------------ + ------------- +
| date_views | total_views |
+ ------------ + ------------- +
| 2012-10-17 | 2 |
| 2012-10-16 | 0 |
| 2012-10-15 | 5 |
| 2012-10-14 | 1 |
| 2012-10-13 | 0 |
| 2012-10-12 | 0 |
| 2012-10-11 | 0 |
| 2012-10-10 | 7 |
+ ------------ + ------------- +
将被退回。
如何处理?
几年前我做了这个,我创建了一个空数组,日期为key,默认值为0.然后我简单地循环结果att更改了这些日期的值每个$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ]] = $ row ['value'];
}
I am creating a graph where I can get the total views everyday for a certain range, or as long it goes back.
The problem I am having is to fill a default number of 0
when no views has been made for a certain day, some days there may be absolutely no views in a day so I need MySQL to return a default of 0
when none is found - I have no idea how to do this.
This is the query I use to get the total views a day:
SELECT DATE(FROM_UNIXTIME(v.date)) AS date_views,
COUNT(v.view_id) AS total_views
FROM
(
views v
)
GROUP BY date_views
ORDER BY v.date DESC
My results return this:
+------------+-------------+
| date_views | total_views |
+------------+-------------+
| 2012-10-17 | 2 |
| 2012-10-15 | 5 |
| 2012-10-14 | 1 |
| 2012-10-10 | 7 |
+------------+-------------+
However there are missing days that I want to return 0
for it, as 2012-10-16
, 2012-10-11
, 2012-10-12
, 2012-10-13
is not included.
So, for example:
+------------+-------------+
| date_views | total_views |
+------------+-------------+
| 2012-10-17 | 2 |
| 2012-10-16 | 0 |
| 2012-10-15 | 5 |
| 2012-10-14 | 1 |
| 2012-10-13 | 0 |
| 2012-10-12 | 0 |
| 2012-10-11 | 0 |
| 2012-10-10 | 7 |
+------------+-------------+
Would be returned.
How would this be approached?
When I did this a couple of years ago I created an empty array with the date as key and the default value 0. Then I simply looped through the result att changed the value for those dates I had.
for each($result as $row){
$date_stats_array[$row['date']] = $row['value'];
}
这篇关于获取每天的总观看次数,包括离开“0视图”一天没有记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!