Oracle SQL获取存在回溯日期交易的商品及分类

核心思路 #

按item_number和category_id分区，以transaction_number升序排序后，用LAG()函数抓取同分区内前一条交易的生效日期，对比当前交易日期是否早于前一条——只要分区内存在任意一条这样的记录，就返回该分区的item_number和category_id。

实现代码 #

第一步：生成带前序日期的数据集

先通过子查询给每条记录添加上同分区内前一条交易的生效日期：

SELECT
    item_number,
    category_id,
    transaction_effective_date,
    -- 取同分区、按transaction_number排序后的上一条日期
    LAG(transaction_effective_date) OVER (
        PARTITION BY item_number, category_id
        ORDER BY transaction_number ASC
    ) AS prev_effective_date
FROM transactions

第二步：筛选并返回目标分组

基于上面的子查询，筛选出存在日期回溯的记录，再通过DISTINCT去重得到唯一的分区信息：

SELECT DISTINCT
    item_number,
    category_id
FROM (
    SELECT
        item_number,
        category_id,
        transaction_effective_date,
        LAG(transaction_effective_date) OVER (
            PARTITION BY item_number, category_id
            ORDER BY transaction_number ASC
        ) AS prev_effective_date
    FROM transactions
) AS subquery
-- 筛选当前日期早于前一条的记录
WHERE transaction_effective_date < prev_effective_date

补充说明 #

这个写法是通用SQL语法，支持所有具备LAG()函数的数据库（比如MySQL 8+、PostgreSQL、SQL Server等）。示例中item_number=100、category_id=55的分区会被正确识别并返回。

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