drop table dm_stock_d;
create table dm_stock_d as
select
date_d
,open
,high
,close
,low
,volume
,price_change
,p_change
,ma5
,ma10
,ma20
,v_ma5
,v_ma10
,v_ma20
,stockid
,case when open <= close then 1 else 0 end as M_STATE
from dw_stock_d;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table tm_getstocklist_d
select t1.stockid,t1.nm,from_unixtime(unix_timestamp(),'yyyyMMdd') from
(select stockid,up,'7' nm from
(SELECT T.STOCKID,
SUM(CASE
WHEN NVL(M_STATE,0) > 0 THEN
1
END) UP,
SUM(CASE
WHEN NVL(M_STATE,0) = 0 THEN
1
END) DO
FROM DM_STOCK_D T
WHERE date_d <= from_unixtime(unix_timestamp(),'yyyy-MM-dd') and date_d >= date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-7)
GROUP BY T.STOCKID) T
order by up desc limit 40) t1
inner join(
select stockid,up,'15' nm from
(SELECT T.STOCKID,
SUM(CASE
WHEN NVL(M_STATE,0) > 0 THEN
1
END) UP,
SUM(CASE
WHEN NVL(M_STATE,0) = 0 THEN
1
END) DO
FROM DM_STOCK_D T
WHERE date_d <= from_unixtime(unix_timestamp(),'yyyy-MM-dd') and date_d >= date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-15)
GROUP BY T.STOCKID) T
order by up desc limit 40
) t2
on t1.stockid=t2.stockid
inner join(
select stockid,up,'30' nm from
(SELECT T.STOCKID,
SUM(CASE
WHEN NVL(M_STATE,0) > 0 THEN
1
END) UP,
SUM(CASE
WHEN NVL(M_STATE,0) = 0 THEN
1
END) DO
FROM DM_STOCK_D T
WHERE date_d <= from_unixtime(unix_timestamp(),'yyyy-MM-dd') and date_d >= date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-30)
GROUP BY T.STOCKID) T
order by up desc limit 40
) t3
on t1.stockid=t3.stockid
inner join(
select stockid,up,'60' nm from
(SELECT T.STOCKID,
SUM(CASE
WHEN NVL(M_STATE,0) > 0 THEN
1
END) UP,
SUM(CASE
WHEN NVL(M_STATE,0) = 0 THEN
1
END) DO
FROM DM_STOCK_D T
WHERE date_d <= from_unixtime(unix_timestamp(),'yyyy-MM-dd') and date_d >= date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-60)
GROUP BY T.STOCKID) T
order by up desc limit 40
) t4
on t1.stockid=t4.stockid
;