mirror of
https://gitee.com/coder-xiaomo/leetcode-problemset
synced 2025-10-12 08:55:14 +08:00
145 lines
6.5 KiB
HTML
145 lines
6.5 KiB
HTML
<p>表：<code>books</code></p>
|
||
|
||
<pre>
|
||
+-------------+---------+
|
||
| Column Name | Type |
|
||
+-------------+---------+
|
||
| book_id | int |
|
||
| title | varchar |
|
||
| author | varchar |
|
||
| genre | varchar |
|
||
| pages | int |
|
||
+-------------+---------+
|
||
book_id 是这张表的唯一主键。
|
||
每一行包含关于一本书的信息，包括其类型和页数。
|
||
</pre>
|
||
|
||
<p>表：<code>reading_sessions</code></p>
|
||
|
||
<pre>
|
||
+----------------+---------+
|
||
| Column Name | Type |
|
||
+----------------+---------+
|
||
| session_id | int |
|
||
| book_id | int |
|
||
| reader_name | varchar |
|
||
| pages_read | int |
|
||
| session_rating | int |
|
||
+----------------+---------+
|
||
session_id 是这张表的唯一主键。
|
||
每一行代表一次阅读事件，有人阅读了书籍的一部分。session_rating 在 1-5 的范围内。
|
||
</pre>
|
||
|
||
<p>编写一个解决方案来找到具有 <strong>两极分化观点</strong> 的书 - 同时获得不同读者极高和极低评分的书籍。</p>
|
||
|
||
<ul>
|
||
<li>如果一本书有至少一个大于等于 <code>4</code> 的评分和至少一个小于等于 <code>2</code> 的评分则是有两极分化观点的书</li>
|
||
<li>只考虑有至少 <code>5</code> 次阅读事件的书籍</li>
|
||
<li>按 <code>highest_rating - lowest_rating</code> 计算评分差幅 <strong>rating spread</strong></li>
|
||
<li>按极端评分（评分小于等于 <code>2</code> 或大于等于 <code>4</code>）的数量除以总阅读事件计算 <strong>极化得分 polarization score</strong></li>
|
||
<li><strong>只包含</strong> 极化得分大于等于 <code>0.6</code> 的书（至少 <code>60%</code> 极端评分）</li>
|
||
</ul>
|
||
|
||
<p>返回结果表按极化得分 <strong>降序</strong> 排序，然后按标题 <strong>降序</strong> 排序。</p>
|
||
|
||
<p>返回格式如下所示。</p>
|
||
|
||
<p> </p>
|
||
|
||
<p><strong class="example">示例：</strong></p>
|
||
|
||
<div class="example-block">
|
||
<p><strong>输入：</strong></p>
|
||
|
||
<p>books 表：</p>
|
||
|
||
<pre class="example-io">
|
||
+---------+------------------------+---------------+----------+-------+
|
||
| book_id | title | author | genre | pages |
|
||
+---------+------------------------+---------------+----------+-------+
|
||
| 1 | The Great Gatsby | F. Scott | Fiction | 180 |
|
||
| 2 | To Kill a Mockingbird | Harper Lee | Fiction | 281 |
|
||
| 3 | 1984 | George Orwell | Dystopian| 328 |
|
||
| 4 | Pride and Prejudice | Jane Austen | Romance | 432 |
|
||
| 5 | The Catcher in the Rye | J.D. Salinger | Fiction | 277 |
|
||
+---------+------------------------+---------------+----------+-------+
|
||
</pre>
|
||
|
||
<p>reading_sessions 表：</p>
|
||
|
||
<pre class="example-io">
|
||
+------------+---------+-------------+------------+----------------+
|
||
| session_id | book_id | reader_name | pages_read | session_rating |
|
||
+------------+---------+-------------+------------+----------------+
|
||
| 1 | 1 | Alice | 50 | 5 |
|
||
| 2 | 1 | Bob | 60 | 1 |
|
||
| 3 | 1 | Carol | 40 | 4 |
|
||
| 4 | 1 | David | 30 | 2 |
|
||
| 5 | 1 | Emma | 45 | 5 |
|
||
| 6 | 2 | Frank | 80 | 4 |
|
||
| 7 | 2 | Grace | 70 | 4 |
|
||
| 8 | 2 | Henry | 90 | 5 |
|
||
| 9 | 2 | Ivy | 60 | 4 |
|
||
| 10 | 2 | Jack | 75 | 4 |
|
||
| 11 | 3 | Kate | 100 | 2 |
|
||
| 12 | 3 | Liam | 120 | 1 |
|
||
| 13 | 3 | Mia | 80 | 2 |
|
||
| 14 | 3 | Noah | 90 | 1 |
|
||
| 15 | 3 | Olivia | 110 | 4 |
|
||
| 16 | 3 | Paul | 95 | 5 |
|
||
| 17 | 4 | Quinn | 150 | 3 |
|
||
| 18 | 4 | Ruby | 140 | 3 |
|
||
| 19 | 5 | Sam | 80 | 1 |
|
||
| 20 | 5 | Tara | 70 | 2 |
|
||
+------------+---------+-------------+------------+----------------+
|
||
</pre>
|
||
|
||
<p><strong>输出：</strong></p>
|
||
|
||
<pre class="example-io">
|
||
+---------+------------------+---------------+-----------+-------+---------------+--------------------+
|
||
| book_id | title | author | genre | pages | rating_spread | polarization_score |
|
||
+---------+------------------+---------------+-----------+-------+---------------+--------------------+
|
||
| 1 | The Great Gatsby | F. Scott | Fiction | 180 | 4 | 1.00 |
|
||
| 3 | 1984 | George Orwell | Dystopian | 328 | 4 | 1.00 |
|
||
+---------+------------------+---------------+-----------+-------+---------------+--------------------+
|
||
</pre>
|
||
|
||
<p><strong>解释：</strong></p>
|
||
|
||
<ul>
|
||
<li><strong>了不起的盖茨比（book_id = 1）：</strong>
|
||
|
||
<ul>
|
||
<li>有 5 次阅读事件（满足最少要求）</li>
|
||
<li>评分：5, 1, 4, 2, 5</li>
|
||
<li>大于等于 4 的评分：5，4，5（3 次事件）</li>
|
||
<li>小于等于 2 的评分：1，2（2 次事件）</li>
|
||
<li>评分差：5 - 1 = 4</li>
|
||
<li>极端评分（≤2 或 ≥4）：所有 5 次事件（5，1，4，2，5）</li>
|
||
<li>极化得分：5/5 = 1.00（≥ 0.6，符合）</li>
|
||
</ul>
|
||
</li>
|
||
<li><strong>1984 (book_id = 3):</strong>
|
||
<ul>
|
||
<li>有 6 次阅读事件（满足最少要求）</li>
|
||
<li>评分：2，1，2，1，4，5</li>
|
||
<li>大于等于 4 的评分：4，5（2 次事件）</li>
|
||
<li>小于等于 2 的评分：2，1，2，1（4 次事件）</li>
|
||
<li>评分差：5 - 1 = 4</li>
|
||
<li>极端评分（≤2 或 ≥4）：所有 6 次事件（2，1，2，1，4，5）</li>
|
||
<li>极化得分：6/6 = 1.00 (≥ 0.6，符合）</li>
|
||
</ul>
|
||
</li>
|
||
<li><strong>未包含的书：</strong>
|
||
<ul>
|
||
<li>杀死一只知更鸟（book_id = 2）：所有评分为 4-5，没有低分（≤2）</li>
|
||
<li>傲慢与偏见（book_id = 4）：只有 2 次事件（< 最少 5 次）</li>
|
||
<li>麦田里的守望者（book_id = 5）：只有 2 次事件（< 最少 5 次）</li>
|
||
</ul>
|
||
</li>
|
||
</ul>
|
||
|
||
<p>结果表按极化得分降序排序，然后按标题降序排序。</p>
|
||
</div>
|