我正在计算LaserSheet模型的统计信息,以便为仪表板页面构建一个morris.js图表。我目前使用的是一个统计数据:
# 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或晚于该日期。
我可以手动完成昨天的操作:
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今天的内容是:
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。
发布于 2016-04-24 23:40:12
我无法编写远程、高效甚至语法有效的ruby代码,但这里有一些原始的SQL可能会对您有所帮助。
您应该使用generate_series生成一个日期列表,并在此列表上使用左连接:
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)现在您已经知道了如何生成一组日期,您所要做的就是用正确的子句连接这些日期。
但首先,我们需要一些测试数据:
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)最后的查询应该如下所示:
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
;结果如下所示
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)https://stackoverflow.com/questions/36823804
复制相似问题