时间戳

首页 > 解决方案 > 存储去年的每月分析数据

database - 存储去年的每月分析数据

问题描述

我正在尝试存储过去 12 个月（即一年）用户每月发送的电子邮件数量。每个月将有 2 个值，即成功发送的电子邮件数量，以及由于某种原因未能发送的电子邮件数量。

我的数据库是支持 JSON 数据的 PostgreSQL。我在想这样的事情：一个存储所有月度数据的列，其结构如下：

{
    "08-2019": {"successes": 348, "fails": 1},
    "07-2019": {"successes": 947, "fails": 7},
    "06-2019": {"successes": 428, "fails": 2},
    "05-2019": {"successes": 638, "fails": 5},
    "04-2019": {"successes": 354, "fails": 2},
    "03-2019": {"successes": 693, "fails": 0},
    "02-2019": {"successes": 461, "fails": 9},
    "01-2019": {"successes": 211, "fails": 1},
    "12-2018": {"successes": 414, "fails": 3},
    "11-2018": {"successes": 627, "fails": 7},
    "10-2018": {"successes": 241, "fails": 2},
    "09-2018": {"successes": 124, "fails": 4}
}

换句话说，键是月份和年份，它们存储另一个带有成功和失败次数的 JSON 对象。只有在发送电子邮件时才会更新此结构。可以说，在 2019 年 9 月，一封电子邮件已成功发送。由于数据库中不存在键“09-2019”，因此删除了最旧的键，即“09-2018”，并添加了一个新的键值对，显然是"09-2019": {"successes": 1, "fails": 0}. 当在 9 月发送另一封电子邮件时，密钥已经存在，因此只需对其进行更新。

有时会发生的情况是，在某些月份，将不会发送任何电子邮件，因此，该月的结构中将没有数据。我想在查询过程中解决这个问题。当用户从数据库中检索此分析数据时，将创建一个新的 JSON 数据，其中键为过去 12 个月，每个键的值为{"successes": 0, "fails": 0}. 然后将比较这两种结构。将更新新的 JSON 结构，以便数据库中存在的任何键，该键的值将替换为数据库中的值。然后，这个新结构将返回给用户，因此他们只能看到过去 12 个月的数据。

我的问题是：这种方法是个好主意吗？我不知道分析数据是如何存储在生产中的，对于我的用例，我无法分配大量资源来存储大量数据，因为这只是一个资金非常有限的副项目。

标签： databasepostgresql

解决方案

  1. 我不建议使用 JSON，因为当数据增长时您会遇到问题。如果您只对 JSON 感到满意，那么至少使用 JSONB 数据类型，您可以在其中使用很少的索引，而 JSON 类型的列没有适当的索引来提高性能。

  2. 当我们不了解架构时，大多数情况下建议使用 JSON 类型列，但对于您的情况，您可以有效地使用其他类型列。您可以实现如下相同。

date_keys表将具有类似的列id, date_value， data表将具有类似的列date_keys_id, success_count, failed_count，您可以在其中存储每个日期的数据。

通过这种方式，您可以在表大小增加时以有效的方式进行查询。我在使用 JSON 时遇到了很多问题，这就是为什么建议采用这种方式的原因。

在 date_keys 表中可以使用brin索引来加快查询，在data表中可以使用通用类型索引btree来加快查询。

例如。

date_keys: id = 1, date_value = '10-2018'

data: date_keys_id = 1, success_count = 10, failed_count = 0

推荐阅读