Hive 经典面试案例 十题

最新推荐文章于 2024-04-26 18:40:55 发布
最新推荐文章于 2024-04-26 18:40:55 发布
阅读量1.5k 收藏 20
点赞数 3
分类专栏： Hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_36690988/article/details/103712369
版权

第一题

需求
我们有如下的用户访问数据
    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



实现
数据准备

CREATE TABLE test_sql.test1 ( 
        userId string, 
        visitDate string,
        visitCount INT )
    ROW format delimited FIELDS TERMINATED BY "\t";
    INSERT INTO TABLE test_sql.test1
    VALUES
        ( '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

 

SELECT t2.userid,
       t2.visitmonth,
       subtotal_visit_cnt,
       sum(subtotal_visit_cnt) over (partition BY userid
                                     ORDER BY visitmonth) AS total_visit_cnt
FROM
  (SELECT userid,
          visitmonth,
          sum(visitcount) AS subtotal_visit_cnt
   FROM
     (SELECT userid,
             date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') AS visitmonth,
             visitcount
      FROM test_sql.test1) t1
   GROUP BY userid,
            visitmonth)t2
ORDER BY t2.userid,
         t2.visitmonth


第二题
需求
有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，
访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，数据如下：

              

                u1    a
                u2    b
                u1    b
                u1    a
                u3    c
                u4    b
                u1    a
                u2    c
                u5    b
                u4    b
                u6    c
                u2    c
                u1    b
                u2    a
                u2    a
                u3    a
                u5    a
                u5    a
                u5    a
请统计：
(1)每个店铺的UV（访客数）
(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数




实现
数据准备

CREATE TABLE test_sql.test2 ( 
                         user_id string, 
                         shop string )
            ROW format delimited FIELDS TERMINATED BY '\t';    
            INSERT INTO TABLE test_sql.test2 VALUES
            ( 'u1', 'a' ),
            ( 'u2', 'b' ),
            ( 'u1', 'b' ),
            ( 'u1', 'a' ),
            ( 'u3', 'c' ),
            ( 'u4', 'b' ),
            ( 'u1', 'a' ),
            ( 'u2', 'c' ),
            ( 'u5', 'b' ),
            ( 'u4', 'b' ),
            ( 'u6', 'c' ),
            ( 'u2', 'c' ),
            ( 'u1', 'b' ),
            ( 'u2', 'a' ),
            ( 'u2', 'a' ),
            ( 'u3', 'a' ),
            ( 'u5', 'a' ),
            ( 'u5', 'a' ),
            ( 'u5', 'a' );          

 
查询SQL实现

(1)方式1：
        SELECT shop,
               count(DISTINCT user_id)
        FROM test_sql.test2
        GROUP BY shop
方式2：
        SELECT t.shop,
               count(*)
        FROM
          (SELECT user_id,
                  shop
           FROM test_sql.test2
           GROUP BY user_id,
                    shop) t
        GROUP BY t.shop
(2)    
SELECT t2.shop,
       t2.user_id,
       t2.cnt
FROM
  (SELECT t1.*,
          row_number() over(partition BY t1.shop
                            ORDER BY t1.cnt DESC) rank
   FROM
     (SELECT user_id,
             shop,
             count(*) AS cnt
      FROM test_sql.test2
      GROUP BY user_id,
               shop) t1)t2
WHERE rank <= 3              

     
第三题
需求

已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。
数据样例:2017-01-01,10029028,1000003251,33.57。
请给出sql进行统计:
(1)给出 2017年每个月的订单数、用户数、总成交金额。
(2)给出2017年11月的新客数(指在11月才有第一笔订单)


实现
数据准备

CREATE TABLE test_sql.test3 ( 
            dt string,
            order_id string, 
            user_id string, 
            amount DECIMAL ( 10, 2 ) )
ROW format delimited FIELDS TERMINATED BY '\t';
INSERT INTO TABLE test_sql.test3 VALUES ('2017-01-01','10029028','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-01-01','10029029','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-01-01','100290288','1000003252',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-02-02','10029088','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-02-02','100290281','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-02-02','100290282','1000003253',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-11-02','10290282','100003253',234);
INSERT INTO TABLE test_sql.test3 VALUES ('2018-11-02','10290284','100003243',234);


查询SQL

(1)
SELECT t1.mon,
       count(t1.order_id) AS order_cnt,
       count(DISTINCT t1.user_id) AS user_cnt,
       sum(amount) AS total_amount
FROM
  (SELECT order_id,
          user_id,
          amount,
          date_format(dt,'yyyy-MM') mon
   FROM test_sql.test3
   WHERE date_format(dt,'yyyy') = '2017') t1
GROUP BY t1.mon
(2)
SELECT count(user_id)
FROM test_sql.test3
GROUP BY user_id
HAVING date_format(min(dt),'yyyy-MM')='2017-11';


第四题
需求

有一个5000万的用户文件(user_id，name，age)，一个2亿记录的用户看电影的记录文件(user_id，url)，根据年龄段观看电影的次数进行排序？        
1
实现
数据准备
CREATE TABLE test_sql.test4user
           (user_id string,
            name string,
            age int);

CREATE TABLE test_sql.test4log
                        (user_id string,
                        url string);
                        
INSERT INTO TABLE test_sql.test4user VALUES('001','u1',10);
INSERT INTO TABLE test_sql.test4user VALUES('002','u2',15);   
INSERT INTO TABLE test_sql.test4user VALUES('003','u3',15);   
INSERT INTO TABLE test_sql.test4user VALUES('004','u4',20);   
INSERT INTO TABLE test_sql.test4user VALUES('005','u5',25);   
INSERT INTO TABLE test_sql.test4user VALUES('006','u6',35);   
INSERT INTO TABLE test_sql.test4user VALUES('007','u7',40);
INSERT INTO TABLE test_sql.test4user VALUES('008','u8',45);  
INSERT INTO TABLE test_sql.test4user VALUES('009','u9',50);  
INSERT INTO TABLE test_sql.test4user VALUES('0010','u10',65);  
INSERT INTO TABLE test_sql.test4log VALUES('001','url1');
INSERT INTO TABLE test_sql.test4log VALUES('002','url1');   
INSERT INTO TABLE test_sql.test4log VALUES('003','url2');   
INSERT INTO TABLE test_sql.test4log VALUES('004','url3');   
INSERT INTO TABLE test_sql.test4log VALUES('005','url3');   
INSERT INTO TABLE test_sql.test4log VALUES('006','url1');   
INSERT INTO TABLE test_sql.test4log VALUES('007','url5');
INSERT INTO TABLE test_sql.test4log VALUES('008','url7');  
INSERT INTO TABLE test_sql.test4log VALUES('009','url5');  
INSERT INTO TABLE test_sql.test4log VALUES('0010','url1'); 



查询SQL

SELECT 
t2.age_phase,
sum(t1.cnt) as view_cnt
FROM

(SELECT user_id,
  count(*) cnt
FROM test_sql.test4log
GROUP BY user_id) t1
JOIN
(SELECT user_id,
  CASE WHEN age <= 10 AND age > 0 THEN '0-10' 
  WHEN age <= 20 AND age > 10 THEN '10-20'
  WHEN age >20 AND age <=30 THEN '20-30'
  WHEN age >30 AND age <=40 THEN '30-40'
  WHEN age >40 AND age <=50 THEN '40-50'
  WHEN age >50 AND age <=60 THEN '50-60'
  WHEN age >60 AND age <=70 THEN '60-70'
  ELSE '70以上' END as age_phase
FROM test_sql.test4user) t2 ON t1.user_id = t2.user_id 
GROUP BY t2.age_phase


第五题
需求

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



实现
数据准备

CREATE TABLE test5(
dt string,
user_id string,
age int)
ROW format delimited fields terminated BY ',';
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_1',23);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_2',19);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_3',39);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_1',23);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_3',39);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_1',23);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-12','test_2',19);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-13','test_1',23);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-15','test_2',19);                                        
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-16','test_2',19);          

     

查询SQL

SELECT sum(total_user_cnt) total_user_cnt,
       sum(total_user_avg_age) total_user_avg_age,
       sum(two_days_cnt) two_days_cnt,
       sum(avg_age) avg_age
FROM
  (SELECT 0 total_user_cnt,
          0 total_user_avg_age,
          count(*) AS two_days_cnt,
          cast(sum(age) / count(*) AS decimal(5,2)) AS avg_age
   FROM
     (SELECT user_id,
             max(age) age
      FROM
        (SELECT user_id,
                max(age) age
         FROM
           (SELECT user_id,
                   age,
                   date_sub(dt,rank) flag
            FROM
              (SELECT dt,
                      user_id,
                      max(age) age,
                      row_number() over(PARTITION BY user_id
                                        ORDER BY dt) rank
               FROM test_sql.test5
               GROUP BY dt,
                        user_id) t1) t2
         GROUP BY user_id,
                  flag
         HAVING count(*) >=2) t3
      GROUP BY user_id) t4
   UNION ALL SELECT count(*) total_user_cnt,
                    cast(sum(age) /count(*) AS decimal(5,2)) total_user_avg_age,
                    0 two_days_cnt,
                    0 avg_age
   FROM
     (SELECT user_id,
             max(age) age
      FROM test_sql.test5
      GROUP BY user_id) t5) t6



第六题
需求

请用sql写出所有用户中在今年10月份第一次购买商品的金额，
表ordertable字段:
(购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid     

 
实现
数据准备

CREATE TABLE test_sql.test6 (
        userid string,
        money decimal(10,2),
        paymenttime string,
        orderid string);

INSERT INTO TABLE test_sql.test6 VALUES('001',100,'2017-10-01','123');
INSERT INTO TABLE test_sql.test6 VALUES('001',200,'2017-10-02','124');
INSERT INTO TABLE test_sql.test6 VALUES('002',500,'2017-10-01','125');
INSERT INTO TABLE test_sql.test6 VALUES('001',100,'2017-11-01','126');    

            
查询SQL

SELECT
userid,
paymenttime,
money,
orderid
from
(SELECT userid,
       money,
       paymenttime,
       orderid,
       row_number() over (PARTITION BY userid
                          ORDER BY paymenttime) rank
FROM test_sql.test6
WHERE date_format(paymenttime,'yyyy-MM') = '2017-10') t
WHERE rank = 1


第七题
需求

现有图书管理数据库的三个数据模型如下：
图书（数据表名：BOOK）
    序号      字段名称    字段描述    字段类型
    1       BOOK_ID     总编号         文本
    2       SORT        分类号         文本
    3           BOOK_NAME   书名          文本
    4       WRITER      作者          文本
    5       OUTPUT      出版单位    文本
    6       PRICE       单价          数值（保留小数点后2位）
读者（数据表名：READER）
    序号      字段名称    字段描述    字段类型
    1       READER_ID   借书证号    文本
    2       COMPANY     单位          文本
    3       NAME        姓名          文本
    4       SEX         性别          文本
    5       GRADE       职称          文本
    6       ADDR        地址          文本
借阅记录（数据表名：BORROW LOG）
    序号      字段名称        字段描述    字段类型
    1       READER_ID       借书证号    文本
    2       BOOK_ID          总编号         文本
    3       BORROW_DATE      借书日期    日期
（1）创建图书管理库的图书、读者和借阅三个基本表的表结构。请写出建表语句。
（2）找出姓李的读者姓名（NAME）和所在单位（COMPANY）。
（3）查找“高等教育出版社”的所有图书名称（BOOK_NAME）及单价（PRICE），结果按单价降序排序。
（4）查找价格介于10元和20元之间的图书种类(SORT）出版单位（OUTPUT）和单价（PRICE），结果按出版单位（OUTPUT）和单价（PRICE）升序排序。
（5）查找所有借了书的读者的姓名（NAME）及所在单位（COMPANY）。
（6）求”科学出版社”图书的最高单价、最低单价、平均单价。
（7）找出当前至少借阅了2本图书（大于等于2本）的读者姓名及其所在单位。
（8）考虑到数据安全的需要，需定时将“借阅记录”中数据进行备份，请使用一条SQL语句，在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK.井且将“借阅记录”中现有数据全部复制到BORROW_L0G_ BAK中。
（9）现在需要将原Oracle数据库中数据迁移至Hive仓库，请写出“图书”在Hive中的建表语句（Hive实现，提示：列分隔符|；数据表数据需要外部导入：分区分别以month＿part、day＿part 命名）
（10）Hive中有表A，现在需要将表A的月分区　201505　中　user＿id为20000的user＿dinner字段更新为bonc8920，其他用户user＿dinner字段数据不变，请列出更新的方法步骤。（Hive实现，提示：Hlive中无update语法，请通过其他办法进行数据更新）



实现
(1)

-- 创建图书表book

CREATE TABLE test_sql.book(book_id string,
                           `SORT` string,
                           book_name string,
                           writer string,
                           OUTPUT string,
                           price decimal(10,2));
INSERT INTO TABLE test_sql.book VALUES ('001','TP391','信息处理','author1','机械工业出版社','20');
INSERT INTO TABLE test_sql.book VALUES ('002','TP392','数据库','author12','科学出版社','15');
INSERT INTO TABLE test_sql.book VALUES ('003','TP393','计算机网络','author3','机械工业出版社','29');
INSERT INTO TABLE test_sql.book VALUES ('004','TP399','微机原理','author4','科学出版社','39');
INSERT INTO TABLE test_sql.book VALUES ('005','C931','管理信息系统','author5','机械工业出版社','40');
INSERT INTO TABLE test_sql.book VALUES ('006','C932','运筹学','author6','科学出版社','55');

-- 创建读者表reader
CREATE TABLE test_sql.reader (reader_id string,
                              company string,
                              name string,
                              sex string,
                              grade string,
                              addr string);
INSERT INTO TABLE test_sql.reader VALUES ('0001','阿里巴巴','jack','男','vp','addr1');
INSERT INTO TABLE test_sql.reader VALUES ('0002','百度','robin','男','vp','addr2');
INSERT INTO TABLE test_sql.reader VALUES ('0003','腾讯','tony','男','vp','addr3');
INSERT INTO TABLE test_sql.reader VALUES ('0004','京东','jasper','男','cfo','addr4');
INSERT INTO TABLE test_sql.reader VALUES ('0005','网易','zhangsan','女','ceo','addr5');
INSERT INTO TABLE test_sql.reader VALUES ('0006','搜狐','lisi','女','ceo','addr6');

-- 创建借阅记录表borrow_log

CREATE TABLE test_sql.borrow_log(reader_id string,
                                 book_id string,
                                 borrow_date string);
                                 
INSERT INTO TABLE test_sql.borrow_log VALUES ('0001','002','2019-10-14');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0002','001','2019-10-13');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0003','005','2019-09-14');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0004','006','2019-08-15');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0005','003','2019-10-10');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0006','004','2019-17-13');

(2)
    SELECT name,
           company
    FROM test_sql.reader
    WHERE name LIKE '李%';
(3)
    SELECT book_name,
           price
    FROM test_sql.book
    WHERE OUTPUT = "高等教育出版社"
    ORDER BY price DESC;
(4)
    SELECT sort,
           output,
           price
    FROM test_sql.book
    WHERE price >= 10 and price <= 20
    ORDER BY output,price ;
(5)
    SELECT b.name,
           b.company
    FROM test_sql.borrow_log a
    JOIN test_sql.reader b ON a.reader_id = b.reader_id;
(6)
    SELECT max(price),
           min(price),
           avg(price)
    FROM test_sql.book
    WHERE OUTPUT = '科学出版社';
(7)
    SELECT b.name,
           b.company
    FROM
      (SELECT reader_id
       FROM test_sql.borrow_log
       GROUP BY reader_id
       HAVING count(*) >= 2) a
    JOIN test_sql.reader b ON a.reader_id = b.reader_id;
    
(8)
    CREATE TABLE test_sql.borrow_log_bak AS
    SELECT *
    FROM test_sql.borrow_log;
(9)
    CREATE TABLE book_hive ( 
    book_id string,
    SORT string, 
    book_name string,
    writer string, 
    OUTPUT string, 
    price DECIMAL ( 10, 2 ) )
    partitioned BY ( month_part string, day_part string )
    ROW format delimited FIELDS TERMINATED BY '\\|' stored AS textfile;
(10)
    方式1：配置hive支持事务操作，分桶表，orc存储格式
    方式2：第一步找到要更新的数据，将要更改的字段替换为新的值，第二步找到不需要更新的数据，第三步将上两步的数据插入一张新表中。


 


第八题
需求

有一个线上服务器访问日志格式如下（用sql答题）
时间                    接口                         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地址              

  

实现

数据准备
CREATE TABLE test_sql.test8(`date` string,
                interface string,
                ip string);

INSERT INTO TABLE test_sql.test8 VALUES ('2016-11-09 11:22:05','/api/user/login','110.23.5.23');
INSERT INTO TABLE test_sql.test8 VALUES ('2016-11-09 11:23:10','/api/user/detail','57.3.2.16');
INSERT INTO TABLE test_sql.test8 VALUES ('2016-11-09 23:59:40','/api/user/login','200.6.5.166');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 11:14:23','/api/user/login','136.79.47.70');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 11:15:23','/api/user/detail','94.144.143.141');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 11:16:23','/api/user/login','197.161.8.206');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 12:14:23','/api/user/detail','240.227.107.145');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 13:14:23','/api/user/login','79.130.122.205');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:14:23','/api/user/detail','65.228.251.189');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:15:23','/api/user/detail','245.23.122.44');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:17:23','/api/user/detail','22.74.142.137');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:19:23','/api/user/detail','54.93.212.87');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:20:23','/api/user/detail','218.15.167.248');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:24:23','/api/user/detail','20.117.19.75');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 15:14:23','/api/user/login','183.162.66.97');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 16:14:23','/api/user/login','108.181.245.147');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:17:23','/api/user/login','22.74.142.137');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:19:23','/api/user/login','22.74.142.137');



查询SQL

SELECT ip,
       count(*) AS cnt
FROM test_sql.test8
WHERE date_format(date,'yyyy-MM-dd HH') >= '2016-11-09 14'
  AND date_format(date,'yyyy-MM-dd HH') < '2016-11-09 15'
  AND interface='/api/user/login'
GROUP BY ip
ORDER BY cnt desc
LIMIT 10;             


第九题
需求

有一个充值日志表credit_log，字段如下：

`dist_id` int  '区组id',
`account` string  '账号',
`money` int   '充值金额',
`create_time` string  '订单时间'

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


实现
数据准备

CREATE TABLE test_sql.test9(
            dist_id string COMMENT '区组id',
            account string COMMENT '账号',
           `money` decimal(10,2) COMMENT '充值金额',
            create_time string COMMENT '订单时间');

INSERT INTO TABLE test_sql.test9 VALUES ('1','11',100006,'2019-01-02 13:00:01');
INSERT INTO TABLE test_sql.test9 VALUES ('1','22',110000,'2019-01-02 13:00:02');
INSERT INTO TABLE test_sql.test9 VALUES ('1','33',102000,'2019-01-02 13:00:03');
INSERT INTO TABLE test_sql.test9 VALUES ('1','44',100300,'2019-01-02 13:00:04');
INSERT INTO TABLE test_sql.test9 VALUES ('1','55',100040,'2019-01-02 13:00:05');
INSERT INTO TABLE test_sql.test9 VALUES ('1','66',100005,'2019-01-02 13:00:06');
INSERT INTO TABLE test_sql.test9 VALUES ('1','77',180000,'2019-01-03 13:00:07');
INSERT INTO TABLE test_sql.test9 VALUES ('1','88',106000,'2019-01-02 13:00:08');
INSERT INTO TABLE test_sql.test9 VALUES ('1','99',100400,'2019-01-02 13:00:09');
INSERT INTO TABLE test_sql.test9 VALUES ('1','12',100030,'2019-01-02 13:00:10');
INSERT INTO TABLE test_sql.test9 VALUES ('1','13',100003,'2019-01-02 13:00:20');
INSERT INTO TABLE test_sql.test9 VALUES ('1','14',100020,'2019-01-02 13:00:30');
INSERT INTO TABLE test_sql.test9 VALUES ('1','15',100500,'2019-01-02 13:00:40');
INSERT INTO TABLE test_sql.test9 VALUES ('1','16',106000,'2019-01-02 13:00:50');
INSERT INTO TABLE test_sql.test9 VALUES ('1','17',100800,'2019-01-02 13:00:59');
INSERT INTO TABLE test_sql.test9 VALUES ('2','18',100800,'2019-01-02 13:00:11');
INSERT INTO TABLE test_sql.test9 VALUES ('2','19',100030,'2019-01-02 13:00:12');
INSERT INTO TABLE test_sql.test9 VALUES ('2','10',100000,'2019-01-02 13:00:13');
INSERT INTO TABLE test_sql.test9 VALUES ('2','45',100010,'2019-01-02 13:00:14');
INSERT INTO TABLE test_sql.test9 VALUES ('2','78',100070,'2019-01-02 13:00:15');                

查询SQL
WITH TEMP AS
  (SELECT dist_id,
          account,
          sum(`money`) sum_money
   FROM test_sql.test9
   WHERE date_format(create_time,'yyyy-MM-dd') = '2019-01-02'
   GROUP BY dist_id,
            account)
SELECT t1.dist_id,
       t1.account,
       t1.sum_money
FROM
  (SELECT temp.dist_id,
          temp.account,
          temp.sum_money,
          rank() over(partition BY temp.dist_id
                      ORDER BY temp.sum_money DESC) ranks
   FROM TEMP) t1
WHERE ranks = 1             



第十题
需求

有一个账号表如下，请写出SQL语句，查询各自区组的money排名前十的账号（分组取前10）
dist_id string  '区组id',
account string  '账号',
gold     int    '金币'                

实现
数据准备
CREATE TABLE test_sql.test10(
    `dist_id` string COMMENT '区组id',
    `account` string COMMENT '账号',
    `gold` int COMMENT '金币'
);

INSERT INTO TABLE test_sql.test10 VALUES ('1','77',18);
INSERT INTO TABLE test_sql.test10 VALUES ('1','88',106);
INSERT INTO TABLE test_sql.test10 VALUES ('1','99',10);
INSERT INTO TABLE test_sql.test10 VALUES ('1','12',13);
INSERT INTO TABLE test_sql.test10 VALUES ('1','13',14);
INSERT INTO TABLE test_sql.test10 VALUES ('1','14',25);
INSERT INTO TABLE test_sql.test10 VALUES ('1','15',36);
INSERT INTO TABLE test_sql.test10 VALUES ('1','16',12);
INSERT INTO TABLE test_sql.test10 VALUES ('1','17',158);
INSERT INTO TABLE test_sql.test10 VALUES ('2','18',12);
INSERT INTO TABLE test_sql.test10 VALUES ('2','19',44);
INSERT INTO TABLE test_sql.test10 VALUES ('2','10',66);
INSERT INTO TABLE test_sql.test10 VALUES ('2','45',80);
INSERT INTO TABLE test_sql.test10 VALUES ('2','78',98);            

查询SQL
SELECT dist_id,
   account,
   gold
FROM
(SELECT dist_id,
      account,
      gold,
      row_number () over (PARTITION BY dist_id
                          ORDER BY gold DESC) rank
FROM test_sql.test10) t
WHERE rank <= 10


 

Dream__people
关注
专栏目录
[面试]-- Hive面试问题
欢迎来到我的博客，一起探索代码里的世界！
06-06 7969
A所有的hive任务都会有reducer的执行吗？ 答：不是，由于当前hive的优化，使得一般简单的任务不会去用reducer任务；只有稍微复杂的任务才会有reducer任务 举例：使用select * from person ; 就不会有reducer       使用from person p                    insert  into  person2 selec...
Hive SQL大厂面试题必考大全
CSDN 精品推荐
07-17 560
专栏内容涵盖了基于Hive SQL的各类题目，旨在通过解析经典问题和案例，帮助读者深入理解Hive SQL的原理和应用。每篇文章都将附带完整的题目代码和详细的解题思路，理论与实践相结合，帮助你更好地掌握Hive SQL的知识。
Hive的面试题
m0_55834564的博客
04-18 4930
目录 1.请谈一下hive的特点? 2.Hive底层与数据库存交互原理? 3.Hive内部表和外部表的区别? 4.Hive导入数据的五种方式是什么?举例说明 5.hive与传统关系型数据库的区别 6.Hive中创建表有哪几种方式，其区别是什么？ 7.Hive的窗口函数有哪些 8.row_number()，rank()和dense_rank()的区别 9.Hive如何实现分区 10.Hive的两张表关联，使用MapReduce怎么实现？ 11.请说明hive中 Sort By，Or.
hive的面试题
汉子一枚
11-28 2337
1、什么是Hive Hive是一个语句Hadoop的一个数据仓库工具，是将结构化数据文件映射成为一个数据表，并提供类SQL的查询功能。 2、Hive的意义（最初研发的原因） 在hadoop是个好软件，但是不好使用（学习成本太高，坡度陡，难度大）的前提下降低了程序员使用hadoop的学习成本，降低了难度。 3、Hive的内部组成模块，作用分别是什么 解释器-> 编译器（会使用到元数据）-&gt...
Hive 面试题
liu_changshen的博客
11-28 467
什么是Hive? Hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。Hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。 Hive的意义（最初研发的原因）? 降低程序员使用Hadoop的难度，降低学习成本 Hive的内...
大数据面试之Hive SQL经典面试题
涛声依旧, 万古流名
04-26 2757
请注意，这种方法并没有真正地将行转换为独立的列，而是将多个行的值合并到了一个字符串中。在Hive中，由于schema是静态的，将任意数量的行转换为固定数量的列是不直接支持的。如果知道商品ID的最大数量，并且这个数量是固定的，可以使用条件聚合和。然后，在外部查询中，筛选出序列号小于等于 N 的记录，即销售额最高的前 N 个记录。希望将每个用户的购买记录从行格式转换为列格式，以展示每个用户购买的每个商品。列存储每个订单所购买的商品数组，数组中每个元素都是一个元组，包含商品ID和数量。
数据分析+hive基础及进阶SQL练习题+面试
最新发布
08-12
包含内容： （1）HiveSQL基础SQL练习50题 （2）HiveSQL业务强化练习30题 ... 2）HiveSQL影评分析案例 3）学生课程成绩 4）求每一年最大气温的那一天+温度 5）求学生选课情况 6）求月销售额和总销售额
31道Hive大厂高频面试题，学会了进大厂不是梦，建议收藏！
雷恩Layne
09-26 8667
文章目录1. 介绍一下什么是Hive2. Hive的架构原理3. Hive的HSQL转换为MapReduce的过程？4. Hive和RDBMS有什么异同/hive和传统数据库之间的区别5. HiveSQL语句不会转化为MapReduce作业的情况6. 请简单介绍一下Hive的本地模式7. 你使用过哪些 Hive 函数8. 列转行和行转列函数有哪些9. 介绍一下Hive中的窗口函数10. Hive内部表、外部表、分区表、分桶表的区别，以及各自的使用场景11. Order By,Sort By,Distrbu
Hive Sql中六种面试题型总结
热门推荐
lightupworld的博客
09-14 1万+
Hive面试/经典练习题一、N日留存1.思路分析二、连续登陆1.数据准备2.思路分析三、 Top N 一、N日留存 1.思路分析 按天留存率计算 就是指用户在首日新增后，在接下来的后推第N天活跃情况，用后推第N天活跃的用户 / 首日新增用户。就得到后推第N天的新增用户留存率。 留存率计算案例 1月1日，新增用户200人; 次日留存：第2天，1月2日，这200人里面有100人活跃，则次日留存率为： 100 / 200 = 50% 2日留存：第3天，1月3日;这200名新
hive面试题
12-04
自己整理的hive的面试题，加上自己面试的时候遇到的面试题，整合到一起
Hive面试题
qq_37165724的博客
09-07 650
Hive面试题整理（一） 1、Hive表关联查询，如何解决数据倾斜的问题？（☆☆☆☆☆）   1）倾斜原因：map输出数据按key Hash的分配到reduce中，由于key分布不均匀、业务数据本身的特、建表时考虑不周、等原因造成的reduce 上的数据量差异过大。   （1）key分布不均匀;   （2）业务数据本身的特性;   （3）建表时考虑不周;   （4）某些SQL语句本身就有数据倾斜;   如何避免：对于key为空产生的数据倾斜，可以对其赋予一个随机值。   2）解决方案   （1）参数调节：
HIve面试题
qq_27924553的博客
08-01 391
1、行转列与列转行 1.1、行转列 select explode(split(‘aaa,bbb,ccc’,’,’))temp as x –本身返回一张虚拟表，使用later view可直接关联两张表，保持原有映射关系。 静态： select t1.uid ,t2.key ,t2.value from t1 later view explode(map( 'c1',c1, 'c2',c2, 'c3',c3 )) t2 as key,value select s.name,t
2020大数据面试真题（转载）
Solomwn的博客
07-06 700
目录 一.Hadoop 1.hdfs写流程 2.hdfs读流程 3.hdfs的体系结构 4.一个datanode 宕机,怎么一个流程恢复 5.hadoop 的 namenode 宕机,怎么解决 6.namenode对元数据的管理 7.元数据的checkpoint 8.yarn资源调度流程 9.hadoop中combiner和partition的作用 10.用mapreduce怎么处理数据倾斜问题？ 11.shuffle 阶段,你怎么理解的 12.Mapreduce 的 map 数量 和 reduce 数量是
Hive：面试题
不花的花和尚的博客
10-19 1110
目录   Q1：Maven离线仓库的默认位置是什么？     Q2：Hive的主要作用是什么？ Q3：配置hive-env.sh都涉及到哪些属性？（中文描述） Q4：配置hive-site.xml都修改了哪些属性，请写出属性名称并解释该属性。 Q5：配置Mysql时，在CentOS6以及CentOS7中开启Mysql服务的命令分别是什么？ Q6：Mysql中如何修改root密码？ ...
hive—面试题(1)
weixin_45571326的博客
04-27 222
假设现在有100W数据放在hive的这一张内部分区表table1，平均分配到了十个分区，假设有个工作人员不小心删掉了一个hdfs文件夹（hadoop fs rm -r, 分区表的一个分区文件夹）， 请问select count(1) from table1的执行结果。A 90W B 100W C 其他情况，请具体说明??? 自己电脑上有虚拟机集群的， 可以亲测一下哦！！！ ...
大数据技术精要与面试指南
主要技术包括Spark、Hadoop、Hive、HBase等，并提供了相关面试题。 1. 分布式计算框架与资源调度： - MapReduce编程模型用于分布式数据处理，包括partitioner和combiner的使用，以优化数据处理效率。 - 分布式...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值