大数据SQL经典面试题系列(2) - 蚂蚁森林面试题

最新推荐文章于 2024-06-22 09:47:27 发布
置顶 最新推荐文章于 2024-06-22 09:47:27 发布
阅读量1.5k 收藏 19
点赞数 2
分类专栏： # ---手撕SQL 文章标签： 数据库 大数据 hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41106844/article/details/107320047
版权 
准备数据：
plant_carbon.txt	换取树苗所需碳排放量（换树表）
p001	梭梭树	17
p002	沙柳	19
p003	樟子树	146
p004	胡杨	215

user_low_carbon.txt  用户累计减少碳排放量表（用户积碳表）
u_001	2017/1/1	10
u_001	2017/1/2	150
u_001	2017/1/2	110
u_001	2017/1/2	10
u_001	2017/1/4	50
u_001	2017/1/4	10
u_001	2017/1/6	45
u_001	2017/1/6	90
u_002	2017/1/1	10
u_002	2017/1/2	150
u_002	2017/1/2	70
u_002	2017/1/3	30
u_002	2017/1/3	80
u_002	2017/1/4	150
u_002	2017/1/5	101
u_002	2017/1/6	68
u_003	2017/1/1	20
u_003	2017/1/2	10
u_003	2017/1/2	150
u_003	2017/1/3	160
u_003	2017/1/4	20
u_003	2017/1/5	120
u_003	2017/1/6	20
u_003	2017/1/7	10
u_003	2017/1/7	110
u_004	2017/1/1	110
u_004	2017/1/2	20
u_004	2017/1/2	50
u_004	2017/1/3	120
u_004	2017/1/4	30
u_004	2017/1/5	60
u_004	2017/1/6	120
u_004	2017/1/7	10
u_004	2017/1/7	120
u_005	2017/1/1	80
u_005	2017/1/2	50
u_005	2017/1/2	80
u_005	2017/1/3	180
u_005	2017/1/4	180
u_005	2017/1/4	10
u_005	2017/1/5	80
u_005	2017/1/6	280
u_005	2017/1/7	80
u_005	2017/1/7	80
u_006	2017/1/1	40
u_006	2017/1/2	40
u_006	2017/1/2	140
u_006	2017/1/3	210
u_006	2017/1/3	10
u_006	2017/1/4	40
u_006	2017/1/5	40
u_006	2017/1/6	20
u_006	2017/1/7	50
u_006	2017/1/7	240
u_007	2017/1/1	130
u_007	2017/1/2	30
u_007	2017/1/2	330
u_007	2017/1/3	30
u_007	2017/1/4	530
u_007	2017/1/5	30
u_007	2017/1/6	230
u_007	2017/1/7	130
u_007	2017/1/7	30
u_008	2017/1/1	160
u_008	2017/1/2	60
u_008	2017/1/2	60
u_008	2017/1/3	60
u_008	2017/1/4	260
u_008	2017/1/5	360
u_008	2017/1/6	160
u_008	2017/1/7	60
u_008	2017/1/7	60
u_009	2017/1/1	70
u_009	2017/1/2	70
u_009	2017/1/2	70
u_009	2017/1/3	170
u_009	2017/1/4	270
u_009	2017/1/5	70
u_009	2017/1/6	70
u_009	2017/1/7	70
u_009	2017/1/7	70
u_010	2017/1/1	90
u_010	2017/1/2	90
u_010	2017/1/2	90
u_010	2017/1/3	90
u_010	2017/1/4	90
u_010	2017/1/4	80
u_010	2017/1/5	90
u_010	2017/1/5	90
u_010	2017/1/6	190
u_010	2017/1/7	90
u_010	2017/1/7	90
u_011	2017/1/1	110
u_011	2017/1/2	100
u_011	2017/1/2	100
u_011	2017/1/3	120
u_011	2017/1/4	100
u_011	2017/1/5	100
u_011	2017/1/6	100
u_011	2017/1/7	130
u_011	2017/1/7	100
u_012	2017/1/1	10
u_012	2017/1/2	120
u_012	2017/1/2	10
u_012	2017/1/3	10
u_012	2017/1/4	50
u_012	2017/1/5	10
u_012	2017/1/6	20
u_012	2017/1/7	10
u_012	2017/1/7	10
u_013	2017/1/1	50
u_013	2017/1/2	150
u_013	2017/1/2	50
u_013	2017/1/3	150
u_013	2017/1/4	550
u_013	2017/1/5	350
u_013	2017/1/6	50
u_013	2017/1/7	20
u_013	2017/1/7	60
u_014	2017/1/1	220
u_014	2017/1/2	120
u_014	2017/1/2	20
u_014	2017/1/3	20
u_014	2017/1/4	20
u_014	2017/1/5	250
u_014	2017/1/6	120
u_014	2017/1/7	270
u_014	2017/1/7	20
u_015	2017/1/1	10
u_015	2017/1/2	20
u_015	2017/1/2	10
u_015	2017/1/3	10
u_015	2017/1/4	20
u_015	2017/1/5	70
u_015	2017/1/6	10
u_015	2017/1/7	80
u_015	2017/1/7	60

建表语句

create table user_low_carbon(
    user_id String,
    data_dt String,
    low_carbon int
)
row format delimited fields terminated by '\t';

create table plant_carbon(
    plant_id String,
    plant_name String,
    low_carbon int
)
row format delimited fields terminated by '\t';

导入语句：

load data local inpath '/usr/hdk/data/plant_carbon.txt' into table plant_carbon;

load data local inpath '/usr/hdk/data/user_low_carbon.txt' into table user_low_carbon;

第一题

假设2017年1月1日开始记录低碳数据，假设2017年10月1日前满足申领条件的用户都申领了一颗胡杨，其他的能量全部用来申领沙柳。
统计10月1日累计申领沙柳排名前10的用户信息，以及比后一名多领了几颗沙柳。
样式：

user_id    plant_count    less_count
u_002      1000              100
u_003      900               200
u_010      700               ...
  1. 第一步肯定是获取用户到10/1之前积累的碳排放量
select 
    user_id,
    sum(low_carbon)
from
    user_low_carbon
where
    date_format(regexp_replace(data_dt,'/','-'),'yyyy-mm-dd') <= '2017/10/01'
group by
    user_id
;
  1. 第二步是获得胡桃对应的碳排放量
select low_carbon from plant_carbon where plant_id = 'p004';
  1. 第三步是沙柳的
select low_carbon from plant_carbon where plant_id = 'p002';
  1. 把这三张表联合起来做计算
select
    user_id,
    floor((sum_low_carbon-t2.low_carbon)/t3.low_carbon) plant_count
from
    (select 
        user_id,
        sum(low_carbon) sum_low_carbon
    from
        user_low_carbon
    where
        date_format(regexp_replace(data_dt,'/','-'),'yyyy-mm-dd') <= '2017/10/01'
    group by
        user_id) t1,
    (select low_carbon from plant_carbon where plant_id = 'p004') t2,
    (select low_carbon from plant_carbon where plant_id = 'p002') t3
;

5.排序，并把下一行数据放在当前行，然后相减

select
    user_id,
    plant_count,
    (plant_count-lead(t4.plant_count,1,'9999-99-99') over(order by plant_count desc)) lead_plant_count
from 
    (select
        user_id,
        floor((sum_low_carbon-t2.low_carbon)/t3.low_carbon) plant_count
    from
        (select 
            user_id,
            sum(low_carbon) sum_low_carbon
        from
            user_low_carbon
        where
            date_format(regexp_replace(data_dt,'/','-'),'yyyy-mm-dd') <= '2017/10/01'
        group by
            user_id) t1,
        (select low_carbon from plant_carbon where plant_id = 'p004') t2,
        (select low_carbon from plant_carbon where plant_id = 'p002') t3)t4
limit 10
;

输出结果
u_007	66	3
u_013	63	10
u_008	53	7
u_005	46	1
u_010	45	1
u_014	44	5
u_011	39	2
u_009	37	5
u_006	32	9
u_002	23	1

第二题

查询user_local_carbon表中每日流水记录，条件为：
用户在2017年内，连续三天或以上的天数内，每天减少碳排放量都超过100g的低碳流水。

user_id	data_dt	low_carbon
u_002	2017

1.提取2017年单日积碳超过100g的记录

select
    user_id,
    data_dt,
    row_number() over ( PARTITION BY user_id ORDER BY data_dt) rn 
from
    user_low_carbon
where
    substring(data_dt,1,4) = '2017'
group by
    user_id,data_dt
having
    sum(low_carbon) >= 100
;   

输出结果:
u_001	2017/1/2	1
u_001	2017/1/6	2
u_002	2017/1/2	1
u_002	2017/1/3	2
u_002	2017/1/4	3
u_002	2017/1/5	4
u_003	2017/1/2	1
u_003	2017/1/3	2
u_003	2017/1/5	3
u_003	2017/1/7	4
u_004	2017/1/1	1
u_004	2017/1/3	2
u_004	2017/1/6	3
u_004	2017/1/7	4
u_005	2017/1/2	1
u_005	2017/1/3	2
u_005	2017/1/4	3
u_005	2017/1/6	4
u_005	2017/1/7	5
u_006	2017/1/2	1
u_006	2017/1/3	2
u_006	2017/1/7	3
u_007	2017/1/1	1
u_007	2017/1/2	2
u_007	2017/1/4	3
u_007	2017/1/6	4
u_007	2017/1/7	5
u_008	2017/1/1	1
u_008	2017/1/2	2
u_008	2017/1/4	3
u_008	2017/1/5	4
u_008	2017/1/6	5
u_008	2017/1/7	6
u_009	2017/1/2	1
u_009	2017/1/3	2
u_009	2017/1/4	3
u_009	2017/1/7	4
u_010	2017/1/2	1
u_010	2017/1/4	2
u_010	2017/1/5	3
u_010	2017/1/6	4
u_010	2017/1/7	5
u_011	2017/1/1	1
u_011	2017/1/2	2
u_011	2017/1/3	3
u_011	2017/1/4	4
u_011	2017/1/5	5
u_011	2017/1/6	6
u_011	2017/1/7	7
u_012	2017/1/2	1
u_013	2017/1/2	1
u_013	2017/1/3	2
u_013	2017/1/4	3
u_013	2017/1/5	4
u_014	2017/1/1	1
u_014	2017/1/2	2
u_014	2017/1/5	3
u_014	2017/1/6	4
u_014	2017/1/7	5
u_015	2017/1/7	1

然后只需要判断他是否连续三天活跃。
可以看大数据SQL经典面试题系列(1) - 连续3天登录用户

SELECT
    user_id,
    count(1) cnt 
FROM
    (select
        user_id,
        data_dt,
        row_number() over ( PARTITION BY user_id ORDER BY data_dt) rn 
    from
        user_low_carbon
    where
        substring(data_dt,1,4) = '2017'
    group by
        user_id,data_dt
    having
        sum(low_carbon) >= 100
    )t 
GROUP BY
    user_id,
    date_sub(date_format(regexp_replace(data_dt,'/','-'),'yyyy-mm-dd'),rn) 
HAVING
    count( 1 ) >= 3
ORDER BY
    user_id
;

u_002	4
u_005	3
u_008	4
u_009	3
u_010	4
u_011	7
u_013	4
u_014	3
寒 暄
关注
  • 2
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
专栏目录
Hive SQL 练习
bangqin's blog
08-03 1060
hive 数据分析练习题
蚂蚁金服SQL笔试题解答，同时遇到hive使用spark执行时不兼容问题。
A_static的博客
06-29 730
蚂蚁金服SQL笔试题解答，同时遇到hive使用spark执行时不兼容问题。题目：用SQL计算：计算用户连续完成订单间隔最大的天数和最小的天数。 一、按照题目需求解答（只需要找出用户的最大和最小连续区间）：代码如下： 执行结果： 二、思维扩展 需求：找出每个用户的最大和最小连续区间，并且求出最大和最小的持续天数。 分析：注意点，每个用户的...
大数据开发SQL面试题50题（含答案）
最新发布
thenowaiting的专栏
06-22 1016
本文整理了互联网大厂数据开发、数据分析、数仓等数据相关岗位面试过程中经常出现的SQL面试题，并给出了参考答案。涉及了炸裂函数、开窗函数、聚合函数开窗、在线直播人数等 以及这两年各大厂面疯了各种连续问题。
蚂蚁金服试题hive/sql/hql
JavaBigData的博客
11-29 490
d 原理如图 SELECT username,time,SUM(carbon_g) c2,ROW_NUMBER() OVER(PARTITION by username order by time ) rn FROM test191128.user_low_carbon GROUP by username,time HAVING c2 >=100 //t1 SEL...
2022年SQL经典面试题总结（带解析）
热门推荐
hahazz233的博客
06-27 1万+
一、选择题（1）基础题1、要求删除商品表中价格大于3000的商品，下列SQL语句正确的是（）A、DELETE FROM 商品 WHERE 价格>3000B、DELETE * FROM 商品 WHERE 价格>3000C、DELETE FROM 商品D、UPDATE 商品 SET * =NULL WHERE 价格>3000正确答案: A答案解析：这道题考察的是delete语法DELETE FROM table_nameWHERE some_column=some_value;2、在book表中，将工具书类型（
几道sql面试题
MDZ_1122333322的博客
07-31 236
注意：其中的交叉日期，比如vivo品牌，第一次活动时间为2021-06-05到 2021-06-15，第二次活动时间为 2021-06-09到 2021-06-21其中 9号到 15号为重复天数，只统计一次，即 vivo总打折天数为 2021-06-05到 2021-06-21共计 17天。将一条数据拆分成两条(id,dt,p)，并且对数据进行标记：开播为1，关播为-1，1表示有主播开播在线，-1表示有主播关播离线，其中dt为开播时间或者关播时间。需要用到开窗函数：sum() over(…
2024年最新，Hive SQL经典面试题，大数据SQL经典面试题
04-28
2024年最新，Hive SQL经典面试题，大数据SQL经典面试题. ## 1：连续登录n天的用户 ## 2：留存问题 ## 3：Top N问题 ## 4：Explode问题 ## 5：行转列
大数据SQL经典面试题系列() 连续天登录用户.docx
11-12
大数据SQL经典面试题系列() 连续天登录用户.docx
大数据技术sql面试题
04-23
"大数据技术sql面试题" 本文档提供了三道大数据技术SQL面试题的解析，涵盖了数据的累积访问次数、店铺UV和PV统计、访客访问次数排名等方面的知识点。 知识点 1：累积访问次数 在第一题中，我们需要使用 SQL 统计...
大数据技术高频面试题
10-03
在大数据领域，面试通常会涉及到多个关键的技术框架和概念，包括Hadoop、Spark、HBase和Hive等。这些技术是构建大规模数据处理和分析系统的核心组件。以下将详细介绍这些技术的一些重要知识点。 **Hadoop** 是一个...
大数据SQL面试题02.zip
03-15
"大数据SQL面试题02.zip"很可能是包含了一系列针对大数据环境下的SQL问题和解答，旨在帮助求职者准备面试。以下是根据这个主题可能涵盖的一些关键知识点： 1. **大数据SQL基础知识**：面试可能会从基础概念开始，如...
Hive SQL大厂面试题必考大全
CSDN 精品推荐
07-17 447
专栏内容涵盖了基于Hive SQL的各类题目，旨在通过解析经典问题和案例，帮助读者深入理解Hive SQL的原理和应用。每篇文章都将附带完整的题目代码和详细的解题思路，理论与实践相结合，帮助你更好地掌握Hive SQL的知识。
【大数据】关于sql面试题10道,助你升职加薪【精品,建议收藏】
yang_z_1的博客
06-06 2257
【大数据】关于sql面试题10道,助你升职加薪【精品,建议收藏】 文章目录【大数据】关于sql面试题10道,助你升职加薪【精品,建议收藏】前言一、你接触的分区有哪些，他们都有哪些优点1.1.分区表有哪些优点？1.2.分区表有哪些类型？二.索引的正确使用三 sql语句优化四.分析函数4.1常用的分析函数如下所列:五、数据库中食用游标的优缺点六、拉链表怎么实现的？（开链 关链）七.视图7.1视图和表的区别？7.2.视图有哪些？视图的优点有哪些？八 存储过程8.1存储过程创建语法：8.2 存储过程使用：九、存储
33个必须知道的数据分析师SQL面试问题和答案
qq_40523298的博客
10-17 5446
33个必须知道的数据分析师SQL面试问题和答案 SQL是一种强大的编程语言，被广泛用于管理和分析数据。如果你想成为一名数据分析师，或者是正在寻找新工作的现有分析师，准备好回答SQL面试问题是很重要的。 下面是一些最常见的SQL面试问题，在我们的数据分析师SQL面试中，你可能会被问到这些问题，以及如何回答的建议。 什么是SQL，其主要特点是什么？ SQL，即结构化查询语言，是一种用于访问和操作数据库的标准编程语言。SQL以其简单而强大的语法而闻名，这使得它可以很容易地以各种方式查询数据。SQL还支持广泛的数
面试 SQL整理 常见的SQL面试题：大厂经典60题（一）
qq_48811377的博客
04-28 777
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询语言。作用：用于存取数据、查询、更新和管理关系数据库系统。MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
大数据面试之Hive SQL经典面试题
涛声依旧, 万古流名
04-26 1795
请注意，这种方法并没有真正地将行转换为独立的列，而是将多个行的值合并到了一个字符串中。在Hive中，由于schema是静态的，将任意数量的行转换为固定数量的列是不直接支持的。如果知道商品ID的最大数量，并且这个数量是固定的，可以使用条件聚合和。然后，在外部查询中，筛选出序列号小于等于 N 的记录，即销售额最高的前 N 个记录。希望将每个用户的购买记录从行格式转换为列格式，以展示每个用户购买的每个商品。列存储每个订单所购买的商品数组，数组中每个元素都是一个元组，包含商品ID和数量。
数据分析｜SQL面试题集锦
hellojoy的博客
01-23 2169
https://zhuanlan.zhihu.com/p/136619982 写在前面 SQL可谓是数据分析必备技能之一了，面试中也是高频出现，初学SQL可以阅读《SQL必知必会》 全书通俗易懂，看完之后可以到牛客网和LeetCode刷一些SQL题巩固巩固。 数据库SQL实战_牛客网​www.nowcoder.com 题库 - 力扣 (LeetCode)​leetcode-cn.com 不过面试中的SQL大多都是结合部门业务，这里将我自己经历过的和之前看到的一些面试题收集整理过来..
sql练习一(蚂蚁森林)
qq_42506914的博客
03-17 1581
背景说明： 以下表记录了用户每天的蚂蚁森林低碳生活领取的记录流水。 table_name：user_low_carbon seq（key）user_iddata_dtlow_carbon 流水号用户日期减少碳排放（g） xxxxx01u_0012017/1/110 xxxxx02u_0012017/1/2150 xxx...
Java面试题：PL-SQL基础与实战
“玩转”Java系列，涵盖PL-SQL经典试题及基本语法，提供Java和Android相关的学习资料。 在Java面试中，PL-SQL也是考察的一个重要领域，特别是对于那些需要处理数据库操作的岗位。PL-SQL是Oracle数据库使用的编程...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

寒 暄

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值