Найти уникальную запись из таблицы

Поскольку вам кажется, что вы хотите сохранить AD_ID, это немного сложнее, чем простые GROUP BY и UNION.

Вам понадобятся индикаторы, которые AD_DATE для AD_STATUS «ВХОД» являются первыми, а какие AD_DATE для AD_STATUS «ВЫХОД» являются последними за день.

Мы можем сделать эти индикаторы такими, используя ROW_NUMBER() OVER (PARTITION BY .. ORDER BY ..). Для разделения по дням используйте функцию DATE(), чтобы игнорировать время.

После этого вы можете выбрать соответствующие записи IN и OUT за день, а также UNION их и, возможно, отсортировать их по USER_AD, DATE(AD_DATE) и AD_STATUS.

Я буду использовать некоторые cte, чтобы показать эти шаги:

WITH status_per_day AS (
  SELECT 
    records.*,
    ROW_NUMBER() OVER (
      PARTITION BY AD_USER_ID, AD_STATUS, DATE(AD_DATE) 
      ORDER BY AD_DATE ASC) AS first_time,  
    ROW_NUMBER() OVER (
      PARTITION BY AD_USER_ID, AD_STATUS, DATE(AD_DATE) 
      ORDER BY AD_DATE DESC) AS last_time
  FROM records
), 

unioned_statuses AS (
  SELECT *
  FROM status_per_day
  WHERE AD_STATUS = 'IN' 
  AND first_time = 1
  UNION 
  SELECT *
  FROM status_per_day
  WHERE AD_STATUS = 'OUT' 
  AND last_time = 1
)
  
SELECT AD_ID, AD_USER_ID, AD_STATUS, AD_DATE
FROM unioned_statuses
ORDER BY AD_USER_ID, DATE(AD_DATE), AD_STATUS

db<>поиграйте здесь

Это будет немного более эффективно, чем другой ответ, если вы сохраните одни и те же предложения ORDER BY в двух оконных функциях. Вы можете взломать LEAD, чтобы узнать, находитесь ли вы в последней строке.

WITH status_per_day AS (
  SELECT 
    records.*,
    ROW_NUMBER() OVER (
      PARTITION BY AD_USER_ID, DATE(AD_DATE), AD_STATUS 
      ORDER BY AD_DATE) AS rn,  
    LEAD(1) OVER (
      PARTITION BY AD_USER_ID, DATE(AD_DATE), AD_STATUS
      ORDER BY AD_DATE) AS is_not_last,
    MIN(CASE WHEN AD_STATUS = 'IN' THEN AD_DATE END) OVER (
      PARTITION BY AD_USER_ID, DATE(AD_DATE)) AS min_in_date
  FROM records
)  
SELECT AD_ID, AD_USER_ID, AD_STATUS, AD_DATE
FROM status_per_day
WHERE (
  AD_STATUS = 'IN' AND rn = 1
  OR
  AD_STATUS = 'OUT' AND is_not_last IS NULL AND min_in_date < AD_DATE
)
ORDER BY
  AD_USER_ID,
  DATE(AD_DATE),
  AD_STATUS;

db<>рабочий пример

Это причина головной боли.

Есть запись, где сначала out, а потом in время.

Если вы запустите этот раздел:

WITH t1 AS (
select *,
    CASE WHEN(ad_status = 'IN') THEN
        MIN(ad_date) OVER (PARTITION BY cast(ad_date as date), ad_user_id, ad_status = 'IN')
    END AS MIN_IN,
    CASE WHEN(ad_status = 'OUT') THEN
        MAX(ad_date) OVER (PARTITION BY cast(ad_date as date), ad_user_id, ad_status = 'OUT')
    END AS MAX_OUT
from IN_OUT_RECORD
),
base_table as (
    SELECT * FROM t1
    WHERE (ad_status = 'IN' AND ad_date = MIN_IN)
    OR (ad_status = 'OUT' AND ad_date = MAX_OUT)
)
select * from base_table order by cast(ad_date as date);

Вы получаете вывод вроде:

Итак, та же проблема возникает и здесь.

По этой причине
    сначала вам нужно разделить эту таблицу на две части
    а затем вам нужно сравнить время entry и exit.

Итак, ваш следующий код:

base_table2 as (
select bin.ad_user_id,
    bin.ad_date,
    bin.ad_status as in_status, 
    bout.ad_status as out_status,
   --  bin.MIN_IN,
   --  bout.MAX_OUT,
    CASE 
        WHEN(LEAST(bin.MIN_IN, bout.MAX_OUT) = bin.MIN_IN) THEN bin.MIN_IN
        WHEN(GREATEST(bin.MIN_IN, bout.MAX_OUT) = bin.MIN_IN) THEN bin.MIN_IN
    END as entry_time,
    CASE 
        WHEN(GREATEST(bin.MIN_IN, bout.MAX_OUT) = bout.MAX_OUT) THEN bout.MAX_OUT 
        WHEN(LEAST(bin.MIN_IN, bout.MAX_OUT) = bout.MAX_OUT) THEN NULL
    END as exit_time
from       (select * from base_table where ad_status = 'IN')  bin
left join  (select * from base_table where ad_status = 'OUT') bout
on bin.ad_user_id = bout.ad_user_id
AND cast(bin.ad_date as date) = cast(bout.ad_date as date)
)
select * from base_table2

Что дает вам результат:

Затем сравните эту таблицу с базовой таблицей и запросом:

select c1.*
from IN_OUT_RECORD c1
INNER JOIN base_table2 c2
on c1.ad_user_id = c1.ad_user_id
and ( 
   (c1.ad_status = 'IN' and c1.ad_date = c2.entry_time)
   OR
   (c1.ad_status = 'OUT' and c1.ad_date = c2.exit_time)
)
order by c1.ad_date;

Что дает итоговую таблицу в соответствии с вашими результатами:

Это код: db<>fiddle

Но есть несколько возможных сценариев, которые могут произойти следующим образом:

• entry time существует, но нет exit time
• нет entry time но exit time существует

Здесь это не подойдет.
Если вы хотите применить эту логику, обновите создание base_table2 соответствующим образом.

Это хороший вопрос, уделите немного времени на его решение.
Я надеюсь, что это разрешит запрос в соответствии с описанием.

другое решение может быть

1. сначала найдите минимальные и максимальные значения на дату:

SELECT 
    r1.AD_USER_ID, 
    DATE(r1.AD_DATE) d,
    MIN(CASE WHEN r1.AD_STATUS='IN' THEN AD_DATE END) as min_date,
    MAX(CASE WHEN r1.AD_STATUS='OUT' THEN AD_DATE END) as max_date
  FROM records r1
  WHERE r1.AD_USER_ID = 'ST1301220007' 
  GROUP BY r1.AD_USER_ID, DATE(r1.AD_DATE)

2. Затем выполните UNION ALL, чтобы найти нужные записи:

WITH cte AS (
  SELECT 
    r1.AD_USER_ID, 
    DATE(r1.AD_DATE) d,
    MIN(CASE WHEN r1.AD_STATUS='IN' THEN AD_DATE END) as min_date,
    MAX(CASE WHEN r1.AD_STATUS='OUT' THEN AD_DATE END) as max_date
  FROM records r1
  WHERE r1.AD_USER_ID = 'ST1301220007' 
  GROUP BY r1.AD_USER_ID, DATE(r1.AD_DATE)
)
SELECT r1.AD_ID, r1.AD_USER_ID, r1.AD_STATUS, r1.AD_DATE
FROM records r1
INNER JOIN cte ON cte.AD_USER_ID = r1.AD_USER_ID 
              and cte.min_date = r1.AD_DATE
              and r1.AD_STATUS = 'IN'
UNION ALL
SELECT r1.AD_ID, r1.AD_USER_ID, r1.AD_STATUS, r1.AD_DATE
FROM records r1
INNER JOIN cte ON cte.AD_USER_ID = r1.AD_USER_ID 
              and cte.max_date = r1.AD_DATE and cte.max_date > cte.min_date
              and r1.AD_STATUS = 'OUT'
ORDER BY AD_ID

выход:

DBFIDDLE: https://dbfiddle.uk/q2o-QFZO