如何选择每个日期间隔的唯一访问者？ [英] 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））; 
} 
 
 
？> 
  

此查询使用：

• DATE（）从您的数据中获取日期和
  
• COUNT（）来计算特定日期的总数据。
  
• 和GROUP BY根据您选择的字段对您的数据进行分组。

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屋！