数据报表类系统测试

report_date         string,

prd_code            string,

question_code       string,

date_type           string,

value_type          string,

base_value          string,

gmt_create          string,

gmt_modified        string

) partitioned by (pt string)

row format delimited fields terminated by '\"'

lines terminated by '\n'

STORED AS TEXTFILE;

insert overwrite table r_yunong_test #表数据插入

PARTITION (pt='$env.lastPartition')

select  report_date,

prd_code,

question_code,

'D' as date_type,

'01'as value_type

count(case when sid is not null then sid when caseid is not null then caseid else null end) as  base_value，  #特殊字段的处理，验证重点

'$env.date' as gmt_create,

'$env.date' as gmt_modified

from r_test   #从另一个已创建的中间表r_voc_fact_question获取数据

where pt='$env.lastPartition'

and question_code <>'unknown'

group by  report_date,prd_code,question_code;

from voc_tb_*** f,

voc_issue_*** d,

bi_time_*** t,

voc_prd_*** v

where d.issue_code = f.issue_codes

and v.id = f.prd_id_sk

and t.date_id = f.date_id

and v.prd_id=711

and t.day = 20140316

group by f.issue_code

order by sum(f.all_qz_cnt) desc

FROM voc_tb_*** LEFT JOIN voc_issue_*** ON voc_tb_***.issue_code = voc_issue_***.issue_code and voc_issue_***.flow_step=voc_tb_***.dim7

LEFT JOIN voc_prd_*** ON voc_tb_***.prd_id_sk = voc_prd_***.id

LEFT JOIN bi_time_*** ON voc_tb_***.date_id = bi_time_***.date_id

WHERE voc_prd_***.prd_id=711 and bi_time_***.day=20140316

GROUP BY bi_time_***.day,voc_prd_***.prd_id,voc_issue_***.issue_code

ORDER BY index_135 desc