Mysql-窗口函数二

1. 前百分之N的问题 排名 row_number

1.1 需求

• 用户访问次数表，包含用户编号、用户类型、访问次数。
• 要求在剔除访问次数前20%的用户后得到每类用户的平均访问次数。

1.2 准备工作

create table user_visits (
    user_id int,
    user_type varchar(32),
    visit_count int
)
;

insert into user_visits
values
(10, 'A', 352),
(6, 'C', 209),
(7, 'C', 110),
(4, 'E', 101),
(2, 'B', 53),
(20, 'A', 53),
(11, 'C', 33),
(1, 'A', 30),
(9, 'E', 29),
(8, 'B', 6)
;

1.3 分析

1.4 实现

with t1 as(select user_id, user_type, visit_count,
       row_number() over (order by visit_count desc) as rn
from user_visits)
select distinct user_type,
       round(avg(visit_count) over(partition by user_type),1) as avg_cnt
from t1 where rn>(select count(*) from user_visits) * 0.2;

2. 前百分之N的问题 ntile

2.1 介绍

NTILE 是一个窗口函数，用于将查询结果划分为指定数量的分组，并为每个分组分配一个组号。这在分析数据时非常有用，尤其是需要对数据进行分组或均匀分配时。

2.2 语法

NTILE(num_buckets) OVER (PARTITION BY column ORDER BY column)

• num_buckets：要将数据划分的组数（桶数）。
• PARTITION BY column：可选，用于按指定列对数据进行分区。
• ORDER BY column：必需，用于指定每个分区内的排序。

2.2.1 示例

假设我们有一个包含学生成绩的表 student_scores，结构如下：

准备数据：

-- 创建表
CREATE TABLE students_score (
  student_id INT PRIMARY KEY,
  name VARCHAR(255),
  score INT
);

-- 插入数据
INSERT INTO students_score (student_id, name, score) VALUES
(1, 'Alicia', 85),
(2, 'Robert', 90),
(3, 'Charles', 78),
(4, 'David', 92),
(5, 'Eva', 88);

将这些学生按分数划分为 3 组，并查看每个学生所属的组号。

SELECT
    student_id,
    name,
    score,
    NTILE(3) OVER (ORDER BY score DESC) AS group_number
FROM
    students_score;

2.2.2 结果示例

在这个例子中，NTILE(3) 将数据划分为 3 组，并根据分数的降序排序为每个学生分配一个组号。前两个最高分的学生被分配到第一组（组号1），接下来的两个学生被分配到第二组（组号2），而分数最低的学生被分配到第三组（组号3）。

2.2.3 注意事项

• 如果不能均匀分配组，则较小编号的组可能会多出一行。例如，如果有 10 行数据和 3 组，则前两个组将有 4 行数据，最后一个组将有 2 行数据。
• NTILE 通常用于数据分析场景，例如分配排名、分层抽样等。

2.3 需求

• 用户访问次数表，包含用户编号、用户类型、访问次数。
• 要求在剔除访问次数前20%的用户后得到每类用户的平均访问次数。
  

2.4 分析

2.5 实现

with t1 as (
    select
        user_id, user_type, visit_count,
        ntile(10) over (order by visit_count desc) as nt
    from user_visits
)
select
    user_type,
    round(avg(visit_count), 1) as avg_visit
from t1
where t1.nt>2
group by user_type
order by user_type;

3. 前百分之N的问题 百分比 PERCENT_RANK

3.1 语法

PERCENT_RANK() 是 SQL 中的窗口函数，用于计算某行的百分比排名。这个函数在数据分析中常用于了解某一数据点在整体数据中的相对位置。百分比排名的取值范围是从 0 到 1，表示当前行在分区内的排名相对于分区内其他行的百分比位置。

PERCENT_RANK() OVER (PARTITION BY column ORDER BY column)

• PARTITION BY column：可选，用于按指定列对数据进行分区。
• ORDER BY column：必需，用于指定每个分区内的排序。
  PERCENT_RANK() 的计算方式是：

百分比排名 = (当前行的排名 - 1) / (分区内的行总数 - 1)

3.1.1 示例

假设我们有一个包含学生成绩的表 students_score，结构如下：

计算每个学生的百分比排名。

SELECT
    student_id,
    name,
    score,
    PERCENT_RANK() OVER (ORDER BY score DESC) as `percent_rank`
FROM
    students_score;

结果：

3.1.2 注意事项

• 排序顺序：PERCENT_RANK() 的计算依赖于 ORDER BY 子句指定的排序顺序。在上面的例子中，我们按照 score 降序排列，最高分的学生的 percent_rank 是 0。

• 分区：如果使用了 PARTITION BY 子句，则 PERCENT_RANK() 会在每个分区内计算百分比排名，而不是在整个结果集上。

• 相同值：在处理相同值时，PERCENT_RANK() 会为相同值分配相同的排名百分比。

• 第一行和最后一行：第一行的 percent_rank 总是 0，而最后一行的 percent_rank 总是 1。

PERCENT_RANK() 常用于了解数据在分布中的相对位置，对于生成百分比排名或分位数分析非常有用。

3.2 需求

• 用户访问次数表，包含用户编号、用户类型、访问次数。
• 要求在剔除访问次数前20%的用户后得到每类用户的平均访问次数。
  

3.3 实现

with t1 as (
    select
        user_id, user_type, visit_count,
        percent_rank() over (order by visit_count desc) as pr
    from user_visits
)
select
    user_type,
    round(avg(visit_count), 1) as avg_visit
from t1
where pr>0.2
group by user_type
order by user_type;

4. 偏移函数: 求环比增长率

4.1 需求

假设有一个销售数据表 sales，其中记录了每个月的销售额，想要计算每个月的销售额与上个月的销售额之间的变化。

4.2 语法

LAG() 是 SQL 中的一个窗口函数，用于从当前行向上偏移指定数量的行，并返回偏移行的值。它对于访问前面的行数据而不使用自连接非常有用，特别是在时间序列数据和累积计算中。

LAG(column, offset, default) OVER (PARTITION BY partition_column ORDER BY order_column)

参数解释：
参数解释

• column: 这是你要获取前值的列。
• offset: 向上偏移的行数。默认为1，表示返回前一行的值。
• default: 当偏移行超出窗口范围时返回的默认值。如果没有指定，默认值为 NULL。
• PARTITION BY partition_column: 可选项，用于指定分区列。在每个分区内独立计算 LAG 值。如果没有指定，整个结果集将视为一个分区。
• ORDER BY order_column: 必需项，用于指定窗口函数处理数据的顺序。

4.3 示例

假设有一个包含月份销售数据的表 monthly_sales，其结构如下：

获取每个月的销售额，以及与前一个月销售额的差异。

SELECT
    month,
    sales,
    LAG(sales, 1) OVER (ORDER BY month) AS previous_sales,
    LAG(sales, 1, 666) OVER (ORDER BY month) AS previous_sales_2
FROM sales_data;

结果：

4.4 解释

• LAG(sales, 1, 0) OVER (ORDER BY month): 该表达式获取前一个月的销售额。如果没有前一个月的数据（例如对于第一行），则返回默认值 0。
• sales - previous_sales: 计算当前月与上月的销售额差异。

4.5 分析

4.6 实现

drop database if exists db_1;
create database db_1;
use db_1;

-- 创建表
CREATE TABLE sales_data (
    month VARCHAR(7) NOT NULL,
    sales INT NOT NULL,
    PRIMARY KEY (month)
);

-- 插入数据
INSERT INTO sales_data (month, sales) VALUES
('2023-01', 1000),
('2023-02', 1100),
('2023-03', 1050),
('2023-04', 1200),
('2023-05', 1150);

select * from sales_data;

# 目标: 求环比增长率 = (当前月销量 - 上一月销量) / 上一月销量 * 100
select
    month,
    sales,
    lag(sales, 1, 0) over(order by month) as lag_1_sales,
    # sales - (lag(sales, 1) over(order by month)) as diff,
    # (sales - (lag(sales, 1) over(order by month))) / (lag(sales, 1) over(order by month)) * 100 as rate,
    # round((sales - (lag(sales, 1) over(order by month))) / (lag(sales, 1) over(order by month)) * 100, 2) as rate_2,
    concat(round((sales - (lag(sales, 1) over(order by month))) / (lag(sales, 1) over(order by month)) * 100, 1), '%') as rate_3
from sales_data;

4.6 总结

- LAG(字段, [N], [M])：返回分区中当前行前第N行的指定字段的内容，如果没有，默认返回M
- LEAD(字段, [N], [M])：返回分区中当前行后第N行的指定字段的内容，如果没有，默认返回M

• first_val(…)
• last_val(…)
• 注意：M和N可以省略，N默认为1，M默认为NULL。