Снежинка получает предыдущее значение, которое было другим

Думаю, у меня есть кое-что, что вам здесь подойдет. Я тестировал его только с первой группой данных, поэтому, возможно, потребуется настройка.

Что я делаю, так это беру вашу таблицу и нахожу все записи, которые различаются по device_no. Я предполагаю, что device_no не изменится, а затем вернется к предыдущему значению... Я думаю, это сломает это.

Как только это будет в data_delta CTE, я смогу выполнить asof соединение, чтобы получить предыдущую «запись изменения» для каждой записи, которая была у меня в исходной таблице.

Полный код выглядит так, но первый data CTE — это лишь некоторые примеры данных:

with data as (
    select *
    from (values('FD2A',600076,'2011-09-20'::date,'2012-01-03'::date),      
    ('FD2A',208049,'2017-09-11'::date,'2017-09-11'::date),
    ('FD2A',208049,'2017-09-11'::date,'2023-09-12'::date)
    ) x (id_no,device_no,device_date,product_date)
), data_delta as (
    select * from data
    qualify row_number() over (partition by id_no, device_no order by device_date, product_date) = 1
)
select d1.*
     , d2.device_no as prev_device_no
     , d2.device_date as prev_device_date
from data d1
asof join data_delta d2
  match_condition(d1.device_date > d2.device_date)
  on (d1.id_no = d2.id_no)
order by d1.product_date
  ;

попробуйте использовать комбинацию LAG, CTE и CASE:

WITH DeviceHistory AS (
  SELECT 
    ID_NO,
    DEVICE_NO,
    DEVICE_DATE,
    PRODUCT_NO,
    PRODUCT_DATE,
    LAG(DEVICE_NO) OVER (PARTITION BY ID_NO ORDER BY DEVICE_DATE, PRODUCT_DATE) AS prev_device_no,
    LAG(DEVICE_DATE) OVER (PARTITION BY ID_NO ORDER BY DEVICE_DATE, PRODUCT_DATE) AS prev_device_date
  FROM your_table
),
FilteredHistory AS (
  SELECT
    ID_NO,
    DEVICE_NO,
    DEVICE_DATE,
    PRODUCT_NO,
    PRODUCT_DATE,
    CASE 
      WHEN prev_device_no IS NOT NULL AND prev_device_no != DEVICE_NO THEN prev_device_no
      ELSE NULL
    END AS prev_diff_device_no,
    CASE 
      WHEN prev_device_date IS NOT NULL AND prev_device_date != DEVICE_DATE THEN prev_device_date
      ELSE NULL
    END AS prev_diff_device_date,
    ROW_NUMBER() OVER (PARTITION BY ID_NO, DEVICE_NO ORDER BY DEVICE_DATE, PRODUCT_DATE) AS rn
  FROM DeviceHistory
)
SELECT
  ID_NO,
  DEVICE_NO,
  DEVICE_DATE,
  PRODUCT_NO,
  PRODUCT_DATE,
  prev_diff_device_no AS PREV_DEVICE_NO,
  prev_diff_device_date AS PREV_DEVICE_DATE
FROM FilteredHistory
QUALIFY 
  rn = 1 OR prev_diff_device_no IS NOT NULL
ORDER BY ID_NO, DEVICE_DATE, PRODUCT_DATE;