筛选股票代码

--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