Сравнение данных TIMESTAMP между предыдущей строкой и текущей строкой с помощью оператора CASE и выполнение операций в соответствии с разницей Teradata

Мне нужна помощь в создании запроса с использованием функций OLAP Teradata (версия 16.0+) с приведенным ниже сценарием для сравнения и устранения дубликатов объединения из таблицы Teradata.

У меня ниже 9 записей в таблице ABC.

          Existing Data(Table - ABC)    


            ACCOUNT_ID  EXT_REF_NO  SERIAL_NUM  RECORD_START_DT RECORD_END_DT
        1   100000000002195 8495752450757852    341FE4E6A1AF    8/13/2019 12:24:42  8/20/2019 23:59:59
        2   100000000002195 8495752450757852    342FE4E6A1AF    8/21/2019 08:49:08  8/25/2019 23:59:59
        3   100000000002195 8495752450757852    343FE4E6A1AF    8/27/2019 02:42:46  8/26/2019 23:59:59
        4   100000000002195 8495752450757852    344FE4E6A1AF    8/28/2019 06:33:50  8/28/2019 23:59:59
        5   100000000002195 8495752450757852    345FE4E6A1AF    8/30/2019 02:35:32  8/31/2019 23:59:59
        6   100000000002195 8495752450757852    346FE4E6A1AF    9/2/2019 00:25:05   9/1/2019 23:59:59
        7   100000000002195 8495752450757852    347FE4E6A1AF    9/3/2019 03:33:28   9/3/2019 23:59:59
        8   100000000002195 8495752450757852    348FE4E6A1AF    9/4/2019 18:35:45   9/8/2019 23:59:59
        9   100000000002195 8495752450757852    349FE4E6A1AF    9/10/2019 11:22:54  3/16/2020 23:59:59

Output      

            ACCOUNT_ID  EXT_REF_NO  SERIAL_NUM  RECORD_START_DT RECORD_END_DT           
        1   100000000002195 8495752450757852    341FE4E6A1AF    8/13/2019 12:24:42  8/26/2019 23:59:59
        2   100000000002195 8495752450757852    342FE4E6A1AF    8/28/2019 06:33:50  8/28/2019 23:59:59
        3   100000000002195 8495752450757852    343FE4E6A1AF    8/30/2019 02:35:32  9/1/2019 23:59:59
        4   100000000002195 8495752450757852    345FE4E6A1AF    9/3/2019 03:33:28   9/8/2019 23:59:59
        5   100000000002195 8495752450757852    346FE4E6A1AF    9/10/2019 11:22:54  3/16/2020 23:59:59

  1. RECORD_END_DT всегда должно быть больше RECORD_START_DT

  2. Мы будем рассматривать только те записи для сворачивания, где Record_start_dt текущей строки = (RECORD_END_DT + 1 день) для предыдущей строки, если разница больше 1 дня, она не будет учитываться.

  3. Вы можете найти нарушение пункта 1 для строк №-3 и 6, на самом деле это была ошибка для записей с истекшим сроком в тот же день, когда выполнялся ввод данных, вы можете фактически считать RECORD_START_DT как 8/26/2019 00:00:00 и 9/2 / 2019 00:00:00 соответственно для строки № 3 и 6 для расчета

  4. ACCOUNT_ID, EXT_REF_NO, SERIAL_NUM все 3 должны быть рассмотрены для разделения

Я пробовал что-то вроде ниже. Получение только одной выходной строки с минимальным значением DEVICE_START_DATE и максимальным DEVICE_END_DATE, как показано ниже:

ACCOUNT_ID EXT_REF SERIAL_NUM DEVICE_START_DATE DEVICE_END_DATE 100000000002195 8495752450757852 341FE4E6A1AF 13.08.2017 12: 24: 42.000000 16.09.2017 23: 59: 59.000000

 Query: SELECT 
      ACCOUNT_ID,
      EXT_REF, 
      SERIAL_NUM, 
      CASE WHEN (B.DIFF_DAYS <= 1 OR B.DIFF_DAYS IS NULL) THEN
      min(DEVICE_START_DATE) 
      OVER (PARTITION BY ACCOUNT_ID,EXT_REF,SERIAL_NUM order by 
      DEVICE_END_DATE desc)
      WHEN (B.DIFF_DAYS > 1 ) THEN
      min(DEVICE_START_DATE) 
      OVER (PARTITION BY ACCOUNT_ID,EXT_REF,SERIAL_NUM order by 
      DEVICE_END_DATE desc) 
      END AS DEVICE_START_DATE,
      DEVICE_END_DATE
      FROM
      (SELECT A.ACCOUNT_ID,
      A.EXT_REF, 
      A.SERIAL_NUM, 
      A.DEVICE_START_DATE, 
    A.DEVICE_START_DATE_VIRTUAL,
    A.DEVICE_END_DATE, 
    MIN(A.DEVICE_END_DATE)
    OVER ( PARTITION BY A.ACCOUNT_ID,A.EXT_REF,A.SERIAL_NUM ORDER BY A.DEVICE_END_DATE
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS DEVICE_END_DATE_PREVIOUS_ROW,
            TRUNC(A.DEVICE_START_DATE_VIRTUAL) - TRUNC(DEVICE_END_DATE_PREVIOUS_ROW) AS DIFF_DAYS
    FROM
    (SELECT 
    ACCOUNT_ID, 
    EXT_REF, 
    SERIAL_NUM, 
    DEVICE_START_DATE, 
    CASE WHEN DEVICE_START_DATE > DEVICE_END_DATE  
    THEN (DEVICE_START_DATE - INTERVAL '1' DAY)   
    ELSE DEVICE_START_DATE END AS DEVICE_START_DATE_VIRTUAL,
    DEVICE_END_DATE
    FROM NDW_XH_TEMP_TABLES.TEST) A) B
    QUALIFY
    ROW_NUMBER() 
    OVER (PARTITION BY ACCOUNT_ID,EXT_REF,SERIAL_NUM order by DEVICE_END_DATE desc) = 1;

sql rollup olap teradata teradata-sql-assistant
person Debasis Das    schedule 19.05.2020    source источник
comment
Эти SERIAL_NUM являются уникальными значениями, поэтому они не могут учитываться при вычислении дубликатов. Вы хотите сохранить даты нарушения или их нужно исправить? Если вас не волнует временной отрезок, можно ли вместо этого переключиться на DATE?   -  person dnoeth    schedule 19.05.2020
comment
Привет, Дитер, вы можете учитывать фактические даты начала вместо виртуального нарушения дат начала (строки 3 и 6) при написании запроса, что-то вроде (CASE WHEN RECORD_START_DT ›RECORD_END_DT THEN RECORD_START_DT = (RECORD_START_DT - INTERVAL '1' DAY)). RECORD_START_DT и RECORD_END_DT - поля отметки времени (6) в таблице, ACCOUNT_ID - целое число, остальные - VARCHAR. Вы можете переключиться на DATE, если хотите. Спасибо.   -  person Debasis Das    schedule 19.05.2020
comment
Мы не можем изменить источник, так как мы не владеем таблицей, но позже мы можем поделиться одноразовым обновлением с командой источника, чтобы изменить такие ошибочные даты начала записи.   -  person Debasis Das    schedule 19.05.2020

Ответы (1)


arrow_upward
0
arrow_downward

Вам нужны вложенные OLAP-функции, все должно работать как положено:

SELECT 
   ACCOUNT_ID
  ,EXT_REF_NO
  ,SERIAL_NUM
  ,Coalesce(Lag(next_start)
            Over (PARTITION BY ACCOUNT_ID, EXT_REF_NO
                  ORDER BY next_start NULLS LAST)
           ,min_start) AS RECORD_START_DT

-- If your Teradata version doesn't support LAG/LEAD you must switch to the MAX version
--  ,Coalesce(Max(next_start)
--            Over (PARTITION BY ACCOUNT_ID, EXT_REF_NO
--                  ORDER BY next_start NULLS LAST
--                  ROWS BETWEEN 1 Preceding AND 1 Preceding)
--           ,min_start) AS RECORD_START_DT
  ,RECORD_END_DT  
FROM
 (
   SELECT
      ACCOUNT_ID
     ,EXT_REF_NO
     ,SERIAL_NUM
     ,RECORD_START_DT
     ,RECORD_END_DT

     -- to check for a gap
     ,Lag(fixed_start)
      Over (PARTITION BY ACCOUNT_ID, EXT_REF_NO
            ORDER BY fixed_start DESC) AS next_start
--     ,Max(fixed_start)
--      Over (PARTITION BY ACCOUNT_ID, EXT_REF_NO
--            ORDER BY fixed_start DESC
--            ROWS BETWEEN 1 Preceding AND 1 Preceding) AS next_start

     -- used in the outer COALESCE to get the min start for the 1st group
     ,Min(RECORD_START_DT)
      Over (PARTITION BY ACCOUNT_ID, EXT_REF_NO) AS min_start

     -- gap detection
     ,CASE WHEN Cast(RECORD_END_DT AS DATE) + 1 = Cast(next_start AS DATE) THEN 0 ELSE 1 END AS flag
   FROM
    ( -- fixing the bad data first
      SELECT t.*
        ,CASE WHEN RECORD_START_DT > RECORD_END_DT THEN RECORD_START_DT - INTERVAL '1' DAY ELSE RECORD_START_DT END AS fixed_start
      FROM tab AS t
    ) AS fixed_data
   QUALIFY flag = 1
 ) AS dt

Это ищет пробелы, после применения flag = 1 текущая строка получила максимальную дату окончания, а предыдущая строка - совпадающую дату начала. Внешний Select, наконец, добавляет эту дату начала в текущую строку.

person dnoeth    schedule 19.05.2020