|
--start--v1.0
drop ?table ?stock_temp;
create temporary table stock_temp as
select?
t.ts_code
,t.st_price
,t.week_total
,t.week_high_total
,t.week_low_total
,t.month_total
,t.month_high_total
,t.month_low_total
,t.year_high
,t.year_total
,t.year_high_total
,t.year_low_total
,round(cast(t.week_high_total as float)/cast(t.week_total as float),4) w_pc
,round(cast(t.month_high_total as float)/cast(t.month_total as float),4) m_pc
,round(cast(t.year_high_total as float)/cast(t.year_total as float),4) y_pc
from dm_stock_trade_total_result t
where ?t.data_dt='20220323' and cast(t.st_price as float) < cast(t.year_high as float) and t.st_price <> 0 and t.st_price <= 30
and exists (select 1 from stock_code a?
? ? ? ? ? ? ? ? ? ? ? ?inner join stock.stock_baseinfo b
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?on a.ts_code = b.ts_code
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? and b.liquid_assets < 100
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? and b.total_assets < 100
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? and b.industry not in ('全国地产','证券','多元金融')
where a.ts_code <> 'ts_code' and a.list_status='L' and t.ts_code=a.ts_code and a.name not like '%退%' and a.name not like '%ST%'?
and a.name not like '%银行%'
and a.name not like '%商行%'
and a.name not like '%*%'
and a.market <> '科创板'?
and a.market <> 'CDR'
and a.ts_code not like '%.BJ'
and a.ts_code not like '000975%'
and a.ts_code not like '000937%'
and a.ts_code not like '000925%'
--and market = '创业板'?
)
order by round((cast(t.year_high_total as float)/cast(t.year_total as float)),4) desc;
drop ?table ?stock_temp1;
create temporary table stock_temp1 as
select?
tt.ts_code
,tt.st_price
,tt.year_high
,tt.year_total
,tt.year_high_total
,tt.year_low_total
,tt.industry
,tt.w_pc
,tt.m_pc
,tt.y_pc
,tt.rn
from (
select *,row_number() over(partition by t.industry order by w_pc desc) rn from
(select a.*,b.industry from stock_temp a
? ? inner join stock_baseinfo b
? ? ? ? ? ? on a.ts_code = b.ts_code
? ? ? ? ?where (a.w_pc <> a.m_pc
? ? ? ? ?or a.m_pc <> a.y_pc)
? ? ? ? ?and a.w_pc <> 0
? ? ? ? ?and a.m_pc <> 0
? ? ? ? ?and a.y_pc <> 0
? ? ? ? ?) t
) tt
where rn <= 10
union all
select?
tt.ts_code
,tt.st_price
,tt.year_high
,tt.year_total
,tt.year_high_total
,tt.year_low_total
,tt.industry
,tt.w_pc
,tt.m_pc
,tt.y_pc
,tt.rn
from (
select *,row_number() over(partition by t.industry order by m_pc desc) rn from
(select a.*,b.industry from stock_temp a
? ? inner join stock_baseinfo b
? ? ? ? ? ? on a.ts_code = b.ts_code
? ? ? ? ?where (a.w_pc <> a.m_pc
? ? ? ? ?or a.m_pc <> a.y_pc)
? ? ? ? ?and a.w_pc <> 0
? ? ? ? ?and a.m_pc <> 0
? ? ? ? ?and a.y_pc <> 0
? ? ? ? ?) t
) tt
where rn <= 10
union all
select?
tt.ts_code
,tt.st_price
,tt.year_high
,tt.year_total
,tt.year_high_total
,tt.year_low_total
,tt.industry
,tt.w_pc
,tt.m_pc
,tt.y_pc
,tt.rn
from (
select *,row_number() over(partition by t.industry order by y_pc desc) rn from
(select a.*,b.industry from stock_temp a
? ? inner join stock_baseinfo b
? ? ? ? ? ? on a.ts_code = b.ts_code
? ? ? ? ?where (a.w_pc <> a.m_pc
? ? ? ? ?or a.m_pc <> a.y_pc)
? ? ? ? ?and a.w_pc <> 0
? ? ? ? ?and a.m_pc <> 0
? ? ? ? ?and a.y_pc <> 0
? ? ? ? ?) t
) tt
where rn <= 10;
--
drop table stock_result;
create temporary table stock_result as
select distinct t.ts_code,w_pc,m_pc,y_pc from stock_temp1 t
inner join dm_stock_trade_total_result a
? ? ? ? on t.ts_code = a.ts_code
? ? ? ? and a.data_dt='20220323'?
? ? ? ? and cast(a.st_price as float) < cast(a.year_high as float)?
? ? ? ? and a.st_price <> 0 and a.st_price <= 30
order by w_pc desc
limit 30
union all
select distinct t.ts_code,w_pc,m_pc,y_pc from stock_temp1 t
inner join dm_stock_trade_total_result a
? ? ? ? on t.ts_code = a.ts_code
? ? ? ? and a.data_dt='20220323'?
? ? ? ? and cast(a.st_price as float) < cast(a.year_high as float)?
? ? ? ? and a.st_price <> 0 and a.st_price <= 30
order by m_pc desc
limit 30
union all
select distinct t.ts_code,w_pc,m_pc,y_pc from stock_temp1 t
inner join dm_stock_trade_total_result a
? ? ? ? on t.ts_code = a.ts_code
? ? ? ? and a.data_dt='20220323'?
? ? ? ? and cast(a.st_price as float) < cast(a.year_high as float)?
? ? ? ? and a.st_price <> 0 and a.st_price <= 30
order by y_pc desc
limit 30
;
select ts_code,count(*) from stock_result group by ts_code order by count(*) desc
?
|