如何选择每个日期间隔的唯一访问者? [英] How to select unique visitors per date interval?
问题描述
我找到了一些与我正在查找的查询字符串有关的信息,但没有发现任何信息。基本上,我有一个图表,需要在每个时间间隔(小时,天,月或年)显示唯一访问者。因此,类似于Google Analytics或此Google Analytics图表的图表。该表具有访客ID列和时间戳列(风格为0000-00-00 00:00:00)。我需要能够选择每个区间的唯一或总访问者(例如,5日的32位访客,6日的30位等)。
我猜我的问题是,无论如何,只要查询就能有效地做到这一点?或者我需要使用PHP来获取所有数据(问题在于我必须对图上的每个点执行查询,效率不高)?
您需要汇总数据。试试这个查询:
$ p $ SELECT DATE(data_timestamp),COUNT(visitor_id)
FROM analytics_table
WHERE DATE (data_timestamp)BETWEEN'2011-05-01'AND'2011-05-31'
GROUP BY 1
以下是填充每月数据的方式( WARNING:UNTESTED!):
<?php
$ sql =SELECT DATE(data_timestamp),COUNT(visitor_id)
FROM analytics_table
WHERE DATE(data_timestamp)BETWEEN'2011-05-01'AND'2011-05 -31'
GROUP BY 1;
$ rs = mysql_query($ rs);
$ date1 = $ datex ='2011-05-01';
$ date2 ='2011-05-31';
$ arrayData = $ tmpArray = array();
while($ r = mysql_fetch_array($ rs))
{
$ tmpArray [$ r ['date']] = $ r ['count'];
while($ datex <= $ date2)
{
if(isset($ tmpArray [$ datex]))
{
$ arrayData [$ datex] = $ tmpArray [$ datex];
}
else
{
$ arrayData [$ datex] = 0;
}
list($ y,$ m,$ d)= explode(' - ',$ datex);
$ datex = date('Y-m-d',mktime(0,0,0,$ m,$ d,$ y));
}
?>
此查询使用:
I've found a few query strings related to what I'm looking for, but nothing that does this. Basically, I have a graph that needs to display unique visitors per time interval (hour, day, month, or year). So, a graph similar to Google Analytics or this Analytics Graph. The table has a visitor ID column and a timestamp column (in the style 0000-00-00 00:00:00). I need to be able to select the unique or total visitors per interval (eg. 32 visitors on the 5th, 30 on the 6th, etc).
I guess my question is, is there anyway to do this efficiently with just a query? Or will I need to use PHP to get all the data (problem with that is that I would have to do a query for each point on the graph, not efficient)?
You will need to aggregate your data. Try this query:
SELECT DATE(data_timestamp), COUNT(visitor_id)
FROM analytics_table
WHERE DATE(data_timestamp) BETWEEN '2011-05-01' AND '2011-05-31'
GROUP BY 1
Here's how you populate your monthly data (WARNING: UNTESTED!):
<?php
$sql = "SELECT DATE(data_timestamp), COUNT(visitor_id)
FROM analytics_table
WHERE DATE(data_timestamp) BETWEEN '2011-05-01' AND '2011-05-31'
GROUP BY 1";
$rs = mysql_query($rs);
$date1 = $datex = '2011-05-01';
$date2 = '2011-05-31';
$arrayData = $tmpArray = array();
while( $r = mysql_fetch_array($rs) )
{
$tmpArray[$r['date']] = $r['count'];
}
while( $datex <= $date2)
{
if( isset($tmpArray[$datex]) )
{
$arrayData[$datex] = $tmpArray[$datex];
}
else
{
$arrayData[$datex] = 0;
}
list( $y, $m, $d) = explode('-', $datex);
$datex = date('Y-m-d', mktime(0, 0, 0, $m, $d, $y));
}
?>
this query use:
- DATE() to get the date from your data and
- COUNT() to count total data from that particular date.
- and GROUP BY to group your data based on what field you select.
这篇关于如何选择每个日期间隔的唯一访问者?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!