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 ) ;