今天下班挺早,还能看到落日,天气很有趣,出了公司一半是晴天,飘浮的云彩很像中国水墨画里的远山,蔚蓝的天空晕染了一层浅紫,一半是乌云,落日渐薄西山仍熠熠生辉,阳光散落在河道上,马路上,屋顶上,空中落着小雨,偶尔还伴随着几道闪电,大自然真是奇妙。
今天专项训练重点终于来到了计算函数,说简单,但还是需要思考一小会,说不简单,但思路很清晰完全可以下笔。
一共 3 道题:3 简单
1141.查询近30天活跃用户数
输入:
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1 | 1 | 2019-07-20 | open_session |
| 1 | 1 | 2019-07-20 | scroll_down |
| 1 | 1 | 2019-07-20 | end_session |
| 2 | 4 | 2019-07-20 | open_session |
| 2 | 4 | 2019-07-21 | send_message |
| 2 | 4 | 2019-07-21 | end_session |
| 3 | 2 | 2019-07-21 | open_session |
| 3 | 2 | 2019-07-21 | send_message |
| 3 | 2 | 2019-07-21 | end_session |
| 4 | 3 | 2019-06-25 | open_session |
| 4 | 3 | 2019-06-25 | end_session |
+---------+------------+---------------+---------------+
#该表是用户在社交网站的活动记录。
#该表没有主键,可能包含重复数据。
#activity_type 字段为以下四种值 ('open_session', 'end_session', 'scroll_down', 'send_message')。
#每个 session_id 只属于一个用户
输出:
+------------+--------------+
| day | active_users |
+------------+--------------+
| 2019-07-20 | 2 |
| 2019-07-21 | 2 |
+------------+--------------+
请写SQL查询出截至 2019-07-27(包含2019-07-27),近 30 天的每日活跃用户数(当天只要有一条活动记录,即为活跃用户)。以任意顺序返回结果表。
言简意赅,直接按题意写出SQL:
select activity_date as 'day' ,count(distinct user_id) as active_users
from Activity where datediff('2019-07-27',activity_date) < 30 and datediff('2019-07-27',activity_date) >= 0
group by activity_date;
需要注意的是在 where 条件判断中,需要记得添加datediff('2019-07-27',activity_date) >= 0
,因为如果日期大于 2019-07-27,也能得到datediff('2019-07-27',activity_date) < 30
,但这样是不符合题意的。
在题目中,给出了表 Activity 较多字段及内容解释,但这些信息根据题意,无使用价值。
1693.每天的领导和合伙人
输入:
DailySales 表:
+-----------+-----------+---------+------------+
| date_id | make_name | lead_id | partner_id |
+-----------+-----------+---------+------------+
| 2020-12-8 | toyota | 0 | 1 |
| 2020-12-8 | toyota | 1 | 0 |
| 2020-12-8 | toyota | 1 | 2 |
| 2020-12-7 | toyota | 0 | 2 |
| 2020-12-7 | toyota | 0 | 1 |
| 2020-12-8 | honda | 1 | 2 |
| 2020-12-8 | honda | 2 | 1 |
| 2020-12-7 | honda | 0 | 1 |
| 2020-12-7 | honda | 1 | 2 |
| 2020-12-7 | honda | 2 | 1 |
+-----------+-----------+---------+------------+
#该表没有主键
输出:
+-----------+-----------+--------------+-----------------+
| date_id | make_name | unique_leads | unique_partners |
+-----------+-----------+--------------+-----------------+
| 2020-12-8 | toyota | 2 | 3 |
| 2020-12-7 | toyota | 1 | 2 |
| 2020-12-8 | honda | 2 | 2 |
| 2020-12-7 | honda | 3 | 2 |
+-----------+-----------+--------------+-----------------+
写一条 SQL 语句,使得对于每一个 date_id 和 make_name,返回不同的 lead_id 以及不同的 partner_id 的数量。按任意顺序返回结果表。
我想大部分人马上就会想到下面这段SQL,直接对题目进行翻译就好了:
select date_id, make_name
,count(distinct lead_id) as 'unique_leads'
,count(distinct partner_id) as 'unique_partners'
from DailySales group by date_id,make_name;
我又思考了一下,能不能不用 distinct 呢,毕竟还挺消耗性能的。想到可以根据窗口函数 dense_rank() 分别按各个 date 和 name 分组,并按各个 id 顺序排序,取每个分组的最大值就是了。
select t.date_id, t.make_name
,max(t.li_rank) as 'unique_leads'
,max(t.pi_rank) as 'unique_partners' from(
select *
,dense_rank() over(partition by date_id,make_name order by lead_id) as 'li_rank'
,dense_rank() over(partition by date_id,make_name order by partner_id) as 'pi_rank'
from DailySales
) t group by t.date_id,t.make_name;
在我提交上面两种方法后,发现消耗性能都差不多,哈哈哈~
1729.求关注者的数量
输入:
Followers 表:
+---------+-------------+
| user_id | follower_id |
+---------+-------------+
| 0 | 1 |
| 1 | 0 |
| 2 | 0 |
| 2 | 1 |
+---------+-------------+
输出:
+---------+----------------+
| user_id | followers_count|
+---------+----------------+
| 0 | 1 |
| 1 | 1 |
| 2 | 2 |
+---------+----------------+
写出 SQL 语句,对于每一个用户,返回该用户的关注者数量。按 user_id 的顺序返回结果表。
这道题十分简单,没啥好说的啦。
select user_id, count(follower_id) as followers_count
from Followers group by user_id order by user_id;
–今天的练习就到这里啦
–欢迎大家多多交流哦