Блог

Главная - Вопросы по программированию - SQL-запрос для поиска первого события изменения даты

SQL-запрос для поиска первого события изменения даты

#sql #oracle #oracle-sqldeveloper

#sql #Oracle #oracle-sqldeveloper

Вопрос:

Я изменил вопрос, чтобы сделать его более понятным

Я создал запрос, чтобы получить следующий вывод с указанием даты:

 PERSON_nUMBER  base_element_name  effective_start_date  effective_end_date  Value   
           11  Health             2021-11-21            4712-12-31            5.5
           11  Health             2021-10-18            2021-11-20            5.5
           12  Health             2021-11-11            4712-12-31              0
           12  Health             2021-11-01            2021-11-10            5.5
           13  Health             2021-11-01            2021-11-09            6
           
           14 Health              2021-11-10            2021-11-19            6
           14  Health             2021-11-20            2021-11-30            6
           14  Health             2021-12-01            2021-12-05            5
           14  Health             2021-12-06            4712-12-31            5
           
           
           15  Health             2021-11-10           2021-11-19           6
           15  Health             2021-11-20            2021-11-30           6
           15  Health             2021-12-01            2021-12-05           6
           15  Health              2021-12-06            2021-12-15           5
           15  Health              2021-12-16            4712-12-31           5

Я передаю параметр как 01-11-2021 и 30-11-2021

 select 
    petf.person_number
    petf.base_element_name,
    to_char(peef.effective_start_date,'YYYY-MM-DD') effective_start_date ,
     to_char(peef.effective_end_date,'YYYY-MM-DD') effective_end_date,
     pivf.value  
    from pay_element_types_f petf ,
    pay_input_values_f pivf,
    per_all_elementries_f peef
    where pivf.element_type_id = petf.element_type_id
    AND petf.base_element_name in ('Health')
    and peef.element_entry_id= pivf.element_entry_id
    and peef.effective_start_date between :from_date and :to_date

Как я могу настроить приведенный выше запрос таким образом, чтобы при изменении столбца значений, например person_number 12, на выходе должны появиться две строки; и если в двух строках одного и того же сотрудника, например person_number 11, изменений нет, тогда следует выбрать только последнюю строку.

Ожидаемый результат —

 PERSON_nUMBER  base_element_name  effective_start_date    Value   
           11  Health             2021-11-21               5.5
           12  ~Health            2021-11-11               0
           12  Health             2021-11-01              5.5
           13  Health             2021-11-01               6
           14  Health             2021-11-10               6 
           14  ~Health             2021-12-01               5
           15  Health             2021-11-10               6
           15  ~Health             2021-12-06               5

Случай

  1. если значение person_number 12 изменилось с 5.5 на 0, то должны быть указаны обе строки, и в последнем из них base_element_name должно быть добавлено «~».
  2. Если состояние здоровья помечено для сотрудника только один раз и является датой окончания, то должна отображаться только эта строка.
  3. Если значение в диапазоне дат не изменилось, то должна быть указана последняя строка, например-11
  4. Для person # 14 и 15 должна быть указана первая эффективная дата начала и первая эффективная дата начала «последнего» изменения или последнего измененного значения

Ответ №1:

Просто оцените свой запрос, чтобы показать 2 последние даты, затем добавьте ~ ко 2-й последней дате. Надеюсь, это может помочь.

 WITH CTE AS(
select 
    petf.person_number
    petf.base_element_name,
    to_char(peef.effective_start_date,'YYYY-MM-DD') effective_start_date ,
     to_char(peef.effective_end_date,'YYYY-MM-DD') effective_end_date,
     pivf.value  
    ,RANK() OVER(PARTITION BY petf.person_number ORDER BY peef.effective_start_date desc) as my_rank --Add Rank  to Get the 2 latest date.
    from pay_element_types_f petf ,
    pay_input_values_f pivf,
    per_all_elementries_f peef
    where pivf.element_type_id = petf.element_type_id
    AND petf.base_element_name in ('Health')
    and peef.element_entry_id= pivf.element_entry_id
    and peef.effective_start_date between :from_date and :to_date
)

SELECT 
person_number
,CASE WHEN my_rank = 2 THEN CONCAT('~',base_element_name) ELSE base_element_name END as base_element_name
,effective_start_date
,Value
FROM CTE
WHERE my_rank in (1,2)

Комментарии:

1. Это только для # 1? Что происходит, когда подобный случай похож на # 4 … тогда этот ранг не будет работать правильно?

2. Извините. Я не смог полностью прочитать # 4. Но у вас может быть 2 ранга. 1 для даты по убыванию, а другой для ДАТЫ по возрастанию. Для возрастания получите РАНГ 1 в качестве начальной даты для убывания, получите РАНГ 1 в качестве вашей Latest_Date.

3. Хотя это дает мне последнюю дату, но не проверяет, есть ли изменение значения. Если есть только изменение, тогда нам нужно добавить ~

Ответ №2:

Начиная с Oracle 12, вы можете использовать MATCH_RECOGNIZE :

 SELECT person_number,
       CASE cls WHEN 'CHANGED' THEN '~' END || base_element_name
         AS base_element_name,
       effective_start_date,
       effective_end_date,
       value
FROM   (
  SELECT *
  FROM   your_query
  -- Without date filter
)
MATCH_RECOGNIZE (
  PARTITION BY person_number
  ORDER     BY effective_start_date
  MEASURES
    CLASSIFIER() AS cls
  ALL ROWS PER MATCH
  PATTERN (first_row (changed | {- outside_range -} | $))
  DEFINE
   first_row     AS effective_start_date <  :to_date   INTERVAL '1' DAY
                AND effective_end_date   >= :from_date,
   changed       AS value != first_row.value,
   outside_range AS effective_start_date >= :to_date   INTERVAL '1' DAY
);

Что для образца данных:

 CREATE TABLE your_query (
  PERSON_NUMBER,
  base_element_name,
  effective_start_date,
  effective_end_date,
  Value
) AS
SELECT 11, 'Health', DATE '2021-11-21', DATE '4712-12-31', 5.5 FROM DUAL UNION ALL
SELECT 11, 'Health', DATE '2021-10-18', DATE '2021-11-20', 5.5 FROM DUAL UNION ALL
SELECT 12, 'Health', DATE '2021-11-11', DATE '4712-12-31', 0.0 FROM DUAL UNION ALL
SELECT 12, 'Health', DATE '2021-11-01', DATE '2021-11-10', 5.5 FROM DUAL UNION ALL
SELECT 13, 'Health', DATE '2021-11-01', DATE '2021-11-09', 6.0 FROM DUAL UNION ALL
SELECT 14, 'Health', DATE '2021-11-10', DATE '2021-11-19', 6.0 FROM DUAL UNION ALL
SELECT 14, 'Health', DATE '2021-11-20', DATE '2021-11-30', 6.0 FROM DUAL UNION ALL
SELECT 14, 'Health', DATE '2021-12-01', DATE '2021-12-05', 5.0 FROM DUAL UNION ALL
SELECT 14, 'Health', DATE '2021-12-06', DATE '4712-12-31', 5.0 FROM DUAL UNION ALL
SELECT 15, 'Health', DATE '2021-11-10', DATE '2021-11-19', 6.0 FROM DUAL UNION ALL
SELECT 15, 'Health', DATE '2021-11-20', DATE '2021-11-30', 6.0 FROM DUAL UNION ALL
SELECT 15, 'Health', DATE '2021-12-01', DATE '2021-12-05', 6.0 FROM DUAL UNION ALL
SELECT 15, 'Health', DATE '2021-12-06', DATE '2021-12-15', 5.0 FROM DUAL UNION ALL
SELECT 15, 'Health', DATE '2021-12-16', DATE '4712-12-31', 5.0 FROM DUAL UNION ALL
SELECT 16, 'Health', DATE '2021-10-16', DATE '2021-11-15', 4.0 FROM DUAL UNION ALL
SELECT 16, 'Health', DATE '2021-11-16', DATE '4712-12-31', 5.0 FROM DUAL;

(Который включает person # 16, где effective_start_date первая строка находится перед началом диапазона, но изменение происходит в пределах диапазона.)

Выводит:

PERSON_NUMBER ИМЯ_ЭЛЕМЕНТА EFFECTIVE_START_DATE EFFECTIVE_END_DATE ЗНАЧЕНИЕ
11 Здравоохранение 2021-11-21 00:00:00 4712-12-31 00:00:00 5.5
12 Здравоохранение 2021-11-01 00:00:00 2021-11-10 00:00:00 5.5
12 ~ Работоспособность 2021-11-11 00:00:00 4712-12-31 00:00:00 0
13 Здравоохранение 2021-11-01 00:00:00 2021-11-09 00:00:00 6
14 Здравоохранение 2021-11-20 00:00:00 2021-11-30 00:00:00 6
14 ~ Работоспособность 2021-12-01 00:00:00 2021-12-05 00:00:00 5
15 Здравоохранение 2021-11-20 00:00:00 2021-11-30 00:00:00 6
16 Здравоохранение 2021-10-16 00:00:00 2021-11-15 00:00:00 4
16 ~ Работоспособность 2021-11-16 00:00:00 4712-12-31 00:00:00 5

db<>скрипка здесь

Метки: