股票 SQL

insert into stock.dm_stock_trade_total_result
select
'20230928'
,ts_code
,max(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) = to_date('2023-09-28') then close end)
,min(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',365)) then cast(close as float) end)
,max(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',365)) then cast(close as float) end)
,min(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',183)) then cast(close as float) end)
,max(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',183)) then cast(close as float) end)
,min(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',31)) then cast(close as float) end)
,max(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',31)) then cast(close as float) end)
,min(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',7)) then cast(close as float) end)
,max(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',7)) then cast(close as float) end)
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',365)) then 1 end)
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',365)) and status = '1' then 1 end)
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',365)) and status = '0' then 1 end)
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',183)) then 1 end) 
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',183)) and status = '1'  then 1 end) 
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',183)) and status = '0'  then 1 end) 
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',31)) then 1 end)
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',31)) and status = '1'  then 1 end)
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',31)) and status = '0'  then 1 end) 
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',7)) then 1 end) 
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',7)) and status = '1'  then 1 end) 
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',7)) and status = '0'  then 1 end)
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',14)) then 1 end) 
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',14)) and status = '1'  then 1 end) 
,sum(case when to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) <= to_date('2023-09-28') and to_date(concat(substr(trade_date,1,4),'-',substr(trade_date,5,2),'-',substr(trade_date,7,2))) >= to_date(date_sub('2023-09-28',14)) and status = '0'  then 1 end)
,'20230928'
from stock.dm_stock_trade_d 
group by ts_code

select * from stock.dm_stock_trade_total_result

--------------------------------两年
select 
ts_code
,max(case when substr(trade_date,1,6) = '202201' then up end)   202201_up
,max(case when substr(trade_date,1,6) = '202202' then up end)   202202_up
,max(case when substr(trade_date,1,6) = '202203' then up end)   202203_up
,max(case when substr(trade_date,1,6) = '202204' then up end)   202204_up
,max(case when substr(trade_date,1,6) = '202205' then up end)   202205_up
,max(case when substr(trade_date,1,6) = '202206' then up end)   202206_up
,max(case when substr(trade_date,1,6) = '202207' then up end)   202207_up
,max(case when substr(trade_date,1,6) = '202208' then up end)   202208_up
,max(case when substr(trade_date,1,6) = '202209' then up end)   202209_up
,max(case when substr(trade_date,1,6) = '202210' then up end)   202210_up
,max(case when substr(trade_date,1,6) = '202211' then up end)   202211_up
,max(case when substr(trade_date,1,6) = '202212' then up end)   202212_up
,max(case when substr(trade_date,1,6) = '202301' then up end)   202301_up
,max(case when substr(trade_date,1,6) = '202302' then up end)   202302_up
,max(case when substr(trade_date,1,6) = '202303' then up end)   202303_up
,max(case when substr(trade_date,1,6) = '202304' then up end)   202304_up
,max(case when substr(trade_date,1,6) = '202305' then up end)   202305_up
,max(case when substr(trade_date,1,6) = '202306' then up end)   202306_up
,max(case when substr(trade_date,1,6) = '202307' then up end)   202307_up
,max(case when substr(trade_date,1,6) = '202308' then up end)   202308_up
,max(case when substr(trade_date,1,6) = '202309' then up end)   202309_up
from (
select
substr(trade_date,1,6) trade_date
,ts_code
,sum(case when status = 0 then 1 end) down
,sum(case when status = 1 then 1 end) up
from stock.dm_stock_trade_d where  ts_code='300730.SZ'
group by substr(trade_date,1,6),ts_code
) t
group by ts_code

-----------------------------
select 
ts_code
,max(case when substr(trade_date,1,6) = '202301' then down end) 202301_down
,max(case when substr(trade_date,1,6) = '202301' then up end)   202301_up
,max(case when substr(trade_date,1,6) = '202302' then down end) 202302_down
,max(case when substr(trade_date,1,6) = '202302' then up end)   202302_up
,max(case when substr(trade_date,1,6) = '202303' then down end) 202303_down
,max(case when substr(trade_date,1,6) = '202303' then up end)   202303_up
,max(case when substr(trade_date,1,6) = '202304' then down end) 202304_down
,max(case when substr(trade_date,1,6) = '202304' then up end)   202304_up
,max(case when substr(trade_date,1,6) = '202305' then down end) 202305_down
,max(case when substr(trade_date,1,6) = '202305' then up end)   202305_up
,max(case when substr(trade_date,1,6) = '202306' then down end) 202306_down
,max(case when substr(trade_date,1,6) = '202306' then up end)   202306_up
,max(case when substr(trade_date,1,6) = '202307' then down end) 202307_down
,max(case when substr(trade_date,1,6) = '202307' then up end)   202307_up
,max(case when substr(trade_date,1,6) = '202308' then down end) 202308_down
,max(case when substr(trade_date,1,6) = '202308' then up end)   202308_up
from (
select
substr(trade_date,1,6) trade_date
,ts_code
,sum(case when status = 0 then 1 end) down
,sum(case when status = 1 then 1 end) up
from stock.dm_stock_trade_d where trade_date >= '20230101' and trade_date <= '20231231' --and ts_code='603008.SH'
group by substr(trade_date,1,6),ts_code
) t
group by ts_code

select 
ts_code
,sum(case when status = 0 then 1 end) down
,sum(case when status = 1 then 1 end) up
from stock.dm_stock_trade_d where trade_date >= '20230101' and trade_date <= '20231231'
group by ts_code