添加滚动时间窗口：SQL查询日期条件动态优化需求

修改思路 #

把原查询里固定的起始日期'2022-04-01'替换成动态计算的当前日期往前推6个月的日期，这样每次执行查询时都会自动获取最近6个月的交易数据，实现滚动时间窗口的需求。另外要补上原SQL里缺失的表名，同时把字符串常量的双引号改成单引号（符合多数数据库的语法规范）。

不同数据库的修改示例 #

以下是主流数据库对应的完整修改后SQL：

PostgreSQL

SELECT   entity_code,
         contract_category,
         manufacturer_catalog_number,
         product_description,
         manufacturer_top_parent_name,
         base_spend,
         Sum(base_spend) OVER (partition BY contract_category, entity_code ORDER BY base_spend DESC, manufacturer_catalog_number) AS running_sum
FROM     your_table_name  -- 替换为实际业务表名
WHERE    contract_category = 'PRODUCTS'
AND      transaction_date >= CURRENT_DATE - INTERVAL '6 months';

MySQL

SELECT   entity_code,
         contract_category,
         manufacturer_catalog_number,
         product_description,
         manufacturer_top_parent_name,
         base_spend,
         Sum(base_spend) OVER (partition BY contract_category, entity_code ORDER BY base_spend DESC, manufacturer_catalog_number) AS running_sum
FROM     your_table_name  -- 替换为实际业务表名
WHERE    contract_category = 'PRODUCTS'
AND      transaction_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

SQL Server

SELECT   entity_code,
         contract_category,
         manufacturer_catalog_number,
         product_description,
         manufacturer_top_parent_name,
         base_spend,
         Sum(base_spend) OVER (partition BY contract_category, entity_code ORDER BY base_spend DESC, manufacturer_catalog_number) AS running_sum
FROM     your_table_name  -- 替换为实际业务表名
WHERE    contract_category = 'PRODUCTS'
AND      transaction_date >= DATEADD(MONTH, -6, GETDATE());

Oracle

SELECT   entity_code,
         contract_category,
         manufacturer_catalog_number,
         product_description,
         manufacturer_top_parent_name,
         base_spend,
         Sum(base_spend) OVER (partition BY contract_category, entity_code ORDER BY base_spend DESC, manufacturer_catalog_number) AS running_sum
FROM     your_table_name  -- 替换为实际业务表名
WHERE    contract_category = 'PRODUCTS'
AND      transaction_date >= ADD_MONTHS(SYSDATE, -6);

关键说明 #

• 动态日期计算：依赖数据库内置日期函数自动生成6个月前的日期，无需手动维护固定日期，真正实现滚动窗口效果。
• 表名补充：原SQL的FROM子句缺失表名，必须替换为实际的业务表名称才能正常执行。
• 引号规范：将原查询中的双引号"改为单引号'包裹字符串常量，避免部分数据库将双引号识别为标识符导致语法错误。

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