SQL Developer如何识别Price列相邻日正负切换并筛选待上报数据

需求逻辑确认 #

• 校验范围：基于输入的查询日期，仅校验该日期往前推的前1个自然日、前2个自然日的价格数据
• 触发规则：同一Ref ID对应的两日价格发生正负切换时，输出这两日的全量对应数据
• 边界示例：查询日期为2021-09-12时，前两日9月10、11日价格均为负，无符号切换则返回空结果

实现代码（支持MySQL 8.0+/PostgreSQL/Oracle/SQL Server等支持窗口函数的数据库） #

以下为MySQL语法版本，其他数据库适配调整见后续说明：

-- 此处替换为实际查询日期
SET @query_date = '2021-09-13';
-- 替换为实际业务表名
SET @table_name = 'your_price_table';

WITH price_with_prev AS (
    SELECT 
        `Effective Date`,
        Prices,
        `Ref ID`,
        -- 按Ref ID分组，取同组前一天的价格和日期
        LAG(Prices) OVER (PARTITION BY `Ref ID` ORDER BY `Effective Date`) AS prev_price,
        LAG(`Effective Date`) OVER (PARTITION BY `Ref ID` ORDER BY `Effective Date`) AS prev_date
    FROM your_table_name
    -- 提前过滤仅需要计算的两天数据，优化性能
    WHERE `Effective Date` BETWEEN DATE_SUB(@query_date, INTERVAL 2 DAY) AND DATE_SUB(@query_date, INTERVAL 1 DAY)
)
-- 拉取符合条件的Ref ID对应的两日全量数据
SELECT t.`Effective Date`, t.Prices, t.`Ref ID`
FROM your_table_name t
INNER JOIN (
    SELECT DISTINCT `Ref ID`
    FROM price_with_prev
    WHERE 
        -- 校验日期连续，避免缺数导致的误判
        prev_date = DATE_SUB(`Effective Date`, INTERVAL 1 DAY)
        -- 正负乘积必然小于0，可快速判断符号切换
        AND (Prices * prev_price) < 0
) valid_ref ON t.`Ref ID` = valid_ref.`Ref ID`
WHERE t.`Effective Date` BETWEEN DATE_SUB(@query_date, INTERVAL 2 DAY) AND DATE_SUB(@query_date, INTERVAL 1 DAY)
ORDER BY `Ref ID`, `Effective Date`;

适配说明 #

1. 若业务中0值需要归为正/负数统一处理，可调整符号判断条件，比如改为SIGN(Prices) != SIGN(prev_price)自定义符号规则
2. 不同数据库日期函数调整：
   • Oracle：日期加减改为@query_date - 1、@query_date - 2
   • SQL Server：日期加减改为DATEADD(day, -1, @query_date)、DATEADD(day, -2, @query_date)
   • PostgreSQL：变量声明改为WITH params AS (SELECT '2021-09-13'::date AS query_date)后续直接引用即可

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