如何设置MySQL表来跟踪统计数据？ [英] How to setup a MySQL Table to keep track of stats?
问题描述
我有一个新的系统Im建筑，存储人们在我的网站上搜索的内容的条目。
我希望能够保留关键字每天搜索多少次的记录
每天搜索一次，从中我可以每周和每月计算。
此时我有一个条目搜索短语的数量为
点击搜索词组已经获得，以及最后一次更新。
当我开始将程序取出时测试并进一步移动到
使用的工具我担心我的想法是切换到相同的搜索短语的可变的
条目将是一个坏主意，因为在
过去15天我存储了超过300万个独特的搜索短语，
和未知数量的点击。
所以应该我创建了一个滚动数据库，存储每个搜索，然后每周一次将数据转到另一个数据库，用户可以使用，或者
是他们的一个更好的方法，我只保留一个条目每个
搜索短语，我仍然能够记录每日搜索的数量
金额，以便我可以跟踪趋势等在我的网站上搜索阶段？
我计划有一个3-5个月的窗口，我可以查看搜索数据
信息。
I have a new system Im building that stores entries of what people are
searching for on my sites.
I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.
At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.
As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.
So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?
My plan to have a 3-5 month window that I can look at data on search
information.
推荐答案
>我希望能够记录关键字的次数
>I want to be able to keep records of how many times a keyword was
>每天搜索，从中我可以每周和每月计算。
此时我每个搜索短语有一个条目，其中搜索次数为
短语已经获得，并且最后一次更新。
当我开始将程序从测试中移除并进入更多使用的工具时我开始担心我的切换想法对于相同搜索短语的可变条目将是一个坏主意，因为在过去15天内我存储了更多en 300万个独特的搜索短语，
和未知数量的点击。
我应该创建一个存储每个搜索的滚动数据库，然后将该数据滚动到另一个数据库一次一个星期，用户可以使用，或
是他们更好的方式，我只保留一个条目/
搜索短语，我仍然能够记录每日搜索量如何，以便我可以在我的网站的搜索阶段跟踪趋势等吗？
我计划有一个3-5个月的窗口，我可以查看搜索信息的数据。
>searched for daily, and from that I can calculate weekly and monthly.
At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.
As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.
So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?
My plan to have a 3-5 month window that I can look at data on search
information.
这很大程度上取决于你想要什么样的统计数据。
想想里程表的工作原理。给定车辆的计数只是
不断上升和上升（希望你使用的数据类型很大
足以让你避免翻转）。您可以通过减去
期间开头和结尾的读数来获得给定
期间的计数。
因此，如果您每月拍摄活动计数表的快照或
周或其他什么，您可以获得任何月份的计数。您可以
复制整个表格，或者有一个带有计数条目的表格
当前，1月1日，2月1日，3月1日......，
This depends a lot on what kind of statistics you want.
Think about how an odometer works. The count for a given car just
keeps going up and up and up (hopefully you use a data type big
enough so you avoid "rollover"). You get the counts in a given
period by subtracting the readings at the beginning and the end of
the period.
So, if you take snapshots of the active count table each month or
week or whatever, you can get counts for any month. You could
duplicate the whole table, or have one table with count entries
for current, Jan 1, Feb 1, Mar 1, ... .
我在IRC遇到了一个帮我解决问题的人。
这就是我们想出来的
表1：
ID |关键字
Tabl 2：
ID |数|日期时间
如果是新搜索，我们会将搜索短语存储到表1中，然后将
添加到表2中。如果是相同的话在同一天搜索
然后我们将添加到表2中的计数，否则我们在新的一天添加一个新条目
并开始计算。
然后我们将使用复制（我正在学习）将其复制到
另一个DB用于前端使用。
我将在上面的
表中使用BigInt（20），DateTime和VarChar（80）。不知道DateTime是否最适合跟踪天数，但它确实过去
运行良好。
听起来不错？
Gordon Burditt写道：
I ran into a guy in IRC that helped me get it worked out.
This is what we came up with
Table 1:
ID | Keyword
Tabl 2:
ID | Count | DateTime
If it is a new search we will store the search phrase into table 1, and
then add one hit to table 2. If its the same search on the same date
then we will add to the count in table 2, otherwise we add a new entry
for a new day and start the count over.
Then we will use replication (Im learning about that) to copy it to
another DB for front end usage.
Im going to use BigInt(20), DateTime, and VarChar(80) on the above
tables. Dont know if DateTime is best for tracking days or not, but it
has worked well in the past.
Sound good?
Gordon Burditt wrote:
我希望能够记录关键字的次数
每天搜索，从中我可以每周和每月计算。
此时我每个搜索短语有一个条目，其中包含
点击搜索词组已经获得，以及最后一次更新。
当我开始将程序从测试中移除并进入更多
二手工具我担心我的想法是切换到相同的搜索短语的可变的
条目将是一个坏主意，因为在
最后15我已经存储了超过300万个独特的搜索短语，
和未知数量的点击。
所以我应该制作一个滚动数据库来存储每次搜索然后每周一次将数据转到另一个数据库，用户可以使用，或者
是他们更好的方式，我只保留一个条目
搜索短语我仍然可以记录每日搜索的数量
金额，以便我可以在我的网站上搜索搜索阶段的趋势等？
我计划有一个3-5个月的窗口，我可以查看搜索数据
信息。
I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.
At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.
As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.
So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?
My plan to have a 3-5 month window that I can look at data on search
information.
这很大程度上取决于你想要什么样的统计数据。
想想里程表的工作原理。给定车辆的计数只是
不断上升和上升（希望你使用的数据类型很大
足以让你避免翻转）。您可以通过减去
期间开头和结尾的读数来获得给定
期间的计数。
因此，如果您每月拍摄活动计数表的快照或
周或其他什么，您可以获得任何月份的计数。你可以
复制整个表，或者有一个表有计数条目
当前，1月1日，2月1日，3月1日，....
This depends a lot on what kind of statistics you want.
Think about how an odometer works. The count for a given car just
keeps going up and up and up (hopefully you use a data type big
enough so you avoid "rollover"). You get the counts in a given
period by subtracting the readings at the beginning and the end of
the period.
So, if you take snapshots of the active count table each month or
week or whatever, you can get counts for any month. You could
duplicate the whole table, or have one table with count entries
for current, Jan 1, Feb 1, Mar 1, ... .
l3vi写道：
l3vi wrote:
我遇到一个IRC的人，帮我搞定了锻炼了。
这就是我们想出的结果
表1：
ID |关键字
Tabl 2：
ID |数|日期时间
如果是新搜索，我们会将搜索短语存储到表1中，然后将
添加到表2中。如果是相同的话在同一天搜索
然后我们将添加到表2中的计数，否则我们在新的一天添加一个新条目
并开始计算。
然后我们将使用复制（我正在学习）将其复制到
另一个DB用于前端使用。
I ran into a guy in IRC that helped me get it worked out.
This is what we came up with
Table 1:
ID | Keyword
Tabl 2:
ID | Count | DateTime
If it is a new search we will store the search phrase into table 1, and
then add one hit to table 2. If its the same search on the same date
then we will add to the count in table 2, otherwise we add a new entry
for a new day and start the count over.
Then we will use replication (Im learning about that) to copy it to
another DB for front end usage.
explodeIm将在
以上的
上使用BigInt（20），DateTime和VarChar（80）
explodeIm going to use BigInt(20), DateTime, and VarChar(80) on the
above
表。不知道DateTime是否最适合跟踪天数，但它确实过去
运行良好。
听起来不错？
Gordon Burditt写道：
tables. Dont know if DateTime is best for tracking days or not, but it
has worked well in the past.
Sound good?
Gordon Burditt wrote:
>我希望能够记录关键字的次数是每天搜索的，每天搜索一次，从中我可以每周和每月计算。
>
>此时此刻我有每个搜索词组一个条目，其数量为
>点击搜索词组已获得，以及上次更新时间。
>
>当我开始将程序从测试中移除并进入更多的
>使用的工具我担心我的想法切换到可变的
>相同搜索短语的条目将是一个坏主意，因为在
>过去15天内，我已经存储了超过300万个独特的搜索短语，
>和未知数量的命中。
>
>因此，我应该创建一个存储每个搜索的滚动数据库，然后将该数据滚动到另一个数据库中。用户可以使用的一周，或者
>是他们更好的方式，我只保留一个条目每个
>搜索短语，我仍然能够保持记录如何每日搜索
>数量，以便我可以在我的网站上搜索搜索阶段的趋势等？
>
>我计划有一个3-5个月的窗口，我可以查看搜索数据
>信息。
>I want to be able to keep records of how many times a keyword was
>searched for daily, and from that I can calculate weekly and monthly.
>
>At this point I have one entry per search phrase with the number of
>hits the search phrase has gotten, and the last time it was updated.
>
>As I start to take the program out of testing and move in more into a
>used tool Im getting worried that my idea of switching to mutable
>entries for the same search phrase would be a bad idea as within the
>last 15 days I have stored more then 3 million unique search phrases,
>and a unknown number of hits.
>
>So should I make a rolling database that stores each search then rolls
>that data over to another database once a week that users can use, or
>is their a better way of doing it where I only keep one entry per
>search phrase and am still able to keep records how daily search
>amounts so that I can track trends, etc in search phases on my sites?
>
>My plan to have a 3-5 month window that I can look at data on search
>information.
这很大程度上取决于你想要什么样的统计数据。
想想里程表的工作原理。给定车辆的计数只是
不断上升和上升（希望你使用的数据类型很大
足以让你避免翻转）。您可以通过减去
期间开头和结尾的读数来获得给定
期间的计数。
因此，如果您每月拍摄活动计数表的快照或
周或其他什么，您可以获得任何月份的计数。你可以
复制整个表，或者有一个表有计数条目
当前，1月1日，2月1日，3月1日，....
This depends a lot on what kind of statistics you want.
Think about how an odometer works. The count for a given car just
keeps going up and up and up (hopefully you use a data type big
enough so you avoid "rollover"). You get the counts in a given
period by subtracting the readings at the beginning and the end of
the period.
So, if you take snapshots of the active count table each month or
week or whatever, you can get counts for any month. You could
duplicate the whole table, or have one table with count entries
for current, Jan 1, Feb 1, Mar 1, ... .
我假设您使用的是PHP
这里我会做什么
拆分搜索使用explode的字符串（）
删除噪音词，例如，我的，是，等等。当然，等等，
见 http://drupal.org/node/1202
你可以修改单词列表适合。
按照建议在数据库中存储剩余字数
您可能会发现metaphone（）和levenshtein（）等函数很有用
见 http://www.php.net/manual/en/ PHP的ref.strings.php
ASP和其他语言有类似的功能
I am assuming you are using PHP
Here what i would do
Split search string using explode()
Remove noise words such as the,my,are,and,.when ,so,of,there,etc
see http://drupal.org/node/1202
You can modify word list to suit.
Store remaining word in database as suggested
You may find functions such as metaphone() and levenshtein() useful
see http://www.php.net/manual/en/ref.strings.php for PHP
ASP and other languages have similar functions
这篇关于如何设置MySQL表来跟踪统计数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
