根据定义的月份和阈值比较并获取插入表中的新数据 [英] Compare and get the new data inserted into table based on month and threshold defined

问题描述

我有FM_TBL表，该表的month_id列是数字数据类型，日期以"YYYYMM"格式存储在其中.

I have FM_TBL table which has month_id column which is number data type and dates are stored in this in format 'YYYYMM'.

我想比较上个月和当月的数据，并以此为基础找出当月插入到FM_TABLE的新行数.

I want to compare the data from previous month and current month and based on this find out the number of new rows inserted into FM_TABLE in the current month.

我只想查看相差不在-10和10之间的行数.

And i want to see only the number of rows for which the difference is not in between -10 and 10.

例如，如果我使用:ROUND (100 * ( (num_rows - num_rows_prev) / num_rows), 2) diff_pct

，如果我可以放下diff_pct not between -10 and 10

那么我只能得到当月新插入的行数以及不在-10到10之间的行数.

then I can get only the number of rows which are newly inserted in current month and number of rows not in between -10 and 10.

有用的链接: [！[在此处输入图片描述] [1]] [1]

[![enter image description here][1]][1]

我在下面的查询中比较上个月和当月的行数，如果FM_TBL表中添加了新行数并且新行数的阈值不在百分比之间，则返回结果- 10和2. [！[在此处输入图片描述] [2]] [2]

I have below query which is comparing the number of rows from previous month and current month and return the result if there is new number of rows adding in FM_TBL table and the threshold of the new number of rows is not in between percentage -10 and 2. [![enter image description here][2]][2]

现在，我想为FM_TBL表创建新查询，该查询应该为我提供从上面查询中可以看到的结果中的DIFF行数.

Now i want to create new query for FM_TBL table which should give me those number of DIFF rows from the result which i can see from above query.

推荐答案

应该选择存在条件的行不在abs(10％)中

should be a select for the rows where exist the condition for not in abs(10% )

select * from  my_table  m
inner join  (
    select SYS_DB_NAME, ENTITY_ID, MONTH_ID
    from my_table 
    where MONTH_ID =  to_char(sysdate, 'YYYY') || lpad( extract(month from sysdate), 2,'0')
    minus  
    select  SYS_DB_NAME, ENTITY_ID, MONTH_ID
    from my_table 
    where MONTH_ID =  to_char(sysdate, 'YYYY') || lpad(( extract(month from sysdate) -1) , 2,'0') 
) T on m.SYS_DB_NAME = t.SYS_DB_NAME 
      AND m.ENTITY_ID = t.ENTITY_ID 
        AND m.MONTH_ID = t.MONTH_ID 
where exists (
   select t1.tot_act_month
         , t2.tot_prev_month
        , abs(100*((t1.tot_act_month -t2.tot_prev_month)/t2.tot_prev_month)) rate 
        from  (
        select count(*) tot_act_month
        from my_table 
        where MONTH_ID =  to_char(sysdate, 'YYYY') || lpad( extract(month from sysdate), 2,'0') 
        ) t1 
        cross join  (
        select count(*) tot_prev_month
        from my_table 
        where MONTH_ID =  to_char(sysdate, 'YYYY') || lpad( extract(month from sysdate), 2,'0') 
        ) t2 
        where abs(100*((t1.tot_act_month -t2.tot_prev_month)/t2.tot_prev_month))  <= 10.0 
)

这篇关于根据定义的月份和阈值比较并获取插入表中的新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！