mouf/database.dbstats

此包提供了一系列类,可用于创建包含数据库高级统计信息的“聚合”表。DB_Stats可用于初始化聚合表,并自动创建触发器以实时更新数据。

3.0.x-dev 2015-05-18 15:27 UTC

This package is auto-updated.

Last update: 2024-09-15 05:28:42 UTC


README

#DB_Stats

##介绍

DB_Stats 是一个用于在MySQL表中计算统计信息的PHP库。它允许对表进行多维分析(就像OLAP一样,但功能较弱)。当你想分析表中的数据时,你只需将 DB_Stats 插入该表。要分析的数据表称为“源表”。DB_Stats 将生成一个汇总表(也称为“目标表”或“统计表”),其中包含从初始表中的聚合统计信息。DB_Stats 还会在源表上生成触发器。当从源表中插入、更新或删除数据时,触发器将自动更新汇总表中的统计信息。

然后,你可以使用 DB_Stats_Query 类查询此表中的数据,该类为DB_Stats表提供了查询功能。

##理解DB_Stats

为了使用 DB_Stats,了解 DB_Stats 的工作方式非常重要。下面是一个示例源表。下面的表包含医疗记录中的人员。

有几个列用于描述这些病人

  • 姓名
  • 工作
  • 位置(国家/城市)
  • 用户被添加到数据库中的日期
  • 有关人员的集合数据(年龄 / 体重(千克) / 身高(厘米))

我们将这些列分为两组: 维度。维度是我们将过滤的内容。“值”是我们将汇总以生成统计信息的内容。在这个示例中,我们将选择这些维度

  • 工作
  • 位置(国家/城市)
  • 创建日期(年/月/日)

如你所见,一个 维度 可以包含多个列(在这个示例中,位置由2个列组成,创建日期由3个列组成)。值将是

  • 年龄总和
  • 体重总和
  • 身高总和
  • 病人数量

计算平均值:值是从原始数据集中求和的数字。在这个示例中,我们可能想得到数据库中病人年龄的平均值。然而,DB_Stats 只能提供年龄的总和。但希望我们也可以计算病人数量(每次插入一行数据时,将统计行数加1)。因此,我们可以通过将年龄总和除以病人数量来计算平均值。

有了这些维度和值,汇总表看起来可能像这样

在这个示例中,数据已按所有列分组。由于有两位病人都是IT顾问,住在美国的旧金山,并且是在2009-08-02创建的,他们的数据被汇总。当然,这可能看起来相当无意义,因为简单的“GROUP BY”请求确实可以做到这一点。但请记住

  • 这些数据是实时更新的,因此源表可以包含数百万条记录,你仍然可以立即获得结果
  • DB_Stats提供更多功能,下面我们将看到

事实上,DB_Stats不仅提供“GROUP BY”一层的统计数据,它还会根据任何相关的列组合执行所有可能的GROUP BY操作。因此,汇总表也将包含这些数据

为了理解这一点,你必须知道当一列包含“NULL”时,这意味着该列的值是对该列所有可能值的求和。例如,ID为“5”的行在其所有与维度相关的列中都包含NULL。因此,它将包含所有患者的总和。因此,我们可以立即看到数据库中有5名患者,他们年龄的总和是210岁。因此,平均年龄是42岁。ID为“7”的行包含所有是教师且居住在法国的患者等...

重要!
根据其维度,“NULL”列的所有组合都是可用的。维度是由一个有序的列列表组成的。这意味着对于YEAR/MONTH/DAY维度,我们可以搜索有关2009年创建的患者统计数据,我们可以搜索有关2009年6月创建的患者统计数据,但我们无法搜索有关无论何年6月创建的患者统计数据。

##使用库

现在我们已经理解了理论,让我们进入编码。

首先,DB_Stats与Mouf框架兼容,因此以下通过代码创建的任何对象也可以使用Mouf图形界面创建。

###创建DB_Stats对象 在这部分,我们将重点关注程序化创建对象。为了创建一个汇总表,我们需要定义一个DB_Stats对象。

+--------------------------------------------------------+
| DB_Stats                                               |
+--------------------------------------------------------+
| + setDbConnection(DB_Connection connection)            |
| + setSourceTable(string tableName)                     |
| + setStatsTable(string tableName)                      |
| + setDimensions(array<DB_Dimension> dimensions)        |
| + addDimension(DB_Dimension dimension)                 |
| + setValues(array<DB_Stat_Column> values)              |
| + addValue(DB_Stat_Column value)                       |
| + createStatsTable()                                   |
| + fillTable()                                          |
| + createTrigger()                                      |
+--------------------------------------------------------+

第一步是获取数据库连接。这是通过DB_Connection对象完成的

$conn = new DB_MySqlConnection();
$conn->setHost("localhost");
$conn->setDbName("myDb");
$conn->setUser("root");
$conn->connect();

$dbStats = new DB_Stats($conn, "patients", "patientsrollup");

使用这段代码,我们创建了一个到MySql的连接,创建了一个Db_Stats对象,并将其连接到连接。然后,我们设置源表和汇总表。

创建维度

现在,我们需要设置维度。为此,我们将使用`DB_Stats->addDimension`方法。此方法接受一个DB_Dimension对象作为参数。
+--------------------------------------------------------+
| DB_Dimension                                           |
+--------------------------------------------------------+
| + setColumns(array<DB_StatColumn> columns)             |
| + addColumn(DB_StatColumn column)                      |
+--------------------------------------------------------+

维度由一组列组成。

+--------------------------------------------------------+
| DB_StatColumn                                          |
+--------------------------------------------------------+
| + setColumnName(string name)                           |
| + setDataOrigin(string dataOrigin)                     |
| + setType(string type)                                 |
+--------------------------------------------------------+

以下是“工作”维度的声明示例

$jobColumn = new DB_StatColumn();
$jobColumn->setColumnName("job");
$jobColumn->setDataOrigin("[statcol].job");
$jobColumn->setType("VARCHAR(255)");

$jobDimension = new DB_Dimension();
$jobDimension->addColumn($jobColumn);

$dbStats->addDimension($jobDimension);

重要的是要理解在维度中声明的每个“列”都将在汇总表中创建一个列。列的类型是通过DB_StatColumn->setType方法设置的。因此,通过创建“工作”维度,我们将“工作”列添加到汇总表中。数据源指数据来源的地方。它将来自源表的“工作”列。我们称此列为“[statcol].job”。

重要!当引用源表中的列时,始终用“[statcol].”前缀列名。

###创建维度 - 高级

我们创建了一个简单的维度,只有一个列,但许多维度并不那么简单。让我们关注日期维度。在“日期”维度中,源表中的一个单一字段为多个列提供数据。以下是维度

$year = new DB_StatColumn();
$year->setColumnName("year");
$year->setDataOrigin("YEAR([statcol].CreationDate)");
$year->setType("INT");

$month = new DB_StatColumn();
$month->setColumnName("month");
$month->setDataOrigin("MONTH([statcol].CreationDate)");
$month->setType("INT");

$day = new DB_StatColumn();
$day->setColumnName("day");
$day->setDataOrigin("DAY([statcol].CreationDate)");
$day->setType("INT");

$dateDimension = new DB_Dimension();
// Remember the order is important. We add the year first, then the month and finally the day
$dateDimension->addColumn($year);
$dateDimension->addColumn($month);
$dateDimension->addColumn($day);

$dbStats->addDimension($dateDimension);

在这个示例中,看看数据源。它是通过MySql函数动态计算的。例如,YEAR([statcol].CreationDate)将返回存储在CreationDate列中的年份。

##创建值

我们已经创建了维度列,现在我们需要创建值。值是绑定到DB_Stats对象的一组DB_StatColumn对象。

以下是一个示例

$totalAge = new DB_StatColumn();
$totalAge->setColumnName("sumage");
$totalAge->setDataOrigin("[statcol].age");
$totalAge->setType("BIGINT");

$dbStats->addValue($totalAge);

这将创建一个“sumage”列在统计表中,该列将包含患者的年龄总和。数据源与我们在维度中看到的类似。你必须用“[statcol].”前缀任何源表中的列。

您可以在 dataOrigin 字段中使用任何 MySql 函数来进行转换。您还可以使用源表中的多个列,等等。例如,我们可以通过这种方式计算体质量指数的总和。

$bodyMassIndex->setDataOrigin("[statcol].weight/([statcol].height*[statcol].height)");

特殊情况:"cnt" 列是一个特殊情况。实际上,该列不指向任何字段,当添加新行时,它只是增加一个。在这种情况下,您只需为 dataOrigin 提供一个 "1" 的值。因此,每次有新行匹配统计信息时,"cnt" 列都将增加 1。

$cnt = new DB_StatColumn();
$cnt->setColumnName("cnt");
$cnt->setDataOrigin("1");
$cnt->setType("BIGINT");

$dbStats->addValue($cnt);

### 创建表和触发器

我们的 DB_Stats 对象现在已完全配置。我们只需执行一些方法来设置系统。

// Creates the stats table
$dbStats->createStatsTable();

// Fills the table with stats
$dbStats->fillTable();

// Creates the triggers
$dbStats->createTrigger();

DB_Stats->createStatsTable 将在数据库中创建表。DB_Stats->fillTable 将清除统计表并用来自源表的最新计算的统计信息填充它。请注意,如果源表很大,此操作可能需要一些时间!DB_Stats->createTrigger 将在源表上创建触发器,该触发器将自动更新统计表。

就是这样!现在,我们有一个功能齐全的汇总表,我们可以查询它来检索源表的统计信息。汇总表已经索引,并将以光速提供结果!

您可以直接在该表上执行查询,或者使用专为在统计表上执行查询而设计的 DB_Stats_Query 类。

## 已知限制

由于 DB_Stats 的工作方式,您在用 DB_Stats 开发时应注意以下问题

  • DB_Stats 不支持在多个表上执行统计。您不能对多个表进行 JOIN 并计算这些 JOIN 表的统计(因此无法像在 OLAP 中那样绘制雪花或星形模式)
  • 由于 DB_Stats 实时写入统计表,源表的写入过程可能会严重减慢,尤其是如果您有很多维度。尽量减少维度数量,或者确保在不太经常修改的表上使用这些维度。
  • 只能添加值。您可以通过将值的总和除以行数的总和来计算平均值,但无法计算列的 "最大值" 或 "最小值"。