核心思路
- 一般的做法是
group by xx,yy
再多次的sum(if(......))
,在行转列中也用过这个技巧了,即把符合某条件的才进行统计 - 好处是避免多次加载表,得到多个指标,可以只加载一次表就得到多个指标。
小米电商
要求:编写SQ
L能运行,数据正确且符合规范,如遇到自定义函数或不记得的函数可以用XX
代替
- 已知有如下两个表表
sale
:字段如下
Create table sale_order(
Order_id bigint comment '订单ID',
User_id bigint comment '用户ID',
Order_status int,
Create_time string,
Last_update_time string,
Product_id bigint,
Product_num bigint
);
- 用户注册表:
Create table user_info(
user_id bigint comment'用户ID,唯一主键',
sex string.
age int
);
问题:用一条SQL
生成完整的用户画像表,包含如下字段:
user_id, sex, age, d7order_num, d14_order_num
,后面两个字段分别为近7
天订单数量,近14
天订单数量。
create table sale_order(
order_id bigint comment '订单ID',
user_id bigint comment '用户ID',
order_status int ,
create_time string,
last_update_time string,
product_id bigint,
product_num bigint
);
create table user_info(
user_id bigint comment '用户ID,唯一主键',
sex string,
age int
);
select u.*,
s.d7order_num,
s.d14order_num
from user_info u
left join (
select user_id,
count(if(create_time >= '7天前' and create_time <= '今天', order_id,null)) as d7order_num,
count(if(create_time >= '14天前' and create_time <= '今天', order_id,null)) as d14order_num
from sale_order
where create_time >= '14天前'
group by user_id) s
on u.user_id = s.user_id;