????????在數(shù)據(jù)分析和數(shù)據(jù)庫操作中，SQL 查詢是至關(guān)重要的一環(huán)。本文將通過分析四道典型的 SQL 題目，深入探討如何從復(fù)雜的業(yè)務(wù)需求中構(gòu)建準(zhǔn)確高效的 SQL 查詢。

一、刪除學(xué)生表冗余信息

需求解讀

????????給定一個(gè)學(xué)生表，其中包含自動(dòng)編號(hào)、學(xué)號(hào)、姓名、課程編號(hào)、課程名稱和分?jǐn)?shù)等字段。要求刪除除了自動(dòng)編號(hào)不同，其他信息（學(xué)號(hào)、姓名、課程編號(hào)、課程名稱、分?jǐn)?shù)）都相同的冗余記錄。

學(xué)生表

自動(dòng)編號(hào) ? 學(xué)號(hào) ?姓名 課程編號(hào) 課程名稱 分?jǐn)?shù)
1 ? ? 2005001 張三 ? 0001 ? 數(shù)學(xué) ? 69
2 ? ? 2005002 李四 ? 0001 ? 數(shù)學(xué) ? 89
3 ? ? 2005001 張三 ? 0001 ? 數(shù)學(xué) ? 69

代碼片段

建表

CREATE TABLE students (id INT, -- 自動(dòng)編號(hào)student_id STRING, -- 學(xué)號(hào)student_name STRING, -- 姓名course_id STRING, -- 課程編號(hào)course_name STRING, -- 課程名稱score INT -- 分?jǐn)?shù)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

導(dǎo)入數(shù)據(jù)

INSERT INTO students
VALUES ('1', '2005001', '張三', '0001', '數(shù)學(xué)', '69'),('2', '2005002', '李四', '0001', '數(shù)學(xué)', '89'),('3', '2005001', '張三', '0001', '數(shù)學(xué)', '69');

SQL如下

select *
from students
where id not in (select min(id)from studentsgroup by student_id, student_name, course_id, course_name, score);

如果查詢結(jié)果符合預(yù)期，再將SELECT *替換為DELETE來執(zhí)行實(shí)際的刪除操作。

二、尋找 GDP 增速超過羅湖區(qū)的區(qū)

1）表名：macro_index_data

2）字段名：數(shù)據(jù)期（年月）（occur_period)、地區(qū)代碼（area_code）、指標(biāo)代碼

（index_code)、指標(biāo)類型(增速、總量）(index_type)、指標(biāo)值(index_value)、數(shù)據(jù)更新時(shí)間

(update_time)。說明：羅湖區(qū)的區(qū)劃代碼為440305000000、GDP指標(biāo)代碼為gmjj_jjzl_01、指標(biāo)類型的枚舉值分別是增速(TB)、總量(JDZ)

3）請(qǐng)寫出，2020年4個(gè)季度中GDP的增速都超過羅湖區(qū)同期的區(qū)有哪些

需求解讀

????????本題要求找出 2020 年四個(gè)季度中 GDP 增速都超過羅湖區(qū)同期的區(qū)。涉及到從macro_index_data表中篩選出符合條件的數(shù)據(jù)。

代碼剖析

????????查詢語句通過兩個(gè)子查詢分別計(jì)算羅湖區(qū)和其他區(qū)的 GDP 增速數(shù)據(jù)。在每個(gè)子查詢中，使用sum函數(shù)與case when語句根據(jù)月份范圍計(jì)算每個(gè)季度的 GDP 增速指標(biāo)值。然后通過join條件，將其他區(qū)與羅湖區(qū)的數(shù)據(jù)進(jìn)行比較，篩選出四個(gè)季度增速都大于羅湖區(qū)的區(qū)。例如：

代碼片段

建表

CREATE TABLE macro_index_data (occur_period string, -- 數(shù)據(jù)期（年月）area_code string, -- 地區(qū)代碼index_code string, -- 指標(biāo)代碼index_type string, -- 指標(biāo)類型index_value double, -- 指標(biāo)值，這里使用 double，可根據(jù)實(shí)際調(diào)整update_time timestamp -- 數(shù)據(jù)更新時(shí)間
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t' -- 假設(shè)字段分隔符為制表符，可根據(jù)實(shí)際修改
STORED AS TEXTFILE;

?

導(dǎo)入數(shù)據(jù)?

????????數(shù)據(jù)暫無可由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 (--先求出羅湖區(qū)2020年四季度的GDP指標(biāo)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 (--再求出其它區(qū)2020年四季度的GDP指標(biāo)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;

三、核酸檢測(cè)人數(shù)相關(guān)統(tǒng)計(jì)

1）表1：t_syrkxxb (實(shí)有人口信息表)，字段名：姓名(xm)、證件號(hào)碼(zjhm)、證件類型(zjlx)、出

生日期(csrq）、居住地址(jzdz)、所在街道(jdmc)、所在社區(qū)(sqmc)、聯(lián)系電話(lxdh)、更新時(shí)間(gxsj)

2）表2：t_hsjcqkb （核酸檢測(cè)情況表），字段名：姓名(xm)、證件號(hào)碼(zjhm)、證件類型(zjlx)、

檢測(cè)機(jī)構(gòu)(jcjgou)、檢測(cè)時(shí)間(jcsj)、報(bào)告時(shí)間(bgsj)、檢測(cè)結(jié)果(jcjguo)

3）說明：實(shí)有人口信息表中，因網(wǎng)格統(tǒng)計(jì)的時(shí)候一人有多處房產(chǎn)或者在多地有居住過的，會(huì)有多

條數(shù)據(jù)，僅取最新一條記錄；核酸檢測(cè)情況表中，同一人在同一天內(nèi)不同檢測(cè)機(jī)構(gòu)檢測(cè)多次的算多次檢

測(cè)，同一人在同一天內(nèi)同一檢測(cè)機(jī)構(gòu)檢測(cè)多次的只算最后一次

4）請(qǐng)寫出各街道已參與核酸檢測(cè)總?cè)藬?shù)、今日新增人數(shù)、已檢測(cè)人數(shù)占總?cè)丝跀?shù)的比例;

需求解讀

????????需要計(jì)算各街道已參與核酸檢測(cè)總?cè)藬?shù)、今日新增人數(shù)以及已檢測(cè)人數(shù)占總?cè)丝跀?shù)的比例。涉及到t_syrkxxb（實(shí)有人口信息表）和t_hsjcqkb（核酸檢測(cè)情況表）兩張表。

代碼剖析

通過三個(gè)子查詢和連接操作來實(shí)現(xiàn)。首先從實(shí)有人口信息表計(jì)算各街道總?cè)丝跀?shù)。對(duì)于今日新增人數(shù)，從核酸檢測(cè)表中篩選出今日（2021 - 07 - 29）檢測(cè)的用戶，通過左連接排除之前檢測(cè)過的用戶后按街道分組計(jì)數(shù)。已參與核酸檢測(cè)總?cè)藬?shù)的計(jì)算，先從實(shí)有人口表中獲取每個(gè)用戶的最新記錄，再與檢測(cè)過的用戶連接并按街道分組計(jì)數(shù)。最后將三個(gè)結(jié)果集連接并選擇相應(yīng)字段。

代碼片段

建表

創(chuàng)建實(shí)有人口信息表t_syrkxxb

CREATE TABLE t_syrkxxb (xm STRING,zjhm STRING,zjlx STRING,csrq STRING,jzdz STRING,jdmc STRING,sqmc STRING,lxdh STRING,gxsj TIMESTAMP
);

?

創(chuàng)建核酸檢測(cè)情況表t_hsjcqkb

CREATE TABLE t_hsjcqkb (xm STRING,zjhm STRING,zjlx STRING,jcjgou STRING,jcsj TIMESTAMP,bgsj TIMESTAMP,jcjguo STRING 
);

?導(dǎo)入數(shù)據(jù)?

????????數(shù)據(jù)暫無可由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 `已參與核酸檢測(cè)總?cè)藬?shù)`,t7.add_num `今日新增人數(shù)`,t6.person_num `已檢測(cè)人數(shù)占總?cè)丝跀?shù)的比例`
from (--各街道總?cè)丝跀?shù)select jdmc,count(*) `person_num`from t_syrkxxbgroup by jdmc
) t6
join (--各街道今日新增人數(shù)：以前沒有檢測(cè)過的用戶select t4.jdmc,count(*) `add_num`from (select zjhm,zjlx,jdmcfrom t_hsjcqkbwhere date_format('jcsj','yyyy-MM-dd') = '2021-07-29') t4left join (--求出檢測(cè)過的用戶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 (--求出各街道已參與核酸檢測(cè)總?cè)藬?shù)select t1.jdmc,count(*) `jiance_person_num`from (--先將實(shí)有人口表按更新時(shí)間排序后過濾出最新的記錄select t1.*from (select *,row_number() over(partition by zjhm order by gxsj desc)`rank_gxsj`from t_syrkxxbgroup by zjhm ) t1where rank_gxsj = '1') t2join (--求出檢測(cè)過的用戶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;  

四、查詢用戶連續(xù)三天登錄數(shù)據(jù)

需求解讀

????????給定用戶登錄記錄表，要查詢出用戶連續(xù)三天登錄的所有數(shù)據(jù)記錄。

代碼剖析

????????查詢過程分為四步。首先使用lead()函數(shù)求出每行日期后面第三行的日期later3dt，同時(shí)用date_add()函數(shù)求出真正第三天的日期true3dt。第二步通過if函數(shù)判斷兩者是否相等來標(biāo)記是否連續(xù)登錄。第三步篩選出標(biāo)記為 1 的記錄，即連續(xù)登錄三天的起始日期。第四步通過與一個(gè)包含 0、1、2 的數(shù)組進(jìn)行笛卡爾積操作和date_add()函數(shù)計(jì)算出連續(xù)三天的日期。核心代碼如下：

?代碼片段

建表

create table user_log(id int,dt string
)
row format delimited
fields terminated by '\t';

導(dǎo)入數(shù)據(jù)

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;

?第二步

判斷是否連續(xù)登錄三天

with t as (select*,lead(dt,2) over(partition by id order by dt) later3dt,date_add(dt,2) true3dtfrom user_log
) select *,if(later3dt==true3dt,1,0) num from t;

?第三步

篩選出連續(xù)登錄三天的每個(gè)起始日期

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(later3dt==true3dt,1,0) num from t
)select * from t1 where num=1;

?第四步

表合并求最終結(jié)果（和一個(gè)三行的表進(jìn)行合并）（笛卡爾積）

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(later3dt==true3dt,1,0) num from t
),t2 as (select * from t1 where num=1
) 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(later3dt==true3dt,1,0) num from t
),t2 as (select * from t1 where num=1
) select id, date_add(dt,d.list) dt2  from t2,(select explode(array(0,1,2)) list) d;

結(jié)果

四、總結(jié)

????????通過對(duì)這四道 SQL 題目的分析，我們可以看到在處理實(shí)際業(yè)務(wù)需求中的數(shù)據(jù)查詢時(shí)，需要深入理解業(yè)務(wù)邏輯，構(gòu)建合適的查詢語句。對(duì)代碼進(jìn)行優(yōu)化是提高查詢效率和代碼質(zhì)量的關(guān)鍵，這包括合理使用數(shù)據(jù)庫函數(shù)、優(yōu)化連接條件和處理日期數(shù)據(jù)等方面。希望這些分析和建議能幫助大家在 SQL 查詢中更加得心應(yīng)手，寫出高效準(zhǔn)確的代碼。