SQL面试题

第一题 

1.创建数据表

CREATETABLE `mili` (
 
   statics_date varchar(255) NOT NULL,
 
   channel varchar(30) NOT NULL,
 
        type tinyint NOT NULL,
 
        `desc` varchar(30) NOT NULL,
 
        registerTime datetime NOT NULL,
 
        nickName varchar(30) NOT NULL,
 
        `change` int(10) NOT NULL,
 
        uid int(10) NOT NULL,
 
        balance int(10) NOT NULL,
 
        registerip varchar(255) NOT NULL
 
 );

2.插入数据

 -- 插入数据
insert into mili values
('2023/2/1', 'vivo', 1, '账号创建', '2023-02-01 20:59:47', 34508904, 0, 34508904,0,'125.212.159.63'),
('2023/2/1', '苹果', 2, '充值', '2023-02-01 18:59:47', 34508904, 0, 34508904,0,'103.199.35.197'),
('2023/2/1', '苹果', 2, '账号创建', '2023-02-01 13:59:47', 98385134, 0, 98385134,0,'113.185.35.201'),
('2023/2/1', '小米', 2, '提现', '2023-02-01 15:59:47', 34508904, 0, 34508904,0,'129.130.146.80'),
('2023/2/1', '小米', 2, '充值', '2023-02-01 15:59:47', 98385134, 0, 98385134,0,'129.130.146.80'),
('2023/3/2', 'vivo', 2, '提现', '2023-03-02 15:22:24', 55808099, 0, 55808099,0,'129.130.146.80'),
('2023/3/2', '华为', 2, '提现', '2023-03-02 15:22:24', 44808088, 0, 44808088,0,'129.130.146.80'),
('2023/3/17', '华为', 2, '账号创建', '2023-03-17 15:22:24', 22808000, 0, 22808000,0,'119.130.146.10'),
('2023/3/17', 'vivo', 2, '充值', '2023-03-17 15:22:24', 22808000, 0, 22808000,0,'119.130.146.10'),
('2023/3/17', '华为', 2, '提现', '2023-03-17 15:22:24', 22808000, 0, 22808000,0,'119.130.146.10'),
('2023/3/16', 'vivo', 1, '账号创建', '2023-03-16 20:59:47', 89008904, 0, 89008904,0,'125.212.159.63');

1. 首先从 mili 表中筛选出所需数据，使用 SELECT 语句，选择 statics_date, channel, uid，同时使用 AS 关键字给三个字段重新命名为“日期”、“渠道”、“用户数量”；
2. 筛选条件是 `desc` 字段等于“账号创建”，使用 WHERE 语句；
3. 使用 GROUP BY 语句按“日期”、“渠道”分组，统计每组的用户数量，并对两个字段进行升序排序；
4. 最后使用 LIMIT 语句，只取前五行。

select
	statics_date as 日期,
	channel as 渠道,
	count(distinct uid) as 用户数量
from 
	mili
where
	`desc` = "账号创建"
GROUP BY
	日期, 渠道
ORDER BY
	日期 desc, 渠道 desc 
limit 5;

2.从表mili中选择registerip作为注册IP，统计不重复的uid数作为用户数量，并且按照registerTime降序排列后用逗号分隔渠道，取名为渠道。然后按照用户数量降序排列，只保留用户数量大于等于2的数据。

解题思路：

1. 首先从表mili中选择需要的列。

2. 用GROUP BY对registerip进行分组，并用count(distinct uid)函数统计不重复的用户数量。

3. 在统计渠道时，需要使用GROUP_CONCAT函数将渠道以逗号分隔的格式连接起来。

4. 最后使用HAVING进行筛选，只保留用户数量大于等于2的数据，并按照用户数量降序排列。

select 
	registerip as 注册ip,
	count(distinct uid) as 用户数量,
	GROUP_CONCAT(distinct channel order by registerTime desc SEPARATOR '，') as 渠道
from
	mili 
GROUP BY
	注册ip
having 
	用户数量 >= 2
ORDER BY
	用户数量 desc

1. 1.使用`date(registerTime) = statics_date`条件将`mili`表中`registerTime`字段的日期和`statics_date`中的所有日期进行匹配。
2. 2.使用`GROUP BY`对`日期`进行分组。
3. 3. 在`SELECT`语句中，使用`count(distinct case...`将查询结果按条件进行统计。例如，`count(distinct case when `desc`="充值" then uid end)`将计算每个日期中`desc`为“充值”的用户数。
4. 4.最后，使用`concat(round(count(distinct case when `desc`="提现" then uid end ) / count(distinct case when `desc`="充值" then uid end ),2) * 100,'%')`将计算每个日期中进行了“提现”操作的用户相对于进行了“充值”操作的用户的比例，并将结果格式化成百分数形式。

select 
	statics_date as 日期,
	count(distinct case when `desc`="充值" then uid end) as `新充用户`,
	count(distinct case when `desc`="提现" and uid in(
		select uid from mili where `desc`="账号创建" and date(registerTime) = statics_date) then uid end
	) as `新提现用户`,
	concat(round(count(distinct case when `desc`="提现" and uid in(
		select uid from mili where `desc`="账号创建") then uid end
	) / count(distinct case when `desc`="充值"  and uid in(
		select uid from mili where `desc`="账号创建") then uid end
	),2)  * 100,'%') as `新用户充提比`
from 
	mili
where 
	date(registerTime) = statics_date
GROUP BY
	日期

 第二题

列名：statics_date:统计日期，channel：渠道，type：类型，desc：描述，registerTime：用户注册时间，用SQL，写出每日用户新增最多的5个渠道

SELECT  
    statics_date AS 日期,  
    channel AS 渠道,  
    COUNT(DISTINCT uid) AS 新增用户数  
FROM  
    mili  
WHERE  
    `desc` = '账号创建'  
GROUP BY  
    statics_date,  
    channel  
ORDER BY  
    statics_date,  
    COUNT(DISTINCT uid) DESC  
LIMIT 5;

第三题 ：