oracle查询交易链

查找某天的交易链，建立二叉树

create or replace procedure sp_mid_game_trade_list is
/******************************************************************
      存储过程名称：
      存储过程内容：计算某天某个DB的交易链
      作者姓名: 
      编写时间: 2017年11月2日
      修改；            

      输入参数：
              v_date  --统计日期 （yyyy-mm-dd）
      输出参数:
      算法描述：
              
    *******************************************************************/
  cursor cur_1 is
    select distinct dbname, sell_roleid
      from game_empty_trad_list;
begin
  for rec_1 in cur_1 loop
    insert into game_TRAD_CONNECT_UN_list
      select /*+parallel(8)*/
       dt, LEVEL, rec_1.dbname, sell_roleid, buy_roleid, rec_1.sell_roleid, 0
        from game_empty_trad_list a
       start with sell_roleid = rec_1.sell_roleid
      -- AND dbname = rec_1.dbname
      connect by NOCYCLE prior buy_roleid = sell_roleid -- AND PRIOR dbname=dbname
      ;
    commit;
  
  ----return; -- 测试，只执行一个号
  end loop;
exception
  when others then
    rollback;
    
end sp_mid_game_trade_list;

 

create or replace procedure SP_game_TRAD_CONNECT_UN_LIST
as
/******************************************************************
      存储过程名称：
      存储过程内容：计算某天某个DB的交易链
      作者姓名: 
      编写时间: 2017年11月6日
      修改；            

      输入参数：
              v_date  --统计日期 （yyyy-mm-dd）
      输出参数:
      算法描述：
              
    *******************************************************************/
v_begin_date date;
v_db number;
v_date date;

cursor cur_db is 
         select distinct dbname from game_empty_trad_emoney_un 
         where record_date >= v_date-7;  -- 取7天内的DB         
         
         
begin

     v_date := trunc(sysdate)-1;
     --DATE'2017-10-18'; -- 开服第一天
      -- 

  
  dbms_output.put_line(v_date);
  
  --1、每条交易卖家 买家
--- 耗时 444
-- CREATE TABLE game_trad_sell_buy AS
delete from game_trad_sell_buy;
commit;

insert into game_trad_sell_buy
SELECT /*+parallel(8)*/ a.record_date,a.dbname,a.col_3,a.role_id sell_roleid,b.role_id buy_roleid,a.trad_num sell_num,b.trad_num buy_num
FROM
(
  SELECT row_number()OVER(PARTITION BY col_3 ORDER BY trad_num DESC)trad_rank,t.*
  FROM 
  (
    SELECT record_date,dbname,col_3,role_id,SUM(col_5+col_6+col_7+col_8)trad_num,SUM(col_6)trad_emoney_num
    FROM gameb_ods.game_bigtable_log a
    WHERE actiontype=21
          AND record_date<trunc(sysdate)  --当前日期
          and record_date>=v_date -- 取一周的数据
          -- to_date('2017-10-18 00:00:00','YYYY-MM-DD hh24:mi:SS') --游戏公测日期
    GROUP BY record_date,dbname,col_3,role_id
  )t
)a -- sell:trad_rank=1
LEFT JOIN 
(
  SELECT row_number()OVER(PARTITION BY col_3 ORDER BY trad_num DESC)trad_rank,t.*
  FROM 
  (
    SELECT col_3,role_id,SUM(col_5+col_6+col_7+col_8)trad_num,SUM(col_6)trad_emoney_num
    FROM gameb_ods.game_bigtable_log a
    WHERE actiontype=21
          AND record_date<trunc(sysdate)   --当前日期
          and record_date>=v_date -- 取一周的数据
    GROUP BY col_3,role_id

  )t
)b -- buy:trad_rank=2
ON a.col_3=b.col_3 AND a.trad_rank=b.trad_rank-1
WHERE a.trad_rank=1;
commit;

-----------------------------
--2、玩家交易魔石明细

delete from game_trad_sell_emoney;
commit;

insert into game_trad_sell_emoney
SELECT /*+parallel(8)*/ col_3,role_id,SUM(col_6)trad_emoney
FROM gameb_ods.game_bigtable_log a
WHERE actiontype=21
      AND record_date<trunc(sysdate) --当前日期
      and record_date>=v_date -- 取一周的数据
GROUP BY col_3,role_id;
commit;


--3、玩家空交易明细
delete from game_empty_trad;
commit;

insert into game_empty_trad
SELECT t.*,CASE WHEN sell_emoney=0 THEN 'item_empty'  WHEN sell_emoney=sell_num THEN 'emoney_empty' ELSE 'ALL_empty' END AS empty_type
FROM
(
  SELECT a.*,b.trad_emoney sell_emoney 
  FROM
  (
    SELECT *
    FROM game_trad_sell_buy
    WHERE buy_num=0
  )a
  LEFT JOIN 
  game_trad_sell_emoney b
  ON a.col_3=b.col_3 AND a.sell_roleid=b.role_id
)t;
commit;

--3.1、玩家魔石空交易明细
delete from game_empty_trad_emoney;
commit;

insert into game_empty_trad_emoney
SELECT *
from game_empty_trad
WHERE empty_type!='item_empty';
commit;



--4、异常玩家
--4.1、玩家角色表
--4.1.1、角色表
delete from game_user;
commit;

insert into game_user
    SELECT /*+parallel(8)*/ record_date,dbname,account_id,role_id,r_level,battle_lev,vip_lev,emoney emoney_save
    FROM
    (
    SELECT t.*,row_number()OVER(PARTITION BY dbname,account_id,role_id ORDER BY last_login_time DESC)save_rank
    FROM gameb_ods.game_user t
    WHERE record_date< trunc(sysdate)      --当前日期
          and record_date>=v_date -- 取一周的数据
    )t;
commit;

--4.1.2、角色消耗表  
--4.1.2.1、导出消耗表
delete from game_action15;
commit;

INSERT INTO game_action15
    SELECT /*+parallel(8)*/ *
    from gameB_ODS.game_BIGTABLE_LOG t
    WHERE record_date<trunc(sysdate)  --当前日期
    and record_date>=v_date -- 取一周的数据
    AND actiontype=15;
COMMIT;


--4.1.2.2、生成角色消耗表

delete from game_user_emoneycost;
commit;

INSERT INTO game_user_emoneycost
    SELECT /*+parallel(8)*/ dbname,account_id,role_id,SUM(col_4)emoney_cost
    from game_action15 t
    WHERE record_date<trunc(sysdate)  --当前日期
    and record_date>=v_date -- 取一周的数据
    GROUP BY dbname,account_id,role_id;
COMMIT;


--4.1.3、玩家角色表  
delete from game_user_info;
COMMIT;

INSERT INTO game_user_info
SELECT /*+parallel(8)*/ a.*,b.emoney_cost
FROM game_user a
LEFT JOIN game_user_emoneycost b
ON a.dbname=b.dbname AND a.role_id=b.role_id;
COMMIT;


--4.2、异常玩家定义
--低消耗低等级：等级低于79级，魔石存点大于10000，且魔石无消耗或魔石消耗率低（累计消耗魔石/魔石存点<0.1）
--高额空交易：单日累计空交易大于10万；
--大量空交易：空交易次数大于2或者单笔空交易大于10000

--------------------------
-- 删除异常数据
delete 
  FROM  game_user_info  t
  where t.account_id is null
  AND record_date>=v_date;
  
  commit;
--------------------------

--4.2.1、空交易次数玩家

delete from game_empty_trad_roleidcnt;
COMMIT;

INSERT INTO game_empty_trad_roleidcnt
  SELECT /*+parallel(8)*/ dbname,roleid,COUNT(1)empty_cnt,SUM(sell_emoney)sell_emoney
  FROM
  (
    SELECT dbname,sell_roleid roleid,sell_emoney
    from game_empty_trad
    WHERE empty_type!='item_empty'
  )t
  GROUP BY dbname,roleid;
COMMIT;



--4.2.3、低消耗用户
delete from game_user_info_type_tmp;
COMMIT;

INSERT INTO game_user_info_type_tmp 
SELECT /*+parallel(8)*/ a.*,'异常玩家'user_type,'低消耗低等级' reason_type
FROM
(
  SELECT t.*,
  (case emoney_save 
    when '0' then 100.0
      else emoney_cost*1.0/emoney_save 
        end) as cost_pec
  FROM  game_user_info  t
  where t.record_date = trunc(sysdate)-1  -- 昨天的日志
)a
WHERE r_level<79 AND round(emoney_save)>9999 AND cost_pec<0.1;
COMMIT;



--4.2.4、单日累计空交易大于10万

INSERT INTO game_user_info_type_tmp 
SELECT record_date,dbname,account_id,role_id,r_level,battle_lev,vip_lev,emoney_save,emoney_cost,(emoney_cost+1)*1.0/(emoney_save+1) cost_pec,'异常玩家'user_tpye ,'高额空交易' reason_type
FROM
(
  SELECT /*+parallel(8)*/ a.*,b.roleid type_role,c.user_type
  FROM game_user_info  a
  LEFT JOIN 
  (SELECT dbname,roleid FROM game_empty_trad_roleidcnt WHERE  sell_emoney>=100000 ) b
  ON a.dbname=b.dbname AND a.role_id=b.roleid
  LEFT JOIN  game_user_info_type_tmp  c
  ON a.dbname=c.dbname AND a.role_id=c.role_id
  where a.record_date = trunc(sysdate)-1
)
WHERE type_role IS NOT NULL AND user_type IS  NULL;
commit;



--4.2.5、大量空交易

INSERT INTO game_user_info_type_tmp 
SELECT /*+parallel(8)*/ record_date,dbname,account_id,role_id,r_level,battle_lev,vip_lev,emoney_save,emoney_cost,(emoney_cost+1)*1.0/(emoney_save+1) cost_pec,'疑似作弊'user_tpye ,'大量空交易' reason_type
FROM
(
  SELECT a.*,b.roleid type_role,c.user_type
  FROM game_user_info  a
  LEFT JOIN 
  (SELECT dbname,roleid FROM game_empty_trad_roleidcnt WHERE empty_cnt>=2 OR (empty_cnt=1 AND sell_emoney>=10000 )) b
  ON a.dbname=b.dbname AND a.role_id=b.roleid
  LEFT JOIN  game_user_info_type_tmp  c
  ON a.dbname=c.dbname AND a.role_id=c.role_id
  where a.record_date = trunc(sysdate)-1
)
WHERE type_role IS NOT NULL AND user_type IS  NULL;
commit;


--4.2.6、存档丢失问题解决--高额空交易

INSERT INTO game_user_info_type_tmp 
SELECT /*+parallel(8)*/ record_date,dbname,account_id,roleid role_id,r_level,battle_lev,vip_lev,emoney_save,emoney_cost,(emoney_cost+1)*1.0/(emoney_save+1) cost_pec,'疑似作弊'user_tpye,'高额空交易' reason_type
FROM
(
  SELECT a.*,c.record_date,account_id,r_level,battle_lev,vip_lev,emoney_save,emoney_cost,cost_pec,USER_TYPE
  from
  (
    SELECT * 
    FROM game_empty_trad_roleidcnt 
    WHERE  sell_emoney>=100000 
  )a
  LEFT JOIN  game_user_info_type_tmp  c
  ON a.dbname=c.dbname AND a.roleid=c.role_id
)t
WHERE user_type IS NULL;
commit;

--4.2.7、存档丢失问题解决--大量空交易
INSERT INTO game_user_info_type_tmp 
SELECT /*+parallel(8)*/ record_date,dbname,account_id,roleid role_id,r_level,battle_lev,vip_lev,emoney_save,emoney_cost,(emoney_cost+1)*1.0/(emoney_save+1) cost_pec,'疑似作弊'user_tpye,'大量空交易' reason_type
FROM
(
  SELECT a.*,c.record_date,account_id,r_level,battle_lev,vip_lev,emoney_save,emoney_cost,cost_pec,USER_TYPE
  from
  (
    SELECT * 
    FROM game_empty_trad_roleidcnt 
    WHERE empty_cnt>=2 OR (empty_cnt=1 AND sell_emoney>=10000 )
  )a
  LEFT JOIN  game_user_info_type_tmp  c
  ON a.dbname=c.dbname AND a.roleid=c.role_id
)t
WHERE user_type IS NULL;
commit;

-- 把上面的计算结果去重，之前不想用merge into，是避免效率问题
delete from game_user_info_type;
COMMIT;

INSERT INTO game_user_info_type
select distinct  * from game_user_info_type_tmp;
commit;


--5、异常帐号链条抓取
--5.1、异常帐号相关交易
delete from game_empty_trad_emoney_un;
commit;

INSERT INTO game_empty_trad_emoney_un
SELECT /*+parallel(8)*/ record_date,dbname,col_3,sell_roleid,buy_roleid,sell_num,buy_num,sell_emoney,empty_type,sell_user_type,buy_user_type
FROM
(
  SELECT /*+PARALLEL(8)*/ a.*,b.user_type sell_user_type,c.user_type buy_user_type
  FROM game_empty_trad_emoney a
  LEFT JOIN game_user_info_type  b
  ON a.dbname=b.dbname AND a.sell_roleid=b.role_id
  LEFT JOIN game_user_info_type  c
  ON a.dbname=c.dbname AND a.buy_roleid=c.role_id
)
WHERE sell_user_type IS NOT NULL;
COMMIT;

--5.2、源头帐号
delete from game_trad_base_un;
commit;

INSERT INTO game_trad_base_un
SELECT /*+PARALLEL(8)*/ a.*,b.buy_roleid
FROM 
(
  SELECT DISTINCT dbname,sell_roleid
  FROM game_empty_trad_emoney_un t
)a
LEFT JOIN 
(  
  SELECT DISTINCT dbname,buy_roleid
  FROM game_empty_trad_emoney_un 
)b
ON a.dbname=b.dbname AND a.sell_roleid=b.buy_roleid
WHERE b.buy_roleid IS NULL;
COMMIT;

-- 开始循环计算每天的交易链
 while v_date <= trunc(sysdate) loop
     
   -- 删除关系链结果表特定日期的数据
   delete from game_TRAD_CONNECT_UN_LIST where dt = v_date;
   commit; 
   
   DBMS_OUTPUT.PUT_LINE(sysdate || '  ' || v_date);
   -- 只有某个区服的帐号，减少遍历
      for c_row in cur_db loop    
        --DBMS_OUTPUT.PUT_LINE(sysdate || '  ' || C_ROW.dbname);
        
        -- 清空临时表
        delete from game_empty_trad_list;
        commit; 
        -- 把某天某个服的数据插入临时表
        insert into game_empty_trad_list
          select distinct trunc(t.record_date) as dt,t.dbname, t.sell_roleid, t.buy_roleid
          from game_empty_trad_emoney_un t 
          where t.record_date >= v_date and t.record_date < v_date+1 
          and t.dbname = c_row.dbname;
        commit;
          
          -- 开始计算每天交易链
          sp_mid_game_trade_list;
          
          ----return; -- 测试一天一个DB的数据
       end loop;
       
       -- 标识当天交易链的最后一级
          merge into game_TRAD_CONNECT_UN_LIST t3
            using (
            select base_roleid,max(con_level) max_con_level from game_TRAD_CONNECT_UN_LIST
            where dt = v_date
            group by base_roleid
            ) t2
            on (t3.base_roleid = t2.base_roleid and t3.con_level = max_con_level)
            when matched then
              update set t3.is_max_level = 1;
            commit;
            
        v_date := v_date + 1;
    end loop;

     --- SP_ETL_ERROR_LOGS_PRO('ks_kill_sp_timeout','timeout',to_char(sysdate,'yyyymmdd'),v_txt);

end SP_game_TRAD_CONNECT_UN_LIST;