sql - Oracle analytical function suggestions -
i have metrics tracked on project value keeps increasing time goes by.
sample data:
project | date | metric% --------------------------------- p1 | 05-jan-2015 | 40 p1 | 10-jan-2015 | 50 p1 | 05-feb-2015 | 60 p1 | 01-mar-2015 | 70 p1 | 25-mar-2015 | 80 p1 | 03-apr-2015 | 90
i need month wise effective value below. if there metric value on 1st of month, used else last measured value in previous month used.
project | month start date | effective metric% ------------------------------------------------ p1 | 01-feb-2015 | 50 p1 | 01-mar-2015 | 70 p1 | 01-apr-2015 | 80 p1 | 01-may-2015 | 90
i'm using sql below this:
select sq2.project_id, sq2.mon_start_dt, sq2.metric_value (select sq1.project_id, trunc(sq1.metric_measurement_dt, 'month') mon_start_dt, -- gives date in format 01mmyyyy /* check if metric_measurement_dt 1st day of month (01mmyyyy), if use metric value else use previous metric value */ case when extract(day sq1.metric_measurement_dt) = 1 sq1.metric_current_val else sq1.metric_previous_val end metric_value, ( /* current , previous metric values measurement dates */ select project_id, metric_measurement_dt, row_number() on (partition project_id, extract(month metric_measurement_dt) order metric_measurement_dt) mm_start_row, metric_value metric_current_val, lag(metric_value) on (partition project_id order metric_measurement_dt) metric_previous_val, table t1 ) sq1 sq1.mm_start_row = 1) sq2 sq2.metric_value not null;
the above query not give me row 01-may-2015
. believe because there no measurement has happened in month of may.
can give comments on fixing issue?
here alternative approach. each record, current value , previous value. also, enumerate values within month. then, choose first value in month. if day of month "1", take current value. otherwise, take previous value:
select project_id, trunc(metric_measurement_dt, 'month') mon, (case when extract(day metric_measurement_dt) = 1 metric_value else prev_value end) metric_value (select project_id, metric_measurement_dt, metric_value, row_number() on (partition project_id, extract(month metric_measurement_dt) order metric_measurement_dt) seqnum, lag(metric_value) on (partition project_id order metric_measurement_dt) prev_value table t1 ) t seqnum = 1
Comments
Post a Comment