MYSQL/HIVESQL笔试题（四）：HIVESQL（四）

7 手写HQL 第7题

有一个线上服务器访问日志格式如下（用sql答题）

时间接口 ip地址

2016-11-09 11：22：05 /api/user/login 110.23.5.33

2016-11-09 11：23：10 /api/user/detail 57.3.2.16

.....

2016-11-09 23：59：40 /api/user/login 200.6.5.166

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

数据集

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

1）建表

create table ip(
    time string,
    interface string,
    ip string)
row format delimited fields terminated by '	';

2）最终SQL

select
    ip,
    interface,
    count(*) ct
from
    ip
where
    date_format(time,'yyyy-MM-dd HH')>='2016-11-09 14'
    and 
    date_format(time,'yyyy-MM-dd HH')<='2016-11-09 15'
    and
    interface='/api/user/login'
group by
ip,interface
order by
    ct desc
limit 2;t1

8 手写SQL 第8题

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

1）建表（MySQL）

CREATE TABLE `account`
(   `dist_id` int（11）DEFAULT NULL COMMENT '区组id',
    `account` varchar（100）DEFAULT NULL COMMENT '账号',
    `gold` int（11）DEFAULT 0 COMMENT '金币'）;

2）最终SQL

select
    *
from
    account as a
where
    (select
        count(distinct(a1.gold))
    from
        account as a1 
    where
        a1.dist_id=a.dist_id
        and
        a1.gold>a.gold)<3

9 手写HQL 第9题

1）有三张表分别为会员表（member）销售表（sale）退货表（regoods）

（1）会员表有字段memberid（会员id，主键）credits（积分）；

（2）销售表有字段memberid（会员id，外键）购买金额（MNAccount）；

（3）退货表中有字段memberid（会员id，外键）退货金额（RMNAccount）。

2）业务说明

（1）销售表中的销售记录可以是会员购买，也可以是非会员购买。（即销售表中的memberid可以为空）；

（2）销售表中的一个会员可以有多条购买记录；

（3）退货表中的退货记录可以是会员，也可是非会员；

（4）一个会员可以有一条或多条退货记录。

查询需求：分组查出销售表中所有会员购买金额，同时分组查出退货表中所有会员的退货金额，把会员id相同的购买金额-退款金额得到的结果更新到表会员表中对应会员的积分字段（credits）

数据集

sale
1001    50.3
1002    56.5
1003    235
1001    23.6
1005    56.2
        25.6
        33.5

regoods
1001    20.1
1002    23.6
1001    10.1
        23.5
        10.2
1005    0.8

1）建表

create table member(memberid string,credits double) row format delimited fields terminated by '	';

create table sale(memberid string,MNAccount double) row format delimited fields terminated by '	';

create table regoods(memberid string,RMNAccount double) row format delimited fields terminated by '	';

2）最终SQL

insert into table member
select
    t1.memberid,
    MNAccount-RMNAccount
from
    (select
        memberid,
        sum(MNAccount) MNAccount
    from
        sale
    where
        memberid!=''
    group by
        memberid
    )t1
join
    (select
        memberid,
        sum(RMNAccount) RMNAccount
    from
        regoods
    where
        memberid!=''
    group by
        memberid
    )t2
on
    t1.memberid=t2.memberid;