sql数据练习

---组内排名
select *from (
select  brank,keyword,sum(price) as `营业额` ,dense_rank() over (partition  by keyword order by sum(price) desc) as `排名`
from CityRecord group by brank,keyword )t1
where `排名`<=10

--if匹配语句
select item_id, sum(if(flag==1,1,0))  as `浏览人数`,sum(if(flag==2,1,0))as `收藏人数`,
sum(if(flag==3,1,0)) as `订单人数`,sum(if(flag==4,1,0)) as `付款人数`,(`付款人数`/`浏览人数`) as `转化率` from UserBea  
group by item_id 
order by `转化率` desc

--查询每日新增的用户 
select count(user_id) as `新增人数` ,toDate(`日期`) as `日期` from(
select user_id,MIN(times) as `日期` from UserBea ub2 where user_id in(
select DISTINCT  user_id from UserBea)
group by user_id 
)group by `日期`

--查询七天内活跃用户
select  user_id ,count(`日期`) as `登陆次数`from(   
select user_id,  toYYYYMMDD(toDate(times)) AS `日期` from UserBea
where  `日期`<=toYYYYMMDD(now()) and (toYYYYMMDD(now())-8)<`日期`)
group by user_id 
having `登陆次数`>=10

--查询七天内连续登陆的用户
select user_id ,count( DISTINCT  `日期`) as `七天登陆次数` from (
select user_id,  toYYYYMMDD(toDate(times)) AS `日期` from UserBea
where  `日期`<=toYYYYMMDD(now()) and (toYYYYMMDD(now())-7)<`日期`)t1
group by user_id 
having `七天登陆次数`=7

--建表语句一 
CREATE TABLE alibaba.UserBea
(

    `user_id` Int64,

    `cate_id` Int32,

    `item_id` Int32,

    `times` Int64,

    `ip` String,

    `flag` Int8
)
ENGINE = MergeTree
ORDER BY user_id
SETTINGS index_granularity = 8192;

--建表语句二
CREATE TABLE alibaba.CityRecord
(

    `user_id` String,

    `item_id` String,

    `cate_id` String,
    `order_id` String,

    `times` Int64,

    `name` String,

    `keyword` String,

    `factory` String,

    `price` Float32,

    `pro` String,

    `city` String,

    `par` String,
    `brank`  String
)
ENGINE = MergeTree
ORDER BY user_id
SETTINGS index_granularity = 8192;