计算日期范围内的设备总数

问题描述

限时删除！！

项目:我正在研究一个有关一些房间和房间中使用的设备的项目.该软件是关于安排房间中的设备的.换句话说，它是一种预订软件，可以在不同的房间中为所需的日期和时间范围保留选定的设备.我在使用Php的MYsSQL数据库中有很多表，但我会提到我的问题所涉及的表.我要联系我的问题的表是设备表(表A)，时间表表(表B)和相关时间表中使用的设备(表C).

Project: I am working on a project which is about some rooms and equipments using in the rooms. The software is about scheduling the equipments in the rooms. In other words, it is a reservation software that reserves selected equipments in separate rooms for needed dates and times ranges. I have many tables in MYsSQL database working with Php but I will mention the tables my question is about. The tables I will relate my questions are equipment table (Table A), schedule table (Table B) and equipments using in the related schedule (Table C).

表A:设备清单表

eqid   | eqName    | available|

1      |  book     |   90     |

2      |  pen      |   82     |

3      |  computer |   25     |

在表A中； eqid表示设备的唯一ID，eqName表示设备的名称，available表示现有的可用设备总数.

In table A; eqid represents unique id of an equipment, eqName represents name of an equipment, available represents total available equipments existing.

表B:时间表表

scheduleid | startDate  | endDate    | startTime | endTime  |  office  |

1          | 2012-08-27 | 2012-08-27 | 08:30:00  | 10:00:00 |  room1   |

2          | 2012-08-27 | 2012-08-27 | 09:30:00  | 11:00:00 |  room3   |

3          | 2012-08-28 | 2012-08-30 | 08:30:00  | 12:00:00 |  room2   |

4          | 2012-08-29 | 2012-08-31 | 11:30:00  | 14:00:00 |  room1   |

5          | 2012-08-28 | 2012-08-28 | 10:30:00  | 14:00:00 |  room3   |

6          | 2012-08-27 | 2012-08-30 | 08:30:00  | 10:00:00 |  room4   |

7          | 2012-08-27 | 2012-08-27 | 10:30:00  | 12:00:00 |  room4   |

8          | 2012-08-27 | 2012-08-30 | 08:30:00  | 11:00:00 |  room6   |

9          | 2012-08-27 | 2012-08-27 | 10:30:00  | 12:00:00 |  room5   |

在表B中； scheduleid表示时间表的唯一ID，startDate和endDate是时间表的日期范围，时间表的startTime和endTime时间范围，office表示时间表将在何处发生.让我在这里举一个例子. Scheduleid 1表示在2012年8月27日(星期一)有预订，时间是08.30至10:00.由于它在同一天开始和结束，因此这只是在room1中预订的一天.但是，Scheduleid 3表示预订从2012年8月28日(星期二)开始，一直持续到2012年8月30日(星期四)08:30-12:00 ...换句话说，该预订持续3天，每天从08:30到12:00 ...因此，在room2中有一个星期二至星期四的08:30到12:00的预订.我希望这很清楚.

In table B; scheduleid represents unique id for a schedule, startDate and endDate are date range for a schedule, startTime and endTime time range for a schedule, office means that where the schedule will take place. Let me give an example here. Scheduleid 1 means there is a reservation on 27th of august 2012, Monday and it is from 08.30 to 10:00. As it start and end on same day this is just one day reservation in room1. However, Scheduleid 3 means there is a reservation starts on 28th of august 2012, Tuesday and goes on until 30th of august 2012, Thursday at 08:30-12:00... in other words, it lasts for 3 days and everyday from 08:30 to 12:00... So there is a reservation from Tuesday to Thursday at 08:30 to 12:00 in room2... I hope this is clear.

表C:相关时间表中使用的设备

Table C: equipments using in the related schedule

Autoid  | scheduleid |  eqid   | amountInSch|

1       |     1      |   1     |      2     |

2       |     1      |   2     |      3     |

3       |     1      |   3     |      1     |

4       |     2      |   1     |      1     |

5       |     2      |   2     |      1     |

6       |     2      |   3     |      2     |

7       |     3      |   2     |      1     |

8       |     3      |   3     |      3     |

9       |     4      |   2     |      1     |

10      |     4      |   3     |      1     |

11      |     5      |   1     |      1     |

12      |     6      |   1     |      1     |

13      |     6      |   3     |      2     |

14      |     6      |   2     |      4     |

15      |     7      |   1     |      5     |

16      |     7      |   2     |      6     |

17      |     8      |   2     |      1     |

18      |     9      |   1     |      8     |

19      |     9      |   2     |      5     |

20      |     9      |   3     |      6     |

在表C中:Autoid代表由自动增量生成的唯一自动ID，scheduleid来自表B，eqid来自表A，amountInSch代表相关时间表中要使用的设备数量.我想在这里举一个例子.表C中的Scheduleid 1有3行.这意味着TAble B中与之相关的Scheduleid 1将在表B的room1指定的日期和时间使用2本书(当量1)，3笔(当量2)和1台计算机(当量3).另一个示例是表C中的Scheduleid 3与2行相关.这意味着从2012年8月27日至30日每天的08:30至12:00，将在room2中使用1笔(eqId 2)和3台计算机(eqId 3).

In table C: Autoid represents unique automatic id generated by auto-increment, scheduleid comes from Table B, eqid comes from Table A, amountInSch represents how many (amount) equipment will use in the related schedule. I want to give an example here. Scheduleid 1 in Table C, there are 3 rows. This means that scheduleid 1 related in TAble B will use 2 books (eqid 1), 3 pens (eqid 2) and 1 computer (eqid 3) in room1 specified dates and times in table B . Another example is that scheduleid 3 in Table C is related 2 rows. It means that 1 pen (eqId 2) and 3 computers (eqId 3) will be using in room2 from 27th to 30th of august 2012 everyday from 08:30 to 12:00.

以上是说明，并提供了有关该项目的一些信息.表行不是永久的.预订时，表B中将有新行，如果选择了设备，则表C中将有新行...

The above is the explanation and give some information about the project. The table rows are not permanent. When you make a reservation, there will be a new row in Table B and if it is selected an equipment, there will be new rows in table C...

问题:

当我提供eqId，startDate，endDate，startTime和endTime ...时，我想计算特定设备的剩余数量.

I want to calculate left amount of a specific equipment when I supply eqId, startDate, endDate, startTime and endTime...

一个例子:

eqId:1(本书)

eqId: 1 (book)

startDate:2012-08-27

startDate: 2012-08-27

结束日期:2012-08-27

endDate: 2012-08-27

开始时间:08:30:00

startTime: 08:30:00

结束时间:12:00:00

endTime: 12:00:00

结果应该是:计划中使用了14本书，剩余76本书可用

Result should be: 14 books used in schedule and 76 left available books

因为:如果您查看scheduleId和相关的eqId，您只会看到与我的查询(日期和eqId)有关的1，2，6，7，7，9 scheduleId.如果将表C中的所有相关金额加起来，将得到错误的结果.换句话说，eqId(1-book)和1，2，6，7，9 scheduleIds的相关量分别为2，1，1，5，5，8.因此，如果将它们相加，将得到17，这是错误的.因为1和9的时间表在开始时间和结束时间上不相交，而6和7的时间表也不相交.因此，他们2保持孤独，可以单独计算.我们必须将1和9求和为8，因为8大于2.对于6和7，相同，因为5大于1而被认为是5 ...

Because: if you look scheduleIds and related eqIds, you will only see 1, 2, 6, 7, 9 scheduleIds related to my query(dates and eqId). If you sum the all amount of related in Table C, you will get the wrong result. In other words, related amounts for eqId(1-book) and for 1, 2, 6, 7, 9 scheduleIds are 2, 1, 1, 5, 8 respectively. So if you sum them you will get 17 which is wrong. Because, 1 and 9 schedule don't intersect each other in terms of start and end Times, and 6 and 7 don't intersect each other either. as a result of them 2 stays lonely and can be count separately. We must consider 1 and 9 as summed 8 because 8 is bigger than 2. it is same for 6 and 7, considered as 5 because of 5 is bigger than 1...

乡亲们！我不确定如何在编程算法中总结一下.有没有办法在SQL中执行操作，还是必须同时使用PHP和Mysql?以及如何?

So folks! I am not sure how I can sum/ this in programming algorithm. Is there a way to do in SQL or do I have to use PHP and Mysql together? and How?

干杯！

推荐答案

我从以下SQL开始，以收集与给定范围相交的所有日期范围:

I started with the following SQL to gather all date ranges that intersect with the given range:

SELECT MAX(available) - IFNULL(SUM(amountInSch), 0)
FROM Table1
LEFT JOIN Table3 USING (eqid)
LEFT JOIN Table2 USING (scheduleid)
WHERE DATE(startDate) <= '2012-08-27' AND DATE(endDate) >= '2012-08-27'
  AND endTime > '08:30' AND startTime < '12:00'
  AND eqid = 1

小提琴

这只是第一部分.接下来，您必须找出可能的重叠之处；这对于SQL来说是不切实际的，所以我建议在PHP中做到这一点.

This is only the first part. Next up you have to work out the possible overlaps; this wouldn't be practical to do with SQL, so I would suggest to do this in PHP.

不幸的是，我选择的通用算法是O(n ** 2)，它像这样:

The generic algorithm I would pick is unfortunately O(n**2), it goes like this:

• 创建一个以时间为横轴的时间轴(每天划分)
• 在每个日期/时间范围内迭代并标记其左边缘和右边缘的时间，以创建每个可能排列的时间段.
• 使用细分，您可以垂直汇总重叠部分，并获得每日的最大值.

希望有帮助.

这篇关于计算日期范围内的设备总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..