我应该使用什么数据库表布局来快速检索日期范围内的聚合/不同数据？

Question

我正在编写一个网络应用程序来分析我的网络服务器日志。

我计划有一个每天运行的 SQL 作业，以非规范化 SQL 数据库中的 Web 服务器日志，以便 Web 应用程序不会读取原始 Web 服务器日志。

我希望 Web 应用程序用户输入一个日期范围，然后让 Web 应用程序返回：

• 一个表，其中一列包含每个浏览器，下一列包含该日期范围的唯一客户端 IP 数量
• 一个表包含每个操作系统一列和下一列中该日期范围的唯一客户端 IP 数量
• 一个表，其中一列中包含每个浏览器 + 操作系统，下一列中该日期范围内的唯一客户端 IP 数量

（您可以在 Google 中看到这个想法分析。）

我们有大约 100,000每月唯一的客户端 IP，并且我希望将非规范化数据保留一年（尽管其中许多客户端 IP 每月都是相同的）。

1. 放置非规范化信息的表格布局是什么？
2. Web 应用程序要有效检索所需信息的 SQL 查询是什么？

（我不是问如何让 SQL 作业写入这些表；我可以弄清楚这一点。）

Answer 1

我会总结 SQL 作业的访问次数，并将每日结果放入表中，如 [logsum]:

Table [logsum]:
sum_id (int / auto_increment)
sum_day (date)
sum_name (string)
sum_count (number)

并将非规范化日志数据放入 [logaccess]:

Table [logaccess]:
access_id (int / auto_increment)
access_day (date)
access_ip (string)
access_browser (string)
access_os (string)
access_click_count (int)

SQL-job:

1)将所有日志条目添加到 [Loginfo] 并汇总每个 IP 和每天的点击次数

for each line in log
{
  info = parse(line)
  execute_sql('REPLACE logaccess
               SET 
                 access_day=date(),
                 access_ip='& info[IP] &',
                 access_browser'& info[browser] &',
                 access_os='& info[OS] &',
                 access_click_count=IF(ISNULL(access_click_count),0,access_click_count) + 1) 
               WHERE access_day=date() AND access_ip='& info[IP] &';')
}

2) 汇总并保存到 [logsum]：

//- get OS count per day
res = execute_sql('SELECT access_day, access_os, count(access_id) AS C FROM logaccess GROUP BY access_day, access_os;');

//- write to [logsum]
for each record in res
{
   REPLACE logsum SET 
     sum_day=record['access_day'],
     sum_name=record['access_os'],
     sum_count=record['c']
   WHERE sum_day=record['access_day'] AND sum_name=record['access_os'];
}


//- get browser count per Day
res = execute_sql('SELECT access_day, access_browser, count(access_id) AS C FROM logaccess GROUP BY access_day, access_browser;');

//- write to [logsum]
for each record in res
{
   REPLACE logsum SET 
     sum_day=record['access_day'],
     sum_name=record['access_browser'],
     sum_count=record['c']
   WHERE sum_day=record['access_day'] AND sum_name=record['access_browser'];
}

//- get IP count per Day
res = execute_sql('SELECT access_day, count(access_id) AS C FROM logaccess GROUP BY access_day;')

//- write to [logsum]
for each record in res
{
   REPLACE logsum SET 
     sum_day=record['access_day'],
     sum_name='ip',
     sum_count=record['c']
   WHERE sum_day=record['access_day'] AND sum_name='ip';
}

//- get click count per Day
res = execute_sql('SELECT access_day, sum(access_click_count) AS C FROM logaccess GROUP BY access_day;')

//- write to [logsum]
for each record in res
{
   REPLACE logsum SET 
     sum_day=record['access_day'],
     sum_name='clicks',
     sum_count=record['c']
   WHERE sum_day=record['access_day'] AND sum_name='clicks';
}

3) 清理/删除超过 1 年的条目：

DELETE FROM logaccess WHERE access_day<DATE_ADD(date(),INTERVAL 1 year)

也许这对您有帮助
问候托马斯