sql做题第十三天

第二十九例：文本函数，截取出年龄

• 题目地址：截取出年龄牛客题霸牛客网 (nowcoder.com)
  
• 初始化数据：

droptable if exists user_submit;

CREATETABLE `user_submit` (

`id` intNOTNULL,

`device_id` intNOTNULL,

`profile` varchar(100) NOTNULL,

`blog_url` varchar(100) NOTNULL

);

INSERTINTO user_submit VALUES(1,2138,'180cm,75kg,27,male','http:/url/bisdgboy777');

INSERTINTO user_submit VALUES(1,3214,'165cm,45kg,26,female','http:/url/dkittycc');

INSERTINTO user_submit VALUES(1,6543,'178cm,65kg,25,male','http:/url/tigaer');

INSERTINTO user_submit VALUES(1,4321,'171cm,55kg,23,female','http:/url/uhsksd');

INSERTINTO user_submit VALUES(1,2131,'168cm,45kg,22,female','http:/url/sysdney');

• 题目描述：想要统计每个年龄的用户分别有多少参赛者

• 分析1：需要截取文本内容，可以使用函数：substring_index再字段profile中
  用法： substring_index（“待截取有用部分的字符串”，“截取数据依据的字符”，截取字符的位置N）

select substring_index(profile,',',2)

from

user_submit

• 代码示例：

select

substring_index(substring_index(profile,',',-2),',',1) as age,

count(device_id) as number

from

user_submit

groupby

age;

第三十例：窗口函数，找出每个学校GPA最低的同学

• 题目地址：找出每个学校GPA最低的同学牛客题霸牛客网 (nowcoder.com)
  
• 初始化数据：

droptable if exists user_profile;

CREATETABLE `user_profile` (

`id` intNOTNULL,

`device_id` intNOTNULL,

`gender` varchar(14) NOTNULL,

`age` int ,

`university` varchar(32) NOTNULL,

`gpa` float,

`active_days_within_30` int ,

`question_cnt` int ,

`answer_cnt` int

);

INSERTINTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);

INSERTINTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);

INSERTINTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);

INSERTINTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);

INSERTINTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);

INSERTINTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);

INSERTINTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

• 题目描述：想要找到每个学校gpa最低的同学来做调研

• 分析1：先统计每个学校最低gpa的同学：

select

university,

min(gpa) min_gpa

from

user_profile

groupby

university;

• 分析2：通过use_profile表关联上面的数据，然后通过内连接（inner join）去匹配相应学校与最低gpa的值

select

up1.device_id,up1.university,up1.gpa

from

user_profile up1

inner join

(select

university,

min(gpa) min_gpa

from

user_profile

groupby

university) up2

on

up1.university=up2.university

and

up1.gpa=up2.gpa;

• 代码示例：

select

up1.device_id,

up1.university,

up1.gpa

from

user_profile up1

inner join (

   select

     university,

     min(gpa) min_gpa

   from

     user_profile

   groupby

     university

 ) up2

on

up1.university = up2.universityand up1.gpa = up2.min_gpa

orderby

up1.universityasc;

• 分析2：窗口函数，先按university分组计算，同时按gpa排序。基本语法

<窗口函数> over (partition by <用于分组的列名> orderby <用于排序的列名>)

• <窗口函数>的位置，可以放以下两种函数：

1. 专用窗口函数rank, dense_rank, row_number等专用窗口函数。
   
2. 聚合函数，如sum. avg, count, max, min等，因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
   
3. 专用窗口函数rank说明:RANK函数返回当前行在分区中的名次。如果存在名次相同的数据，后续的排名将会产生跳跃
   
4. 专用窗口函数ROW_NUMBER说明:ROW_NUMBER函数可以为分区中的每行数据分配一个序列号，序列号从1开始。
   
5. 专用窗口函数dense_rank说明:存在名次相同的数据，后续的排名也是连续值。
   

• 代码示例

select

device_id,

university,

gpa

from

(

   select

   *,

   row_number() over (

       partition by university

       orderby

           gpa

       )as rn

   from

   user_profile

) as temp

where

temp.rn = 1;