mysql - 如何建模数据库以处理多个时区的计数器

Question

我有一个包含分析信息的表格，即:每个页面上的页面浏览量。

field        type
---------------------------
page_id          long
created_time     long (epoch UTC - rounded by hour)
page_views       long

我将纪元向下舍入到小时(例如:1398456553 ==> 1398456000)，允许该表每小时汇总信息。

当客户请求他们的数据时，我们可以进行适当的调整，让他/她能够在他们本地的时区看到数据。

如果客户端的本地时区是UTC，查询很简单:

SELECT
    FROM_UNIXTIME(st.`created_time`, '%Y-%m-%d') AS created_at,
    SUM(st.`page_views`) AS page_views
FROM `page_stats` st
WHERE st.`created_time` 
    BETWEEN 1396310400 -- 01 Apr 2014 00:00:00 GMT
    AND 1397088000 -- 10 Apr 2014 00:00:00 GMT
GROUP BY created_at;

如果客户的时区在其他地方(例如:-03:00)，查询需要更多操作，以调整日期以更正 TZ:

SELECT
    DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(st.`created_time`), '+00:00', '-03:00'), '%Y-%m-%d') AS created_at,
    SUM(st.`page_views`) AS page_views
FROM `page_stats` st
WHERE st.`created_time` 
    BETWEEN 1396321200 -- 01 Apr 2014 03:00:00 GMT
    AND 1397098800 -- 10 Apr 2014 03:00:00 GMT
GROUP BY created_at;

这种方法适用于小周期(< 30 天)，但当日期范围代表几个月时，它的扩展性很差，因为要选择的行数以及 DATE_FORMAT 等函数需要转换。

理想的数据粒度是DAY，但我无法按天创建聚合表，因为按天汇总在每个 TZ 上都不同。

为表建模以在大型数据集上提供 TZ 保真度的正确方法应该是什么？

值得注意的是，我可以在这个组上允许一些错误(< 2%)，也许一些 Probabilist Data Structure 可能有助于解决问题，但我还没有弄清楚。

Answer 1

首先，请注意 TimeZone != Offset。查看timezone tag wiki .

其次，如果您要按多个时区的目标日期进行聚合，您可能只想选择几个相关时区并将它们的本地日期预先计算到数据中的唯一列中。然后在查询时很容易聚合。当然，如果你想支持 the IANA tzdb 中的所有 500 多个时区，这个策略就站不住脚了。 .

另一种策略是舍入构建另一组表，将项目预先聚合到 15 分钟的桶中。为什么是15分钟？因为并非所有时区偏移量都是以整小时为单位的。考虑在委内瑞拉使用 -4:30，在印度使用 +5:30，在尼泊尔使用 +5:45，在澳大利亚部分地区使用 +8:45。获得这些预聚合后，您可以在查询时将那些转换为特定客户端时区的详细信息。

最后，您可能会认为像 MySQL 这样的关系数据库可能不是完成这项特定工作的最佳工具。一个 OLAP 多维数据集可以很好地工作，因此可以在几个 nosql 数据库中的任何一个中使用 map/reduce 函数。您可能希望将数据从 MySQL 复制到单独的“报告存储”或“数据仓库”，并从那里进行查询。