HQL&SQL练习

一

我们有如下的用户访问数据

userId	visitDate	visitCount
u01	      2017/1/21	    5
u02	      2017/1/23	    6
u03	      2017/1/22	    8
u04	      2017/1/20	    3
u01	      2017/1/23	    6
u01	      2017/2/21	    8
u02	      2017/1/23	    6
u01	      2017/2/22	    4

要求使用SQL统计出每个用户的累积访问次数，如下表所示：

用户id	月份	小计	累计
u01	    2017-01	  11	11
u01	    2017-02	  12	23
u02	    2017-01	  12	12
u03	    2017-01	  8	    8
u04	    2017-01	  3	    3

答案：

select
userid,
from_unixtime(unix_timestamp('2017/1/21','yyyy/m/dd'),'yyyy-MM') as mth,
sum(visitcount) as visit_xj,
sum(sum(visitcount)) over(partition by userid order by from_unixtime(unix_timestamp('2017/1/21','yyyy/m/dd'),'yyyy-MM')) as visit_lj
from tb
group by userid,from_unixtime(unix_timestamp('2017/1/21','yyyy/m/dd'),'yyyy-MM')

二

– 有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，
– 访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：

-- 1）每个店铺的UV（访客数）

select
shop,
count(distinct user_id) as uv
from tb
group by shop

-- 2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

with tmp as (
select
shop,user_id,count(1) as times
from tb
group by shop,user_id
)
select
shop,user_id,times
from (
  select 
  shop,user_id,times,row_number() over(partitioni by shop order by times desc) as rn
  from tmp
)
where rn<=4

三

已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。
– 请给出sql进行统计:数据样例:2017-01-01,10029028,1000003251,33.57。

-- 1）给出 2017年每个月的订单数、用户数、总成交金额。

select
  substr(date,1,7) as mth,
  count(1) as order_cnt,
  count(distinct user_id) as user_cnt,
  sum(amount) as amount
from stg.order
group by substr(date,1,7)

-- 2）给出2017年11月的新客数(指在11月才有第一笔订单)

with a as (
select
	user_id
from stg.order
where substr(date,1,7)='2017-11'
group by user_id
),
b as (
select
	user_id
from stg.order
where substr(date,1,7)<'2017-11'
group by user_id
)

select
  count(1) as new_users
from a left join b on a.user_id=b.user_id
wehre b.user_id is null

四

有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。
– （活跃用户指连续两天都有访问记录的用户）
日期 用户 年龄

2019-02-11,test_1,23
2019-02-11,test_2,19
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-12,test_2,19
2019-02-13,test_1,23
2019-02-15,test_2,19
2019-02-16,test_2,19

with a as (
select
user_id,
max(age)
from tb 
group by user_id
),
b as (
select
dt,user_id,max(age) as age
from tb
group by dt,user_id
)

select 
tmp1.user_cnt,
tmp1.avg_age,
tmp2.new_users,
tmp2.new_user_avg_age
from 
(
select
count(1) as user_cnt,
avg(age) as avg_age
from a
) tmp1

join

(
  select
    count(1) as new_users,
    avg(age) as new_user_avg_age
  from 
  (
     select
       user_id,max(age) as age
     from 
      (
        select
          dt,user_id,age,lead(dt) over(partition by user_id order by dt) as next_dt
        from b
      ) o 1
     where next_dt is not null
     group by user_id
  )o2
) tmp2

五

有一个线上服务器访问日志格式如下（用sql答题）
时间 dt 接口 interface_n ip地址

2016-11-09 14:22:05		/api/user/login			110.23.5.33
2016-11-09 14:23:10		/api/user/detail		57.3.2.16
2016-11-09 15:59:40		/api/user/login			200.6.5.166

… …

求11月9号下午14点（14-15点），访问/api/user/login接口的top10的ip地址

with tmp as (
select
ip,times
from tb
where substr(dt,12,2)=‘14’ and interface_n=‘/api/user/login’
group by ip
)
select
ip,times
from
tmp
order by times
limit 10

六

有一个充值日志表如下：

CREATE TABLE `credit_log`
(
    `dist_id` int（11）DEFAULT NULL COMMENT '区组id',
    `account` varchar（100）DEFAULT NULL COMMENT '账号',
    `money` int(11) DEFAULT NULL COMMENT '充值金额',
    `create_time` datetime DEFAULT NULL COMMENT '订单时间'
)ENGINE=InnoDB DEFAUILT CHARSET-utf8

请写出SQL语句，查询充值日志表2015年7月9号每个区组下充值额最大的账号，要求结果：
区组id，账号，金额，充值时间

– 7
有一个账号表如下，请写出SQL语句，查询各自区组的money排名前十的账号（分组取前10）

CREATE TABIE `account` 
(
    `dist_id` int（11）		 DEFAULT NULL COMMENT '区组id'，
    `account` varchar（100） DEFAULT NULL COMMENT '账号' ,
    `money` int（11）			 DEFAULT NULL COMMENT '金币' 
    PRIMARY KEY （`dist_id`，`account`），
）ENGINE=InnoDB DEFAULT CHARSET-utf8