【Sql】SQL经典10题

准备工作

show databases ;
create database if not exists db;
use db;
-- 一些语句会走 MapReduce，所以慢。 可以开启本地化执行的优化。
set hive.exec.mode.local.auto=true;-- (默认为false)

访问量统计

准备数据+需求分析

CREATE TABLE db.test1 (
		userId string,
		visitDate string,
		visitCount INT )
	ROW format delimited FIELDS TERMINATED BY "\t";

INSERT overwrite TABLE db.test1
VALUES
    ( 'u01', '2017/1/21', 5 ),
    ( 'u02', '2017/1/23', 6 ),
    ( 'u03', '2017/1/22', 8 ),
    ( 'u04', '2017/1/20', 3 ),
    ( 'u01', '2017/1/23', 6 ),
    ( 'u01', '2017/2/21', 8 ),
    ( 'u02', '2017/1/23', 6 ),
    ( 'u01', '2017/2/22', 4 );

-- 要求使用 SQL 统计出每个用户的累计访问次数，如下表所示：
|用户id | 月份 | 小计 | 累积
----------------------------
| u01 | 2017-01 | 11 | 11 |
| u01 | 2017-02 | 12 | 23 |
| u02 | 2017-01 | 12 | 12 |
| u03 | 2017-01 | 8  | 8  |
| u04 | 2017-01 | 3  | 3  |

答案

with t1 as (
		select 
			userId,
			date_format(regexp_replace(visitDate, "/", "-"), 'yyyy-MM') vm,
			visitCount
from tb ),
t2 as (select 
	userId,
	vm,
	sum(vm) as monthSum
	from t1 group by userId, vm)
select 
	userId '用户id',
	vm '月份',
	monthSum '小计',
	sum(monthSum) over(partition by userId order by monthSum desc) '累计'
from t2;

电商场景TopN统计

准备数据+需求分析

-- 需求有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志， 
-- 访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，数据如下：

CREATE TABLE db.test2 (
						 user_id string,
						 shop string )
			ROW format delimited FIELDS TERMINATED BY '\t';
INSERT INTO TABLE db.test2 VALUES
( 'u1', 'a' ),
( 'u2', 'b' ),
( 'u1', 'b' ),
( 'u1', 'a' ),
( 'u3', 'c' ),
( 'u4', 'b' ),
( 'u1', 'a' ),
( 'u2', 'c' ),
( 'u5', 'b' ),
( 'u4', 'b' ),
( 'u6', 'c' ),
( 'u2', 'c' ),
( 'u1', 'b' ),
( 'u2', 'a' ),
( 'u2', 'a' ),
( 'u3', 'a' ),
( 'u5', 'a' ),
( 'u5', 'a' ),
( 'u5', 'a' );
--（1）每个店铺的UV（访客数）
-- UV和PV
-- PV是访问当前网站所有的次数
-- UV是访问当前网站的客户数(需要去重)
--(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

答案

-- 2.1
select 
	shop,
	count(distinct user_id) as cnt
from test group by shop 

-- 2.2
with t1 as (
select 
	shop,
	user_id,
	count(user_id) as cnt
from test group by shop, user_id),
t2 as (
select 
	shop,
	user_id,
	cnt,
	row_number over(partition by shop order by cnt desc) rn
from t1 )
select * from t2 where rn <= 3;

订单量统计

准备数据+需求分析

-- 需求已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。 
-- 数据样例:2017-01-01,10029028,1000003251,33.57。 

CREATE TABLE db.test3 (
			dt string,
			order_id string,
			user_id string,
			amount DECIMAL ( 10, 2 ) )
ROW format delimited FIELDS TERMINATED BY '\t';

INSERT overwrite TABLE db.test3 VALUES
 ('2017-01-01','10029028','1000003251',33.57),
 ('2017-01-01','10029029','1000003251',33.57),
 ('2017-01-01','100290288','1000003252',33.57),
 ('2017-02-02','10029088','1000003251',33.57),
 ('2017-02-02','100290281','1000003251',33.57),
 ('2017-02-02','100290282','1000003253',33.57),
 ('2017-11-02','10290282','100003253',234),
 ('2018-11-02','10290284','100003243',234);
-- 请给出sql进行统计: 
-- 	(1)给出 2017年每个月的订单数、用户数、总成交金额。
-- 	(2)给出2017年11月的新客数(指在11月才有第一笔订单)

答案

-- 3.1
with t1 as (
select 
	date_format(dt, 'yyyy-MM') as month
	order_id,
	user_id,
	amount
from test)
select 
	month,
	count(order_id),
	count(user_id),
	sum(amount)
from t1 group by month having substr(month, 1, 4) = '2017'


-- 3.2 求新客户数(求最小消费的时间为2017-11)
with t1 as (
select
	date_format(dt, 'yyyy-MM') as month
	order_id,
	user_id,
	amount
),
t2 as (select 
	user_id, 
	min(month) as minMonth 
from t1 group by user_id) 
select count(user_id) from t2 where minMonth = '2017-11'

大数据排序

准备数据+需求分析

-- 需求有一个5000万的用户文件(user_id，name，age)，一个2亿记录的用户看电影的记录文件(user_id，url)，

CREATE TABLE db.test4user
	(user_id string,name string,age int);

CREATE TABLE db.test4log
	(user_id string,url string);

INSERT INTO TABLE db.test4user VALUES('001','u1',10),
('002','u2',15),
('003','u3',15),
('004','u4',20),
('005','u5',25),
('006','u6',35),
('007','u7',40),
('008','u8',45),
('009','u9',50),
('0010','u10',65);
INSERT INTO TABLE db.test4log VALUES('001','url1'),
('002','url1'),
('003','url2'),
('004','url3'),
('005','url3'),
('006','url1'),
('007','url5'),
('008','url7'),
('009','url5'),
('0010','url1');

-- 根据年龄段观看电影的次数进行排序？

答案

活跃用户统计

准备数据+需求分析

-- 需求有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。
-- （活跃用户指连续两天都有访问记录的用户）
CREATE TABLE test5(
dt string,
user_id string,
age int)
ROW format delimited fields terminated BY ',';
INSERT overwrite TABLE db.test5 VALUES ('2019-02-11','test_1',23),
('2019-02-11','test_2',19),
('2019-02-11','test_3',39),
('2019-02-11','test_1',23),
('2019-02-11','test_3',39),
('2019-02-11','test_1',23),
('2019-02-12','test_2',19),
('2019-02-13','test_1',23),
('2019-02-15','test_2',19),
('2019-02-16','test_2',19);

-- 有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）
-- type     总数   平均年龄
-- '所有用户'  3    27
-- '活跃用户'  1    19

答案

电商购买金额统计

准备数据+需求分析

-- 表ordertable字段:(购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid
CREATE TABLE db.test6 (
		userid string,
		money decimal(10,2),
		paymenttime string,
		orderid string);

INSERT INTO TABLE db.test6 VALUES('001',100,'2017-10-01','123'),
('001',200,'2017-10-02','124'),
('002',500,'2017-10-01','125'),
('001',100,'2017-11-01','126');

-- 请用sql写出所有用户中在今年10月份第一次购买商品的金额

答案

电商分组TopN

准备数据+需求分析

-- 需求有一个账号表如下，请写出SQL语句

CREATE TABLE db.test10(
	`dist_id` string COMMENT '区组id',
	`account` string COMMENT '账号',
	`gold` int COMMENT '金币');

INSERT INTO TABLE db.test10 VALUES ('1','77',18),
 ('1','88',106),
 ('1','99',10),
 ('1','12',13),
 ('1','13',14),
 ('1','14',25),
 ('1','15',36),
 ('1','16',12),
 ('1','17',158),
 ('2','18',12),
 ('2','19',44),
 ('2','10',66),
 ('2','45',80),
 ('2','78',98);

-- 查询各自区组的money排名前十的账号（分组取前10） 
-- dist_id string '区组id', account string '账号', gold int '金币'

答案

教育领域SQL

准备数据+需求分析

-- 现有图书管理数据库的三个数据模型如下： 图书（数据表名：BOOK）

-- 读者（数据表名：READER）

-- 借阅记录（数据表名：BORROW LOG）

CREATE TABLE db.book(book_id string,
		`SORT` string,
		book_name string,
		 writer string,
		 OUTPUT string,
		 price decimal(10,2));
INSERT INTO TABLE db.book VALUES
('001','TP391','信息处理','author1','机械工业出版社','20'),
('002','TP392','数据库','author12','科学出版社','15'),
('003','TP393','计算机网络','author3','机械工业出版社','29'),
('004','TP399','微机原理','author4','科学出版社','39'),
('005','C931','管理信息系统','author5','机械工业出版社','40'),
('006','C932','运筹学','author6','科学出版社','55');

CREATE TABLE db.reader (reader_id string,
				company string,
				name string,
				sex string,
				grade string,
				addr string);
INSERT INTO TABLE db.reader VALUES
('0001','阿里巴巴','jack','男','vp','addr1'),
('0002','百度','robin','男','vp','addr2'),
('0003','腾讯','tony','男','vp','addr3'),
('0004','京东','jasper','男','cfo','addr4'),
('0005','网易','zhangsan','女','ceo','addr5'),
('0006','搜狐','lisi','女','ceo','addr6');


CREATE TABLE db.borrow_log(reader_id string,
				book_id string,
				borrow_date string);

INSERT INTO TABLE db.borrow_log VALUES ('0001','002','2019-10-14'),
('0002','001','2019-10-13'),
('0003','005','2019-09-14'),
('0004','006','2019-08-15'),
('0005','003','2019-10-10'),
('0006','004','2019-17-13');

-- （1）创建图书管理库的图书、读者和借阅三个基本表的表结构。请写出建表语句。
-- （2）找出姓李的读者姓名（NAME）和所在单位（COMPANY）。
-- （3）查找“高等教育出版社”的所有图书名称（BOOK_NAME）及单价（PRICE），结果按单价降序排序。
-- （4）查找价格介于10元和20元之间的图书种类(SORT）出版单位（OUTPUT）和单价（PRICE），
-- -- 结果按出版单位 （OUTPUT）和单价（PRICE）升序排序。 
-- （5）查找所有借了书的读者的姓名（NAME）及所在单位（COMPANY）。
-- （6）求”科学出版社”图书的最高单价、最低单价、平均单价。
-- （7）找出当前至少借阅了2本图书（大于等于2本）的读者姓名及其所在单位。
-- （8）考虑到数据安全的需要，需定时将“借阅记录”中数据进行备份，
-- -- 请使用一条SQL语句，在备份用户bak下创建 与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK.
-- -- 井且将“借阅记录”中现有数据全部复制到 BORROW_L0G_ BAK中。
--（9）现在需要将原Oracle数据库中数据迁移至Hive仓库，请写出“图书”在Hive中的建表语句（Hive实现，提示：列 分隔符|；
-- -- 数据表数据需要外部导入：分区分别以month＿part、day＿part 命名）

答案

服务器SQL统计

准备数据+需求分析

-- 有一个线上服务器访问日志格式如下（用sql答题）
|       时间       |      接口       |    ip地址   |
-------------------------------------------------
| 2016/11/9 14:22 | /api/user/login | 110.23.5.33 |
| 2016/11/9 14:23 | /api/user/detail | 57.3.2.16 |
| 2016/11/9 15:59 | /api/user/login | 200.6.5.166 |
|       ...       |       ...       |     ...     |

CREATE TABLE db.test8(`date` string,
				interface string,
				ip string);

INSERT INTO TABLE db.test8 VALUES
('2016-11-09 11:22:05','/api/user/login','110.23.5.23'),
('2016-11-09 11:23:10','/api/user/detail','57.3.2.16'),
('2016-11-09 23:59:40','/api/user/login','200.6.5.166'),
('2016-11-09 11:14:23','/api/user/login','136.79.47.70'),
('2016-11-09 11:15:23','/api/user/detail','94.144.143.141'),
('2016-11-09 11:16:23','/api/user/login','197.161.8.206'),
('2016-11-09 12:14:23','/api/user/detail','240.227.107.145'),
('2016-11-09 13:14:23','/api/user/login','79.130.122.205'),
('2016-11-09 14:14:23','/api/user/detail','65.228.251.189'),
('2016-11-09 14:15:23','/api/user/detail','245.23.122.44'),
('2016-11-09 14:17:23','/api/user/detail','22.74.142.137'),
('2016-11-09 14:19:23','/api/user/detail','54.93.212.87'),
('2016-11-09 14:20:23','/api/user/detail','218.15.167.248'),
('2016-11-09 14:24:23','/api/user/detail','20.117.19.75'),
('2016-11-09 15:14:23','/api/user/login','183.162.66.97'),
('2016-11-09 16:14:23','/api/user/login','108.181.245.147'),
('2016-11-09 14:17:23','/api/user/login','22.74.142.137'),
('2016-11-09 14:19:23','/api/user/login','22.74.142.137');
 

-- 求11月9号下午14点（14-15点），访问/api/user/login接口的top10的ip地址

答案

充值日志SQL统计

准备数据+需求分析

-- 需求有一个充值日志表credit_log，
-- 字段如下： `dist_id` int '区组id', `account` string '账号', `money` int '充值金额',
-- ` create_time` string '订单时间' 请写出SQL语句，查询充值日志表2019年01月02号每个区组下充值额最大的账号，
-- 要求结果： 区组id，账号，金额，充值时间
CREATE TABLE db.test9(
			dist_id string COMMENT '区组id',
			account string COMMENT '账号',
		   `money` decimal(10,2) COMMENT '充值金额',
			create_time string COMMENT '订单时间');

INSERT INTO TABLE db.test9 VALUES ('1','11',100006,'2019-01-02 13:00:01'),
 ('1','22',110000,'2019-01-02 13:00:02'),
 ('1','33',102000,'2019-01-02 13:00:03'),
 ('1','44',100300,'2019-01-02 13:00:04'),
 ('1','55',100040,'2019-01-02 13:00:05'),
 ('1','66',100005,'2019-01-02 13:00:06'),
 ('1','77',180000,'2019-01-03 13:00:07'),
 ('1','88',106000,'2019-01-02 13:00:08'),
 ('1','99',100400,'2019-01-02 13:00:09'),
 ('1','12',100030,'2019-01-02 13:00:10'),
 ('1','13',100003,'2019-01-02 13:00:20'),
 ('1','14',100020,'2019-01-02 13:00:30'),
 ('1','15',100500,'2019-01-02 13:00:40'),
 ('1','16',106000,'2019-01-02 13:00:50'),
 ('1','17',100800,'2019-01-02 13:00:59'),
 ('2','18',100800,'2019-01-02 13:00:11'),
 ('2','19',100030,'2019-01-02 13:00:12'),
 ('2','10',100000,'2019-01-02 13:00:13'),
 ('2','45',100010,'2019-01-02 13:00:14'),
 ('2','78',100070,'2019-01-02 13:00:15');

 -- --请写出SQL语句，查询充值日志表2019年01月02号每个区组下充值额最大的账号，要求结果：
--区组id，账号，金额，充值时间

答案