--每秒交易所成交量
select b.tps, b.l_business_time
from (select count(*) as tps, a.l_business_time
from trade.trealdeal a
group by a.l_business_time) b
where b.tps >= 20
order by b.tps desc;
--每秒交易系统成交回报数量
select b.tps, b.d_datetime
from (select count(*) as tps, a.l_business_time
from trade.trealdeal a
group by a.d_datetime) b
where b.tps >= 20
order by b.tps desc;
--每秒委托数量
select b.tps, b.l_entrust_time
from (select count(*) as tps, a.l_entrust_time
from trade.tentrusts a
group by a.l_entrust_time) b
where b.tps >= 20
order by b.tps desc;
--每秒申报数量
select b.tps, b.l_report_time
from (select count(*) as tps, a.l_report_time
from trade.tentrusts a
group by a.l_report_time) b
where b.tps >= 20
order by b.tps desc;
--成交延时排名
select *
from (select (SUBSTR(b.datetime, 0, 2) - SUBSTR(b.Business_Time, 0, 2)) * 3600 +
(SUBSTR(b.datetime, 3, 2) - SUBSTR(b.Business_Time, 3, 2)) * 60 +
(SUBSTR(b.datetime, 5, 2) - SUBSTR(b.Business_Time, 5, 2)) as delat,
b.*
from (select to_char(a.d_datetime, 'hh24miss') as datetime,
lpad(a.l_Business_Time, 6, '0') as Business_Time,
a.d_datetime,
a.L_BUSINESS_TIME,
a.vc_deal_no,
a.vc_inter_code,
a.vc_report_code,
a.l_entrust_serial_no
from trade.trealdeal a) b) c
where c.delat >= 3
and c.delat <= 100
order by c.delat desc;
--申报延时
select *
from (select (SUBSTR(b.report_time, 0, 2) - SUBSTR(b.entrust_time, 0, 2)) * 3600 +
(SUBSTR(b.report_time, 3, 2) - SUBSTR(b.entrust_time, 3, 2)) * 60 +
(SUBSTR(b.report_time, 5, 2) - SUBSTR(b.entrust_time, 5, 2)) as delat,
b.*
from (select lpad(a.l_entrust_time, 6, '0') as entrust_time,
lpad(a.l_report_time, 6, '0') as report_time,
a.l_entrust_time,
a.l_report_time,
a.l_date,
a.vc_inter_code,
a.vc_report_code,
a.l_entrust_serial_no
from trade.tentrusts a
where ((a.l_entrust_time >= 93000 and
a.l_entrust_time < 113000) or
(a.l_entrust_time >= 130000 and
a.l_entrust_time < 150000))) b) c
where c.delat >= 2
order by c.delat desc;