杭州营销型网站制作,山东建设管理局网站,建设局考试通知文件网站,Wordpress模板服务在数据分析和数据库操作中#xff0c;SQL 查询是至关重要的一环。本文将通过分析四道典型的 SQL 题目#xff0c;深入探讨如何从复杂的业务需求中构建准确高效的 SQL 查询。 一、删除学生表冗余信息 需求解读 给定一个学生表#xff0c;其中包含自动编号、学号、姓名、课程编… 在数据分析和数据库操作中SQL 查询是至关重要的一环。本文将通过分析四道典型的 SQL 题目深入探讨如何从复杂的业务需求中构建准确高效的 SQL 查询。 一、删除学生表冗余信息 需求解读 给定一个学生表其中包含自动编号、学号、姓名、课程编号、课程名称和分数等字段。要求删除除了自动编号不同其他信息学号、姓名、课程编号、课程名称、分数都相同的冗余记录。 学生表 自动编号 学号 姓名 课程编号 课程名称 分数 1 2005001 张三 0001 数学 69 2 2005002 李四 0001 数学 89 3 2005001 张三 0001 数学 69 代码片段
建表
CREATE TABLE students (id INT, -- 自动编号student_id STRING, -- 学号student_name STRING, -- 姓名course_id STRING, -- 课程编号course_name STRING, -- 课程名称score INT -- 分数
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY \t
STORED AS TEXTFILE;导入数据
INSERT INTO students
VALUES (1, 2005001, 张三, 0001, 数学, 69),(2, 2005002, 李四, 0001, 数学, 89),(3, 2005001, 张三, 0001, 数学, 69); SQL如下
select *
from students
where id not in (select min(id)from studentsgroup by student_id, student_name, course_id, course_name, score);
如果查询结果符合预期再将SELECT *替换为DELETE来执行实际的删除操作。 二、寻找 GDP 增速超过罗湖区的区 1表名macro_index_data 2字段名数据期年月occur_period)、地区代码area_code、指标代码 index_code)、指标类型(增速、总量(index_type)、指标值(index_value)、数据更新时间 (update_time)。说明罗湖区的区划代码为440305000000、GDP指标代码为gmjj_jjzl_01、指标类型的枚举值分别是增速(TB)、总量(JDZ) 3请写出2020年4个季度中GDP的增速都超过罗湖区同期的区有哪些 需求解读 本题要求找出 2020 年四个季度中 GDP 增速都超过罗湖区同期的区。涉及到从macro_index_data表中筛选出符合条件的数据。 代码剖析 查询语句通过两个子查询分别计算罗湖区和其他区的 GDP 增速数据。在每个子查询中使用sum函数与case when语句根据月份范围计算每个季度的 GDP 增速指标值。然后通过join条件将其他区与罗湖区的数据进行比较筛选出四个季度增速都大于罗湖区的区。例如 代码片段
建表
CREATE TABLE macro_index_data (occur_period string, -- 数据期年月area_code string, -- 地区代码index_code string, -- 指标代码index_type string, -- 指标类型index_value double, -- 指标值这里使用 double可根据实际调整update_time timestamp -- 数据更新时间
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY \t -- 假设字段分隔符为制表符可根据实际修改
STORED AS TEXTFILE; 导入数据 数据暂无可由AI生成放入 HDFS 中例如在/user/hive/data/macro_index_data.txt路径下使用以下命令
LOAD DATA INPATH /user/hive/data/macro_index_data.txt INTO TABLE macro_index_data; SQL如下
select t2.area_code
from (--先求出罗湖区2020年四季度的GDP指标select area_code,sum(case when month(occur_period) between 1 and 3 thenindex_value else 0 end) one, sum(case when month(occur_period) between 4 and 6 thenindex_value else 0 end) two, sum(case when month(occur_period) between 7 and 9 thenindex_value else 0 end) three, sum(case when month(occur_period) between 10 and 12 thenindex_value else 0 end) four from macro_index_datawhere area_code 440305000000 and index_code gmjj_jjzl_01 and index_type TB and year(occur_period) 2020 group by area_code
) t1
join (--再求出其它区2020年四季度的GDP指标select area_code,sum(case when month(occur_period) between 1 and 3 thenindex_value else 0 end) one, sum(case when month(occur_period) between 4 and 6 thenindex_value else 0 end) two, sum(case when month(occur_period) between 7 and 9 thenindex_value else 0 end) three, sum(case when month(occur_period) between 10 and 12 thenindex_value else 0 end) four from macro_index_datawhere area_code 440305000000 and index_code gmjj_jjzl_01 and index_type TB and year(occur_period) 2020 group by area_code
) t2 on t2.one t1.oneand t2.two t1.twoand t2.three t1.threeand t2.four t1.four;三、核酸检测人数相关统计 1表1t_syrkxxb (实有人口信息表)字段名姓名(xm)、证件号码(zjhm)、证件类型(zjlx)、出 生日期(csrq、居住地址(jzdz)、所在街道(jdmc)、所在社区(sqmc)、联系电话(lxdh)、更新时间(gxsj) 2表2t_hsjcqkb 核酸检测情况表字段名姓名(xm)、证件号码(zjhm)、证件类型(zjlx)、 检测机构(jcjgou)、检测时间(jcsj)、报告时间(bgsj)、检测结果(jcjguo) 3说明实有人口信息表中因网格统计的时候一人有多处房产或者在多地有居住过的会有多 条数据仅取最新一条记录核酸检测情况表中同一人在同一天内不同检测机构检测多次的算多次检 测同一人在同一天内同一检测机构检测多次的只算最后一次 4请写出各街道已参与核酸检测总人数、今日新增人数、已检测人数占总人口数的比例; 需求解读 需要计算各街道已参与核酸检测总人数、今日新增人数以及已检测人数占总人口数的比例。涉及到t_syrkxxb实有人口信息表和t_hsjcqkb核酸检测情况表两张表。 代码剖析
通过三个子查询和连接操作来实现。首先从实有人口信息表计算各街道总人口数。对于今日新增人数从核酸检测表中筛选出今日2021 - 07 - 29检测的用户通过左连接排除之前检测过的用户后按街道分组计数。已参与核酸检测总人数的计算先从实有人口表中获取每个用户的最新记录再与检测过的用户连接并按街道分组计数。最后将三个结果集连接并选择相应字段。 代码片段
建表
创建实有人口信息表t_syrkxxb
CREATE TABLE t_syrkxxb (xm STRING,zjhm STRING,zjlx STRING,csrq STRING,jzdz STRING,jdmc STRING,sqmc STRING,lxdh STRING,gxsj TIMESTAMP
); 创建核酸检测情况表t_hsjcqkb
CREATE TABLE t_hsjcqkb (xm STRING,zjhm STRING,zjlx STRING,jcjgou STRING,jcsj TIMESTAMP,bgsj TIMESTAMP,jcjguo STRING
); 导入数据 数据暂无可由AI生成放入 HDFS 中例如在/user/hive/data/t_hsjcqkb.txt路径下使用以下命令
LOAD DATA INPATH /user/hive/data/t_hsjcqkb.txt INTO TABLE t_hsjcqkb;LOAD DATA INPATH /user/hive/data/t_syrkxxb.txt INTO TABLE t_syrkxxb; SQL如下
select t6.jdmc 所在街道,t8.jiance_person_num 已参与核酸检测总人数,t7.add_num 今日新增人数,t6.person_num 已检测人数占总人口数的比例
from (--各街道总人口数select jdmc,count(*) person_numfrom t_syrkxxbgroup by jdmc
) t6
join (--各街道今日新增人数以前没有检测过的用户select t4.jdmc,count(*) add_numfrom (select zjhm,zjlx,jdmcfrom t_hsjcqkbwhere date_format(jcsj,yyyy-MM-dd) 2021-07-29) t4left join (--求出检测过的用户select zjhm,zjlxfrom t_hsjcqkbgroup by zjhm,zjlx) t5 on t4.zjhm t5.zjhmand t4.zjlx t5.zjlxwhere t5.zjhm is nullgroup by jdmc
) t7 on t6.jdmc t7.jdmc
join (--求出各街道已参与核酸检测总人数select t1.jdmc,count(*) jiance_person_numfrom (--先将实有人口表按更新时间排序后过滤出最新的记录select t1.*from (select *,row_number() over(partition by zjhm order by gxsj desc)rank_gxsjfrom t_syrkxxbgroup by zjhm ) t1where rank_gxsj 1) t2join (--求出检测过的用户select zjhm,zjlxfrom t_hsjcqkbgroup by zjhm,zjlx) t3 on t2.zjhm t3.zjhm and t2.zjlx t3.zjlxgroup by t1.jdmc
) t8 t6.jdmc t8.jdmc; 四、查询用户连续三天登录数据 需求解读 给定用户登录记录表要查询出用户连续三天登录的所有数据记录。 代码剖析 查询过程分为四步。首先使用lead()函数求出每行日期后面第三行的日期later3dt同时用date_add()函数求出真正第三天的日期true3dt。第二步通过if函数判断两者是否相等来标记是否连续登录。第三步筛选出标记为 1 的记录即连续登录三天的起始日期。第四步通过与一个包含 0、1、2 的数组进行笛卡尔积操作和date_add()函数计算出连续三天的日期。核心代码如下 代码片段
建表
create table user_log(id int,dt string
)
row format delimited
fields terminated by \t; 导入数据
INSERT INTO user_log
VALUES
(1, 2024-04-25),
(1, 2024-04-26),
(1, 2024-04-27),
(1, 2024-04-28),
(1, 2024-04-30),
(1, 2024-05-01),
(1, 2024-05-02),
(1, 2024-05-04),
(1, 2024-05-05),
(2, 2024-04-25),
(2, 2024-04-28),
(2, 2024-05-02),
(2, 2024-05-03),
(2, 2024-05-04); SQL如下
第一步
求解每行日期后面第三行的日期 lead()和 真正第三天的日期
select*,lead(dt,2) over(partition by id order by dt) later3dt,date_add(dt,2) true3dt
from user_log;第二步
判断是否连续登录三天
with t as (select*,lead(dt,2) over(partition by id order by dt) later3dt,date_add(dt,2) true3dtfrom user_log
) select *,if(later3dttrue3dt,1,0) num from t; 第三步
筛选出连续登录三天的每个起始日期
with t as (select*,lead(dt,2) over(partition by id order by dt) later3dt,date_add(dt,2) true3dtfrom user_log
) ,t1 as (select *,if(later3dttrue3dt,1,0) num from t
)select * from t1 where num1; 第四步
表合并求最终结果和一个三行的表进行合并笛卡尔积
with t as (select*,lead(dt,2) over(partition by id order by dt) later3dt,date_add(dt,2) true3dtfrom user_log
) ,t1 as (select *,if(later3dttrue3dt,1,0) num from t
),t2 as (select * from t1 where num1
) select id, date_add(dt,d.list) dt2 from t2,(select explode(array(0,1,2)) list) d;按照需求修改代码
with t as (select*,lead(dt,2) over(partition by id order by dt) later3dt,date_add(dt,2) true3dtfrom user_log
) ,t1 as (select *,if(later3dttrue3dt,1,0) num from t
),t2 as (select * from t1 where num1
) select id, date_add(dt,d.list) dt2 from t2,(select explode(array(0,1,2)) list) d;结果 四、总结 通过对这四道 SQL 题目的分析我们可以看到在处理实际业务需求中的数据查询时需要深入理解业务逻辑构建合适的查询语句。对代码进行优化是提高查询效率和代码质量的关键这包括合理使用数据库函数、优化连接条件和处理日期数据等方面。希望这些分析和建议能帮助大家在 SQL 查询中更加得心应手写出高效准确的代码。
