mysql - 使用 mySQL 从多个表返回排名

Question

这是我的表结构:

___语言:

|--------|------------|
| LAN_Id | LAN_En     |
|--------|------------|
| DI     | Direct     |
| WE     | Web        |
| OT     | Other      |
|--------|------------|

___分割:

|--------|------------|
| SEG_Id | SEG_Code   |
|--------|------------|
| 1      | DI         |
| 2      | WE         |
| 3      | OT         |
|--------|------------|

___预订:

|--------|------------------|
| BOO_Id | BOO_Segmentation |
|--------|------------------|
| 1      | 1                |
| 2      | 1                |
| 3      | 2                |
|--------|------------------|

___计费数据:

|--------|---------------|------------|------------|
| BIL_Id | BIL_BookingId | BIL_Date   | BIL_Item   |
|--------|---------------|------------|------------|
| 1      | 1             | 2017-02-21 | Night      |
| 2      | 1             | 2017-02-22 | Night      |
| 3      | 1             | 2017-02-23 | Night      |
| 4      | 1             | 2017-02-24 | Night      |
| 5      | 2             | 2017-02-25 | Night      |
| 6      | 2             | 2017-02-26 | Night      |
| 7      | 3             | 2017-02-28 | Night      |
| 8      | 3             | 2017-03-01 | Night      |
| 9      | 3             | 2017-03-02 | Night      |
| 10     | 3             | 2017-03-03 | Night      |
|--------|---------------|------------|------------|

---

我想知道某个日期范围内最受欢迎的分割。

所需的结果应该是以下日期范围内的结果:

表格 2017-02-01 至 2017-02-28(含)

|------------|------------|------------|--------------|------------|
| ROO_Name   | Night_Nb   | Percentage | Booking_Nb   | Percentage |
|------------|------------|------------|--------------|------------|
| Direct     | 6          | 85.71      | 2            | 66.66      |
| Website    | 1          | 14.28      | 1            | 33.33      |
| Other      | 0          | 0          | 0            | 0          |
|------------|------------|------------|--------------|------------|

---

我已经尝试过的:

SELECT r.SEG_Id
     , Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) Night_Nb
     , Concat(
         Format(
           Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) 
           / TotalBookings 
           * 100
         , 0) ) AS PercentageTotal
  FROM (  ___Segmentations r LEFT JOIN ___Bookings b ON r.SEG_Id = b.BOO_Segmentation
       ) INNER JOIN (SELECT BOO_HotelId
                          , Count(*) AS TotalBookings
                       FROM ___Bookings 
                      GROUP BY BOO_HotelId
                    ) AS TotalHotelBookings 
                 ON r.SEG_HotelId = TotalHotelBookings.BOO_HotelId
 WHERE r.SEG_HotelId = :hotel_id
 GROUP BY r.SEG_Id
 ORDER BY NumBookings DESC

但实际上并没有用。

谁能帮我解决这个问题？

您可以使用 SQL Fiddle: http://sqlfiddle.com/#!9/1aa10a

Answer 1

我建议我们逐步构建查询。验证查询结果是否如我们在每一步所期望的那样。当某事“不起作用”时，备份一个步骤。

我们想要返回三行，一个用于 ___Segmentations 中的每一行，用于特定的 hotelid

 SELECT r.seg_id
      , r.seg_text
   FROM ___Segmentations r
  WHERE r.seg_hotelid = :hotel_id
  ORDER BY r.seg_id

将外部联接添加到 __Bookings

 SELECT r.seg_id
      , r.seg_text
      , b.boo_id
   FROM ___Segmentations r
   LEFT
   JOIN ___Bookings b
     ON b.boo_segmentation = r.seg_id
  WHERE r.seg_hotelid = :hotel_id
  ORDER
     BY r.seg_id
      , b.boo_id

将外部连接添加到 ___BillableDatas

 SELECT r.seg_id
      , r.seg_text
      , b.boo_id
      , d.bil_id
   FROM ___Segmentations r
   LEFT
   JOIN ___Bookings b
     ON b.boo_segmentation = r.seg_id
   LEFT
   JOIN `___BillableDatas` d
     ON d.bil_bookingid = b.boo_id
  WHERE r.seg_hotelid = :hotel_id
  ORDER
     BY r.seg_id
      , b.boo_id
      , d.bil_id

如果这是我们感兴趣的行，我们可以进行聚合。

 SELECT r.seg_id
      , r.seg_text
      , COUNT(DISTINCT b.boo_id) AS cnt_bookings
      , COUNT(DISTINCT d.bil_id) AS cnt_billable
   FROM ___Segmentations r
   LEFT
   JOIN ___Bookings b
     ON b.boo_segmentation = r.seg_id
   LEFT
   JOIN `___BillableDatas` d
     ON d.bil_bookingid = b.boo_id
  WHERE r.seg_hotelid = :hotel_id
  GROUP
     BY r.seg_id
      , r.seg_text
  ORDER
     BY r.seg_text

现在获取带有“总数”的聚合。

我将采用的方法是使用 CROSS JOIN 操作制作行的“副本”。我们可以对我们编写的第一个查询返回的行进行连接，引用为内联 View 。 (在下面别名为 q。)

如果我们有一组完整的行，并为每个 seg_id/seg_text(我们编写的第一个查询)重复，我们可以使用条件聚合。

我们写的最后一个查询(就在上面)是下面查询中的一个内联 View ，别名为 c。

所有行的 cnt_bookings 的 SUM 是总数。

对于单个计数，我们可以仅包含具有匹配 seg_id 的行，即该子集的总数。

 SELECT q.seg_id
      , q.seg_text
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_bookings,0))  AS cnt_bookings
      , SUM(c.cnt_bookings)                          AS tot_bookings
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_billable,0))  AS cnt_billable
      , SUM(c.cnt_billable)                          AS tot_billable
   FROM ( SELECT t.seg_id
               , t.seg_text
            FROM ___Segmentations t
           WHERE t.seg_hotelid = :hotel_id_1
           ORDER BY t.seg_id
        ) q
  CROSS
   JOIN ( SELECT r.seg_id
               , COUNT(DISTINCT b.boo_id) AS cnt_bookings
               , COUNT(DISTINCT d.bil_id) AS cnt_billable
            FROM ___Segmentations r
            LEFT
            JOIN ___Bookings b
              ON b.boo_segmentation = r.seg_id
            LEFT
            JOIN `___BillableDatas` d
              ON d.bil_bookingid = b.boo_id
           WHERE r.seg_hotelid = :hotel_id
           GROUP
              BY r.seg_id
        ) c
  GROUP
     BY q.seg_id
      , q.seg_text
  ORDER
     BY q.seg_text

在SELECT列表中，我们可以做除法得到百分比:cnt_bookings * 100.0/tot_bookings

例如

 SELECT q.seg_id
      , q.seg_text

      , SUM(IF(c.seg_id=q.seg_id,c.cnt_bookings,0))  AS cnt_bookings
      , SUM(c.cnt_bookings)                          AS tot_bookings
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_bookings,0))
        * 100.0 / SUM(c.cnt_bookings)                AS pct_bookings

      , SUM(IF(c.seg_id=q.seg_id,c.cnt_billable,0))  AS cnt_billable
      , SUM(c.cnt_billable)                          AS tot_billable
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_billable,0))
        * 100.0 / SUM(c.cnt_billable)                AS pct_billable

修改 ORDER BY 子句以按您想要的顺序返回行

从 SELECT 列表中删除返回 tot_bookings 和 tot_billable 的表达式。

编辑

我想我错过了日期标准。我们可以把外连接变成内连接，把 CROSS JOIN 换成 LEFT JOIN。我们有可能为 cnt_bookings 和 cnt_billable 返回 NULL 值，我们可以将它们包装在 IFNULL() 或 COALESCE() 函数中以用零替换 NULL。

 SELECT q.seg_id
      , q.seg_text

      , SUM(IF(c.seg_id=q.seg_id,c.cnt_bookings,0))  AS cnt_bookings
      , SUM(c.cnt_bookings)                          AS tot_bookings
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_bookings,0))
        * 100.0 / SUM(c.cnt_bookings)                AS pct_bookings

      , SUM(IF(c.seg_id=q.seg_id,c.cnt_billable,0))  AS cnt_billable
      , SUM(c.cnt_billable)                          AS tot_billable
      , SUM(IF(c.seg_id=q.seg_id,c.cnt_billable,0))
        * 100.0 / SUM(c.cnt_billable)                AS pct_billable

   FROM ( SELECT t.seg_id
               , t.seg_text
            FROM ___Segmentations t
           WHERE t.seg_hotelid = :hotel_id_1
           ORDER BY t.seg_id
        ) q
   LEFT
   JOIN ( SELECT r.seg_id
               , COUNT(DISTINCT b.boo_id) AS cnt_bookings
               , COUNT(DISTINCT d.bil_id) AS cnt_billable
            FROM ___Segmentations r
            JOIN ___Bookings b
              ON b.boo_segmentation = r.seg_id
            JOIN `___BillableDatas` d
              ON d.bil_bookingid = b.boo_id
             AND d.bil_date BETWEEN '2017-02-21' AND '2017-02-28'
           WHERE r.seg_hotelid = :hotel_id
           GROUP
              BY r.seg_id
        ) c
     ON 1=1   
  GROUP
     BY q.seg_id
      , q.seg_text
  ORDER
     BY q.seg_text