如何编写按产品匹配专属时间范围的SQL WHERE动态过滤子句？

问题背景 #

需要根据每个产品的专属时间范围过滤销售数据，统计各产品对应时段内的总销量。现有两张核心数据表：

• 产品时间范围表：存储每个产品的有效统计时段
• 销售数据表：存储所有产品的销售记录

原尝试的SQL语句因未关联产品时间范围表，无法实现按产品匹配对应时间范围的过滤，以下是修正后的解决方案：

SQL解决方案 #

核心逻辑是将产品时间范围表与销售数据表通过产品名称关联，用对应产品的起止日期过滤销售记录。

正确SQL语句 #

SELECT 
    p.name,
    CONCAT(p.start_date, ' to ', p.end_date) AS time_frame,
    SUM(s.sales) AS sales
FROM sales_data s
JOIN product_dates p ON s.name = p.name
WHERE s.date BETWEEN p.start_date AND p.end_date
GROUP BY p.name, p.start_date, p.end_date

关键说明 #

• 使用JOIN关联两张表，确保每条销售记录匹配到对应产品的时间范围
• WHERE子句中，用当前产品的start_date和end_date精准过滤销售日期
• 分组时需包含产品名称、起止日期（或拼接后的time_frame，不同SQL语法略有差异），保证统计维度准确

Pandas解决方案 #

如果用Python Pandas处理，可通过数据框合并+布尔索引实现需求：

代码实现 #

import pandas as pd

# 产品时间范围表
product_dates = [
        {'name': 'product_1', 'start_date': '2024-01-01', 'end_date': '2024-05-01'},
        {'name': 'product_2', 'start_date': '2024-03-05', 'end_date': '2024-04-26'},
        {'name': 'product_3', 'start_date': '2024-02-09', 'end_date': '2024-11-08'}
]
df_product = pd.DataFrame(product_dates)

# 销售数据表
sales_data = [
        {'name': 'product_1', 'date': '2024-01-01', 'sales': 1},
        {'name': 'product_2', 'date': '2024-01-01', 'sales': 3},
        {'name': 'product_3', 'date': '2024-01-01', 'sales': 4},
        # 更多销售记录...
]
df_sales = pd.DataFrame(sales_data)

# 转换日期类型为datetime，避免字符串比较误差
df_product[['start_date', 'end_date']] = df_product[['start_date', 'end_date']].astype('datetime64[ns]')
df_sales['date'] = df_sales['date'].astype('datetime64[ns]')

# 合并表，匹配产品的时间范围
merged_df = pd.merge(df_sales, df_product, on='name')

# 筛选符合产品时间范围的销售记录
filtered_df = merged_df[(merged_df['date'] >= merged_df['start_date']) & (merged_df['date'] <= merged_df['end_date'])]

# 统计各产品总销量并生成时间范围字段
result_df = filtered_df.groupby('name').agg(
    time_frame=('start_date', lambda x: f"{x.iloc[0].date()} to {x.iloc[0].date()}"),
    sales=('sales', 'sum')
).reset_index()

print(result_df)

关键说明 #

• 先转换所有日期字段为datetime类型，确保日期比较逻辑正确
• 通过merge按产品名称关联表，让每条销售记录带上对应产品的起止日期
• 用布尔索引精准筛选符合时间范围的销售数据
• 分组聚合时拼接起止日期为time_frame，同时计算总销量

内容的提问来源于stack exchange，提问作者Tyler Moore