hive sql 语句学习

use liuliangyuan680_ind;
--drop table llid;
create table llid(insurance_no string,group_no int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE; 


load data  inpath '/apps-data/tmp/gbd-ide/hive-liuliangyuan680/file/id.txt'
into table liuliangyuan680_ind.llid;


select * from liuliangyuan680_ind.llid;


select count(insurance_no) from liuliangyuan680_ind.llid;






SELECT * from liuliangyuan680_ind.llid a , liuliangyuan680_ind.rxw_xmsb_djmxypb b where a.insurance_no =b.insurance_no;


select * from liuliangyuan680_ind.rxw_xmsb_djmxypb;










use liuliangyuan680_ind;
--drop table summary3month;
CREATE table summary3month AS
select insurance_no, sum( case when visit_type='1' and out_hosp_date is not null and in_hosp_date is not null then  (datediff(out_hosp_date,in_hosp_date)+1) else 0 end) hosp_days, 
--总频次
COUNT(DISTINCT(case when visit_type='1' then visit_no else null end)) hosp_freq,
COUNT(DISTINCT(case when visit_type='2' then visit_no else null end)) visit_freq,
COUNT(DISTINCT(case when diagnose_code is not null then diagnose_code else null end)) diagnose_num,COUNT(distinct medical_org_code) as org_num,count(DISTINCT bill_no) as bill_num,count(DISTINCT bill_detail_no) as detail_num,
--看医生数,看病月数
count (distinct doctor_code) as doctor_num,
count (distinct month(in_hosp_date)) as month_num,
--住院药品数，诊疗项目数，服务设施数,总金额，总赔付
count (distinct (case when (visit_type="1" and project_type="1") then project_name else null end)) as hosp_medicine_num,
count (distinct (case when (visit_type="1" and project_type="2") then project_name else null end)) as hosp_check_num,
count (distinct (case when (visit_type="1" and project_type="3") then project_name else null end)) as hosp_service_num,
sum(case when visit_type='1' then mx_sum_amount else null end) as hosp_sum_amount,
sum(case when visit_type='1' then mx_apply_pay_amount else null end) as hosp_apply_amount,


--门诊药品数，诊疗项目数，服务设施数,总金额，总赔付
count (distinct (case when (visit_type="2" and project_type="1") then project_name else null end)) as visit_medicine_num,
count (distinct (case when (visit_type="2" and project_type="1") then bill_detail_no else null end)) as visit_medicine_freq,
count (distinct (case when (visit_type="2" and project_type="2") then project_name else null end)) as visit_check_num,
count (distinct (case when (visit_type="2" and project_type="2") then bill_detail_no else null end)) as visit_check_freq,
count (distinct (case when (visit_type="2" and project_type="3") then project_name else null end)) as visit_service_num,
count (distinct (case when (visit_type="2" and project_type="3") then bill_detail_no else null end)) as visit_service_freq,
sum(case when visit_type='2' then mx_sum_amount else null end) as visit_sum_amount,
sum(case when visit_type='2' then mx_apply_pay_amount else null end) as visit_apply_amount


from liuliangyuan680_ind.rxw_xmsb_djmxypb 


where (visit_type = '1' and 
       year(in_hosp_date) ="2014" ) or 
       (visit_type = '2' and year(in_hosp_date) ="2014" ) 
group BY insurance_no;








use liuliangyuan680_ind;
--drop table summary_full_201410;
CREATE table summary_full_201410 AS
select insurance_no, sum( case when visit_type='1' and out_hosp_date is not null and in_hosp_date is not null then  (datediff(out_hosp_date,in_hosp_date)+1) else 0 end) hosp_days,
COUNT(DISTINCT(case when visit_type='1' then visit_no else null end)) hosp_freq,
COUNT(DISTINCT(case when visit_type='2' then visit_no else null end)) visit_freq,
COUNT(DISTINCT(case when diagnose_code is not null then diagnose_code else null end)) diagnose_num,COUNT(distinct medical_org_code) as org_num,count(DISTINCT bill_no) as bill_num,count(DISTINCT bill_detail_no) as detail_num,
--看医生数
count (distinct doctor_code) as doctor_num,


--住院药品数，诊疗项目数，服务设施数,总金额，总赔付
count (distinct (case when (visit_type="1" and project_type="1") then project_name else null end)) as hosp_medicine_num,
count (distinct (case when (visit_type="1" and project_type="2") then project_name else null end)) as hosp_check_num,
count (distinct (case when (visit_type="1" and project_type="3") then project_name else null end)) as hosp_service_num,
sum(case when visit_type='1' then mx_sum_amount else null end) as hosp_sum_amount,
sum(case when visit_type='1' then mx_apply_pay_amount else null end) as hosp_apply_amount,


--门诊药品数，诊疗项目数，服务设施数,总金额，总赔付
count (distinct (case when (visit_type="2" and project_type="1") then project_name else null end)) as visit_medicine_num,
count (distinct case when (visit_type="2" and project_type="1") then bill_detail_no else null end)) as visit_medicine_freq,
count (distinct (case when (visit_type="2" and project_type="2") then project_name else null end)) as visit_check_num,
count (distinct (case when (visit_type="2" and project_type="2") then bill_detail_no else null end)) as visit_check_freq,
count (distinct (case when (visit_type="2" and project_type="3") then project_name else null end)) as visit_service_num,
count (distinct (case when (visit_type="2" and project_type="3") then bill_detail_no else null end)) as visit_service_freq,
sum(case when visit_type='2' then mx_sum_amount else null end) as visit_sum_amount,
sum(case when visit_type='2' then mx_apply_pay_amount else null end) as visit_apply_amount
from liuliangyuan680_ind.rxw_xmsb_djmxypb 
where (visit_type = '1' and 
        year(in_hosp_date) ="2014" and 
        month(in_hosp_date)="11") or 
        (visit_type = '2' and year(in_hosp_date) ="2014" and 
        month(in_hosp_date)="11") 
group BY insurance_no;
















use liuliangyuan680_ind;
--drop table sefinal;
create table sefinal as SELECT
b.insurance_no,
b.medical_org_name,
b.medical_dept_name,
b.doctor_code
from liuliangyuan680_ind.llid a , liuliangyuan680_ind.rxw_xmsb_djmxypb b where a.insurance_no =b.insurance_no;


select * from liuliangyuan680_ind.sefinal;










insert overwrite local directory '/home/wyp/wyp' select * from liuliangyuan680_ind.selfinal; 




















































use liuliangyuan680_ind;
drop table llid;
create table llid(insurance_no string, group_no int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE; 


load data  inpath '/apps-data/tmp/gbd-ide/hive-liuliangyuan680/file/id.txt'
into table liuliangyuan680_ind.llid;


select * from liuliangyuan680_ind.llid;


select count(insurance_no) from liuliangyuan680_ind.llid;






use liuliangyuan680_ind;
--drop table sefinal;
create table sefinal as SELECT
a.group_no,
b.insurance_no,
b.diagnose_code,
b.project_name,
b.medical_org_name,
b.medical_dept_name,
b.doctor_code
from liuliangyuan680_ind.llid a , liuliangyuan680_ind.rxw_xmsb_djmxypb b where a.insurance_no =b.insurance_no;




use liuliangyuan680_ind;
--drop table sefinal;
create table selfinal as SELECT
a.group_no,
b.insurance_no,
b.diagnose_code,
b.project_name,
b.medical_org_name,
b.medical_dept_name,
b.doctor_code
from liuliangyuan680_ind.llid a join liuliangyuan680_ind.rxw_xmsb_djmxypb b on ( a.insurance_no =b.insurance_no);




select * from liuliangyuan680_ind.sefinal;


select count(DISTINCT insurance_no) from liuliangyuan680_ind.sefinal;






---社保号个数1484,群数10
select count(DISTINCT insurance_no) from liuliangyuan680_ind.selfinal;
select count(DISTINCT group_no) from liuliangyuan680_ind.selfinal;




select  medical_org_name,COUNT(medical_org_name) as time1 FROM liuliangyuan680_ind.selfinal a WHERE
a.group_no=1 group by medical_org_name order by time1 ;




select  medical_org_name,COUNT(medical_org_name) FROM liuliangyuan680_ind.selfinal  
group by group_no and medical_org_name;


select  medical_org_name, COUNT(medical_org_name) FROM liuliangyuan680_ind.selfinal
where group_no=1;