/*电商项目基础查询
温馨提示:本任务使用的云试验平台为上课时所用,非电商项目实验平台,数据库名 kaikeba
先使用 desc 表名;语句查看字段,在进行查询。
- 激活天数距今超过 500 天的男女分布情况(使用 user_info)(使用 user_info 表)
- 不同性别、教育程度的用户分布情况(使用 user_info) (使用 user_info 表)
- 2019 年前 4 个月中,全天各个时间段的不同品类购买金额分布(24 小时制,使用 user_trade 表)
- 统计在 2018 年购买的商品品类在两个以上的用户数(使用 user_trade 表)*/
– 1. 激活天数距今超过 500 天的男女分布情况(使用 user_info)(使用 user_info 表)
/*
1.现将激活天数计算
2.再将性别分段
3.根据性别分段统计用户个数
/
select sex,count(distinct user_id)
from user_info
where datediff(current_date,to_date(firstactivetime))>500
group by sex;
– 2. 不同性别、教育程度的用户分布情况(使用 user_info) (使用 user_info 表)
/
1.将性别和教育程度分段
2.在统计各个分段的用户数
get_json_object(string json_string, string path)
解析json字符串的一个字段
第一个参数填写json对象变量,第二个参数使用$表示json变量标识,
然后用 . 或 [] 读取对象或数组;如果输入的json字符串无效,那么返回NULL。
每次只能返回一个数据项。
*/
select sex,get_json_object(extra1,’
.
e
d
u
c
a
t
i
o
n
′
)
,
c
o
u
n
t
(
d
i
s
t
i
n
c
t
u
s
e
r
i
d
)
f
r
o
m
u
s
e
r
i
n
f
o
g
r
o
u
p
b
y
s
e
x
,
g
e
t
j
s
o
n
o
b
j
e
c
t
(
e
x
t
r
a
1
,
′
.education'), count(distinct user_id) from user_info group by sex,get_json_object(extra1,'
.education′),count(distinctuserid)fromuserinfogroupbysex,getjsonobject(extra1,′.education’);
– 方法二
select sex,extra2[‘education’],count(user_id)
from user_info
group by sex,extra2[‘education’];
– 3. 2019 年前 4 个月中,全天各个时间段的不同品类购买金额分布(24 小时制,使用 user_trade 表)
/*
substr(string A,int startindex,intlen),
1.先将将2019年的前4个月限定条件
2.再将不同购买品类分段
3.统计不同品类的购买金额
字符串的截取,如果不指定len,则从起始位置截取到最后。
*/
select substr(from_unixtime(pay_time,‘yyyy-MM-dd hh’),12),goods_category,sum(pay_amount)
from user_trade
where dt between ‘2019-01’ and ‘2019-04’
group by substr(from_unixtime(pay_time,‘yyyy-MM-dd hh’),12),goods_category;
– 4. 统计在 2018 年购买的商品品类在两个以上的用户数(使用 user_trade 表)
/*
1.先分析2018年的用户购买的商品种类,在统计种类大于2的用户
2.用子查询来统计购买商品种类大于2的所有用户数*/
select count(distinct a.user_name)
from(select user_name
from user_trade
where year(dt)=‘2018’
group by user_name
having count(distinct goods_category)>2
)a;