2021-05-17

12道金牌hql

2017-10-01	10029011	1000003251	19.50
2017-10-03	10029012	1000003251	29.50
2017-10-04	10029013	1000003252	39.50
2017-10-05	10029014	1000003253	49.50
2017-11-01	10029021	1000003251	130.50
2017-11-03	10029022	1000003251	230.50
2017-11-04	10029023	1000003252	330.50
2017-11-05	10029024	1000003253	430.50
2017-11-07	10029025	1000003254	530.50
2017-11-15	10029026	1000003255	630.50
2017-12-01	10029027	1000003252	112.50
2017-12-03	10029028	1000003251	212.50
2017-12-04	10029029	1000003253	312.50
2017-12-05	10029030	1000003252	412.50
2017-12-07	10029031	1000003258	512.50
2017-12-15	10029032	1000003255	612.50

==================================== 第三题 ===============================

– 已知一个表STG.ORDER,有如下字段:Date,Order_id,User_id,amount。
– 请给出sql进行统计:数据样例:2017-01-01,10029028,1000003251,33.57。
– 1）给出 2017年每个月的订单数、用户数、总成交金额。
– 2）给出2017年11月的新客数(指在11月才有第一笔订单)

drop table if exists test_three_ORDER;

create table test_three_ORDER
(
Date String COMMENT ‘下单时间’,
Order_id String COMMENT ‘订单ID’,
User_id String COMMENT ‘用户ID’,
amount decimal(10,2) COMMENT ‘金额’
)
row format delimited fields terminated by ‘\t’;

load data local inpath “/opt/module/hive/datas/test/3.txt” into table test_three_ORDER;

– 1）给出 2017年每个月的订单数、用户数、总成交金额。

SELECT 
	o1.mo as `月份`,
	COUNT(o1.order_id ) as `订单数`,
	count(DISTINCT o1.user_id ) as `用户数`,
	sum(o1.amount ) as `总成交金额`
FROM 
	(SELECT 
		date_format(`date` ,"yyyy-MM") mo,
		order_id ,
		user_id ,
		amount 
	FROM 
		test_three_order)  as o1
group by
	o1.mo;

– 2）给出2017年11月的新客数(指在11月才有第一笔订单)

SELECT 
	COUNT(DISTINCT user_id ) 
FROM 
	test_three_order
where date_format(`date` ,"yyyy-MM") = "2017-11"
	AND user_id NOT IN 
		(SELECT 
			user_id 
		FROM 
			test_three_order
		where datediff("2017-11-01",`date`) > 0
		group BY 
			user_id ) ;