Rails :如何构建每天/月/年的统计数据，或者数据库不可知的SQL函数是如何缺失的(例如，：STRFTIME、DATE_FORMAT、DATE_TRUNC)
我已经在网上到处找了，但我一点头绪都没有。
假设您必须在Rails应用程序的管理区域中构建一个仪表板，并且您希望使用SQLite3 development、MySQL
- production (相当标准的设置)
获得每个app的订阅数量
基本上，有两种选择：
1) 使用Subscriber.all从数据库中检索所有行，并使用Enumerable.group_by在Rails应用程序中按天聚合：
@subscribers = Subscriber.all
@subscriptions_per_day = @subscribers.group_by { |s| s.created_at.beginning_of_day }我认为这是一个非常糟糕的主意。从数据库中检索所有行对于小型应用程序来说是可以接受的，但它根本不会扩展。数据库聚合和日期函数来拯救！
2) 使用聚合和日期函数在数据库中运行SQL查询：
Subscriber.select('STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions').group('day')它将在此SQL查询中运行：
SELECT STRFTIME("%Y-%m-%d", created_at) AS day, COUNT(*) AS subscriptions
FROM subscribers
GROUP BY day好多了。现在，聚合是在数据库中完成的，数据库针对这类任务进行了优化，每天只有一行数据从数据库返回到Rails应用程序。
..。等等..。现在，该应用程序必须在我使用MySQL的生产环境中上线！用DATE_FORMAT()替换STRFTIME()。如果明天我切换到PostgreSQL呢？用DATE_TRUNC()替换DATE_FORMAT()。
我喜欢使用SQLite进行开发。简单和容易。我也喜欢Rails是数据库不可知的这个想法。但是，为什么Rails不提供一种方法来转换执行完全相同任务的函数，但是在每个关系型数据库管理系统中具有不同的语法(这种差异非常愚蠢，但是嘿，抱怨它已经太晚了)？
我真不敢相信，对于Rails应用程序的一个基本功能，我在网上找到的答案如此之少:计算每天、每月或每年的订阅量。
告诉我我漏掉了什么:)
编辑
自从我发布这个问题以来，已经有几年了。经验表明，我应该为dev和prod使用相同的DB。所以我现在认为数据库不可知的需求是无关紧要的。
Dev/prod parity FTW。
回答 6
Stack Overflow用户
发布于 2010-12-21 18:14:54
我最终写了我自己的宝石。查看它，并随时贡献：https://github.com/lakim/sql_funk
它允许您进行如下调用：
Subscriber.count_by("created_at", :group_by => "day")Stack Overflow用户
发布于 2011-03-07 10:33:17
不幸的是，您提到了Rails完全忽略的一些相当困难的问题。ActiveRecord::Calculations文档的编写就像您所需要的一样，但是数据库可以做更高级的事情。正如Donal Fellows在他的评论中提到的那样，这个问题比看起来要棘手得多。
在过去的两年中，我开发了一个大量使用聚合的Rails应用程序，并尝试了几种不同的方法来解决这个问题。不幸的是，我没有忽略像夏令时这样的东西的奢侈，因为统计数据“只是趋势”。我生成的计算结果由我的客户按照精确的规格进行了测试。
稍微扩展一下这个问题，我想您会发现您当前的按日期分组的解决方案是不够的。使用STRFTIME似乎是一个自然而然的选择。主要的问题是，它不允许您按任意时间段分组。如果您希望按年、月、日、小时和/或分钟进行聚合，则STRFTIME可以很好地工作。如果没有，你会发现自己在寻找另一种解决方案。另一个巨大的问题是聚合后的聚合。例如，你想按月分组，但又想从每月15号开始分组。您将如何使用STRFTIME来完成此任务？你必须按天分组，然后按月分组，但之后会有人计算每月15日的起始偏移量。最后一条稻草是，按STRFTIME分组需要按字符串值分组，在聚合时执行聚合时，您会发现这非常慢。
我得到的性能最好、设计最好的解决方案是基于整数时间段的解决方案。下面是我的一个mysql查询的摘录：
SELECT
field1, field2, field3,
CEIL((UNIX_TIMESTAMP(CONVERT_TZ(date, '+0:00', @@session.time_zone)) + :begin_offset) / :time_interval) AS time_period
FROM
some_table
GROUP BY
time_period在这种情况下，:time_interval是分组周期中的秒数(例如，86400表示每日)，:begin_offset是偏移周期开始的秒数。CONVERT_TZ()业务说明了mysql解释日期的方式。Mysql始终假定日期字段位于mysql本地时区。但是因为我是以协调时存储时间的，所以如果我想让UNIX_TIMESTAMP()函数给我一个正确的响应，我必须将它从协调时转换为会话时区。时间段最终是一个整数，它描述了自unix时间开始以来的时间间隔数。此解决方案更加灵活，因为它允许您按任意时段进行分组，并且不需要在聚合时进行聚合。
现在，让我们来谈谈我真正的观点。对于健壮的解决方案，我建议您考虑根本不使用Rails来生成这些查询。最大的问题是，聚合的性能特征和微妙之处在不同的数据库中是不同的。您可能会发现一种设计在您的开发环境中工作得很好，但在生产环境中却不是这样，反之亦然。为了让Rails在查询构造中很好地处理这两个数据库，您将经历很多困难。
相反，我建议您在所选的数据库中生成特定于数据库的视图，并将这些视图带到正确的环境中。尝试像对任何其他ActiveRecord表(id和all)一样对视图建模，当然也要使视图中的字段在不同的数据库中完全相同。因为这些统计数据是只读查询，所以您可以使用模型来支持它们，并假装它们是完全成熟的表。如果有人试图保存、创建、更新或销毁，只需引发一个异常。
通过使用Rails的方式，您不仅可以简化模型管理，还可以为聚合特性编写单元测试，这种方式在纯SQL中是做梦也想不到的。如果您决定切换数据库，您将不得不重写这些视图，但您的测试将告诉您哪里错了，并使工作变得容易得多。
Stack Overflow用户
发布于 2013-04-23 12:05:56
我刚刚发布了一个gem，它允许您使用MySQL轻松实现这一点。https://github.com/ankane/groupdate
你也应该试着在开发中运行MySQL。你的开发和生产环境应该尽可能地接近--更少的机会让一些东西在开发中工作并完全中断生产。
https://stackoverflow.com/questions/4028878
复制相似问题