devbox
从前慢现在也慢
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

经典Hive-SQL面试题_date_format(min(dt),

作者：从前慢现在也慢 | 2024-05-06 08:21:23

赞

踩

date_format(min(dt),

以下文章来源于大数据技术与数仓 ，作者西贝

大数据技术与数仓

专注分享数据仓库与大数据技术(Flink/Hadoop/Spark/Hive)相关内容。关注我可以免费领取大数据书籍与视频。我的博客:https://jiamaoxiang.top/

 

第一题

需求

  1. 我们有如下的用户访问数据
  2.     userId  visitDate   visitCount
  3.     u01 2017/1/21   5
  4.     u02 2017/1/23   6
  5.     u03 2017/1/22   8
  6.     u04 2017/1/20   3
  7.     u01 2017/1/23   6
  8.     u01 2017/2/21   8
  9.     U02 2017/1/23   6
  10.     U01 2017/2/22   4
  11. 要求使用SQL统计出每个用户的累积访问次数，如下表所示：
  12.     用户id    月份  小计  累积
  13.     u01 2017-01 11  11
  14.     u01 2017-02 12  23
  15.     u02 2017-01 12  12
  16.     u03 2017-01 8   8
  17.     u04 2017-01 3   3

实现

数据准备

  1. CREATE TABLE test_sql.test1 ( 
  2.         userId string, 
  3.         visitDate string,
  4.         visitCount INT )
  5.     ROW format delimited FIELDS TERMINATED BY "\t";
  6.     INSERT INTO TABLE test_sql.test1
  7.     VALUES
  8.         ( 'u01', '2017/1/21', 5 ),
  9.         ( 'u02', '2017/1/23', 6 ),
  10.         ( 'u03', '2017/1/22', 8 ),
  11.         ( 'u04', '2017/1/20', 3 ),
  12.         ( 'u01', '2017/1/23', 6 ),
  13.         ( 'u01', '2017/2/21', 8 ),
  14.         ( 'u02', '2017/1/23', 6 ),
  15.         ( 'u01', '2017/2/22', 4 );

查询SQL

  1. SELECT t2.userid,
  2.        t2.visitmonth,
  3.        subtotal_visit_cnt,
  4.        sum(subtotal_visit_cnt) over (partition BY userid ORDER BY visitmonth) AS total_visit_cnt
  5. FROM
  6.   (SELECT userid,
  7.           visitmonth,
  8.           sum(visitcount) AS subtotal_visit_cnt
  9.    FROM
  10.      (SELECT userid,
  11.              date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') AS visitmonth,
  12.              visitcount
  13.       FROM test_sql.test1) t1
  14.    GROUP BY userid,
  15.             visitmonth)t2
  16. ORDER BY t2.userid,
  17.          t2.visitmonth

第二题

需求

  1. 有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，
  2. 访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，数据如下：
  3.  
  4.                 u1  a
  5.                 u2  b
  6.                 u1  b
  7.                 u1  a
  8.                 u3  c
  9.                 u4  b
  10.                 u1  a
  11.                 u2  c
  12.                 u5  b
  13.                 u4  b
  14.                 u6  c
  15.                 u2  c
  16.                 u1  b
  17.                 u2  a
  18.                 u2  a
  19.                 u3  a
  20.                 u5  a
  21.                 u5  a
  22.                 u5  a
  23. 请统计：
  24. (1)每个店铺的UV（访客数）
  25. (2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

实现

数据准备

  1. CREATE TABLE test_sql.test2 ( 
  2.                          user_id string, 
  3.                          shop string )
  4.             ROW format delimited FIELDS TERMINATED BY '\t'; 
  5.             INSERT INTO TABLE test_sql.test2 VALUES
  6.             ( 'u1', 'a' ),
  7.             ( 'u2', 'b' ),
  8.             ( 'u1', 'b' ),
  9.             ( 'u1', 'a' ),
  10.             ( 'u3', 'c' ),
  11.             ( 'u4', 'b' ),
  12.             ( 'u1', 'a' ),
  13.             ( 'u2', 'c' ),
  14.             ( 'u5', 'b' ),
  15.             ( 'u4', 'b' ),
  16.             ( 'u6', 'c' ),
  17.             ( 'u2', 'c' ),
  18.             ( 'u1', 'b' ),
  19.             ( 'u2', 'a' ),
  20.             ( 'u2', 'a' ),
  21.             ( 'u3', 'a' ),
  22.             ( 'u5', 'a' ),
  23.             ( 'u5', 'a' ),
  24.             ( 'u5', 'a' );          

查询SQL实现

  1. (1)方式1：
  2.         SELECT shop,
  3.                count(DISTINCT user_id)
  4.         FROM test_sql.test2
  5.         GROUP BY shop
  6. 方式2：
  7.         SELECT t.shop,
  8.                count(*)
  9.         FROM
  10.           (SELECT user_id,
  11.                   shop
  12.            FROM test_sql.test2
  13.            GROUP BY user_id,
  14.                     shop) t
  15.         GROUP BY t.shop
  16. (2)    
  17. SELECT t2.shop,
  18.        t2.user_id,
  19.        t2.cnt
  20. FROM
  21.   (SELECT t1.*,
  22.           row_number() over(partition BY t1.shop
  23.                             ORDER BY t1.cnt DESC) rank
  24.    FROM
  25.      (SELECT user_id,
  26.              shop,
  27.              count(*) AS cnt
  28.       FROM test_sql.test2
  29.       GROUP BY user_id,
  30.                shop) t1)t2
  31. WHERE rank <= 3                    

第三题

需求

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

实现

数据准备

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

查询SQL

  1. (1)
  2. SELECT t1.mon,
  3.        count(t1.order_id) AS order_cnt,
  4.        count(DISTINCT t1.user_id) AS user_cnt,
  5.        sum(amount) AS total_amount
  6. FROM
  7.   (SELECT order_id,
  8.           user_id,
  9.           amount,
  10.           date_format(dt,'yyyy-MM') mon
  11.    FROM test_sql.test3
  12.    WHERE date_format(dt,'yyyy') = '2017') t1
  13. GROUP BY t1.mon
  14. (2)
  15. SELECT count(user_id)
  16. FROM test_sql.test3
  17. GROUP BY user_id
  18. HAVING date_format(min(dt),'yyyy-MM')='2017-11';

第四题

需求

有一个5000万的用户文件(user_id，name，age)，一个2亿记录的用户看电影的记录文件(user_id，url)，根据年龄段观看电影的次数进行排序？        

实现

数据准备

  1. CREATE TABLE test_sql.test4user
  2.            (user_id string,
  3.             name string,
  4.             age int);
  5.  
  6. CREATE TABLE test_sql.test4log
  7.                         (user_id string,
  8.                         url string);
  9.  
  10. INSERT INTO TABLE test_sql.test4user VALUES('001','u1',10);
  11. INSERT INTO TABLE test_sql.test4user VALUES('002','u2',15);   
  12. INSERT INTO TABLE test_sql.test4user VALUES('003','u3',15);   
  13. INSERT INTO TABLE test_sql.test4user VALUES('004','u4',20);   
  14. INSERT INTO TABLE test_sql.test4user VALUES('005','u5',25);   
  15. INSERT INTO TABLE test_sql.test4user VALUES('006','u6',35);   
  16. INSERT INTO TABLE test_sql.test4user VALUES('007','u7',40);
  17. INSERT INTO TABLE test_sql.test4user VALUES('008','u8',45);  
  18. INSERT INTO TABLE test_sql.test4user VALUES('009','u9',50);  
  19. INSERT INTO TABLE test_sql.test4user VALUES('0010','u10',65);  
  20. INSERT INTO TABLE test_sql.test4log VALUES('001','url1');
  21. INSERT INTO TABLE test_sql.test4log VALUES('002','url1');   
  22. INSERT INTO TABLE test_sql.test4log VALUES('003','url2');   
  23. INSERT INTO TABLE test_sql.test4log VALUES('004','url3');   
  24. INSERT INTO TABLE test_sql.test4log VALUES('005','url3');   
  25. INSERT INTO TABLE test_sql.test4log VALUES('006','url1');   
  26. INSERT INTO TABLE test_sql.test4log VALUES('007','url5');
  27. INSERT INTO TABLE test_sql.test4log VALUES('008','url7');  
  28. INSERT INTO TABLE test_sql.test4log VALUES('009','url5');  
  29. INSERT INTO TABLE test_sql.test4log VALUES('0010','url1'); 

查询SQL

  1. SELECT 
  2. t2.age_phase,
  3. sum(t1.cnt) as view_cnt
  4. FROM
  5.  
  6. (SELECT user_id,
  7.   count(*) cnt
  8. FROM test_sql.test4log
  9. GROUP BY user_id) t1
  10. JOIN
  11. (SELECT user_id,
  12.   CASE WHEN age <= 10 AND age > 0 THEN '0-10' 
  13.   WHEN age <= 20 AND age > 10 THEN '10-20'
  14.   WHEN age >20 AND age <=30 THEN '20-30'
  15.   WHEN age >30 AND age <=40 THEN '30-40'
  16.   WHEN age >40 AND age <=50 THEN '40-50'
  17.   WHEN age >50 AND age <=60 THEN '50-60'
  18.   WHEN age >60 AND age <=70 THEN '60-70'
  19.   ELSE '70以上' END as age_phase
  20. FROM test_sql.test4user) t2 ON t1.user_id = t2.user_id 
  21. GROUP BY t2.age_phase

第五题

需求

  1. 有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）
  2. 日期 用户 年龄
  3. 2019-02-11,test_1,23
  4. 2019-02-11,test_2,19
  5. 2019-02-11,test_3,39
  6. 2019-02-11,test_1,23
  7. 2019-02-11,test_3,39
  8. 2019-02-11,test_1,23
  9. 2019-02-12,test_2,19
  10. 2019-02-13,test_1,23
  11. 2019-02-15,test_2,19
  12. 2019-02-16,test_2,19

实现

数据准备

  1. CREATE TABLE test5(
  2. dt string,
  3. user_id string,
  4. age int)
  5. ROW format delimited fields terminated BY ',';
  6. INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_1',23);
  7. INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_2',19);
  8. INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_3',39);
  9. INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_1',23);
  10. INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_3',39);
  11. INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_1',23);
  12. INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-12','test_2',19);
  13. INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-13','test_1',23);
  14. INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-15','test_2',19);                                        
  15. INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-16','test_2',19);                

查询SQL

  1. SELECT sum(total_user_cnt) total_user_cnt,
  2.        sum(total_user_avg_age) total_user_avg_age,
  3.        sum(two_days_cnt) two_days_cnt,
  4.        sum(avg_age) avg_age
  5. FROM
  6.   (SELECT 0 total_user_cnt,
  7.           0 total_user_avg_age,
  8.           count(*) AS two_days_cnt,
  9.           cast(sum(age) / count(*) AS decimal(5,2)) AS avg_age
  10.    FROM
  11.      (SELECT user_id,
  12.              max(age) age
  13.       FROM
  14.         (SELECT user_id,
  15.                 max(age) age
  16.          FROM
  17.            (SELECT user_id,
  18.                    age,
  19.                    date_sub(dt,rank) flag
  20.             FROM
  21.               (SELECT dt,
  22.                       user_id,
  23.                       max(age) age,
  24.                       row_number() over(PARTITION BY user_id
  25.                                         ORDER BY dt) rank
  26.                FROM test_sql.test5
  27.                GROUP BY dt,
  28.                         user_id) t1) t2
  29.          GROUP BY user_id,
  30.                   flag
  31.          HAVING count(*) >=2) t3
  32.       GROUP BY user_id) t4
  33.    UNION ALL SELECT count(*) total_user_cnt,
  34.                     cast(sum(age) /count(*) AS decimal(5,2)) total_user_avg_age,
  35.                     0 two_days_cnt,
  36.                     0 avg_age
  37.    FROM
  38.      (SELECT user_id,
  39.              max(age) age
  40.       FROM test_sql.test5
  41.       GROUP BY user_id) t5) t6

第六题

需求

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

实现

数据准备

  1. CREATE TABLE test_sql.test6 (
  2.         userid string,
  3.         money decimal(10,2),
  4.         paymenttime string,
  5.         orderid string);
  6.  
  7. INSERT INTO TABLE test_sql.test6 VALUES('001',100,'2017-10-01','123');
  8. INSERT INTO TABLE test_sql.test6 VALUES('001',200,'2017-10-02','124');
  9. INSERT INTO TABLE test_sql.test6 VALUES('002',500,'2017-10-01','125');
  10. INSERT INTO TABLE test_sql.test6 VALUES('001',100,'2017-11-01','126');                

查询SQL

  1. SELECT
  2. userid,
  3. paymenttime,
  4. money,
  5. orderid
  6. from
  7. (SELECT userid,
  8.        money,
  9.        paymenttime,
  10.        orderid,
  11.        row_number() over (PARTITION BY userid
  12.                           ORDER BY paymenttime) rank
  13. FROM test_sql.test6
  14. WHERE date_format(paymenttime,'yyyy-MM') = '2017-10') t
  15. WHERE rank = 1

第七题

需求

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

实现

  1. (1)
  2.  
  3. -- 创建图书表book
  4.  
  5. CREATE TABLE test_sql.book(book_id string,
  6.                            `SORT` string,
  7.                            book_name string,
  8.                            writer string,
  9.                            OUTPUT string,
  10.                            price decimal(10,2));
  11. INSERT INTO TABLE test_sql.book VALUES ('001','TP391','信息处理','author1','机械工业出版社','20');
  12. INSERT INTO TABLE test_sql.book VALUES ('002','TP392','数据库','author12','科学出版社','15');
  13. INSERT INTO TABLE test_sql.book VALUES ('003','TP393','计算机网络','author3','机械工业出版社','29');
  14. INSERT INTO TABLE test_sql.book VALUES ('004','TP399','微机原理','author4','科学出版社','39');
  15. INSERT INTO TABLE test_sql.book VALUES ('005','C931','管理信息系统','author5','机械工业出版社','40');
  16. INSERT INTO TABLE test_sql.book VALUES ('006','C932','运筹学','author6','科学出版社','55');
  17.  
  18.  
  19. -- 创建读者表reader
  20.  
  21. CREATE TABLE test_sql.reader (reader_id string,
  22.                               company string,
  23.                               name string,
  24.                               sex string,
  25.                               grade string,
  26.                               addr string);
  27. INSERT INTO TABLE test_sql.reader VALUES ('0001','阿里巴巴','jack','男','vp','addr1');
  28. INSERT INTO TABLE test_sql.reader VALUES ('0002','百度','robin','男','vp','addr2');
  29. INSERT INTO TABLE test_sql.reader VALUES ('0003','腾讯','tony','男','vp','addr3');
  30. INSERT INTO TABLE test_sql.reader VALUES ('0004','京东','jasper','男','cfo','addr4');
  31. INSERT INTO TABLE test_sql.reader VALUES ('0005','网易','zhangsan','女','ceo','addr5');
  32. INSERT INTO TABLE test_sql.reader VALUES ('0006','搜狐','lisi','女','ceo','addr6');
  33.  
  34. -- 创建借阅记录表borrow_log
  35.  
  36. CREATE TABLE test_sql.borrow_log(reader_id string,
  37.                                  book_id string,
  38.                                  borrow_date string);
  39.  
  40. INSERT INTO TABLE test_sql.borrow_log VALUES ('0001','002','2019-10-14');
  41. INSERT INTO TABLE test_sql.borrow_log VALUES ('0002','001','2019-10-13');
  42. INSERT INTO TABLE test_sql.borrow_log VALUES ('0003','005','2019-09-14');
  43. INSERT INTO TABLE test_sql.borrow_log VALUES ('0004','006','2019-08-15');
  44. INSERT INTO TABLE test_sql.borrow_log VALUES ('0005','003','2019-10-10');
  45. INSERT INTO TABLE test_sql.borrow_log VALUES ('0006','004','2019-17-13');
  46.  
  47. (2)
  48.     SELECT name,
  49.            company
  50.     FROM test_sql.reader
  51.     WHERE name LIKE '李%';
  52. (3)
  53.     SELECT book_name,
  54.            price
  55.     FROM test_sql.book
  56.     WHERE OUTPUT = "高等教育出版社"
  57.     ORDER BY price DESC;
  58. (4)
  59.     SELECT sort,
  60.            output,
  61.            price
  62.     FROM test_sql.book
  63.     WHERE price >= 10 and price <= 20
  64.     ORDER BY output,price ;
  65. (5)
  66.     SELECT b.name,
  67.            b.company
  68.     FROM test_sql.borrow_log a
  69.     JOIN test_sql.reader b ON a.reader_id = b.reader_id;
  70. (6)
  71.     SELECT max(price),
  72.            min(price),
  73.            avg(price)
  74.     FROM test_sql.book
  75.     WHERE OUTPUT = '科学出版社';
  76. (7)
  77.     SELECT b.name,
  78.            b.company
  79.     FROM
  80.       (SELECT reader_id
  81.        FROM test_sql.borrow_log
  82.        GROUP BY reader_id
  83.        HAVING count(*) >= 2) a
  84.     JOIN test_sql.reader b ON a.reader_id = b.reader_id;
  85.  
  86. (8)
  87.     CREATE TABLE test_sql.borrow_log_bak AS
  88.     SELECT *
  89.     FROM test_sql.borrow_log;
  90. (9)
  91.     CREATE TABLE book_hive ( 
  92.     book_id string,
  93.     SORT string, 
  94.     book_name string,
  95.     writer string, 
  96.     OUTPUT string, 
  97.     price DECIMAL ( 10, 2 ) )
  98.     partitioned BY ( month_part string, day_part string )
  99.     ROW format delimited FIELDS TERMINATED BY '\\|' stored AS textfile;
  100. (10)
  101.     方式1：配置hive支持事务操作，分桶表，orc存储格式
  102.     方式2：第一步找到要更新的数据，将要更改的字段替换为新的值，第二步找到不需要更新的数据，第三步将上两步的数据插入一张新表中。

第八题

需求

  1. 有一个线上服务器访问日志格式如下（用sql答题）
  2. 时间                    接口                         ip地址
  3. 2016-11-09 14:22:05        /api/user/login             110.23.5.33
  4. 2016-11-09 14:23:10        /api/user/detail            57.3.2.16
  5. 2016-11-09 15:59:40        /api/user/login             200.6.5.166
  6. … …
  7. 求11月9号下午14点（14-15点），访问/api/user/login接口的top10的ip地址                

实现

数据准备

  1. CREATE TABLE test_sql.test8(`date` string,
  2.                 interface string,
  3.                 ip string);
  4.  
  5. INSERT INTO TABLE test_sql.test8 VALUES ('2016-11-09 11:22:05','/api/user/login','110.23.5.23');
  6. INSERT INTO TABLE test_sql.test8 VALUES ('2016-11-09 11:23:10','/api/user/detail','57.3.2.16');
  7. INSERT INTO TABLE test_sql.test8 VALUES ('2016-11-09 23:59:40','/api/user/login','200.6.5.166');
  8. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 11:14:23','/api/user/login','136.79.47.70');
  9. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 11:15:23','/api/user/detail','94.144.143.141');
  10. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 11:16:23','/api/user/login','197.161.8.206');
  11. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 12:14:23','/api/user/detail','240.227.107.145');
  12. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 13:14:23','/api/user/login','79.130.122.205');
  13. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:14:23','/api/user/detail','65.228.251.189');
  14. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:15:23','/api/user/detail','245.23.122.44');
  15. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:17:23','/api/user/detail','22.74.142.137');
  16. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:19:23','/api/user/detail','54.93.212.87');
  17. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:20:23','/api/user/detail','218.15.167.248');
  18. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:24:23','/api/user/detail','20.117.19.75');
  19. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 15:14:23','/api/user/login','183.162.66.97');
  20. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 16:14:23','/api/user/login','108.181.245.147');
  21. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:17:23','/api/user/login','22.74.142.137');
  22. INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:19:23','/api/user/login','22.74.142.137');

查询SQL

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

第九题

需求

  1. 有一个充值日志表credit_log，字段如下：
  2.  
  3. `dist_id` int  '区组id',
  4. `account` string  '账号',
  5. `money` int   '充值金额',
  6. `create_time` string  '订单时间'
  7.  
  8. 请写出SQL语句，查询充值日志表2019年01月02号每个区组下充值额最大的账号，要求结果：
  9. 区组id，账号，金额，充值时间        

实现

数据准备

  1. CREATE TABLE test_sql.test9(
  2.             dist_id string COMMENT '区组id',
  3.             account string COMMENT '账号',
  4.            `money` decimal(10,2) COMMENT '充值金额',
  5.             create_time string COMMENT '订单时间');
  6.  
  7. INSERT INTO TABLE test_sql.test9 VALUES ('1','11',100006,'2019-01-02 13:00:01');
  8. INSERT INTO TABLE test_sql.test9 VALUES ('1','22',110000,'2019-01-02 13:00:02');
  9. INSERT INTO TABLE test_sql.test9 VALUES ('1','33',102000,'2019-01-02 13:00:03');
  10. INSERT INTO TABLE test_sql.test9 VALUES ('1','44',100300,'2019-01-02 13:00:04');
  11. INSERT INTO TABLE test_sql.test9 VALUES ('1','55',100040,'2019-01-02 13:00:05');
  12. INSERT INTO TABLE test_sql.test9 VALUES ('1','66',100005,'2019-01-02 13:00:06');
  13. INSERT INTO TABLE test_sql.test9 VALUES ('1','77',180000,'2019-01-03 13:00:07');
  14. INSERT INTO TABLE test_sql.test9 VALUES ('1','88',106000,'2019-01-02 13:00:08');
  15. INSERT INTO TABLE test_sql.test9 VALUES ('1','99',100400,'2019-01-02 13:00:09');
  16. INSERT INTO TABLE test_sql.test9 VALUES ('1','12',100030,'2019-01-02 13:00:10');
  17. INSERT INTO TABLE test_sql.test9 VALUES ('1','13',100003,'2019-01-02 13:00:20');
  18. INSERT INTO TABLE test_sql.test9 VALUES ('1','14',100020,'2019-01-02 13:00:30');
  19. INSERT INTO TABLE test_sql.test9 VALUES ('1','15',100500,'2019-01-02 13:00:40');
  20. INSERT INTO TABLE test_sql.test9 VALUES ('1','16',106000,'2019-01-02 13:00:50');
  21. INSERT INTO TABLE test_sql.test9 VALUES ('1','17',100800,'2019-01-02 13:00:59');
  22. INSERT INTO TABLE test_sql.test9 VALUES ('2','18',100800,'2019-01-02 13:00:11');
  23. INSERT INTO TABLE test_sql.test9 VALUES ('2','19',100030,'2019-01-02 13:00:12');
  24. INSERT INTO TABLE test_sql.test9 VALUES ('2','10',100000,'2019-01-02 13:00:13');
  25. INSERT INTO TABLE test_sql.test9 VALUES ('2','45',100010,'2019-01-02 13:00:14');
  26. INSERT INTO TABLE test_sql.test9 VALUES ('2','78',100070,'2019-01-02 13:00:15');                

查询SQL

  1. WITH TEMP AS
  2.   (SELECT dist_id,
  3.           account,
  4.           sum(`money`) sum_money
  5.    FROM test_sql.test9
  6.    WHERE date_format(create_time,'yyyy-MM-dd') = '2019-01-02'
  7.    GROUP BY dist_id,
  8.             account)
  9. SELECT t1.dist_id,
  10.        t1.account,
  11.        t1.sum_money
  12. FROM
  13.   (SELECT temp.dist_id,
  14.           temp.account,
  15.           temp.sum_money,
  16.           rank() over(partition BY temp.dist_id
  17.                       ORDER BY temp.sum_money DESC) ranks
  18.    FROM TEMP) t1
  19. WHERE ranks = 1             

第十题

需求

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

实现

数据准备

  1. CREATE TABLE test_sql.test10(
  2.     `dist_id` string COMMENT '区组id',
  3.     `account` string COMMENT '账号',
  4.     `gold` int COMMENT '金币'
  5. );
  6.  
  7. INSERT INTO TABLE test_sql.test10 VALUES ('1','77',18);
  8. INSERT INTO TABLE test_sql.test10 VALUES ('1','88',106);
  9. INSERT INTO TABLE test_sql.test10 VALUES ('1','99',10);
  10. INSERT INTO TABLE test_sql.test10 VALUES ('1','12',13);
  11. INSERT INTO TABLE test_sql.test10 VALUES ('1','13',14);
  12. INSERT INTO TABLE test_sql.test10 VALUES ('1','14',25);
  13. INSERT INTO TABLE test_sql.test10 VALUES ('1','15',36);
  14. INSERT INTO TABLE test_sql.test10 VALUES ('1','16',12);
  15. INSERT INTO TABLE test_sql.test10 VALUES ('1','17',158);
  16. INSERT INTO TABLE test_sql.test10 VALUES ('2','18',12);
  17. INSERT INTO TABLE test_sql.test10 VALUES ('2','19',44);
  18. INSERT INTO TABLE test_sql.test10 VALUES ('2','10',66);
  19. INSERT INTO TABLE test_sql.test10 VALUES ('2','45',80);
  20. INSERT INTO TABLE test_sql.test10 VALUES ('2','78',98);            

查询SQL

  1. SELECT dist_id,
  2.    account,
  3.    gold
  4. FROM
  5. (SELECT dist_id,
  6.       account,
  7.       gold,
  8.       row_number () over (PARTITION BY dist_id
  9.                           ORDER BY gold DESC) rank
  10. FROM test_sql.test10) t
  11. WHERE rank <= 10
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/543316
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号