数据分析学习课程1-----SQL------Day5解题过程中遇到的函数

1. 窗口函数

窗口函数的基本结构

窗口函数通常与OVER()子句一起使用，该子句定义了窗口函数的计算范围和排序方式。OVER()子句可以包含以下部分：

• PARTITION BY子句（可选）：将结果集分成多个分区，窗口函数在每个分区内独立计算。
• ORDER BY子句（可选，但某些窗口函数需要）：定义窗口内行的排序方式。

窗口函数类型

窗口函数大致可以分为以下几类：

1. 排名函数：如ROW_NUMBER()、RANK()、DENSE_RANK()等，用于为结果集中的行分配一个唯一的序号。

2. 分析函数：如SUM()、AVG()、MIN()、MAX()、COUNT()等聚合函数也可以作为窗口函数使用，它们对每个窗口内的行进行聚合计算，但保留结果集中的每一行。

3. 行值函数：如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