计算租赁期价格

Question

我正在为一个项目工作，我需要在选定的租期内计算假日住房的价格。我需要一些帮助来构建一个SQL查询，这个查询结合了下面的表，并将数据转换成一个输出，其中包含了每个房子所需期间的价格。它应该包含停留费用，以及附加的费用类型，以及租房者应该为每个cost_type支付的金额。

我有一份费用表，使房主全年都能有多个价格：

+----------------+----------+--------------+
| costprofile_id | house_id | profile_name |
+----------------+----------+--------------+
|              1 |      312 | summer       |
+----------------+----------+--------------+
|              2 |      312 | winter       |
+----------------+----------+--------------+

我有一个名为costprofile_items的表，它通过外键costprofile_id链接到一个成本配置文件。如果所选期间的价格使用此cost_type，则此表包含租者应支付给所有者的所有不同金额。每增加一笔数额都可以用四种不同的方式计算：

1. 每晚
2. 每次入住
3. 每人
4. 每人每晚

每个金额对总租金的贡献方式存储在calculation_type列中。这就是costprofile_items表的样子：

+---------------------+----------------+--------+-------------+----------------------+
| costprofile_item_id | costprofile_id | amount | cost_type   | calculation_type     |
+---------------------+----------------+--------+-------------+----------------------+
|                   1 |              1 |     20 | usage_cost  | per_night            |
+---------------------+----------------+--------+-------------+----------------------+
|                   2 |              1 |    8.5 | cleaning    | per_stay             |
+---------------------+----------------+--------+-------------+----------------------+
|                   3 |              1 |   0.82 | tourist_tax | per_person_per_night |
+---------------------+----------------+--------+-------------+----------------------+

我还有一个表中的价格，其中每一行表示可以在start_date和end_date之间使用的每晚价格( start_date的工作日等于到达房屋的工作日，end_date的平日等于出发的工作日)。该行还包含一个列夜，该列夜确定使用此价格需要多长时间的子周期。这张表是这样的：

+----------+----------+----------------+------------+------------+-----------+--------+
| price_id | house_id | costprofile_id | start_date | end_date   | per_night | nights |
+----------+----------+----------------+------------+------------+-----------+--------+
|        1 |        1 |              1 | 2014-08-04 | 2014-12-01 |        60 |      7 |
+----------+----------+----------------+------------+------------+-----------+--------+
|        2 |        1 |              1 | 2014-08-08 | 2014-12-05 |        70 |      3 |
+----------+----------+----------------+------------+------------+-----------+--------+
|        3 |        1 |              2 | 2014-12-01 | 2015-03-02 |         0 |      1 |
+----------+----------+----------------+------------+------------+-----------+--------+

在表中，您可以看到，对于给定的房子，您可以预订从8月8日至11日，这将花费3*70 =210欧元的停留。如果你和4人在一起，电费/煤气费是3*20 =60欧元，清洁费是8.5欧元，旅游税是0.82*4*3 =9.84欧元。所以你周末的总花费是288.34欧元。这也应该是可能的结合这个周末，例如，2倍的周价格，如表的第一行所描述的。在这种情况下，8月8日至25日的价格为288.34 + 2*582.96 =1454.26欧元。注意，计算类型per_stay和per_person只需要从第一个子周期中选择，因此最后一个示例中的清理只需要支付一次。

我用来计算价格的最后一个表格是表prices_per_group。此表通过外键price_id连接到价格。在上面的价格表中，您可以在最后一行看到每晚的价格等于0。在这种情况下，房主给出了他在此期间接受的每几个人的每晚价格，这一价格是活跃的。这些不同的价格就是这样储存的：

+--------------------+----------+------------+-----------+
| price_per_group_id | price_id | group_size | per_night |
+--------------------+----------+------------+-----------+
|                  1 |        3 |          5 |        50 |
+--------------------+----------+------------+-----------+
|                  2 |        3 |          4 |        45 |
+--------------------+----------+------------+-----------+

如你所见，从12月1日开始的一周(或之后的任何星期一，但在3月2日之前)，如果你和5个人在一起，每晚花费50欧元，如果你和4个人在一起，则花费45欧元。

我希望现在能清楚地知道我是如何存储和计算所有不同的价格。

通过首先使用以下查询查询每个可用房屋的所有成本类型，我成功地完成了这些计算：

SELECT * FROM (
        SELECT prices.house_id, 
               prices.price_id, 
               prices.costprofile_id, 
               prices.nights, 
               prices.start_date, 
               prices.end_date, 
               MIN( 
                   prices.per_night + COALESCE(prices_per_group.per_night, 0) 
               ) AS per_night       /* Add the price per night from prices and prices_per_group (if one has a non-zero value the other is always zero) */
        FROM prices
        LEFT JOIN prices_per_group ON prices.price_id = prices_per_group.price_id
        WHERE prices.house_id IN (
                                    /* Query that returns a set with the ids of all available houses here */
                                 )
        AND (
                prices_per_group.price_id IS NULL OR      /* If true, no row in prices_per_group is pointing to the price_id currently being evaluated */
                prices_per_group.group_size >= 4          /* If true, the group_size satisfies the requested number of persons */
            )
        GROUP BY prices.price_id
    ) AS possible_prices
INNER JOIN costprofile_items ON costprofile_items.costprofile_id = possible_prices.costprofile_id
ORDER BY price_id ASC

之后，我使用PHP循环遍历包含某栋房子价格信息的所有行。我从start_date开始，使用它可以找到的第一个可用价格行执行步骤，并重复这一步骤，直到我到达end_date为止。我现在的方法的问题是它太慢了。对于1000台房屋，For服务器需要0.3秒的执行时间。也许可以在我的PHP代码中进行一些优化，但我希望有人能帮助我将所有这些都放在SQL中。例如，这种方式按价格排序更容易实现，在快速执行上述查询后只要求获得大的结果，使我的执行时间跳到0.12秒。

欢迎所有的帮助和建议。

Answer 1

最后，我决定缓存所有的价格，而不是实时计算它们。这样可以获得更好的性能，并允许比动态内部查询计算的定价要复杂得多。每天晚上都会有一个cronjob运行，可以填满21张桌子(每个可能的租期都有一张桌子)。期限定价表包含密钥、到达日期的值对以及相应的计算期间价格。可选地，您可以为组大小添加一个列，结果是每个持续时间、每个组大小、每个到达日期的价格。它需要相当多的数据库记录，但是如果您创建索引，这是非常迅速的。