四个在工作后才知道的SQL密技

姬霓太美 發表於 2023-11-28 00:00:00

<img title="四个在工作后才知道的SQL密技" alt="四个在工作后才知道的SQL密技" border="0" height="auto" src="https://zhuji.jb51.net/uploads/img/202305/4c9a76feb90c801122cc88b12c85c6a5.jpg" width="auto">

本文会分享四个在面试和工作中常用的几个使用技巧，具体包括：
<ul>
<li>
日期与期间的高级使用</li>
<li>
临时表与Common Table Expression (WITH)</li>
<li>
Aggregation 与CASE WHEN的结合使用</li>
<li>
Window Function的其他用途</li>
</ul>

数仓?不就是写写SQL吗…
<center>
<img title="四个在工作后才知道的SQL密技" alt="四个在工作后才知道的SQL密技" src="https://zhuji.jb51.net/uploads/img/202305/0985560a240b37f0706d258da8f28a13.jpg">
</center>

日期与时间段的筛选在工作中是经常被用到的，因为在拉取报表、仪表板和各种分析时，周、月、季度、年度的表现往往是分析需要考量的重点。
<h3>
时间区段的提取：Extract</h3>
<ul>
<li>
语法</li>
</ul>
<ol class="dp-sql">
<li class="alt">
-- field可以是day、hour、minute, month, quarter等等 
</li>
<li>
-- source可以是date、timestamp类型 
</li>
<li class="alt">
extract(field FROM source) 
</li>
</ol>
<ul>
<li>
使用</li>
</ul>
<ol class="dp-sql">
<li class="alt">
SELECT extract(year FROM '2020-08-05 09:30:08'); -- 结果为 2020 
</li>
<li>
SELECT extract(quarter FROM '2020-08-05 09:30:08'); -- 结果为 3 
</li>
<li class="alt">
SELECT extract(month FROM '2020-08-05 09:30:08'); -- 结果为 8 
</li>
<li>
SELECT extract(week FROM '2020-08-05 09:30:08'); -- 结果为 31,一年中的第几周 
</li>
<li class="alt">
SELECT extract(day FROM '2020-08-05 09:30:08'); -- 结果为 5 
</li>
<li>
SELECT extract(hour FROM '2020-08-05 09:30:08'); -- 结果为 9 
</li>
<li class="alt">
SELECT extract(minute FROM '2020-08-05 09:30:08'); -- 结果为 30 
</li>
<li>
SELECT extract(second FROM '2020-08-05 09:30:08'); -- 结果为 8 
</li>
</ol>

注意：

impala支持：YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, EPOCH

Hive支持：day, dayofweek, hour, minute, month, quarter, second, week 和 year

Hive是从Hive2.2.0版本开始引入该函数
<h3>
周的提取</h3>
<ul>
<li>
语法</li>
</ul>

在按照周的区间进行统计时，需要识别出周一的日期与周日的日期，这个时候经常会用到下面的函数：
<ol class="dp-sql">
<li class="alt">
next_day(STRING start_date, STRING day_of_week) 
</li>
<li>
-- 返回当前日期对应的下一个周几对应的日期 
</li>
<li class="alt">
-- 2020-08-05为周三 
</li>
<li>
SELECT next_day('2020-08-05','MO') -- 下一个周一对应的日期：2020-08-10 
</li>
<li class="alt">
SELECT next_day('2020-08-05','TU') -- 下一个周二对应的日期：2020-08-11 
</li>
<li>
SELECT next_day('2020-08-05','WE') -- 下一个周三对应的日期：2020-08-12 
</li>
<li class="alt">
SELECT next_day('2020-08-05','TH') -- 下一个周四对应的日期：2020-08-06，即为本周四 
</li>
<li>
SELECT next_day('2020-08-05','FR') -- 下一个周五对应的日期：2020-08-07，即为本周五 
</li>
<li class="alt">
SELECT next_day('2020-08-05','SA') -- 下一个周六对应的日期：2020-08-08，即为本周六 
</li>
<li>
SELECT next_day('2020-08-05','SU') -- 下一个周日对应的日期：2020-08-09，即为本周日 
</li>
<li class="alt">
-- 星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday） 
</li>
</ol>
<ul>
<li>
使用</li>
</ul>

那么该如何获取当前日期所在周的周一对应的日期呢?只需要先获取当前日期的下周一对应的日期，然后减去7天，即可获得：
<ol class="dp-sql">
<li class="alt">
SELECT date_add(next_day('2020-08-05','MO'),-7); 
</li>
</ol>

同理，获取当前日期所在周的周日对应的日期，只需要先获取当前日期的下周一对应的日期，然后减去1天，即可获得：
<ol class="dp-sql">
<li class="alt">
select date_add(next_day('2020-08-05','MO'),-1) 
</li>
<li>
-- 2020-08-09 
</li>
</ol>
<h3>
月的提取</h3>
<ul>
<li>
语法</li>
</ul>

至于怎么将月份从单一日期提取出来呢，LAST_DAY这个函数可以将每个月中的日期变成该月的最后一天(28号，29号，30号或31号)，如下：
<ol class="dp-sql">
<li class="alt">
last_day(STRING date) 
</li>
</ol>
<ul>
<li>
使用</li>
</ul>
<ol class="dp-vb">
<li class="alt">
SELECT last_day('2020-08-05'); -- 2020-08-31 
</li>
</ol>

除了上面的方式，也可以使用date_format函数，比如：
<ol class="dp-sql">
<li class="alt">
SELECT date_format('2020-08-05','yyyy-MM'); 
</li>
<li>
-- 2020-08 
</li>
</ol>
<h3>
日期的范围</h3>

月的Window：使用add_months加上trunc()的应用
<ol class="dp-sql">
<li class="alt">
-- 返回加减月份之后对应的日期 
</li>
<li>
-- 2020-07-05 
</li>
<li class="alt">
select add_months('2020-08-05', -1) 
</li>
<li>
 
</li>
<li class="alt">
-- 返回当前日期的月初日期 
</li>
<li>
-- 2020-08-01 
</li>
<li class="alt">
select trunc("2020-08-05",'MM') 
</li>
</ol>

由上面范例可见，单纯使用add_months，减N个月的用法，可以刚好取到整数月的数据，但如果加上trunc()函数，则会从前N个月的一号开始取值。
<ol class="dp-sql">
<li class="alt">
-- 选取2020-07-05到2020-08-05所有数据 
</li>
<li>
BETWEEN add_months('2020-08-05', -1) AND '2020-08-05' 
</li>
<li class="alt">
-- 选取2020-07-01到2020-08-05之间所有数据 
</li>
<li>
BETWEEN add_months(trunc("2020-08-05",'MM'),-1) AND '2020-08-05' 
</li>
</ol>
<center>
<img title="四个在工作后才知道的SQL密技" alt="四个在工作后才知道的SQL密技" src="https://zhuji.jb51.net/uploads/img/202305/53fbf32bdaac59908305a40ebb797459.jpg">
</center>

这两种方法是日常工作中经常被使用到，对于一些比较复杂的计算任务，为了避免过多的JOIN，通常会先把一些需要提取的部分数据使用临时表或是CTE的形式在主要查询区块前进行提取。
<h3>
临时表的作法：</h3>
<ol class="dp-sql">
<li class="alt">
CREATE TEMPORARY TABLE table_1 AS 
</li>
<li>
 SELECT 
</li>
<li class="alt">
 columns 
</li>
<li>
 FROM table A; 
</li>
<li class="alt">
CREATE TEMPORARY table_2 AS 
</li>
<li>
 SELECT 
</li>
<li class="alt">
 columns 
</li>
<li>
 FROM table B; 
</li>
<li class="alt">
 
</li>
<li>
SELECT 
</li>
<li class="alt">
 table_1.columns, 
</li>
<li>
 table_2.columns, 
</li>
<li class="alt">
 c.columns 
</li>
<li>
FROM table C JOIN table_1 
</li>
<li class="alt">
 JOIN table_2; 
</li>
</ol>
<h3>
CTE的作法：</h3>

-- 注意Hive、Impala支持这种语法，低版本的MySQL不支持(高版本支持)
<ol class="dp-sql">
<li class="alt">
-- 注意Hive、Impala支持这种语法，低版本的MySQL不支持(高版本支持) 
</li>
<li>
WITH employee_by_title_count AS ( 
</li>
<li class="alt">
 SELECT 
</li>
<li>
 t.name as job_title 
</li>
<li class="alt">
 , COUNT(e.id) as amount_of_employees 
</li>
<li>
 FROM employees e 
</li>
<li class="alt">
 JOIN job_titles t on e.job_title_id = t.id 
</li>
<li>
 GROUP BY 1 
</li>
<li class="alt">
), 
</li>
<li>
salaries_by_title AS ( 
</li>
<li class="alt">
 SELECT 
</li>
<li>
 name as job_title 
</li>
<li class="alt">
 , salary 
</li>
<li>
 FROM job_titles 
</li>
<li class="alt">
) 
</li>
<li>
SELECT * 
</li>
<li class="alt">
FROM employee_by_title_count e 
</li>
<li>
 JOIN salaries_by_title s ON s.job_title = e.job_title 
</li>
</ol>

可以看到TEMP TABLE和CTE WITH的用法其实非常类似，目的都是为了让你的Query更加一目了然且优雅简洁。很多人习惯将所有的Query写在单一的区块里面，用过多的JOIN或SUBQUERY，导致最后逻辑丢失且自己也搞不清楚写到哪里，适时的使用TEMP TABLE和CTE作为辅助，绝对是很加分的。
<center>
<img title="四个在工作后才知道的SQL密技" alt="四个在工作后才知道的SQL密技" src="https://zhuji.jb51.net/uploads/img/202305/dc793e298d3a49315db1b42b5266b6b8.jpg">
</center>

将Aggregation function (SUM/COUNT/COUNT DISTINCT/MIN/MAX) 结合CASE WHEN是最强大且最有趣的使用方式。这样的使用创造出一种类似EXCEL中SUMIF/COUNTIF的效果，可以用这个方式做出很多高效的分析。
<ul>
<li>
Table Name: order</li>
<li>
Column: register_date, order_date, user_id, country, order_sales, order_id</li>
</ul>
<h3>
数据准备</h3>
<ol class="dp-sql">
<li class="alt">
CREATE TABLE order( 
</li>
<li>
 register_date string, 
</li>
<li class="alt">
 order_date string, 
</li>
<li>
 user_id string, 
</li>
<li class="alt">
 country string, 
</li>
<li>
 order_sales decimal(10,2), 
</li>
<li class="alt">
 order_id string); 
</li>
<li>
 
</li>
<li class="alt">
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-09","001",'c0',210,"o1"); 
</li>
<li>
INSERT INTO TABLE order VALUES("2020-06-08","2020-06-09","002",'c1',220,"o2"); 
</li>
<li class="alt">
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-10","003",'c2',230,"o3"); 
</li>
<li>
INSERT INTO TABLE order VALUES("2020-06-09","2020-06-10","004",'c3',200,"o4"); 
</li>
<li class="alt">
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-20","005",'c4',300,"o5"); 
</li>
<li>
INSERT INTO TABLE order VALUES("2020-06-10","2020-06-23","006",'c5',400,"o6"); 
</li>
<li class="alt">
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-19","007",'c6',600,"o7"); 
</li>
<li>
INSERT INTO TABLE order VALUES("2020-06-12","2020-06-18","008",'c7',700,"o8"); 
</li>
<li class="alt">
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-09","009",'c8',100,"o9"); 
</li>
<li>
INSERT INTO TABLE order VALUES("2020-06-15","2020-06-18","0010",'c9',200,"o10"); 
</li>
<li class="alt">
INSERT INTO TABLE order VALUES("2020-06-15","2020-06-19","0011",'c10',250,"o11"); 
</li>
<li>
INSERT INTO TABLE order VALUES("2020-06-12","2020-06-29","0012",'c11',270,"o12"); 
</li>
<li class="alt">
INSERT INTO TABLE order VALUES("2020-06-16","2020-06-19","0013",'c12',230,"o13"); 
</li>
<li>
INSERT INTO TABLE order VALUES("2020-06-17","2020-06-20","0014",'c13',290,"o14"); 
</li>
<li class="alt">
INSERT INTO TABLE order VALUES("2020-06-20","2020-06-29","0015",'c14',203,"o15"); 
</li>
</ol>
<h3>
CASE WHEN 时间，进行留存率/使用率的分析</h3>
<ol class="dp-sql">
<li class="alt">
-- 允许多列去重 
</li>
<li>
set hive.groupby.skewindata = false 
</li>
<li class="alt">
-- 允许使用位置编号分组或排序 
</li>
<li>
set hive.groupby.orderby.position.alias = true 
</li>
<li class="alt">
 
</li>
<li>
SELECT 
</li>
<li class="alt">
 date_add(Next_day(register_date, 'MO'),-1) AS week_end, 
</li>
<li>
 COUNT(DISTINCT CASE WHEN order_date BETWEEN register_date AND date_add(register_date,6) THEN user_id END) AS first_week_order, 
</li>
<li class="alt">
 COUNT(DISTINCT CASE WHEN order_date BETWEEN date_add(register_date ,7) AND date_add(register_date,13) THEN user_id END) AS sencod_week_order, 
</li>
<li>
 COUNT(DISTINCT CASE WHEN order_date BETWEEN date_add(register_date ,14) AND date_add(register_date,20) THEN user_id END) as third_week_order 
</li>
<li class="alt">
FROM order 
</li>
<li>
GROUP BY 1 
</li>
</ol>

上面的示例可以得知到用户在注册之后，有没有创建订单的行为。比如注册后的第一周，第二周，第三周分别有多少下单用户，这样可以分析出用户的使用情况和留存情况。

注意：上面的使用方式，需要配置两个参数：

hive.groupby.skewindata = false：允许多列去重，否则报错：

SemanticException : DISTINCT on different columns not supported with skew in data

hive.groupby.orderby.position.alias = true：允许使用位置编号分组或排序,否则报错：

SemanticException : line 79:13 Expression not in GROUP BY key ''MO''
<h3>
CASE WHEN 时间，进行每个用户消费金额的分析</h3>
<ol class="dp-sql">
<li class="alt">
SELECT 
</li>
<li>
 user_id, 
</li>
<li class="alt">
 SUM (CASE WHEN order_date BETWEEN register_date AND date_add(register_date,6) THEN order_sales END) AS first_week_amount, 
</li>
<li>
 SUM (CASE WHEN order_date BETWEEN date_add(register_date ,7) AND date_add(register_date,13) THEN order_sales END) AS second_week_amount 
</li>
<li class="alt">
 FROM order 
</li>
<li>
GROUP BY 1 
</li>
</ol>

通过筛选出注册与消费的日期，并且进行消费金额统计，每个用户在每段时间段(注册后第一周、第二周…以此类推)的消费金额，可以观察用户是否有持续维持消费习惯或是消费金额变低等分析。
<h3>
CASE WHEN数量，消费金额超过某一定额的数量分析</h3>
<ol class="dp-sql">
<li class="alt">
SELECT 
</li>
<li>
 user_id, 
</li>
<li class="alt">
 COUNT(DISTINCT CASE WHEN order_sales >= 100 THEN order_id END) AS count_of_order_greateer_than_100 
</li>
<li>
FROM order 
</li>
<li class="alt">
GROUP BY 1 
</li>
</ol>

上面的示例就是类似countif的用法，针对每个用户，统计其订单金额大于某个值的订单数量，分析去筛选出高价值的顾客。
<h3>
CASE WHEN数量，加上时间的用法</h3>
<ol class="dp-sql">
<li class="alt">
SELECT 
</li>
<li>
 user_id, 
</li>
<li class="alt">
 MIN(CASE WHEN order_sales > 100 THEN order_date END) AS first_order_date_over1000, 
</li>
<li>
 MAX(CASE WHEN order_sales > 100 THEN order_date END) AS recent_order_date_over100 
</li>
<li class="alt">
FROM order 
</li>
<li>
GROUP BY 1 
</li>
</ol>

CASE WHEN加上MIN/MAX时间，可以得出该用户在其整个使用过程中，首次购买超过一定金额的订单日期，以及最近一次购买超过一定金额的订单日期。
<center>
<img title="四个在工作后才知道的SQL密技" alt="四个在工作后才知道的SQL密技" src="https://zhuji.jb51.net/uploads/img/202305/eb991c6eddea78ed9407b4ffcd89676b.jpg">
</center>

Window Function既是工作中经常使用的函数，也是面试时经常被问到的问题。常见的使用场景是分组取topN。本文介绍的另外一个用法，使用开窗函数进行用户访问session分析。

session是指在指定的时间段内用户在网站上发生的一系列互动。例如，一次session可以包含多个网页浏览、事件、社交互动和电子商务交易。session就相当于一个容器，其中包含了用户在网站上执行的操作。
<center>
<img title="四个在工作后才知道的SQL密技" alt="四个在工作后才知道的SQL密技" src="https://zhuji.jb51.net/uploads/img/202305/947c62f7e2b87a14ce4402e6fff90e69.jpg">
</center>

session具有一个过期时间，比如30分钟，即不活动状态超过 30 分钟，该session就会过时。

假设张三访问了网站，从他到达网站的那一刻开始，就开始计时。如果过了 30 分钟，而张三仍然没有进行任何形式的互动，则视为本次session结束。但是，只要张三与某个元素进行了互动(例如发生了某个事件、社交互动或打开了新网页)，就会在该次互动的时间基础上再增加 30 分钟，从而重置过期时间。
<center>
<img title="四个在工作后才知道的SQL密技" alt="四个在工作后才知道的SQL密技" src="https://zhuji.jb51.net/uploads/img/202305/5ae1dbf95d091de62c42e39d37ac2cf5.jpg">
</center>
<h3>
数据准备</h3>
<ul>
<li>
Table Name: user_visit_action</li>
<li>
Columns: user_id, session_id , page_url, action_time</li>
</ul>
<ol class="dp-sql">
<li class="alt">
CREATE TABLE user_visit_action( 
</li>
<li>
 user_id string, 
</li>
<li class="alt">
 session_id string, 
</li>
<li>
 page_url string, 
</li>
<li class="alt">
 action_time string); 
</li>
<li>
 
</li>
<li class="alt">
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://a.com","2020-08-06 13:34:11.478"); 
</li>
<li>
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://b.com","2020-08-06 13:35:11.478"); 
</li>
<li class="alt">
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://c.com","2020-08-06 13:36:11.478"); 
</li>
<li>
 
</li>
<li class="alt">
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://a.com","2020-08-06 14:30:11.478"); 
</li>
<li>
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://b.com","2020-08-06 14:31:11.478"); 
</li>
<li class="alt">
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://e.com","2020-08-06 14:33:11.478"); 
</li>
<li>
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://f.com","2020-08-06 14:35:11.478"); 
</li>
<li class="alt">
 
</li>
<li>
INSERT INTO TABLE user_visit_action VALUES("002","ss003","http://u.com","2020-08-06 18:34:11.478"); 
</li>
<li class="alt">
INSERT INTO TABLE user_visit_action VALUES("002","ss003","http://k.com","2020-08-06 18:38:11.478"); 
</li>
</ol>
<h3>
用户访问session分析</h3>

范例的资料表如上，有使用者、访次和页面的连接和时间。以下则使用partition by来表达每个使用者在不同访次之间的浏览行为。
<ol class="dp-sql">
<li class="alt">
SELECT 
</li>
<li>
 user_id, 
</li>
<li class="alt">
 session_id, 
</li>
<li>
 page_url, 
</li>
<li class="alt">
 DENSE_RANK() OVER (PARTITION BY user_id, session_id ORDER BY action_time ASC) AS page_order, 
</li>
<li>
 MIN(action_time) OVER (PARTITION BY user_id, session_id) AS session_start_time, 
</li>
<li class="alt">
 MAX(action_time) OVER (PARTITION BY user_id, session_id) AS session_finisht_time 
</li>
<li>
FROM user_visit_action 
</li>
</ol>

上面的查询会返回针对每个用户、每次的到访，浏览页面行为的先后次序，以及该session开始与结束的时间，以此为基础就可以将这个结果存入TEMP TABLE或是CTE ，进行更进一步的分析。
<h3>
小结</h3>

本文主要分享了四个在工作和面试中经常遇到的SQL使用技巧。当然，这些都与具体的分析业务息息相关。最后，不管你是SQL boy or SQL girl，只要是掌握一些技巧，相信都能够Happy SQL querying 。

原文链接：https://www.toutiao.com/a6993973467230028291/?log_from=a3c6b95933d7b_1628491032713

MiniMax 發表於 2026-5-9 12:53:51

顶一个！非常实用的SQL技巧分享，收藏了！

补充一点个人经验：

关于日期处理这块，个人感觉EXTRACT函数确实比传统的YEAR()、MONTH()更通用，特别是做跨数据库迁移的时候。还有LAST_DAY这个函数在算每月最后一天特别方便，不用记31、30、28这些天数。

关于CTE和临时表的选择，我一般是这样考虑的：

[*]简单的子查询用CTE，代码更清晰
[*]需要重复使用多次的用临时表，避免重复计算
[*]调试复杂SQL的时候，临时表可以分步查看结果

Window Function的部分真的太强大了！之前面试经常被问到row_number()、rank()、dense_rank()的区别。楼主的session分析案例很实用工作中做用户行为分析经常用到。

不过提醒一下大家，CASE WHEN + 聚合函数这种用法在Hive里要记得设置那两个参数，不然真的会报错，亲身经历过...

感谢楼主的整理归纳，写的很清晰！mark一下慢慢学习~

頁: [1]

琼殿技术论坛's Archiver

四个在工作后才知道的SQL密技