如何通过Laravel统计独特的访客并将其显示在图表中？

I want to make a overview of my unique visitors (a bit like cloudflare does) via Laravel.

我想通过Laravel概述我的独特访客(有点像cloudflare)。

What I already have:

我已经拥有的东西:

I have a database sheme, but I don't think that is is good:

我有一个数据库sheme,但我不认为这是好的:

I want to store as much as possible data into my database, but not the place, which browser etc...

我想尽可能多地将数据存储到我的数据库中,但不是存储地点,哪个浏览器等...

I want to be able to view all visitors (unique, so not twice the same IP in one day) per day, so count the IP adressess, and that the whole month long.

我希望能够每天查看所有访问者(唯一的,因此一天内不是同一个IP的两倍),因此请计算IP地址,以及整个月。

For example:

The month April needs to be displayed in my view like this:

4月份需要在我的视图中显示如下:

['1/04', 5],
['2/04', 58],
['3/04', 496],
['4/04', 654],
['5/04', 854],
['6/04', 756],
['7/04', 254],
['8/04', 887],
['9/04', 452],
['10/04', 985],
['11/04', 745],
['12/04', 120],
['13/04', 985],
['14/04', 745],
['15/04', 321],
['16/04', 852],
['17/04', 753],
['18/04', 951],
['19/04', 791],
['20/04', 167],
['21/04', 761],
['22/04', 349],
['23/04', 741],
['24/04', 258],
['25/04', 963],
['26/04', 317],
['27/04', 482],
['28/04', 341],
['29/04', 654],
['30/04', 751]

So every day the number of IP's must been counted and that for a month long, wich will depends on the currend month.

因此,每天必须计算IP的数量,并且持续一个月,取决于当前月份。

The output will look simmular to this:

输出看起来与此类似:

I would like to receive an controller, view and database sheme, if possible. I searched already the whole internet, but didn't found anything... If someone could help me, I would be very pleased!

如果可能的话,我想收到一个控制器,视图和数据库sheme。我已经搜索了整个互联网,但没有找到任何东西......如果有人能帮助我,我会非常高兴!

Kindest regards,

Robin

2 个解决方案

#1

If I understand your goal correctly, this is the way I would solve the issue.

如果我正确理解你的目标,这就是我解决问题的方法。

Make these changes to your database:

对数据库进行以下更改:

1) Remove the hits field.

2) Store dates in a YYYY-MM-DD format. 

Then check for an existing record in the database or create a new one when the new user logins or requests a page (you should probably cache this reduce hits on the DB):

然后检查数据库中的现有记录,或者在新用户登录或请求页面时创建一个新记录(您应该在数据库上缓存此减少命中):

$visitor = Visit::firstOrCreate(['date'=>$current_date,'ip'=>$ip_address);

Then when it is time to retrieve the data for your view you can call:

然后,当需要检索视图的数据时,您可以调用:

$visits = Visit::selectRaw('date count(date) as hits'))->groupBy('date')->whereRaw("`date` between $begin and $end")->get();

This should get you going, but you'll have to work out the views and such on your own.

这应该让你前进,但你必须自己解决这些问题。

#2

The main problem of this architecture is that MySql is not good at aggregations. What you need is aggregated data inside time series. For this you can use Cassandra time series or a MongoDb approach.

这种架构的主要问题是MySql不擅长聚合。您需要的是时间序列内的汇总数据。为此,您可以使用Cassandra时间序列或MongoDb方法。

When a new unique ip is inserted in db following nested inserts are performed:

在执行嵌套插入后,在db中插入新的唯一ip时:

1. Number of hits for current year is increased

当年的点击次数增加

2. Number of hits for current month is increased

当月的点击次数增加

3. Number of hits for current week is increased

本周的点击次数增加

4. Number of hits for current day is increased

当天的点击次数增加

5. Number of hits for current hour is increased

当前小时的点击次数增加

6. Number of hits for current minute is increased.

当前分钟的命中数增加。

For instance when a new hit is recorded at 2015-06-14 23:11:23 in a MongoDb strategy:

例如,在2015-06-14 23:11:23在MongoDb策略中记录新的命中时:

{
_id : MongoId(),
year : 2015,
hits : ++,
months : {
    6 : {
        hits : ++,
        days : {
            14 : {
                hits : ++,
                hours : {
                    23 : {
                        hits : ++,
                        minutes : {
                            11 : {
                                hits : ++
                            }
                        }
                    }
                }
            }
        }
    }
}
}

With this strategy you can query a date range limited by minute with a nice time complexity.

使用此策略,您可以查询受分钟限制的日期范围,并且时间复杂度很高。

You can also use Redis if you only need the recent data (for example only the last month). Which then you increment site hits for current_month, day_14_of_current_month, hour_23_of_current_day, minute_23_of_current_hour as your aggregation keys. Redis retrieves a single key with O(1) complexity, and multiple keys (for example day 0 to 5th of current month) with O(n).

如果您只需要最近的数据(例如仅上个月),也可以使用Redis。然后,您将current_month,day_14_of_current_month,hour_23_of_current_day,minute_23_of_current_hour的网站点击次数增加为您的汇总密钥。 Redis检索具有O(1)复杂度的单个密钥,以及具有O(n)的多个密钥(例如,当前月的第0到第5天)。

I have used the Redis approach to provide statistics for a bit.ly like website written in Laravel 5, after each request a rabbitmq message was being published with request ip data, multiple workers concurrently pop jobs, and insert the aggregation data into redis, so the statistics were near real time.

我使用Redis方法提供统计数据,就像在Laravel 5中编写的网站一样,在每个请求发出带有请求ip数据的rabbitmq消息,多个worker同时弹出作业,并将聚合数据插入redis之后,所以统计数据接近实时。