mysql - 从 MySQL 查询计算百分比

Question

我正在尝试查找最畅销的 5 本书。

我计算前 5 名畅销书的想法是这样的:

percentage = number_of_SUCCESS_transactions_each_book / total_number_transactions_each_book

Fetch the result(book_id, percentage) sorted in DESC order, with a LIMIT of 5

为了便于理解，以下是包含数据的表的简单表示:

tblPayments
-----------
trans_id | book_id | payment_status | purchase_date
---------------------------------------------------
1   |   233 | SUCCESS   | 2017-04-05
2   |   145 | FAILED    | 2017-04-10
3   |   233 | FAILED    | 2017-04-05
4   |   233 | SUCCESS   | 2017-04-05


tblBooks
--------
book_id | book_name
-------------------
233 | My Autobiography
145 | How to learn English
201 | Finding Nemo

我将查询特定日期之间最畅销的 5 本书。例如，2017-04-01 到 2017-04-25

我期望的输出是这样的:

book_id | book_name  | percentage
----------------------------------
233 | My Autobiography      | 67
145 | How to learn English  | 0
201 | Finding Nemo          | 0

经过几个小时的头脑 Storm ，我的想法是:

SELECT b.`book_id`, ( 
    (   
        ( SELECT COUNT(*) FROM `tblPayments` WHERE `book_id` = b.`book_id` AND `payment_status` = 'SUCCESS' ) / 
        ( SELECT COUNT(*) FROM `tblPayments` WHERE `book_id` = b.`book_id` ) 
    ) * 100.0 ) AS `percentage` 
FROM `tblPayments` AS b 
WHERE b.`purchase_date` BETWEEN '2017-04-01' AND '2017-04-25' 
GROUP BY b.`book_id` 
ORDER BY `percentage` DESC LIMIT 5

可以进一步改进吗？它会导致数据库出现任何性能问题吗？

现在我正在乘火车回我家。所以我是在平板电脑上从我的脑海里写下这篇文章的。当我大约 6 小时后回到家时，我就可以测试它了。所以我想同时在这里问一下。

或者您有比这更好的方法的建议吗？

谢谢

<小时/>

编辑

感谢@Strawberry 和@Stefano Zanini 的回答。

还有一个疑问。如果我只是 JOIN 与 tblBooks 来获取结果集中的 book_name 字段，可以吗？

我的意思是，这个tblPayments 表应该有大量行。那么 JOIN 可以吗？或者我应该在 PHP 中获取这 5 行，然后执行另一个查询来获取这 5 本书中每本书的 book_name ？什么是有效的方法？

Answer 1

DROP TABLE IF EXISTS transactions;

CREATE TABLE transactions
(transaction_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,book_id INT NOT NULL
,transaction_status VARCHAR(12) NOT NULL 
,transaction_date DATE NOT NULL
);

INSERT INTO transactions VALUES
(1,233,'SUCCESS','2017-04-05'),
(2,145,'FAILED','2017-04-10'),
(3,233,'FAILED','2017-04-05'),
(4,233,'SUCCESS','2017-04-05');

SELECT book_id
     , SUM(CASE WHEN transaction_status = 'success' THEN 1 ELSE 0 END)/COUNT(*) success_rate 
  FROM transactions 
 GROUP 
    BY book_id
+---------+--------------+
| book_id | success_rate |
+---------+--------------+
|     145 |       0.0000 |
|     233 |       0.6667 |
+---------+--------------+

我省略了一些琐碎的部分。