每日摘要的 SQL 架构

Question

我有一个数据库，它以以下格式存储来自不同位置的 ping：

|    Date    | Source | Language |   OS   | Version | more...
| 2011-10-30 | App1   | en       | XP     | 1.0     | ...
| 2011-10-30 | App2   | de       | 10.7.1 | 1.3     | ...

它非常适合提取快照信息。我希望能够存储从上表生成的每日摘要，以便我可以获得信息如何随时间变化的图表。

示例：

• 显示日期与版本使用情况的图表
• 显示日期与操作系统版本的图表

问题是上表中的几个列（语言、操作系统、版本）可能具有可变数量的值。我没有受过太多数据库设计方面的教育，无法理解如何存储这些信息以便于检索。

有人可以提出任何建议吗？

Answer 1

这里使用数据库规范化。确保您的数据库采用3-NF。

例如，在您的情况下，

创建一个表 OS_TYPE 说它有值

Id Value
1  Win XP
2  Win 7
3  Ubuntu Linux

等。

当您需要插入事务表而不是插入操作系统名称时，查找表并获取 Id，然后将其与其他数据一起插入。在 OS_TYPE 表和 DATA 表之间保留外键引用。因此，对于 Win XP version-1 或 Win XP Service pack 2，您将在 DATA 表中具有相同的条目 ID 1。

这样您将获得可分析和处理的一致数据。获得正确的数据后，请在 SQL 中使用GROUP BY、ORDER BY 和 HAVING 子句来处理数据。

Answer 2

您可以很容易地从当前表结构中获取这些统计信息。 SQL 关键字 GROUP BY 应该是您的朋友。

要获取您键入的每个日期的版本使用情况

SELECT Date, Version, count(*) as NumberOfEntries
FROM YourTable
GROUP BY Date, Version
ORDER BY Date, Version

（ORDER BY Date, Version 只是为了获得良好的排序输出）

如果您想获得每个源和版本组合的统计信息，请将问题修改为

SELECT Date, Source, Version, count(*) as NumberOfEntries
FROM YourTable
GROUP BY Date, Source, Version
ORDER BY Date, Source, Version

If您希望获得您键入的每个日期的操作系统版本

SELECT Date, OS, count(*) as NumberOfEntries
FROM YourTable
GROUP BY Date, OS
ORDER BY Date, OS

如果您没有大量数据（例如几百万行），那么使用合适的索引不会出现任何性能问题。