mysql date转string_掌握MySQL！你有多熟练？

#想要获取top10%的值？—— percentile 百分位函数
 
-- 获取income字段的top10%的阈值
select percentile(CAST (salary AS int),0.9)) as income_top10p_threshold from table_1;
 
-- 获取income字段的10个百分位点
select percentile(CAST (salary AS int),array(0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0)) as income_percentiles
from table_1;
 
#CAST它常用于string/int/double型的转换。

SELECT t.用户类型,avg(t.访问量) from
(SELECT *,row_number() over  (ORDER BY 访问量 desc) as ranking from 拼多多访问) t
where t.ranking>(SELECT count(用户编号) from 拼多多访问)*0.2
GROUP BY t.用户类型;

select concat('www','.iteblog','.com') from
iteblog;
 
--得到 www.iteblog.com
 
#变形①--以分隔符连接
SELECT CONCAT_WS(',','GOOD','JOB') 
 
---得到GOOD,JOB
 
#变形②--同组连接
select dept_no,group_concat(emp_no) as employees from dept_emp
group by dept_no
;
 
--将表dept_emp中相同dept_no的emp_no连接（from牛客网53题）

-- 以","为分隔符分割字符串，并转化为array
Select split("1,2,3",",")as value_array from table_1;
 
-- 结合array index,将原始字符串分割为3列
select value_array[0],value_array[1],value_array[2] from 
(select  split("1,2,3",",")as value_array from table_1 )t

select substr('abcde',3,2) from
iteblog;
 
-- 得到cd

#总长度-剔除（，）长度
#剔除（，）用replace
SELECT  CHAR_LENGTH('10,好,B')-CHARACTER_LENGTH(replace('10,好,B',',',''))

SELECT replace('10,好,B',',',''),
CHAR_LENGTH('10,好,B'),
CHARACTER_LENGTH(replace('10,好,B',',','')),
length('10,好,B'),
length(replace('10,好,B',',',''))

【思路拆解】
Step1：明确最终输出:包含robot的电影【分类名称】以及对应分类的电影【数目】
Step2：明确限制条件
             ①film.description包含robot(like '%robot%') 
             ②查找出category_id，满足(count(film_category.category_id))>=5
 
【解答】
SELECT c.`name`,count(f.film_id) from film f
LEFT JOIN film_category fc
on f.film_id=fc.film_id
LEFT JOIN category c
on c.category_id=fc.category_id
where f.description like '%robot%'
and fc.category_id=(
select category_id from film_category
group by category_id
having count(film_id)>=5);

【已知】
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
 
【思路一】用distinct去重
select distinct(salary) from salaries
where to_date='9999-01-01'
order by salary desc;
 
【思路二】用group by
select salary from salaries
where to_date='9999-01-01'
group by salary
order by salary desc;

【已知】
CREATE TABLE IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
 
【求解】
select title,count(distinct emp_no) as t from titles
group by title
having count(distinct emp_no)>=2
;

【已知】
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
 
【思路1】用limit函数来限制
select emp_no,salary from salaries  
order by salary desc 
limit 1,1
 
【思路3】第二大就是比最大小的最大
select emp_no,max(salary)
from salaries
where salary < (select max(salary) from salaries)
 
【思路2】用窗口函数排序
select t.emp_no,t.salary from
(select *,row_number() over (order by salary desc) as ranking from salaries) t
where t.ranking=2
;

SELECT t.user_id from
(SELECT user_id,post_time,
ROW_number() over (PARTITION by user_id ORDER BY post_time) as row1,
row_number() over (order by post_time) as row2,
row_number() over (order by post_time)-ROW_number() over (PARTITION by user_id ORDER BY post_time) as diff
from tablename)t
GROUP BY t.user_id,t.diff
having count(t.user_id)>=3
;

SELECT concat(t.user_id,'-',GROUP_CONCAT(DISTINCT t.scene SEPARATOR '-') ) as reasult FROM
(SELECT *,
row_number() over (PARTITION by user_id order by time) as ranking
from scene) t
where t.ranking<3
GROUP BY t.user_id
;

SELECT t.year,t.month,t.value,sum(value) over (PARTITION by year order by t.month) as ysum,
sum(value) over (order by t.ranking) as sum
FROM
(SELECT year(fdate) as year,
month(fdate) as month,
row_number() over (order by fdate) as ranking,
sum(value) as value
from sales
group by year(fdate),month(fdate))t;

【生成表】
create table userlog 
(
    id int ,
    name varchar(10),
    EmailAddress varchar(50),
    lastlogon varchar(50)
);
insert into userlog values(100,'test4','test4@yahoo.cn','2007-11-25 16:31:26');
insert into userlog values(13,'test1','test4@yahoo.cn','2007-3-22 16:27:07');
insert into userlog values(19,'test1','test4@yahoo.cn','2007-10-25 14:13:46');
insert into userlog values(42,'test1','test4@yahoo.cn','2007-10-25 14:20:10');
insert into userlog values(45,'test2','test4@yahoo.cn','2007-4-25 14:17:39');
insert into userlog values(49,'test2','test4@yahoo.cn','2007-5-25 14:22:36');
 
UPDATE userlog set lastlogon=DATE_FORMAT(lastlogon,'%Y-%m-%d %H:%i:%s');
SELECT * from userlog;
 
【查询登录次数】
SELECT name,max(lastlogon) as 最近一次登录时间,
count(DISTINCT date(lastlogon)) as 登录次数 from userlog
group by name;

【1】每日DUA人数
SELECT `登录日期`,count(`用户ID`) as DAU from 活跃用户表
where `登录日期`>20190531
GROUP BY `登录日期`;

【2】每日领取红包人员构成（表连接的时候不仅要id连接，还要时间连接）
 
SELECT p.`抢红包日期`,
count(DISTINCT case when u.`是新用户`='1' then p.`用户ID` else null end) as 新用户数,
count(DISTINCT case when u.`是新用户`='0' then p.`用户ID` else null end) as 老用户数,
count(DISTINCT case when u.`是新用户`is null then p.`用户ID` else null end) as 未登录用户数,
round(sum(p.`金额`)/count(DISTINCT p.`用户ID`),2) as 人均领取金额,
count(p.`用户ID`)/count(DISTINCT p.`用户ID`) as 人均领取次数
from `领取红包表` p
LEFT JOIN `活跃用户表` u
on p.`用户ID`=u.`用户ID` and p.`抢红包日期`=u.`登录日期`
where p.`抢红包日期`>20190531
GROUP BY p.`抢红包日期`
;

【3】每月领取红包状况~这个很简单
SELECT DATE_FORMAT(`抢红包日期`,'%Y-%m') as 月份,
count(DISTINCT `抢红包日期`) as 领取天数,
count(DISTINCT `用户ID`) as 每个月领取红包人数,
sum(`金额`)/count(DISTINCT `用户ID`) as 人均领取金额,
count(`用户ID`)/count(DISTINCT `用户ID`) as 人均领取次数
from `领取红包表`
GROUP BY DATE_FORMAT(`抢红包日期`,'%Y-%m');

【4】登录用户中，每月领取红包人数 和 未领取红包人数
SELECT DATE_FORMAT(u.`登录日期`,'%Y-%m') as 月份,
count(DISTINCT case when p.`用户ID` is not null then u.`用户ID` else null end)as 领红包人数,
count(DISTINCT  case when p.`用户ID` is null then u.`用户ID` else null end)as 未领红包人数
from `活跃用户表` u
LEFT JOIN `领取红包表` p
on u.`用户ID`=p.`用户ID` and u.`登录日期`=p.`抢红包日期`
group by DATE_FORMAT(u.`登录日期`,'%Y-%m')
;

【5】各用户首次登录的日期
 
SELECT t1.`用户ID`,t2.`登录日期` as 注册日期 FROM
(SELECT `用户ID` from `活跃用户表`
group by `用户ID`)t1
LEFT JOIN
(SELECT * from `活跃用户表`
where `是新用户`=1)t2
on t1.`用户ID`=t2.`用户ID`
;

【6】每日登录用户的次日留存、领取红包用户的次日留存、未领取红包用户的次日留存
SELECT t1.`登录日期`,t1.`当日登录人数`,t2.`领取红包数`,t2.`当日领取红包占比`,t1.`用户次日留存率`,t3.`当日领红包次日留存率` from
(SELECT a.`登录日期`,
count(DISTINCT a.`用户ID`) 当日登录人数,
count(case when DATE_ADD(a.`登录日期`,INTERVAL 1 day)=b.`登录日期` then b.`用户ID` else null end) as 次日登录人数,
count(case when DATE_ADD(a.`登录日期`,INTERVAL 1 day)=b.`登录日期` then b.`用户ID` else null end)/count(DISTINCT a.`用户ID`) as 用户次日留存率
from `活跃用户表` a
LEFT JOIN `活跃用户表` b
on a.`用户ID`=b.`用户ID`
GROUP BY a.`登录日期`) t1
JOIN
(SELECT u.`登录日期`,count(DISTINCT u.`用户ID`) as 当日登录数,
count(DISTINCT p.`用户ID`)as 领取红包数,
count(DISTINCT p.`用户ID`)/count(DISTINCT u.`用户ID`) as 当日领取红包占比
from `活跃用户表` u
LEFT JOIN `领取红包表` p
on u.`用户ID`=p.`用户ID` and u.`登录日期`=p.`抢红包日期`
group by u.`登录日期`)t2
on t1.`登录日期`=t2.`登录日期`
JOIN
(SELECT p1.`抢红包日期`,
count(DISTINCT p1.`用户ID`) as 当日抢红包人数,
count(DISTINCT case when DATE_ADD(p1.`抢红包日期`,INTERVAL 1 day)=u1.`登录日期` then u1.`用户ID` else null end) as 领红包次日登录人数,
count(DISTINCT case when DATE_ADD(p1.`抢红包日期`,INTERVAL 1 day)=u1.`登录日期` then u1.`用户ID` else null end)/count(DISTINCT p1.`用户ID`) as 当日领红包次日留存率
from `领取红包表` p1
LEFT JOIN `活跃用户表` u1
on p1.`用户ID`=u1.`用户ID` 
group by p1.`抢红包日期`)t3
on t2.`登录日期`=t3.抢红包日期
;

SELECT t1.`登录日期` as 日期,t1.`用户ID`,t2.`金额` FROM
(SELECT `登录日期`,用户ID from `活跃用户表`
where `登录日期`>=20190601 and `是新用户`=1)t1
LEFT JOIN
(SELECT t.`用户ID`,p.`抢红包日期`,p.`金额` from 
(SELECT `用户ID`,min(`抢红包时间`) as 第一次领取时间 from `领取红包表`
GROUP BY `用户ID`)t
JOIN `领取红包表` p
on t.`用户ID`=p.`用户ID` and t.`第一次领取时间`=p.`抢红包时间`) t2
on t1.`用户ID`=t2.`用户ID` and t1.`登录日期`=t2.`抢红包日期`
;

SELECT t1.`用户ID`,t1.`首次抢红包时间`,
min(TIMESTAMPDIFF(minute,t1.`首次抢红包时间`,t2.`抢红包时间`)) as 抢红包时间间隔 
FROM
(SELECT p.`用户ID`,min(p.`抢红包时间`) as 首次抢红包时间 from `活跃用户表` u
LEFT JOIN `领取红包表` p
on u.`用户ID`=p.`用户ID` and u.`登录日期`=p.`抢红包日期`
where u.`是新用户`='1'
GROUP BY p.`用户ID`) t1
LEFT JOIN `领取红包表` t2
ON t1.`用户ID`=t2.`用户ID`
where t1.`首次抢红包时间`!=t2.`抢红包时间`
group by t1.`用户ID`;
 
 
#窗口函数来排序求解
SELECT t1.用户ID,timeDIFF(t2.抢红包时间,t1.抢红包时间) from
(SELECT * FROM
(SELECT `抢红包时间`,`用户ID`,row_number() over (PARTITION by `用户ID` ORDER BY `抢红包时间`) as ranking from `领取红包表`
WHERE `用户ID` in
(SELECT `用户ID` from `活跃用户表`
where `是新用户`=1)) t
where t.ranking=1) t1
LEFT JOIN 
(SELECT * FROM
(SELECT `抢红包时间`,`用户ID`,row_number() over (PARTITION by `用户ID` ORDER BY `抢红包时间`) as ranking from `领取红包表`
WHERE `用户ID` in
(SELECT `用户ID` from `活跃用户表`
where `是新用户`=1)) t
where t.ranking=2) t2
on t1.用户ID=t2.用户ID
;