正文

掌握窗函数，轻松提升数据分析效率：揭秘SQL、Python中实用技巧与案例

/0 浏览量
1013

窗函数（Window Function）是一种在SQL和Python中进行复杂数据分析的强大工具。它们允许你基于一组记录的窗口对数据进行计算，而不是仅对单条记录进行计算。这为处理聚合、排名、排名百分比、移动平均和其他复杂的数据分析任务提供了极大的灵活性。

窗函数简介

窗函数通常用于执行以下操作：

  • 聚合：计算窗口内的聚合值，如平均数、总和等。
  • 排名：为窗口内的行分配排名。
  • 移动计算：计算移动平均数、移动最大值等。
  • 密度计算：计算窗口内特定值的密度。

SQL中的窗函数

在SQL中，窗函数通常与OVER子句一起使用。以下是一些常见的SQL窗函数及其用法：

1. ROW_NUMBER()

SELECT
  employee_id,
  employee_name,
  department,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM
  employees;

这个例子中，ROW_NUMBER()为每个员工根据其薪水降序排列分配了一个唯一的排名。

2. RANK()

SELECT
  employee_id,
  employee_name,
  department,
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM
  employees;

RANK()函数与ROW_NUMBER()类似，但它会为具有相同值的行分配相同的排名。

3. DENSE_RANK()

SELECT
  employee_id,
  employee_name,
  department,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM
  employees;

DENSE_RANK()函数类似于RANK()，但会为具有相同值的行分配连续的排名。

4. SUM()

SELECT
  employee_id,
  employee_name,
  department,
  SUM(salary) OVER (PARTITION BY department) AS total_department_salary
FROM
  employees;

这个例子中，SUM()函数计算每个部门的总薪水。

Python中的窗函数

在Python中，可以使用pandas库中的rolling()和apply()函数来实现窗函数的功能。

1. rolling()

import pandas as pd

# 创建一个示例DataFrame
data = {'value': [1, 2, 3, 4, 5]}
df = pd.DataFrame(data)

# 计算移动平均
rolling_mean = df['value'].rolling(window=3).mean()
print(rolling_mean)

2. apply()

import pandas as pd

# 创建一个示例DataFrame
data = {'value': [1, 2, 3, 4, 5]}
df = pd.DataFrame(data)

# 定义一个自定义函数
def custom_function(row):
    return sum(row)

# 应用自定义函数到DataFrame的列上
df['sum'] = df.apply(custom_function, axis=1)
print(df)

实用技巧与案例

案例一：销售数据排名

假设你是一家零售公司的数据分析师，需要分析不同销售代表在特定月份的销售业绩。你可以使用SQL中的窗函数来计算每个销售代表的排名。

SELECT
  sales_rep_id,
  sales_amount,
  RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM
  sales_data;

案例二：计算移动平均

如果你正在分析股票市场数据，你可能需要计算移动平均来识别趋势。在Python中，你可以使用pandas的rolling()函数来计算移动平均。

import pandas as pd

# 创建一个示例股票价格DataFrame
data = {'price': [100, 102, 101, 103, 105, 107, 106, 108, 109, 110]}
df = pd.DataFrame(data)

# 计算移动平均
rolling_mean = df['price'].rolling(window=3).mean()
print(rolling_mean)

掌握窗函数可以极大地提升数据分析的效率，尤其是在处理复杂的数据分析任务时。无论是使用SQL还是Python，窗函数都是数据分析师的宝贵工具。通过本文的介绍，希望你能更好地理解窗函数的原理和应用，并在实际工作中运用它们来提升数据分析的能力。

-- 展开阅读全文 --