5. 带条件的聚合统计

核心思路

• 一般的做法是group by xx,yy 再多次的sum(if(......))，在行转列中也用过这个技巧了，即把符合某条件的才进行统计
• 好处是避免多次加载表，得到多个指标，可以只加载一次表就得到多个指标。

小米电商

要求：编写SQL能运行，数据正确且符合规范，如遇到自定义函数或不记得的函数可以用XX代替

1. 已知有如下两个表表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 
);

2. 用户注册表：

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;