如何编写SELECT查询语句找出products表中的FIFO偏差数据

核心思路 #

FIFO偏差的本质是存在至少一条其他产品记录，与当前记录的创建、交付顺序相悖——即当前产品创建时间更早，但交付时间更晚；或者创建时间更晚，交付时间更早。我们需要筛选出所有符合该特征的记录。

实现方案 #

方法1：自连接查询（兼容多数数据库） #

SELECT DISTINCT p1.*
FROM products p1
JOIN products p2 
  -- 若需按员工维度检查FIFO，保留以下条件；全局检查则删除
  ON p1.employee_code = p2.employee_code
  AND (
    (p1.created_at < p2.created_at AND p1.delivery_date > p2.delivery_date)
    OR (p1.created_at > p2.created_at AND p1.delivery_date < p2.delivery_date)
  );

方法2：窗口函数查询（适用于MySQL 8+、PostgreSQL、SQL Server等） #

通过窗口函数分别按创建时间、交付时间排序，若两条排序的序号不一致，说明该记录违反FIFO规则：

WITH ranked_products AS (
    SELECT 
        *,
        -- 按创建时间排序的序号
        ROW_NUMBER() OVER (PARTITION BY employee_code ORDER BY created_at) AS create_rank,
        -- 按交付时间排序的序号
        ROW_NUMBER() OVER (PARTITION BY employee_code ORDER BY delivery_date) AS delivery_rank
    FROM products
)
SELECT *
FROM ranked_products
WHERE create_rank != delivery_rank;

关键说明 #

• 自连接方法：通过表自联直接比对任意两组记录的时间关系，只要存在违反FIFO的配对，就筛选出对应记录（DISTINCT用于避免重复输出）。
• 窗口函数方法：更直观地通过排序序号判断偏差，当创建顺序排名与交付顺序排名不一致时，即可判定为偏差数据。
• 若无需按员工分组检查全局FIFO，只需删除两个方法中的PARTITION BY employee_code（自连接方法中删除p1.employee_code = p2.employee_code）。
• 注意处理delivery_date为NULL的场景：可根据业务需求添加WHERE delivery_date IS NOT NULL过滤，或调整排序规则（如ORDER BY delivery_date NULLS LAST）。

内容的提问来源于stack exchange，提问作者Ravi Kumar Chenagani