---组内排名
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;
|