获取最大范围内预订数量 [英] Get max bookings count in range

问题描述

我有一个 ParkingLot 模式。停车场有许多可用的许多。然后，用户可以预订一个停车场为一个或多个天。因此，我有一个预订模式。

I have a ParkingLot model. Parking Lots have a number of available lots. Users can then book a parking lot for one or more days. Hence I have a Booking model.

class ParkingLot
  has_many :bookings
end

class Booking
  belongs_to :parking_lot
end

简体USECASE

ParkingLot

由于停车场有5可用批次：

Given a parking lot with 5 available lots:

预订

• 鲍勃书籍从周一的地方星期天
• 在苏让人订票每个周一，周三和周五
• 在亨利的书只在周五。
• 从上周末开始忙，另外4人预定从周六到周日。

修改

该订单有一个 START_DATE ＆放大器;一个 END_DATE ，所以Bob的订单仅有的一个的条目。 周一至周日。 苏在另一方面确实有三个订单，全部首发，并于同一天结束。 周一周一，周三，周三，周五至周五

The bookings have a start_date & an end_date, so Bob's bookings only has one entry. Mon-Sun. Sue on the other hand really has three bookings, all starting and ending on the same day. Mon-Mon, Wed-Wed, Fri-Fri.

这给了我们以下的预订数据：

This gives us following booking data:

为了简单起见，而不是USER_ID（ 1 ）及日期（ 2015年5月15日），我会使用初始（ B ）和工作日（星期一）。

For simplicity, instead of the user_id (1) & the date (2015-5-15), I will use the initial (B) and the week days (Mon).

 ––––––––––––––––––––––––––––––––––––––––––
| id | user_id | start_date| end_date| ... |
|––––––––––––––––––––––––––––––––––––––––––|
|  1 |    B    |    Mon    |   Sun   | ... |
|––––––––––––––––––––––––––––––––––––––––––|
|  2 |    S    |    Mon    |   Mon   | ... |
|  3 |    S    |    Wed    |   Wed   | ... |
|  4 |    S    |    Fri    |   Fri   | ... |
|––––––––––––––––––––––––––––––––––––––––––|
|  5 |    H    |    Fri    |   Fri   | ... |
|––––––––––––––––––––––––––––––––––––––––––|
|  6 |    W    |    Sat    |   Sun   | ... |
|  7 |    X    |    Sat    |   Sun   | ... |
|  8 |    Y    |    Sat    |   Sun   | ... |
|  9 |    Z    |    Sat    |   Sun   | ... |
 ––––––––––––––––––––––––––––––––––––––––––

---

这让我们在接下来的一周：

---

This gives us the following week:

 –––––––––––––––––––––––––––––––––––––––––
| Mon | Tue | Wed | Thu | Fri | Sat | Sun |
|–––––––––––––––––––––––––––––––––––––––––|
|  B  |  B  |  B  |  B  |  B  |  B  |  B  |
|–––––––––––––––––––––––––––––––––––––––––|
|  S  |  -  |  S  |  -  |  S  |  -  |  -  |
|–––––––––––––––––––––––––––––––––––––––––|
|  -  |  -  |  -  |  -  |  H  |  -  |  -  |
|–––––––––––––––––––––––––––––––––––––––––|
|  -  |  -  |  -  |  -  |  -  |  W  |  W  |
|  -  |  -  |  -  |  -  |  -  |  X  |  X  |
|  -  |  -  |  -  |  -  |  -  |  Y  |  Y  |
|  -  |  -  |  -  |  -  |  -  |  Z  |  Z  |
|=========================================|
|  2  |  1  |  2  |  1  |  3  |  5  |  5  | # Bookings Count
|=========================================|
|  3  |  4  |  3  |  4  |  2  |  0  |  0  | # Available lots
 –––––––––––––––––––––––––––––––––––––––––

这些订单都已经在数据库中，所以当一个新用户想预订从周一到周五，有空间这样做。但是，当他想从星期一到预订到周六，这将是不可能的。

These bookings are already in the database, so when a new user wants to book from Monday to Friday, there is space to do so. But when he wants to book from Monday to Saturday, this will not be possible.

我的目标是查询在给定的时间段预约的最大数量。最终导致可用拍

My goal is to query for the max number of bookings in a given time range. Ultimately leading to the available lots

# Mon - Thursday => max bookings: 2 => 3 available lots
# Mon - Friday => max bookings: 3 => 2 available lots
# Mon - Sunday => max bookings: 5 => 0 available lots

一个简单的，但是错误的做法我的是得到了倒在指定时间内的所有预订：

A simple, but wrong approach of mine was to get all bookings that fall in the given time range:

scope :in_range, ->(range) { where("end_date >= ?", range.first).where("start_date <= ?", range.last) }

但是，这决不是正确的。从周一查询到周五返回5的预订，一位来自鲍勃，一个来自亨利和三名来自苏。这会错误地假设停车场是满的。

But this is by no means correct. Querying from Monday to Friday returns 5 bookings, one from Bob, one from Henry and three from Sue. This would falsely assume the Parking Lot is full.

我将如何创建这样一个查询来获取订单的最大计数在给定的时间范围？

How would I create such a query to get the max count of bookings in a given time range?

这也可以是纯 SQL ，我将很乐意把它翻译成 AR lateron。

This can also be pure SQL, I'll be happy to translate it into AR lateron.

推荐答案

有使用日历表的简单方法。如果你不已经有一个，你应该创建它，它有多种用途。

There is a simple way using a calendar table. If you don't have one already you should create it, it has multiple usages.

select
   c.calendar_date
   ,count(b.start_date) -- number of occupied lots
from calendar as c
left join bookings as b -- need left join to get dates where no lot is already booked
  on c.calendar_date between b.start_date and b.end_date

-- restrict to the searched range of dates
where calendar_date between date '2015-05-10' and date '2015-05-18'
group by c.calendar_date
order by c.calendar_date

编辑： 弗拉基米尔·巴拉诺夫建议增加关于如何创建和使用日历表的链接。当然，实际的执行始终是用户和DBMS特定的（如：<一href="http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html"相对=nofollow> MS SQL Server的），因此搜索万年历表+ yourDBMS 的可能会透露一些源$ C ​​$ C为您的系统。

Vladimir Baranov suggested to add a link on how to create and use a calendar table. Of course the actual implementation is always user and DBMS specific (e.g. MS SQL Server), so searching for "calendar table" + yourDBMS will probably reveal some source code for your system.

在事实上，以创建日历表最简单的方法是做计算的年，你需要为preadsheet（Excel等全力以赴，你需要像复活节的计算功能），然后将其推范围到数据库中，这是一个一次性的操作： - ）

In fact the easiest way to create a calendar table is to do the calculation for the range of years you need in a spreadsheet (Excel, etc. go all the functions you need like easter calculation) and then push it to the database, it's a one-time operation :-)

首先，创建 CalendarDay 模式。我已经添加更多的列不仅仅是日，这可能派上用场的未来情景。

First, create the CalendarDay model. I've added more columns than just the day, which may come in handy for future scenarios.

DB /迁移/ 201505XXXXXX_create_calendar_days.rb

class CreateCalendarDays < ActiveRecord::Migration
  def change
    create_table :calendar_days, id: false do |t|
      t.date :day, null: false
      t.integer :year, null: false
      t.integer :month, null: false
      t.integer :day_of_month, null: false
      t.integer :day_of_week, null: false
      t.integer :quarter, null: false
      t.boolean :week_day, null: false
    end

    execute "ALTER TABLE calendar_days ADD PRIMARY KEY (day)"
  end
end

然后，运行后耙分贝：迁移添加rake任务来填充你的模型：

Then, after running rake db:migrate add a rake task to populate your model:

的lib /任务/ calendar_days.rake

namespace :calendar_days do
  task populate: :environment do
    (Date.new(2010,1,1)...Date.new(2049,12,31)).each do |d|
      CalendarDay.create(
        day:          d,
        year:         d.year,
        month:        d.month,
        day_of_month: d.day,
        day_of_week:  d.wday,
        quarter:      (d.month / 4) + 1,
        week_day:     ![0,6].include?(d.wday)
      )
    end
  end
end

和运行 calendar_days：填充

最后，您可以使用ActiveRecord执行复杂的查询，如上图所示：

Lastly, you can use Activerecord to perform complex queries as the one above:

CalendarDay.select("calendar_days.day, count(b.departure_time)")
           .joins("LEFT JOIN bookings as b on calendar_days.day BETWEEN b.departure_time and b.arrival_time")
           .where(:day => start_date..end_date)
           .group(:day)
           .order(:day)

# => SELECT "calendar_days"."day", count(b.departure_time)
#    FROM "calendar_days"
#    LEFT JOIN bookings as b on calendar_days.day BETWEEN b.departure_time and b.arrival_time
#    WHERE ("calendar_days"."day" BETWEEN '2015-05-04 13:41:44.877338' AND '2015-05-11 13:42:00.076805')
#    GROUP BY day  
#    ORDER BY "calendar_days"."day" ASC

---

<子> 1 - 使用情况下，通过增加 TheChamp

---

_{1 - Use case added by TheChamp}

这篇关于获取最大范围内预订数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！