获取每天的总观看次数，包括离开“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屋！