SQL_having_pandas_filter

HAVING子句在SQL中用于对分组后的结果进行过滤，它通常与GROUP BY子句一起使用。HAVING子句允许你指定条件来过滤聚合函数的结果，而WHERE子句则用于在分组之前过滤原始数据。

基本语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition
ORDER BY column_name;

• column_name: 你想要查询的列名。
• aggregate_function: 聚合函数，如SUM(), AVG(), COUNT(), MIN(), MAX()等。
• table_name: 表名。
• condition: HAVING子句的条件，通常涉及到聚合函数。

示例

假设我们有一个名为employees的表，包含以下列：department_id, salary。我们想要找出平均薪资超过50000的部门。

SELECT department_id, 
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

在例子中：
• GROUP BY department_id: 根据department_id列对数据进行分组。
• AVG(salary) AS average_salary: 计算每个部门的平均薪资。
• HAVING AVG(salary) > 50000: 过滤出平均薪资超过50000的部门。

更复杂的示例

假设我们有一个名为sales的表，包含以下列：region_id, year, sales_amount。我们想要找出在过去两年中每年销售额都超过100000的地区。

SELECT region_id
FROM sales
WHERE year > (SELECT YEAR(NOW()) - 2)
GROUP BY region_id
HAVING SUM(CASE WHEN year = (SELECT YEAR(NOW())) THEN sales_amount ELSE 0 END) > 100000
AND SUM(CASE WHEN year = (SELECT YEAR(NOW()) - 1) THEN sales_amount ELSE 0 END) > 100000;

在例子中：
• WHERE year > (SELECT YEAR(NOW()) - 2): 过滤出过去两年的数据。
• GROUP BY region_id: 根据region_id列对数据进行分组。
• HAVING子句包含两个条件：
• SUM(CASE WHEN year = (SELECT YEAR(NOW())) THEN sales_amount ELSE 0 END) > 100000: 过滤出今年销售额超过100000的地区。
• SUM(CASE WHEN year = (SELECT YEAR(NOW()) - 1) THEN sales_amount ELSE 0 END) > 100000: 过滤出去年销售额超过100000的地区。

注意事项

1. 使用场景：HAVING子句通常用于与聚合函数一起使用，而WHERE子句用于过滤原始数据。
2. 条件表达式：HAVING子句中可以使用COUNT(), SUM(), AVG(), MIN(), MAX()等聚合函数。
3. 子查询：HAVING子句中可以使用子查询，如示例中的(SELECT YEAR(NOW()))。

再来一个例子，和pandas .filter 比较

有一个名为orders的表，包含以下列：order_id, customer_id, order_date, total_amount。我们想要找出在过去一年中至少有3个订单且总金额超过5000元的客户。

SQL 查询

SELECT customer_id, COUNT(order_id) AS order_count, SUM(total_amount) AS total_spent
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY customer_id
HAVING COUNT(order_id) >= 3 AND SUM(total_amount) > 5000;

一点说明
在查询中，order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) 用于过滤出在过去一年内发生的订单。
在 SQL 中，DATE_SUB 函数用于从一个日期中减去指定的时间间隔。CURDATE() 函数返回当前日期（不包含时间部分），而 INTERVAL 1 YEAR 指定了时间间隔为 1 年。
将这些组合起来，DATE_SUB(CURDATE(), INTERVAL 1 YEAR) 会计算出当前日期往前推一年的时间点。
• CURDATE(): 返回当前日期，例如 2024-09-25。
• DATE_SUB(…, INTERVAL 1 YEAR): 从当前日期减去 1 年，结果为 2023-09-25。

Pandas 示例
在Pandas中，我们可以使用groupby()方法来分组数据，然后使用filter()方法来实现类似的过滤效果。

import pandas as pd
# 创建一个示例DataFrame
data = {'order_id': [1, 2, 3, 4, 5, 6],'customer_id': [101, 101, 102, 103, 103, 103],'order_date': pd.to_datetime(['2023-04-01', '2023-05-01', '2023-06-01', '2023-07-01', '2023-08-01', '2023-09-01']),'total_amount': [200, 300, 150, 400, 500, 600]
}
df = pd.DataFrame(data)# 过滤出过去一年内的订单
one_year_ago = pd.Timestamp.now() - pd.DateOffset(years=1)
filtered_df = df[df['order_date'] >= one_year_ago]# 使用groupby()和agg()计算每个客户的订单数量和总消费金额
grouped_df = filtered_df.groupby('customer_id').agg(order_count=('order_id', 'count'),total_spent=('total_amount', 'sum')
)# 使用filter()过滤出至少有3个订单且总金额超过5000元的客户
result_df = grouped_df.filter(lambda x: (x['order_count'] >= 3) & (x['total_spent'] > 5000))print(result_df)

解释
• groupby(‘customer_id’): 根据客户ID进行分组。
• .agg(order_count=(‘order_id’, ‘count’), total_spent=(‘total_amount’, ‘sum’)): 计算每个客户的订单数量和总消费金额。
• filter(lambda x: (x[‘order_count’] >= 3) & (x[‘total_spent’] > 5000)): 使用filter()方法来过滤出至少有3个订单且总金额超过5000元的客户。