Welcome to the blog home page: WeChat Search: import_bigdata, original hard core creator of big data domain _import_bigdata_CSDN blog
Welcome to comment, collection, message, message exchange!
This article was originally created by Wang Zhiwu and first published in the CSDN blog!
The first CSDN forum of this article is forbidden to be reproduced without official and personal permission.

This article is about Learning Guide for Big Data Specialists from Zero (Full Upgrade) Part of HIV supplement.

7. Handwritten HQL Question 7

There is an online server access log format as follows (answer in sql)

Time * Interface * ip Address

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

Ask for the ip address of top10 accessing the api/user/login interface at 14 p.m. (14-15 p.m.) on November 9

data set

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) Table building

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

2) Final 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. Handwritten SQL Question 8

There is a table of accounts as follows, please write out the SQL statement to query the top 10 accounts in money of each district group (group top 10)

1) Table building (MySQL)

CREATE TABLE `account`
(   `dist_id` int(11)DEFAULT NULL COMMENT 'Block id',
    `account` varchar(100)DEFAULT NULL COMMENT 'Account number',
    `gold` int(11)DEFAULT 0 COMMENT 'Gold coin');

2) Final 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 Handwritten HQL Title 9

1) There are three tables: member Sales Return Table (regoods)

(1) Membership table has field memberid (member id, primary key) credits (integral);

(2) The sales table has the field memberid (member id, foreign key) purchase amount (MNAccount);

(3) The return table has the field memberid (member id, foreign key) return amount (RMNAccount).

2) Business description

(1) The sales records in the sales table can be purchased by members or non-members. (That is, the memberid in the sales table can be empty);

(2) A member of the sales table may have more than one purchase record;

(3) The return record in the return form can be either a member or a non-member;

(4) A member may have one or more return records.

Query Requirements: Grouping finds the purchase amount of all members in the sales table, and grouping finds the return amount of all members in the return table, updating the result of the same purchase amount-refund amount of members'id to the credit field of corresponding members in the table members table (credits)

data set

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) Table building

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

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

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

2) Final 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;