我已經更改了問題以使其更易于理解
我創建了一個查詢以獲取以下帶有日期的輸出：
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
案件
- 如果 person_number 12 的值從 5.5 變為 0，那么這兩行都應該出現，并且最新的 base_element_name 應該添加“~”。
- 如果只有一次員工的健康標記為結束日期，則應僅顯示該行。
- 如果日期范圍內的值沒有變化，那么最新的行應該是 example-11
- 對于 person#14 和 15 ，第一個有效開始日期應該是“最新”更改或最后更改值的第一次出現有效開始日期
uj5u.com熱心網友回復：
只需對您的查詢進行排名以顯示 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)
uj5u.com熱心網友回復：
從 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;
（其中包括第 16 個人，其中effective_start_date第一行的 位于范圍開始之前，但更改發生在范圍內。）
輸出：
PERSON_NUMBER BASE_ELEMENT_NAME 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<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/373085.html
標籤：sql 甲骨文 oracle-sqldeveloper