php - 获取特定页面 ID 的唯一访客计数 - php

Question

使用 WordPress。我已经收集了每日浏览量(总浏览量和唯一浏览量)，现在想查看某些页面的每月统计信息。我可以获得总体 View ，但在获得独特 View 时遇到问题。我做了以下操作:

$ids = '181,57,123';
$certainPageIds = explode(',', $ids);
foreach($certainPageIds as $id){
    $uniqueViews = $wpdb->get_results("SELECT distinct IP as all_uniques, 
    DATE_FORMAT( insertion_date, '%b' ) as month_name, DATE_FORMAT( insertion_date, 
    '%Y-%M' ) as full_date FROM daily_unique_views WHERE page_id = '".$id."'");
    if($uniqueViews){
        $uniqueViewsEncodedArray = json_decode(json_encode($uniqueViews), True);
        var_dump($uniqueViewsEncodedArray);
    }
}

上面的数组返回以下内容:

array(2) {
  [0]=>
  array(3) {
    ["all_uniques"]=>
    string(13) "111.11.11.111"
    ["month_name"]=>
    string(3) "Dec"
    ["full_date"]=>
    string(13) "2016-December"
  }
  [1]=>
  array(3) {
    ["all_uniques"]=>
    string(13) "22.222.222.22"
    ["month_name"]=>
    string(3) "Dec"
    ["full_date"]=>
    string(13) "2016-December"
  }
}
array(2) {
  [0]=>
  array(3) {
    ["all_uniques"]=>
    string(13) "111.11.11.111"
    ["month_name"]=>
    string(3) "Nov"
    ["full_date"]=>
    string(13) "2016-November"
  }
  [1]=>
  array(3) {
    ["all_uniques"]=>
    string(13) "111.11.11.111"
    ["month_name"]=>
    string(3) "Dec"
    ["full_date"]=>
    string(13) "2016-December"
  }
  [2]=>
  array(3) {
    ["all_uniques"]=>
    string(12) "33.333.3.333"
    ["month_name"]=>
    string(3) "Dec"
    ["full_date"]=>
    string(13) "2016-December"
  }
}
array(1) {
  [0]=>
  array(3) {
    ["all_uniques"]=>
    string(13) "111.11.11.111"
    ["month_name"]=>
    string(3) "Oct"
    ["full_date"]=>
    string(13) "2016-October"
  }
}

因此它返回每个页面的每月独特 View 。但您会发现，111.11.11.111 出现了很多次。所以我 12 月的每个页面都有相同的 IP。但我需要每个月只计算一次相同的IP。我怎样才能达到这个目标？是否与现有结果有关或者应该用 sql 修复它？请问有什么想法吗？

Answer 1

您可以使用 cte(公共(public)表表达式)，如下所示:

DECLARE @ips TABLE(
    id int,
    ip varchar(3),
    month varchar(3)
)

INSERT INTO @ips VALUES (1,'ip1', 'jan')
INSERT INTO @ips VALUES (2,'ip2', 'jan')
INSERT INTO @ips VALUES (3,'ip1', 'feb')
INSERT INTO @ips VALUES (4,'ip2', 'feb')
INSERT INTO @ips VALUES (5, 'ip2', 'jan');

-- Define the CTE expression name and column list.
WITH cte_ips (ip, month)
AS
-- Define the CTE query.
(
    SELECT ip, month FROM @ips
)
-- count the results
SELECT COUNT(DISTINCT ip), COUNT(ip), month FROM Cte_ips GROUP BY Month



-- Define the outer query referencing the CTE name.
SELECT COUNT(DISTINCT IP) AS UniqueIPs, COUNT(IP) AS TotalIPs, month FROM cte_ips
GROUP BY month