Get max bookings count in range

问题:

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

Simplified Usecase

ParkingLot

Given a parking lot with 5 available lots:

Bookings

• Bob books a place from Monday to Sunday
• Sue makes one booking each on Monday, Wednesday and Friday
• Henry books only on Friday.
• Since the weekend is busy, 4 other people book from Saturday to Sunday.

---

Edit

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:

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) }

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?

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

回答1:

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

Edit: 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.

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 :-)

---

Rails use case¹

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

db/migrate/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

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

lib/tasks/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

And run calendar_days:populate

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 - Use case added by TheChamp}

回答2:

I am going to assign some numerical values to the days to simplify this problem. You can make this the 'id' column (or something like that).

Mon-1, Tue-2, Wed-3, Thu-4, Fri-5, Sat-6, Sun-7

Now it's a really simple SQL:

select 5-max(bookings) where id >= 1 and id <= 4

This is Mon to Thu, and you can keep changing the range to get the available lots between a certain period. Bob's requirement can be plugged in place of the values 1 and 4, and he will know if there is an available lot or not.

I would assume that you are going to apply this to a large scale solution and if you are actually using date, the above query can be very easily adapted to get the correct value each time.

回答3:

You need GROUP by day since your bookings are daily based. Check the total bookings in a specific day against your total lots, you get available space for that day.

Let create a table bookings with following entries:

   Book_Date      Slot_Id    Customer_Id
   2015-05-14      1          100
   2015-05-14      2          200
   2015-05-14      3          400
   2015-05-15      1          100
   2015-05-16      1          100
   2015-05-17      1          100

Do this query:

  SELECT book_date , count(*) AS booked, 5- count(*) AS lot_available 
  FROM bookings 
  WHERE bookdate>='2015-05-14' AND book_date<'2015-05-21' 
  GROUP BY book_date

will give you something:

   book_date booked   lot_available
   2015-05-14     3    2
   2015-05-15     1    4 
   2015-05-16     1    4 
   2015-05-17     1    4 

You know now how many lots available for each day.

There is one issue to solve, if there is no booking for a specific day, it will not be listed in above result, you need add a calendar table or build a small temp table to solve it.

Use this to generate a table for next 7 days:

SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY) nday 
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 2 DAY) nday 
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY) nday 
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 4 DAY) nday 
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 5 DAY) nday 
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 6 DAY) nday 
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY) nday 

and check your code to

  SELECT nday AS book_date , count(lot_id) AS booked, 5- count(lot_id) AS lot_available 
  FROM (SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY) nday 
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 2 DAY) AS nday 
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY)  
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 4 DAY)  
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 5 DAY)  
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 6 DAY)  
UNION 
SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY)  ) days
LEFT JOIN bookings 
ON days.nday = books.book_date
GROUP by nday

It will give you something like :

+------------+--------+---------------+
| book_date  | booked | lot_available |
+------------+--------+---------------+
| 2015-05-15 |      2 |             3 |
| 2015-05-16 |      0 |             5 |
| 2015-05-17 |      0 |             5 |
| 2015-05-18 |      0 |             5 |
| 2015-05-19 |      0 |             5 |
| 2015-05-20 |      0 |             5 |
| 2015-05-21 |      0 |             5 |

(The last results is generated using different sample data)

This is a modified version :

SELECT SUM(CASE WHEN lot IS NULL THEN 0 ELSE 1 END) AS booked, nday
FROM (
  SELECT  lot, c.nday  FROM  (   
    SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS nday    
    UNION    SELECT DATE_ADD(CURDATE(), INTERVAL 2 DAY)    
    UNION    SELECT DATE_ADD(CURDATE(), INTERVAL 3 DAY)     
    UNION    SELECT DATE_ADD(CURDATE(), INTERVAL 4 DAY)     
    UNION    SELECT DATE_ADD(CURDATE(), INTERVAL 5 DAY)     
    UNION    SELECT DATE_ADD(CURDATE(), INTERVAL 6 DAY)     
    UNION    SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY)   
    ) c 
    LEFT JOIN bookings l 
      ON l.book_date<=c.nday AND l.end_date >=c.nday 
    ) e
GROUP BY NDAY

gives you results like:

+--------+------------+
| booked | nday       |
+--------+------------+
|      5 | 2015-05-16 |
|      5 | 2015-05-17 |
|      2 | 2015-05-18 |
|      0 | 2015-05-19 |
|      0 | 2015-05-20 |
|      0 | 2015-05-21 |
|      0 | 2015-05-22 |
+--------+------------+

回答4:

I think this can be simplified with a modified data model

To make the example more clear I have changed the names of the Classes to be more descriptive.

We will use classes:

Lot, Space, and Reservation

The relationships should be

Lot    
  has_many :spaces    

Space  
    belongs_to :lot   
    has_many :reservations

Reservations   
    belongs_to: :space   
    reservation_date #not a range just a date

Then you can do something along the lines of:

dates_for_search = [reservation_date1, reservation_date2, reservation_date3]

open_spaces_and_date = []
dates_for_search.each do |date|
   @lot.spaces.each do |space|
     if space.reservations.bsearch{|res| res.reservation_date == date} == nil 
       open_spaces_and_date << {:space => space; :date => date}
     end
   end

#now open_spaces_and_dates will have a list of availability. 
end