今日专项训练 4 题:3 简单 1 中等
1193.每月交易 I
输入:
Transactions table:
+------+---------+----------+--------+------------+
| id | country | state | amount | trans_date |
+------+---------+----------+--------+------------+
| 121 | US | approved | 1000 | 2018-12-18 |
| 122 | US | declined | 2000 | 2018-12-19 |
| 123 | US | approved | 2000 | 2019-01-01 |
| 124 | DE | approved | 2000 | 2019-01-07 |
+------+---------+----------+--------+------------+
输出:
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12 | US | 2 | 1 | 3000 | 1000 |
| 2019-01 | US | 1 | 1 | 2000 | 2000 |
| 2019-01 | DE | 1 | 1 | 2000 | 2000 |
+----------+---------+-------------+----------------+--------------------+-----------------------+
编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。以任意顺序返回结果表。
直接上SQL:
select substr(trans_date,1,7) as 'month', country
,count(id) as trans_count
,sum(case when state = 'approved' then 1 else 0 end ) as approved_count
,sum(amount) as trans_total_amount
,sum(case when state = 'approved' then amount else 0 end ) as approved_total_amount
from Transactions
group by substr(trans_date,1,7),country ;
这里的substr(trans_date,1,7)
也可以切换为DATE_FORMAT(trans_date, '%Y-%m')
1193.各赛事的用户注册率
输入:
Users 表:
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 6 | Alice |
| 2 | Bob |
| 7 | Alex |
+---------+-----------+
#user_id 是该表的主键
Register 表:
+------------+---------+
| contest_id | user_id |
+------------+---------+
| 215 | 6 |
| 209 | 2 |
| 208 | 2 |
| 210 | 6 |
| 208 | 6 |
| 209 | 7 |
| 209 | 6 |
| 215 | 7 |
| 208 | 7 |
| 210 | 2 |
| 207 | 2 |
| 210 | 7 |
+------------+---------+
#(contest_id, user_id) 是该表的主键
输出:
+------------+------------+
| contest_id | percentage |
+------------+------------+
| 208 | 100.0 |
| 209 | 100.0 |
| 210 | 100.0 |
| 215 | 66.67 |
| 207 | 33.33 |
+------------+------------+
写一条 SQL 语句,查询各赛事的用户注册百分率,保留两位小数。返回的结果表按 percentage 的降序排序,若相同则按 contest_id 的升序排序
select contest_id
,round(count(contest_id)/(select count(1) from Users) * 100, 2) as percentage
from Register
group by contest_id
order by percentage desc,contest_id ;
1173.即时食物配送 I
输入:
Delivery 表:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1 | 1 | 2019-08-01 | 2019-08-02 |
| 2 | 5 | 2019-08-02 | 2019-08-02 |
| 3 | 1 | 2019-08-11 | 2019-08-11 |
| 4 | 3 | 2019-08-24 | 2019-08-26 |
| 5 | 4 | 2019-08-21 | 2019-08-22 |
| 6 | 2 | 2019-08-11 | 2019-08-13 |
+-------------+-------------+------------+-----------------------------+
#delivery_id 是表的主键。
#该表保存着顾客的食物配送信息,顾客在某个日期下了订单,并指定了一个期望的配送日期(和下单日期相同或者在那之后)
输出:
+----------------------+
| immediate_percentage |
+----------------------+
| 33.33 |
+----------------------+
如果顾客期望的配送日期和下单日期相同,则该订单称为 「即时订单」,否则称为「计划订单」。写一条 SQL 查询语句获取即时订单所占的百分比, 保留两位小数。
select round(sum(t.imm_flag)/count(t.imm_flag) * 100, 2) as immediate_percentage from(
select
case when order_date = customer_pref_delivery_date then 1 else 0 end as imm_flag
from Delivery ;
) t
也可以将 round(),sum() 与 case…when 整合到一起
select round(
sum(case when order_date = customer_pref_delivery_date then 1 else 0 end)/count(delivery_id)* 100, 2
) as immediate_percentage
from Delivery ;
1211.查询结果的质量和占比
Queries table:
+------------+-------------------+----------+--------+
| query_name | result | position | rating |
+------------+-------------------+----------+--------+
| Dog | Golden Retriever | 1 | 5 |
| Dog | German Shepherd | 2 | 5 |
| Dog | Mule | 200 | 1 |
| Cat | Shirazi | 5 | 2 |
| Cat | Siamese | 3 | 3 |
| Cat | Sphynx | 7 | 4 |
+------------+-------------------+----------+--------+
#此表没有主键,并可能有重复的行。
#此表包含了一些从数据库中收集的查询信息。
#“位置”(position)列的值为 1 到 500 。
#“评分”(rating)列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询
Result table:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog | 2.50 | 33.33 |
| Cat | 0.66 | 33.33 |
+------------+---------+-----------------------+
#Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
#Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33
#Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
#Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33
将查询结果的质量 quality 定义为:各查询结果的评分与其位置之间比率的平均值。
将劣质查询百分比 poor_query_percentage 为:评分小于 3 的查询结果占全部查询结果的百分比。
编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和 劣质查询百分比(poor_query_percentage)。质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。
题目就是一个不说人话 - -,但是可以根据示例了解我们需要做什么。
每一步按照正常的思维来就行:
select query_name
,round(sum(q.rating/q.position)/count(q.result), 2) as quality
,round(sum(case when q.rating < 3 then 1 else 0 end)/count(q.result)* 100, 2) as poor_query_percentage
from Queries q
group by query_name ;
–要9月咯~