首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Rails:按天计算统计数据和分组

问Rails:按天计算统计数据和分组
EN

Stack Overflow用户
提问于 2016-04-24 21:31:34
回答 1查看 376关注 0票数 1

我正在计算LaserSheet模型的统计信息,以便为仪表板页面构建一个morris.js图表。我目前使用的是一个统计数据:

代码语言:javascript
复制
# Show four Mondays ago up to this coming Sunday (4 weeks)
start_date = Time.zone.now.beginning_of_week - 3.weeks
end_date   = Time.zone.now.end_of_week

# Calculate sheets cut per day
empty_dates_hash = Hash[(start_date.to_date..end_date.to_date).collect { |v| [v, 0] }]  
recent_cut_stats = LaserSheet.where('cut_at IS NOT NULL')
                             .where('cut_at > ?', start_date.beginning_of_day)
                             .where('cut_at < ?', end_date.end_of_day)
                             .group("DATE(cut_at::TIMESTAMPTZ AT TIME ZONE '#{Time.zone.now.formatted_offset}'::INTERVAL)")
                             .count
recent_cut_stats = empty_dates_hash.merge(recent_cut_stats)

我想添加一个按天分组的历史“待裁剪的板材”统计数据。为此,我需要找到在该日期或该日期之前为created_at的每一天的所有LaserSheets,其中cut_at为NULL或晚于该日期。

我可以手动完成昨天的操作:

代码语言:javascript
复制
LaserSheet.where('created_at < ?', Time.zone.yesterday.end_of_day)
          .where('cut_at IS NULL OR cut_at > ?', Time.zone.yesterday.end_of_day)
          .count

今天的内容是:

代码语言:javascript
复制
LaserSheet.where('created_at < ?', Time.zone.today.end_of_day)
          .where('cut_at IS NULL OR cut_at > ?', Time.zone.today.end_of_day)
          .count

我可以在[start_date..end_date]中每天重复这个过程,但是效率非常低。有没有办法用一个数据库查询就能做到这一点呢?它看起来并不像简单地按天分组那样微不足道。

我使用的是PostgreSQL和Rails4。

EN

回答 1

Stack Overflow用户

发布于 2016-04-24 23:40:12

我无法编写远程、高效甚至语法有效的ruby代码,但这里有一些原始的SQL可能会对您有所帮助。

您应该使用generate_series生成一个日期列表,并在此列表上使用左连接:

代码语言:javascript
复制
SELECT *
FROM generate_series(
    date_trunc('day', now()) - CAST('7 day' AS interval),
    date_trunc('day', now()),
    CAST('1 day' AS interval)
);

    generate_series
------------------------
 2016-04-17 00:00:00+00
 2016-04-18 00:00:00+00
 2016-04-19 00:00:00+00
 2016-04-20 00:00:00+00
 2016-04-21 00:00:00+00
 2016-04-22 00:00:00+00
 2016-04-23 00:00:00+00
 2016-04-24 00:00:00+00
(8 rows)

现在您已经知道了如何生成一组日期,您所要做的就是用正确的子句连接这些日期。

但首先,我们需要一些测试数据:

代码语言:javascript
复制
SELECT
    CAST(created_at AS timestamp),
    CAST(cut_at AS timestamp)
FROM (
    VALUES
        ('2016-04-20', null),           /* not cut yet */
        ('2016-04-20', '2016-04-22'),   /* cut 2 days ago */
        ('2016-04-20', null),           /* not cut yet */
        ('2016-04-23', '2016-04-23'),   /* cut yesterday */
        ('2016-04-23', null),           /* not cut yet */
        ('2016-04-24', '2016-04-24'),   /* cut today */
        ('2016-04-24', '2016-04-26')    /* cut tomorrow (because I can :p) */
) as laser_sheet(created_at, cut_at);


     created_at      |       cut_at
---------------------+---------------------
 2016-04-20 00:00:00 |
 2016-04-20 00:00:00 | 2016-04-22 00:00:00
 2016-04-20 00:00:00 |
 2016-04-23 00:00:00 | 2016-04-23 00:00:00
 2016-04-23 00:00:00 |
 2016-04-24 00:00:00 | 2016-04-24 00:00:00
 2016-04-24 00:00:00 | 2016-04-26 00:00:00
(7 rows)

最后的查询应该如下所示:

代码语言:javascript
复制
WITH date_serie AS (
    /* generate one row by day for the last 7 days */
    SELECT generate_series as day
    FROM generate_series(
        /* replace "CAST('2016-04-24 16:56:23' AS datetime)" with "now()" to get a dynamic view */
        date_trunc('day', CAST('2016-04-24 16:56:23' AS timestamp)) - CAST('7 day' AS interval),
        date_trunc('day', CAST('2016-04-24 16:56:23' AS timestamp)),
        CAST('1 day' AS interval)
    )
),
laser_sheet AS (
    /* below is some test data */
    SELECT
        CAST(created_at AS timestamp) AS created_at,
        CAST(cut_at AS timestamp) AS cut_at
    FROM (
        VALUES
            ('2016-04-20', null),           /* not cut yet */
            ('2016-04-20', '2016-04-22'),   /* cut 2 days ago */
            ('2016-04-20', null),           /* not cut yet */
            ('2016-04-23', '2016-04-23'),   /* cut yesterday */
            ('2016-04-23', null),           /* not cut yet */
            ('2016-04-24', '2016-04-24'),   /* cut today */
            ('2016-04-24', '2016-04-26')    /* cut tomorrow (because I can :p) */
    ) as laser_sheet(created_at, cut_at)
)
SELECT
    date_serie.day,
    /* we need to count if any laser_sheet matches this day */
    count(laser_sheet.*) as sheets_left_to_cut
FROM
    date_serie
    LEFT JOIN laser_sheet
    /* notice here your custom join clause */
    ON laser_sheet.created_at < date_serie.day
    AND (
        laser_sheet.cut_at IS NULL
        OR laser_sheet.cut_at > date_serie.day
    )
GROUP BY
    date_serie.day
ORDER BY
    date_serie.day
;

结果如下所示

代码语言:javascript
复制
         day         | sheets_left_to_cut
---------------------+--------------------
 2016-04-17 00:00:00 |                  0
 2016-04-18 00:00:00 |                  0
 2016-04-19 00:00:00 |                  0
 2016-04-20 00:00:00 |                  0
 2016-04-21 00:00:00 |                  3
 2016-04-22 00:00:00 |                  2
 2016-04-23 00:00:00 |                  2
 2016-04-24 00:00:00 |                  3
(8 rows)
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/36823804

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档