1. 窗口函数
窗口函数的基本结构
窗口函数通常与OVER()
子句一起使用,该子句定义了窗口函数的计算范围和排序方式。OVER()
子句可以包含以下部分:
PARTITION BY
子句(可选):将结果集分成多个分区,窗口函数在每个分区内独立计算。ORDER BY
子句(可选,但某些窗口函数需要):定义窗口内行的排序方式。
窗口函数类型
窗口函数大致可以分为以下几类:
-
排名函数:如
ROW_NUMBER()
、RANK()
、DENSE_RANK()
等,用于为结果集中的行分配一个唯一的序号。 -
分析函数:如
SUM()
、AVG()
、MIN()
、MAX()
、COUNT()
等聚合函数也可以作为窗口函数使用,它们对每个窗口内的行进行聚合计算,但保留结果集中的每一行。 -
行值函数:如
LEAD()
、LAG()
、FIRST_VALUE()
、LAST_VALUE()
等,用于访问窗口内其他行的值。
使用实例
假设我们有一个名为sales
的表,包含以下列:sale_date
(销售日期)、region
(地区)、salesperson
(销售人员)和amount
(销售额)。
实例1:使用ROW_NUMBER()
为每个地区的销售额排名
SELECT
sale_date,
region,
salesperson,
amount,
ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM
sales;
这个查询为sales
表中的每一行计算了一个rank_in_region
列,表示该行所在地区按销售额降序排列的排名。
实例2:计算每个地区的总销售额和每个销售的贡献百分比
SELECT
sale_date,
region,
salesperson,
amount,
SUM(amount) OVER (PARTITION BY region) AS total_sales_in_region,
ROUND(amount * 100.0 / SUM(amount) OVER (PARTITION BY region), 2) AS sales_percentage_in_region
FROM
sales;
s
这个查询使用了SUM()
窗口函数来计算每个地区的总销售额,并计算了每个销售人员的销售额占该地区总销售额的百分比。
实例3:使用LEAD()
和LAG()
查看前后行的销售额
SELECT
sale_date,
salesperson,
amount,
LAG(amount) OVER (ORDER BY sale_date) AS previous_amount,
LEAD(amount) OVER (ORDER BY sale_date) AS next_amount
FROM
sales;
这个查询使用LAG()
和LEAD()
窗口函数来查看每个销售人员销售额的前一行和后一行的值。这对于分析销售趋势或识别异常值非常有用。
注:窗口函数不能直接引用需要再加一个select才能查询
例如,查找每个学校中gpa排名第一的同学的信息
错误:
select
device_id,university,
rank() over(PARTITION by university ORDER BY gpa asc ) r,
gpa
from user_profile
where r=1
正确:
select device_id,university,gpa
from(
select device_id,university,
rank() over(PARTITION by university ORDER BY gpa asc ) r,gpa
from user_profile) as a
where r=1
2.ROUND(column_name, decimals)
保留两位小数
SELECT ROUND(amount, 2) AS rounded_amount
FROM sales;
四舍五入
SELECT ROUND(amount) AS rounded_amount
FROM sales;
3. union 、union all
union
- 用途:
UNION
用于合并两个或多个SELECT
语句的结果集,并自动去除重复的行。
假设有两个表 Employees1
和 Employees2
,我们想要获取这两个表中所有唯一的员工名字。
SELECT Name FROM Employees1
UNION
SELECT Name FROM Employees2;
union all
- 用途:
UNION ALL
也用于合并两个或多个SELECT
语句的结果集,但它不会去除重复的行。
继续使用 Employees1
和 Employees2
的例子,如果我们想要获取这两个表中所有的员工名字,包括重复的。
SELECT Name FROM Employees1
UNION ALL
SELECT Name FROM Employees2;
4.DATEDIFF()
DATEDIFF
函数用于计算两个日期之间的时间间隔。
DATEDIFF(datepart, startdate, enddate)
datepart
指定了日期的哪一部分用于计算差异,如year
、month
、day
等。
计算两个日期之间的天数差异:
SELECT DATEDIFF(day, '2022-01-01', '2022-01-10') AS DayDiff;
5.substring_index()
语法
SUBSTRING_INDEX(str, delim, count)
str
:要处理的原始字符串。delim
:用作分隔符的字符串。count
:指定返回第几个分割后的子串。如果count
是正数,函数从字符串的左边开始计数并返回第 N 个分隔符左边的所有内容(包括分隔符)。如果count
是负数,则从字符串的右边开始计数并返回第 N 个分隔符右边的所有内容(不包括分隔符)。
示例
统计用户的个人博客用户字段提取出单独记录为一个新的字段
select
device_id,
substring_index(blog_url,'/',-1) as user_name
from user_submit
统计每个年龄的用户分别有多少参赛者
select
substring_index(substring_index(profile,',',3) ,',',-1)as age,
count(device_id) as number
from user_submit
group by age
6. case…when…
将用户年龄分为25岁以上以及25岁以下并计算处于该年龄段用户的个数(分两类)
SELECT
(CASE
WHEN age IS NULL OR age < 25 THEN '25岁以下'
ELSE '25岁及以上'
END )AS age_cut,
COUNT(*) AS number
FROM
user_profile
GROUP BY
age_cut
将用户划分为20岁以下,20-24岁,25岁及以上三个年龄段,分别查看不同年龄段用户的明细情况,请取出相应数据。(注:若年龄为空请返回其他。)
select
device_id ,gender,
(case
when age<20 then '20岁以下'
when age between 20 and 24 then'20-24岁'
when age>=25 then'25岁及以上'
else '其他'
end )as age_cut
from user_profile