Hong's External Brain

Appearance

1. 黑产行为 ​

  • 已知同一用户在移动端连续点击两次时间间隔不高于2秒，则可能产生黑产行为
  • 要求：筛选出21年3月份所有有可能为黑产行为的点击记录

建表和添加数据代码如下：

sql
-- 建表
create table if not exists tencent_video_click(
vuid int,
`date` date,        -- 点击日期
`time` int,         -- '点击时间时间戳格式',
platform int default 21    -- 21:移动端，22:网页端
);
select unix_timestamp('2021-03-01');
-- 插入造的数据
insert into tencent_video_click values
(1, '2021-03-01', 1614528000, 21),
(1, '2021-03-01', 1614528001, 22),
(1, '2021-03-01', 1614528001, 21),
(2, '2021-03-01', 1614528004, 21),
(3, '2021-03-01', 1614528000, 21),
(2, '2021-03-01', 1614528005, 21),
(3, '2021-03-01', 1614528000, 22),
(4, '2021-03-01', 1614528007, 21),
(4, '2021-03-01', 1614528009, 22),
(4, '2021-03-01', 1614528010, 21);

-- 要求：已知同一用户在移动端连续点击两次时间间隔不高于2秒，则可能产生黑产行为
-- 筛选出21年3月份所有有可能为黑产行为的点击记录

答案

sql
-- 步骤
-- 自连接  视作第一次点击记录表  第二点击记录表
-- 连接条件 用户是一样的 平台是一样的 
-- 筛选条件 第二次时间比第一次时间大 但不超过两秒  这个差还得大于0  移动端  21年3月
select `first`.*, `second`.`time` as two from tencent_video_click as `first` join tencent_video_click as `second`
on `first`.vuid=`second`.vuid and `first`.platform=`second`.platform
where `second`.`time` - `first`.`time` <= 2 and `second`.`time` - `first`.`time` > 0
and `first`.platform=21 and year(`first`.`date`) = 2021 and month(`first`.`date`)=3;

2. 门店订单查询 ​

  • 要求：获取每个门店的最后一条订单的订单金额数据和该门店的名称 省份 市 区
sql
-- 创建订单表
create table orders(
order_id varchar(100) primary key,       -- 订单id
shop_id int,                             -- 门店id
profit varchar(100),                     -- 订单金额
business_date datetime                   -- 订单时间
);

-- 插入伪造的数据
insert into orders 
values('D10001', 1256, '￥65.00' , '2021-02-01 12:14:56'),
('D10002', 1345, '￥120.00', '2021-02-01 13:20:13'),
('D10003', 1689, '￥20.00', '2021-02-03 09:26:15'),
('D10004', 1256, '￥150.00', '2021-02-03 16:22:35'),
('D10005', 1256, '￥120.00', '2021-02-06 15:35:26'),
('D10006', 1345, '￥60.00', '2021-02-06 10:23:26'),
('D10007', 1115, '￥40.00', '2021-02-06 11:26:58');

-- 创建门店表
create table shops(
shop_id int primary key,           -- 门店id
`name` varchar(100),               -- 门店名字
province varchar(100),             -- 门店所在省份
city varchar(100),                 -- 门店所在城市
area varchar(100)                  -- 门店所在区
);

-- 添加伪造数据
insert into shops
values(1256, '网红奶茶', '北京市', '北京市', '朝阳区'),
(1345, '萌宠体验馆', '上海市', '上海市', '静安区'),
(1689, '按摩足疗', '河北省', '石家庄市', '长安区'),
(1115, '湖南小吃', '湖南省', '长沙市', '天心区');

答案：

sql
-- 步骤
-- 两表联查  连接条件  店铺id进行关联
-- 最后一条订单 时间最晚 时间可以比较大小 最晚的就是最大的 
-- 以数据为基础进行时间过滤 \


select `name`, province, city, area, profit from 
(select shops.*, orders.order_id, orders.profit, orders.business_date,
max(business_date) over(partition by shops.shop_id) max_date
from orders join shops on orders.shop_id = shops.shop_id) as t
where business_date = max_date;

3.订单表之订单分布 ​

  • 查找出5月份，下单数量分别在0-5，6-10和10以上三个区间内的，客户数量分别有多少？
sql
-- 穿件订单表
create table pull(
pid varchar(100) primary key comment '运单号',
cid varchar(100) comment '客户id',
pdate date comment '订单日期'
);

-- 添加伪造数据
insert into pull values
('PNO0051', 'CC001', '2020/5/1'),
('PNO0052', 'CC001', '2020/5/2'),
('PNO0053', 'CC001', '2020/5/3'),
('PNO0054', 'CC007', '2020/5/4'),
('PNO0055', 'CC007', '2020/5/5'),
('PNO0056', 'CC008', '2020/5/6'),
('PNO0057', 'CC008', '2020/5/7'),
('PNO0058', 'CC009', '2020/5/8'),
('PNO0059', 'CC009', '2020/5/9'),
('PNO0060', 'CC009', '2020/5/10'),
('PNO0061', 'CC010', '2020/5/1'),
('PNO0062', 'CC010', '2020/5/2'),
('PNO0063', 'CC010', '2020/5/3'),
('PNO0064', 'CC010', '2020/5/4'),
('PNO0065', 'CC010', '2020/5/5'),
('PNO0066', 'CC010', '2020/5/6'),
('PNO0067', 'CC011', '2020/5/1'),
('PNO0068', 'CC011', '2020/5/8'),
('PNO0069', 'CC011', '2020/5/9'),
('PNO0070', 'CC012', '2020/5/10'),
('PNO0071', 'CC012', '2020/5/1'),
('PNO0072', 'CC013', '2020/5/2'),
('PNO0073', 'CC014', '2020/5/3'),
('PNO0074', 'CC014', '2020/5/4'),
('PNO0075', 'CC014', '2020/5/5'),
('PNO0076', 'CC014', '2020/5/6'),
('PNO0077', 'CC014', '2020/5/7'),
('PNO0078', 'CC014', '2020/5/8'),
('PNO0079', 'CC014', '2020/5/9'),
('PNO0080', 'CC014', '2020/5/10'),
('PNO0081', 'CC014', '2020/5/1'),
('PNO0082', 'CC015', '2020/5/2'),
('PNO0083', 'CC015', '2020/5/3'),
('PNO0084', 'CC015', '2020/5/4'),
('PNO0085', 'CC015', '2020/5/5'),
('PNO0086', 'CC015', '2020/5/6'),
('PNO0087', 'CC015', '2020/5/1'),
('PNO0088', 'CC015', '2020/5/8'),
('PNO0089', 'CC016', '2020/5/9'),
('PNO0090', 'CC017', '2020/5/10');

答案：

sql
-- 步骤
-- 统计每个用户的订单量
-- 展示范围的话 还需根据订单量做分支运算
-- 对订单分布进行分组 统计每个分布中用户出现的次数
select order_area, count(cid) from
(select cid, count(pid) order_num,
case
	when count(pid) between 0 and 5 then '0-5'
    when count(pid) between 6 and 10 then '6-10'
    when count(pid) > 10 then '10以上'
end as order_area
from pull
where month(pdate) = 5
group by cid) as t group by order_area;

4. 连续七天登陆 ​

  • -- 需求： 6月份 有过连续7天登陆的用户
sql
-- 创建表结构
create table login(
user_id int comment '用户id',
access_time datetime comment '访问时间',
page_id int comment '页面id',
dt date comment '登陆日期'
);

-- 创建伪造数据
insert into login values
(1, '2021-06-01 11:13:15', 10, '2021-06-01'),
(1, '2021-06-02 11:13:15', 10, '2021-06-02'),
(1, '2021-06-03 11:13:15', 10, '2021-06-03'),
(1, '2021-06-04 11:13:15', 10, '2021-06-04'),
(1, '2021-06-05 11:13:15', 10, '2021-06-05'),
(1, '2021-06-06 11:13:15', 10, '2021-06-06'),
(1, '2021-06-07 11:13:15', 10, '2021-06-07'),
(2, '2021-06-01 11:13:15', 10, '2021-06-01'),
(2, '2021-06-03 11:13:15', 10, '2021-06-03'),
(2, '2021-06-04 11:13:15', 10, '2021-06-04'),
(2, '2021-06-05 11:13:15', 10, '2021-06-05'),
(3, '2021-06-01 11:13:15', 10, '2021-06-01'),
(3, '2021-06-07 11:13:15', 10, '2021-06-07'),
(3, '2021-06-08 11:13:15', 10, '2021-06-08'),
(3, '2021-06-09 11:13:15', 10, '2021-06-09'),
(3, '2021-06-10 11:13:15', 10, '2021-06-10'),
(3, '2021-06-11 11:13:15', 10, '2021-06-11'),
(3, '2021-06-12 11:13:15', 10, '2021-06-12'),
(3, '2021-06-13 11:13:15', 10, '2021-06-13'),
(4, '2021-06-01 11:13:15', 10, '2021-06-01'),
(4, '2021-06-03 11:13:15', 10, '2021-06-03'),
(4, '2021-06-05 11:13:15', 10, '2021-06-05'),
(4, '2021-06-07 11:13:15', 10, '2021-06-07'),
(4, '2021-06-09 11:13:15', 10, '2021-06-09'),
(4, '2021-06-11 11:13:15', 10, '2021-06-11'),
(5, '2021-06-01 11:13:15', 10, '2021-06-01'),
(5, '2021-06-07 11:13:15', 10, '2021-06-07'),
(5, '2021-06-08 11:13:15', 10, '2021-06-08'),
(5, '2021-06-09 11:13:15', 10, '2021-06-09'),
(5, '2021-06-11 11:13:15', 10, '2021-06-11'),
(5, '2021-06-12 11:13:15', 10, '2021-06-12'),
(5, '2021-06-13 11:13:15', 10, '2021-06-13');

-- 思路： 按照用户把时间归纳在一起 设置一个排名  
-- 2. 将时间与排名做差
-- 3. 以2的数据为基础 统计每个用户 相同差值出现的次数 找到次数大于等于7的用户
select user_id, diff, count(*) 连续登陆的天数 from
(select *, date_sub(dt, interval ranking day) diff
from (select *,
row_number() over(partition by user_id order by dt) ranking
from login where month(dt) = 6) as t) as t1
group by user_id, diff
having count(*) >= 7;
sql
-- 我写的
select * from login;

create view temp as (
select *,
date_sub(e.dt,interval e.interval_days day) login_first_day
from
(select user_id,dt,
row_number() over(partition by user_id order by dt) interval_days 
from login) as e);

select * from temp;


select user_id , dt,count_login from 
(select *,
count(user_id) over (partition by user_id) count_login 
from temp) as e
where e.count_login >=7;

-- 老师的
-- 思路： 按照用户把时间归纳在一起 设置一个排名  
-- 2. 将时间与排名做差
-- 3. 以2的数据为基础 统计每个用户 相同差值出现的次数 找到次数大于等于7的用户
select user_id, diff, count(*) 连续登陆的天数 from
(select *, date_sub(dt, interval ranking day) diff
from (select *,
row_number() over(partition by user_id order by dt) ranking
from login where month(dt) = 6) as t) as t1
group by user_id, diff
having count(*) >= 7;

5.司机完单量 ​

  • 需求： 7月份 在厦门市有过5天以上的完单 且总完单量在10单以上的最高完单量司机的信息
sql

-- 创建数据表
create table driver_daily(
driver_id int comment '司机编号',
driver_name varchar(100) comment '司机姓名',
city_id int comment '城市编号',
city_name varchar(100) comment '城市名称',
order_id int comment '订单id',
`year` int comment '年',
`month` int comment '月',
`day` int comment '日'
);

-- 插入数据
insert into driver_daily values
('111','王**','32','厦门市','12233',2017,7,1),
('111','王**','32','厦门市','12234',2017,7,1),
('111','王**','32','厦门市','12235',2017,7,1),
('111','王**','32','厦门市','12236',2017,7,1),
('111','王**','32','厦门市','12237',2017,7,1),
('111','王**','32','厦门市','12238',2017,7,1),
('111','王**','32','厦门市','12239',2017,7,1),
('111','王**','32','厦门市','12240',2017,7,1),
('111','王**','32','厦门市','12241',2017,7,1),
('111','王**','32','厦门市','12242',2017,7,1),
('111','王**','32','厦门市','12243',2017,7,1),
('111','王**','32','厦门市','12244',2017,7,1),
('111','王**','32','厦门市','12245',2017,7,1),
('111','王**','32','厦门市','12246',2017,7,1),
('111','王**','32','厦门市','12247',2017,7,1),
('111','王**','32','厦门市','12248',2017,7,1),
('111','王**','32','厦门市','12249',2017,7,1),
('111','王**','32','厦门市','12250',2017,7,1),
('111','王**','32','厦门市','12251',2017,7,1),
('111','王**','32','厦门市','12252',2017,7,1),
('202','林**','32','厦门市','32234',2017,7,1),
('202','林**','32','厦门市','32235',2017,7,1),
('202','林**','32','厦门市','32236',2017,7,2),
('202','林**','32','厦门市','32237',2017,7,2),
('202','林**','32','厦门市','32238',2017,7,3),
('202','林**','32','厦门市','32239',2017,7,3),
('202','林**','32','厦门市','32240',2017,7,4),
('202','林**','32','厦门市','32241',2017,7,4),
('202','林**','32','厦门市','32242',2017,7,5),
('202','林**','32','厦门市','32243',2017,7,5),
('202','林**','32','厦门市','32244',2017,7,6),
('202','林**','32','厦门市','32245',2017,7,6),
('202','林**','32','厦门市','32246',2017,7,7),
('202','林**','32','厦门市','32247',2017,7,7),
('202','林**','32','厦门市','32248',2017,7,7),
('202','林**','32','厦门市','32249',2017,7,8),
('202','林**','32','厦门市','32250',2017,7,8),
('202','林**','32','厦门市','32251',2017,7,8),
('202','林**','32','厦门市','32252',2017,7,9),
('202','林**','32','厦门市','32253',2017,7,9),
('202','林**','32','厦门市','32254',2017,7,10),
('202','林**','32','厦门市','32254',2017,7,11),
(303,'孙**','32','厦门市','32244',2017,7,11),
(303,'孙**','32','厦门市','32274',2017,7,11),
(303,'孙**','32','厦门市','35254',2017,7,11),
(303,'孙**','32','厦门市','322599',2017,7,11),
(303,'孙**','32','厦门市','37854',2017,7,11),
(303,'孙**','32','厦门市','31234',2017,7,11);


select * from driver_daily;

-- 需求： 7月份 在厦门市有过5天以上的完单 且总完单量在10单以上的最高完单量司机的信息
sql
-- 我写的


select * from driver_daily;
-- 需求： 7月份 在厦门市有过5天以上的完单 且总完单量在10单以上的最高完单量司机的信息


-- 7月份 在厦门市有过5天以上的完单司机的信息
create view  temp1 as (
select driver_id,any_value(driver_name) as driver_name,
count(order_id) as total_orders 
from driver_daily 
where month =7 and city_id = 32
group by driver_id having total_orders >=5);


-- 总完单量在10单以上的司机的信息
create view temp2 as (
select driver_id,any_value(driver_name) as driver_name,count(order_id) as total_orders
from driver_daily 
where month =7 and city_id = 32
GROUP BY driver_id having total_orders >=10);


-- 两表取交集 
create view temp3 as (
select temp1.driver_id,temp1.driver_name,temp1.total_orders from temp1
inner join temp2 
on temp1.driver_id = temp2.driver_id);

-- 找最高完单量司机
select driver_id,driver_name,total_orders 
from temp3 
where total_orders = (select max(total_orders) from temp3);
sql
-- luffy的



-- 找到7月份 在厦门 有过5天以上的完单  [需要根据司机进行分类]
select driver_id, group_concat(distinct driver_name) driver_name, count(distinct `day`) from driver_daily 
where `month` = 7 and city_name = '厦门市'
group by driver_id having count(distinct `day`) > 5;

-- 还需找到总完单量 7月份在10单以上的
select driver_id, count(order_id) total_order from driver_daily where `month` = 7
group by driver_id having total_order > 10;

-- 把这两个信息联合在一起 连接点就是司机id
create view v_driver_orders as select t.*, total_order
from (select driver_id, group_concat(distinct driver_name) driver_name, count(distinct `day`) from driver_daily 
where `month` = 7 and city_name = '厦门市'
group by driver_id having count(distinct `day`) > 5) as t join (select driver_id, count(order_id) total_order from driver_daily where `month` = 7
group by driver_id having total_order > 10) as t1
on t.driver_id = t1.driver_id;

select * from v_driver_orders;
-- 这这个里面过滤 找到最高完单量的司机信息
select * from v_driver_orders where total_order = (select max(total_order) from v_driver_orders);

6.用户支付金额 ​

  • 需求1：提取每个用户每日累计充值金额
  • 需求2： 提取每个用户最后登陆7天的充值金额
sql
-- 创建表结构

create table user_log(
login_date date comment '登陆日期',
user_id int comment '用户id'
);

create table payment_log(
order_id int primary key auto_increment comment '订单号',
pay_date date comment '支付日期',
user_id int comment '用户id',
revenue int comment '充值金额'
);

-- 插入数据

insert into user_log values
('2022-03-11', 10),
('2022-03-19', 10),
('2022-03-21', 10),
('2022-03-22', 10),
('2022-03-25', 10),
('2022-03-29', 10),
('2022-04-11', 10),
('2022-04-13', 10),
('2022-04-17', 10),
('2022-03-10', 20),
('2022-03-11', 20),
('2022-03-14', 20),
('2022-03-16', 20),
('2022-03-20', 20),
('2022-04-11', 20),
('2022-04-13', 20),
('2022-04-17', 20),
('2022-04-21', 20),
('2022-05-01', 20);

insert into payment_log(pay_date, user_id, revenue) values
('2022-03-11', 10, 30),
('2022-03-11', 10, 30),
('2022-03-11', 10, 30),
('2022-03-11', 10, 30),
('2022-03-19', 10, 50),
('2022-03-19', 10, 50),
('2022-03-21', 10, 100),
('2022-03-21', 10, 100),
('2022-03-21', 10, 100),
('2022-03-22', 10, 20),
('2022-03-22', 10, 20),
('2022-03-25', 10, 10),
('2022-03-29', 10, 10),
('2022-04-11', 10, 70),
('2022-04-11', 10, 70),
('2022-04-13', 10, 80),
('2022-04-17', 10, 90),
('2022-03-10', 20, 90),
('2022-03-11', 20, 80),
('2022-03-14', 20, 60),
('2022-03-16', 20, 120),
('2022-03-20', 20, 99),
('2022-04-11', 20, 87),
('2022-04-13', 20, 53),
('2022-04-17', 20, 56),
('2022-04-21', 20, 77),
('2022-05-01', 20, 32),
('2022-03-10', 20, 29),
('2022-03-11', 20, 30),
('2022-03-14', 20, 18),
('2022-03-16', 20, 6),
('2022-03-20', 20, 19),
('2022-04-11', 20, 21),
('2022-04-13', 20, 20),
('2022-04-17', 20, 76),
('2022-04-21', 20, 82),
('2022-05-01', 20, 91);

/*
需求1：提取每个用户每日累计充值金额
需求2： 提取每个用户最后登陆7天的充值金额
*/
sql
-- 我写的


-- 需求1：提取每个用户每日累计充值金额
select user_log.user_id,user_log.login_date,sum(payment_log.revenue) as revenue 
from user_log 
left join payment_log 
on user_log.user_id = payment_log.user_id
GROUP BY user_id,login_date 



-- 需求2： 提取每个用户最后登陆7天的充值金额

-- 连表 ，按用户的login_date = 支付的pay_date 
create view temp5 as (
select user_log.user_id,login_date,pay_date,revenue,order_id from user_log 
join payment_log 
on user_log.login_date = payment_log.pay_date
);

-- dense_RANK() 分组 ，按日期排倒序 
create view temp6 as (
select *,
dense_rank() over(partition by user_id order by login_date desc) ranking 
from temp5);

-- 取rank <=7的数据

select user_id,pay_date,revenue,order_id 
from temp6 
where ranking <=7;
sql
-- luffy的

-- 提取每个用户每日累计充值金额  在支付表中 按照用户和日期分组 对充值金额求和
select user_id, pay_date, sum(revenue) total from payment_log
group by user_id, pay_date;

-- 提取每个用户最后登陆7天的充值金额
-- 两表连接 连接条件 用户一样 充值日期与登录日期一样
--  将用户归纳在一起 根据日期降序排序
-- 筛选数据 找到排名 小于8
select user_id, sum(revenue) total from
(select payment_log.*, login_date,
dense_rank() over(partition by user_id order by login_date desc) ranking
from user_log join payment_log
on user_log.user_id = payment_log.user_id and user_log.login_date = payment_log.pay_date) as t
where ranking < 8
group by user_id;

7. 部门员工薪资表中部门最高薪资 ​

  • 需求： 求每个部门 薪水最高的员工信息 列出的数据 部门编号 员工编号 薪水
use interview;
create table dept_emp(
emp_no int primary key comment '员工编号',
dept_no varchar(100) comment '部门编号'
);

create table salaries(
emp_no int primary key,
salary int comment '薪水'
);

insert into dept_emp values(1001, 'D001'),(1002, 'D001'),(1003, 'D002'),(1004, 'D002');
insert into salaries values(1001, 88958),(1002, 72527),(1003, 92527),(1004, 188958);
select * from dept_emp;
select * from salaries;

-- 需求： 求每个部门 薪水最高的员工信息  列出的数据 部门编号  员工编号  薪水
sql
-- 我写的


select * from dept_emp;
select * from salaries;

-- 需求： 求  列出的数据 部门编号  员工编号  薪水

-- 连表 on:emp_no 
create view temp3 as (
select dept_emp.emp_no,dept_no,salary from dept_emp 
left join salaries 
on dept_emp.emp_no = salaries.emp_no );

-- 查询每个部门 薪水最高的员工信息
select emp_no from
(select * ,
max(salary) over (partition by dept_no) max_salary
from temp3) as e
where salary = max_salary;
sql
-- luffy的

-- 整合信息 连接查询
-- 可以使用窗口函数 增加一列数据  按照部门归类 找到这个部门的最高薪资
-- 对数据进行过滤筛选 
select dept_no, emp_no, max_salary from 
(select dept_no, dept_emp.emp_no, salary, 
max(salary) over(partition by dept_no) max_salary
from dept_emp join salaries
on dept_emp.emp_no = salaries.emp_no) as t
where salary=max_salary;

8. 长视频类型表 ​

9.粉丝行为表 ​

  • 需求 求6月 粉丝量排名前3的主播
sql
-- 创建表结构

create table user_event(
id int comment '粉丝id',
dt date comment '日期',
anchor varchar(255) comment '主播',
duration int comment '观看时长-分钟'
);

-- 插入数据
insert into user_event values
(1, '2022-06-01', '乐乐', 30),
(1, '2022-06-01', '花花', 30),
(1, '2022-06-01', '君君', 30),
(1, '2022-06-01', '球球', 30),
(2, '2022-06-01', '乐乐', 30),
(3, '2022-06-01', '乐乐', 30),
(4, '2022-06-01', '乐乐', 30),
(2, '2022-06-01', '花花', 30),
(3, '2022-06-01', '花花', 30),
(5, '2022-06-01', '花花', 30),
(7, '2022-06-01', '花花', 30),
(6, '2022-06-01', '花花', 30),
(3, '2022-06-01', '球球', 30),
(5, '2022-06-01', '球球', 30),
(7, '2022-06-01', '球球', 30),
(8, '2022-06-01', '球球', 30),
(9, '2022-06-01', '球球', 30),
(6, '2022-06-01', '球球', 30),
(10, '2022-06-01', '球球', 30),
(11, '2022-06-01', '球球', 30),
(2, '2022-06-01', '君君', 30),
(5, '2022-06-01', '君君', 30),
(6, '2022-06-01', '君君', 30),
(8, '2022-06-01', '君君', 30),
(10, '2022-06-01', '君君', 30),
(11, '2022-06-01', '君君', 30),
(12, '2022-06-01', '君君', 30),
(13, '2022-06-01', '君君', 30),
(14, '2022-06-01', '君君', 30),
(3, '2022-06-01', '茜茜', 30),
(4, '2022-06-01', '茜茜', 30),
(8, '2022-06-01', '茜茜', 30),
(10, '2022-06-01', '茜茜', 30),
(11, '2022-06-01', '茜茜', 30),
(14, '2022-06-01', '茜茜', 30),
(6, '2022-06-01', '茜茜', 30),
(1, '2022-06-01', '美美', 30),
(2, '2022-06-01', '美美', 30),
(4, '2022-06-01', '美美', 30),
(5, '2022-06-01', '美美', 30),
(7, '2022-06-01', '美美', 30),
(12, '2022-06-01', '美美', 30),
(14, '2022-06-01', '美美', 30),
(18, '2022-06-01', '美美', 30),
(9, '2022-06-01', '波波', 30),
(10, '2022-06-01', '波波', 30),
(15, '2022-06-01', '波波', 30),
(17, '2022-06-01', '波波', 30);
-- 需求 求6月 粉丝量排名前3的主播
sql
-- 我写的（写麻烦了 ，不好）

-- 需求 求6月 粉丝量排名前3的主播
select * from user_event;

-- 按主播分组，统计粉丝量
create view temp1 as (select anchor,
count(id) as fans_amount 
from user_event 
where month(dt) = 6 
GROUP BY anchor ) ;


-- 根据粉丝量给个排名列
create view temp2 as (select *,
DENSE_RANK() over (order by fans_amount desc) ranking 
from temp1);

select * from temp2;

-- 查询排名列中小于等于3的数据
select anchor,fans_amount from temp2 
where ranking <= 3;
sql
-- luffy写的

-- 得求出 每个主播的粉丝量  进行排名  
select anchor, count(id) 粉丝量  from user_event where month(dt) = 6
group by anchor order by 粉丝量 desc limit 3;

10. ​

11. 用户订单查询时间范围以及订单信息 ​

  • 需求1： 获取昨天 本周 本月 创建的用户数 展示格式如 昨天 本周 本月 3 22 30
  • 需求2： 获取昨天 本周 本月 创建的用户数 展示格式如 昨天 3 本周 22 本月 30
  • 需求3：最近3天【最近3天指:前天，昨天，今天】创建的用户里 当天有创建订单的人数 展示格式如 20220528 8 20220527 22 20220526 30
  • 需求4： 取出 20220527创建的用户里 第二次创建订单的时间 展示结果如下 uid 时间 1 2022-05-27 10:10:10 2 2022-05-27 15:12:18
sql
-- 创建表结构
create table t_user(
uid int primary key auto_increment comment '用户id',
role_type tinyint comment '用户身份：1 普通用户  2会员用户',
created_time int comment '时间戳格式的用户创建时间'
);
select unix_timestamp(now()), 
	unix_timestamp('2022-05-27'), 
    unix_timestamp('2022-05-26'), 
    unix_timestamp('2022-05-25'), 
    unix_timestamp('2022-05-24'),
    unix_timestamp('2022-05-23');
-- 添加数据
insert into t_user(role_type, created_time) values
(1, '1653235200'),
(1, '1653234000'),
(1, '1653321600'),
(1, '1653321666'),
(1, '1653325600'),
(1, '1653408000'),
(1, '1653408000'),
(1, '1653408000'),
(1, '1653408000'),
(1, '1653408000'),
(1, '1653408000'),
(1, '1653494400'),
(1, '1653494400'),
(1, '1653580800'),
(1, '1653494400'),
(1, '1653494400'),
(1, '1653580800'),
(1, '1653494400'),
(1, '1653580800'),
(1, '1653706298');

-- 创建表结构
create table user_order(
order_id int primary key auto_increment comment '订单id',
creator_uid int comment '用户id',
created_time int comment '订单创建时间 时间戳格式'
);

-- 添加数据
insert into user_order(creator_uid, created_time) values
(1, 1653237900),
(1, 1653760000),
(1, 1653769200),
(2, 1653578900),
(2, 1653760000),
(2, 1653969200),
(3, 1653890023),
(4, 1653321875),
(4, 1653321900),
(5, 1653325900),
(6, 1653409000),
(7, 1653410000),
(8, 1653409000),
(9, 1653411000),
(10, 1653421000),
(11, 1653424000),
(12, 1653500400),
(13, 1653509400),
(14, 1653589800),
(14, 1653590800),
(15, 1653504400),
(16, 1653784400),
(16, 1653804400),
(17, 1653700800),
(17, 1653720800),
(18, 1653504400),
(19, 1653600800),
(20, 1653716298);
sql
-- luffy的

/*
需求1： 获取昨天  本周  本月 创建的用户数 展示格式如
		昨天	 本周  本月
		 3    22   30
时间的判断  时间函数做运算
	昨天  比当前时间少1天
    本周  获取一下当前时间是星期几   减去星期数 获取星期开始的日期   创建时间在 星期开始日期到当前时间之间 
    本月 年份一样 月份一样 
对时间做判断 满足要求的给个标记 设置为1  不满足要求的设置0
*/
select
sum(if(date_sub(curdate(), interval 1 day) = created_dt, 1, 0)) `昨天`,
sum(if(created_dt <= curdate() and created_dt >= date_sub(curdate(), interval week(curdate(), 1) day), 1, 0)) `本周`,
sum(if(year(created_dt) = year(curdate()) and month(created_dt) = month(curdate()), 1, 0)) `本月`
from (select *, from_unixtime(created_time, '%Y-%m-%d') created_dt from t_user) as t;


/*
需求2： 获取昨天  本周  本月 创建的用户数 展示格式如
	昨天 3
    本周 22
    本月 30
可以以需求1为基础 横向数据展示为纵向  可以使用合并union来完成需求
*/
create view v_create_area as select
sum(if(date_sub(curdate(), interval 1 day) = created_dt, 1, 0)) `昨天`,
sum(if(created_dt <= curdate() and created_dt >= date_sub(curdate(), interval week(curdate(), 1) day), 1, 0)) `本周`,
sum(if(year(created_dt) = year(curdate()) and month(created_dt) = month(curdate()), 1, 0)) `本月`
from (select *, from_unixtime(created_time, '%Y-%m-%d') created_dt from t_user) as t;

select '昨天' as area, `昨天` as 人数 from v_create_area
union
select '本周' as area, `本周` as 人数 from v_create_area
union
select '本月' as area, `本月` as 人数 from v_create_area;


/*
需求3：最近3天【最近3天指:前天，昨天，今天】创建的用户里 当天有创建订单的人数 展示格式如
	20220528    8
    20220527    22
    20220526    30

既有用户创建 又有订单 肯定两表连接查询 连接条件是 用户一样  用户创建时间与下单时间是同一天
筛选条件 只要最近三天的 

分组 按照时间归类 统计用户出现的次数
*/
select date_format(created_dt, '%Y%m%d') 日期, count(uid) 人数
from (select *, from_unixtime(created_time, '%Y-%m-%d') created_dt from t_user) as t1
join
(select *, from_unixtime(created_time, '%Y-%m-%d') order_created_dt from user_order) as t2
on t1.uid=t2.creator_uid and t1.created_dt = t2.order_created_dt
where datediff(curdate(), created_dt) <= 2
group by created_dt order by created_dt desc;

/*
需求4： 取出 20220527创建的用户里  第二次创建订单的时间  展示结果如下
	uid				时间
    1			2022-05-27 10:10:10
    2           2022-05-27 15:12:18
1. 连接查询 连接用户id 
2. 数据的筛选
3. 找第二次下单的时间  可以根据用户进行归类 然后按照下单时间升序排序 设置个排名 排名为2的 就是第二次下单的
*/	
select uid, from_unixtime(order_created_time, '%Y-%m-%d %H:%i:%s') 时间 from
(select uid, t_user.created_time, user_order.created_time as order_created_time, 
row_number() over(partition by uid order by user_order.created_time) ranking
from t_user join user_order
on t_user.uid = user_order.creator_uid
where from_unixtime(t_user.created_time, '%Y%m%d') = '20220527') as t
where ranking = 2;
  • 我的代码
sql

-- 需求1： 获取昨天  本周  本月 创建的用户数 展示格式如
-- 昨天	 本周  本月
-- 	 3    22   30

-- 方法一
drop view temp;
create view temp as (
select *,
if (date_sub('2022-05-25',interval 1 day)=created_time,1,0) 'yesterday',
if (date_sub('2022-05-25',interval 7 day)<=created_time,1,0) 'this_week',
if (date_sub('2022-05-25',interval 30 day) <= created_time,1,0) 'this_month'
from 
(select uid,role_type,
FROM_UNIXTIME(created_time,'%Y-%m-%d')  created_time
from t_user) as t);

select * from temp;

select 
sum(yesterday) as 'yesterday',
sum(this_week) as 'this_week',
sum(this_month) as 'this_month'
from temp;


-- 方法二（这个没一好）

create view temp3 as (
select *,
case 
when (created_time_f = date_sub('2022-05-24',interval 1 day)) then 'yesterday'
when (created_time_f < date_sub('2022-05-24',interval 1 day) and created_time_f >= date_sub('2022-05-23',interval 7 day)) then 'this week'
when (created_time_f < date_sub('2022-04-24',interval 7 day) and created_time_f >= date_sub('2022-05-23',interval 30 day)) then 'this month'
else 'others'
end time_distribution
from (select * ,
FROM_UNIXTIME(created_time,'%Y-%m-%d') created_time_f
from t_user) as e);

select any_value(uid) as uid,
sum(uid) as counting from temp3 
group by time_distribution;
sql
-- 我的
-- 需求2： 获取昨天  本周  本月 创建的用户数 展示格式如
-- 昨天 本周 本月 
-- 3   22  30
sql
-- 需求3：最近3天【最近3天指:前天，昨天，今天】创建的用户里 当天有创建订单的人数 展示格式如
-- 20220528    8
-- 20220527    22
-- 20220526    30