【详述】问题详细描述
【背景】做过哪些操作？
【业务影响】
【StarRocks版本】例如：2.4
【集群规模】例如：3fe（1 follower+2observer）+4be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【附件】
- 建表语句：
CREATE TABLE ba_sa_tpatientvisit(
fid INT,fprn VARCHAR(200),ftimes INT,ficdversion VARCHAR(200),fzyid VARCHAR(200),fage VARCHAR(200),fname VARCHAR(200),fsexbh INT,fsex VARCHAR(200),fbirthday DATETIME,fbirthplace VARCHAR(200),fidcard VARCHAR(200),fcountrybh VARCHAR(200),fcountry VARCHAR(200),fnationalitybh VARCHAR(200),fnationality VARCHAR(200),fjob VARCHAR(200),fstatusbh VARCHAR(200),fstatus VARCHAR(200),fdwname VARCHAR(200),fdwaddr VARCHAR(200),fdwtele VARCHAR(200),fdwpost VARCHAR(200),fhkaddr VARCHAR(200),fhkpost VARCHAR(200),flxname VARCHAR(200),frelate VARCHAR(200),flxaddr VARCHAR(200),flxtele VARCHAR(200),ffbbh VARCHAR(200),ffb VARCHAR(200),fascard1 VARCHAR(200),fascard2 VARCHAR(200),frydate DATETIME,frytime VARCHAR(200),frytykh VARCHAR(200),frydept VARCHAR(200),frybs VARCHAR(200),fcydate DATETIME,fcytime VARCHAR(200),fcytykh VARCHAR(200),fcydept VARCHAR(200),fcybs VARCHAR(200),fdays VARCHAR(200),fmzzdbh VARCHAR(200),fmzzd VARCHAR(200),fmzdoctbh VARCHAR(200),fmzdoct VARCHAR(200),fryinfobh VARCHAR(200),fryinfo VARCHAR(200),fryzdbh VARCHAR(200),fryzd VARCHAR(200),fqzdate DATETIME,fphzd VARCHAR(200),fgmyw VARCHAR(200),fhbsagbh VARCHAR(200) ,fhbsag VARCHAR(200),fhcvabbh VARCHAR(200),fhcvab VARCHAR(200),fhivabbh VARCHAR(200),fhivab VARCHAR(200),fmzcyaccobh VARCHAR(200),fmzcyacco VARCHAR(200),frycyaccobh VARCHAR(200),frycyacco VARCHAR(200),flcblaccobh VARCHAR(200),flcblacco VARCHAR(200),ffsblaccobh VARCHAR(200),ffsblacco VARCHAR(200),fopaccobh VARCHAR(200),fopacco VARCHAR(200),fqjtimes VARCHAR(200),fqjsuctimes VARCHAR(200),fkzrbh VARCHAR(200),fkzr VARCHAR(200),fzrdoctbh VARCHAR(200),fzrdoctor VARCHAR(200),fzzdoctbh VARCHAR(200),fzzdoct VARCHAR(200),fzydoctbh VARCHAR(200),fzydoct VARCHAR(200),fjxdoctbh VARCHAR(200),fjxdoct VARCHAR(200),fyjssxdoctbh VARCHAR(200),fyjssxdoct VARCHAR(200),fsxdoctbh VARCHAR(200),fsxdoct VARCHAR(200),fbmybh VARCHAR(200),fbmy VARCHAR(200),fzlrbh VARCHAR(200),fzlr VARCHAR(200),fqualitybh VARCHAR(200),fquality VARCHAR(200),fzkdoctbh VARCHAR(200),fzkdoct VARCHAR(200),fzknursebh VARCHAR(200),fzknurse VARCHAR(200),fzkrq DATETIME,fmzdeadbh VARCHAR(200),fmzdead VARCHAR(200),fsum1 VARCHAR(200),fcwf VARCHAR(200),fhlf VARCHAR(200),fxyf DOUBLE,fzyf VARCHAR(200),fzchyf VARCHAR(200),fzcyf VARCHAR(200),ffsf VARCHAR(200),fhyf VARCHAR(200),fsyf VARCHAR(200),fsxf VARCHAR(200),fzlf VARCHAR(200),fssf VARCHAR(200),fjsf VARCHAR(200),fjcf VARCHAR(200),fmzf VARCHAR(200),fyef VARCHAR(200),fpcf VARCHAR(200),fqtf VARCHAR(200),fbodybh VARCHAR(200),fbody VARCHAR(200),fisopfirstbh VARCHAR(200),fisopfirst VARCHAR(200),fiszlfirstbh VARCHAR(200),fiszlfirst VARCHAR(200),fisjcfirstbh VARCHAR(200),fisjcfirst VARCHAR(200),fiszdfirstbh VARCHAR(200),fiszdfirst VARCHAR(200),fisszbh VARCHAR(200),fissz VARCHAR(200),fszqx VARCHAR(200),fsamplebh VARCHAR(200),fsample VARCHAR(200),fbloodbh VARCHAR(200),fblood VARCHAR(200),frhbh VARCHAR(200),frh VARCHAR(200),fsxfybh VARCHAR(200),fsxfy VARCHAR(200),fsyfybh VARCHAR(200),fsyfy VARCHAR(200),fredcell VARCHAR(200),fplaque VARCHAR(200),fserous VARCHAR(200),fallblood VARCHAR(200),fotherblood VARCHAR(200),fhzyj VARCHAR(200),fhzyc VARCHAR(200),fhltj VARCHAR(200),fhl1 VARCHAR(200),fhl2 VARCHAR(200),fhl3 VARCHAR(200),fhlzz VARCHAR(200),fhlts VARCHAR(200),fbabynum VARCHAR(200),ftwill VARCHAR(200),fqjbr VARCHAR(200),fqjsuc VARCHAR(200),fthreqz VARCHAR(200),fback VARCHAR(200),fifzdss VARCHAR(200),fifdbz VARCHAR(200),fzlfzy VARCHAR(200),fzktykh VARCHAR(200),fzkdept VARCHAR(200),fzkdate VARCHAR(200),fzktime VARCHAR(200),fsrybh VARCHAR(200),fsry VARCHAR(200),fworkrq DATETIME,fjbfxbh VARCHAR(200),fjbfx VARCHAR(200),ffhgdbh VARCHAR(200),ffhgd VARCHAR(200),fsourcebh VARCHAR(200),fsource VARCHAR(200),fifss VARCHAR(200),fiffyk VARCHAR(200),fbfz VARCHAR(200),fyngr VARCHAR(200),fdatacheck VARCHAR(200),fextend1 VARCHAR(200),fextend2 VARCHAR(200),fextend3 VARCHAR(200),fextend4 VARCHAR(200),fextend5 VARCHAR(200),fextend6 VARCHAR(200),fextend7 VARCHAR(200),fextend8 VARCHAR(200),fextend9 VARCHAR(200),fextend10 VARCHAR(200),fextend11 VARCHAR(200),fextend12 VARCHAR(200),fextend13 VARCHAR(200),fextend14 VARCHAR(200),fextend15 VARCHAR(200),fflag VARCHAR(200),fqshow VARCHAR(200),fnative VARCHAR(200),fcurraddr VARCHAR(200),fcurrtele VARCHAR(200),fcurrpost VARCHAR(200),fjobbh VARCHAR(200),fcstz VARCHAR(200),frytz VARCHAR(200),frytjbh VARCHAR(200),frytj VARCHAR(200),fycljbh VARCHAR(200),fyclj VARCHAR(200),fphzdbh VARCHAR(200),fphzdnum VARCHAR(200),fifgmywbh VARCHAR(200),fifgmyw VARCHAR(200),fnursebh VARCHAR(200),fnurse VARCHAR(200),flyfsbh VARCHAR(200),flyfs VARCHAR(200),fyzouthostital VARCHAR(200),fsqouthostital VARCHAR(200),fisagainrybh VARCHAR(200),fisagainry VARCHAR(200),fisagainrymd VARCHAR(200),fryqhmdays VARCHAR(200),fryqhmhours VARCHAR(200),fryqhmmins VARCHAR(200),fryqhmcounts VARCHAR(200),fryhmdays VARCHAR(200),fryhmhours VARCHAR(200),fryhmmins VARCHAR(200),fryhmcounts VARCHAR(200),ffbbhnew VARCHAR(200),ffbnew VARCHAR(200),fzfje VARCHAR(200),fzhfwlylf VARCHAR(200),fzhfwlczf VARCHAR(200),fzhfwlhlf VARCHAR(200),fzhfwlqtf VARCHAR(200),fzdlblf VARCHAR(200),fzdlsssf VARCHAR(200),fzdlyxf VARCHAR(200),fzdllcf VARCHAR(200),fzllffssf VARCHAR(200),fzllfwlzwlf VARCHAR(200),fzllfssf VARCHAR(200),fzllfmzf VARCHAR(200),fzllfsszlf VARCHAR(200),fkflkff VARCHAR(200),fzylzf VARCHAR(200),fxylgjf VARCHAR(200),fxylxf VARCHAR(200),fxylbqbf VARCHAR(200),fxylqdbf VARCHAR(200),fxylyxyzf VARCHAR(200),fxylxbyzf VARCHAR(200),fhclcjf VARCHAR(200),fhclzlf VARCHAR(200),fhclssf VARCHAR(200),fzhfwlylf01 VARCHAR(200),fzhfwlylf02 VARCHAR(200),fzylzdf VARCHAR(200),fzylzlf VARCHAR(200),fzylzlf01 VARCHAR(200),fzylzlf02 VARCHAR(200),fzylzlf03 VARCHAR(200),fzylzlf04 VARCHAR(200),fzylzlf05 VARCHAR(200),fzylzlf06 VARCHAR(200),fzylqtf VARCHAR(200),fzylqtf01 VARCHAR(200),fzylqtf02 VARCHAR(200),fzcljgzjf VARCHAR(200),fgdlsh VARCHAR(200),fyhzwdjzqk VARCHAR(200),fzwdcsmc VARCHAR(200),fzbdzyqk VARCHAR(200),fwdcsmc VARCHAR(200),fyhxjsj VARCHAR(200),fzwdjjreason VARCHAR(200),fzybsjjreason VARCHAR(200),fbirthplacebh VARCHAR(200),fdwaddrbh VARCHAR(200),fhkaddrbh VARCHAR(200),flxaddrbh VARCHAR(200),fcurraddrbh VARCHAR(200),fyblsh VARCHAR(200))ENGINE=OLAP
DUPLICATE KEY(fid, fprn)
DISTRIBUTED BY HASH(fid, fprn,ftimes) BUCKETS 8
PROPERTIES(“replication_num” = “1”);
CREATE TABLE ba_sa_toperation(fid INT, fprn VARCHAR(200) , ftimes INT, fname VARCHAR(200), foptimes INT, fopcode STRING, fop VARCHAR(200), fopdate DATETIME, fqiekoubh VARCHAR(200), fqiekou VARCHAR(200), fyuhebh VARCHAR(200), fyuhe VARCHAR(200), fdocbh VARCHAR(200), fdocname VARCHAR(200), fmazuibh VARCHAR(200), fmazui VARCHAR(200), fiffsop VARCHAR(200), fopdoct1bh VARCHAR(200), fopdoct1 VARCHAR(200), fopdoct2bh VARCHAR(200), fopdoct2 VARCHAR(200), fmzdoctbh VARCHAR(200), fmzdoct VARCHAR(200), fpx VARCHAR(200), foptykh VARCHAR(200), fopksname VARCHAR(200), fzqssbh VARCHAR(200), fzqss VARCHAR(200), fssjbbh VARCHAR(200), fssjb VARCHAR(200))ENGINE=OLAP
DUPLICATE KEY(fid, fprn)
DISTRIBUTED BY HASH(fid, fprn,ftimes) BUCKETS 8
PROPERTIES(“replication_num” = “2”);
CREATE TABLE ba_sa_tworkroom(
fid INT,ftykh VARCHAR(200),fkh VARCHAR(200),ftype INT,fksname STRING,fstatflag VARCHAR(200),fadoctors VARCHAR(200),fbednum VARCHAR(200),ffzr VARCHAR(200),fpx VARCHAR(200),fcal VARCHAR(200),fblanks INT,fifstat INT,fopendate DATETIME,fdeldate INT,fcaninput INT,fnoused INT,fparent VARCHAR(200),fchildren VARCHAR(200),fzjc VARCHAR(200),fjb INT,fny VARCHAR(200),fcur INT,iscross VARCHAR(200))ENGINE=OLAP
DUPLICATE KEY(fid, ftykh)
DISTRIBUTED BY HASH(fid, ftykh,ftykh) BUCKETS 8
PROPERTIES(“replication_num” = “2”);
CREATE TABLE ba_sa_bi_dim_operation(operation_code VARCHAR(200),operation_name VARCHAR(200),operation_level_code INT,operation_level_name VARCHAR(200),operation_type_code INT,operation_type_name VARCHAR(200),minimally_flag INT,operation_categories_code INT,operation_single_name1 VARCHAR(200),operation_single_name2 VARCHAR(200),rjss_code VARCHAR(200),hospital_level_code INT,hospital_level_name VARCHAR(200),operation_level3_code INT,operation_level3_name VARCHAR(200),operation_code_new VARCHAR(200), operation_name_new VARCHAR(200))ENGINE=OLAPDUPLICATE KEY(operation_code, operation_name)DISTRIBUTED BY HASH(operation_code, operation_name,operation_level_code) BUCKETS 8PROPERTIES(“replication_num” = “2”);
CREATE TABLE ba_sa_tdiagnose(
fid INT,
fprn INT,
ftimes INT,
fzdlx VARCHAR(200),
ficdversion INT,
ficdm STRING,
fjbname VARCHAR(200),
fzljgbh VARCHAR(200),
fzljg VARCHAR(200),
fpx INT,
frybqbh INT,
frybq VARCHAR(200))ENGINE=OLAP
DUPLICATE KEY(fid, fprn)
DISTRIBUTED BY HASH(fid, fprn,ftimes) BUCKETS 8
PROPERTIES(“replication_num” = “2”);
CREATE TABLE bi_dim_disease_risk (
id INT ,
icd10_code STRING ,
icd10_name STRING ,
disease_risk STRING
) ENGINE=OLAP
DUPLICATE KEY(id, icd10_code)
DISTRIBUTED BY HASH(id, icd10_code,icd10_name) BUCKETS 8
PROPERTIES(“replication_num” = “2”);
CREATE TABLE ba_sa_tknubcard(
fid INT,fprn VARCHAR(200),ftimes INT,fflfsbh VARCHAR(200),fflfs VARCHAR(200),fflcxbh VARCHAR(200),fflcx VARCHAR(200),fflzzbh VARCHAR(200),fflzz VARCHAR(200),fyjy VARCHAR(200),fycs VARCHAR(200),fyts VARCHAR(200),fyrq1 VARCHAR(200),fyrq2 VARCHAR(200),fqjy VARCHAR(200),fqcs VARCHAR(200),fqts VARCHAR(200),fqrq1 VARCHAR(200),fqrq2 VARCHAR(200),fzname VARCHAR(200),fzjy VARCHAR(200),fzcs VARCHAR(200),fzts VARCHAR(200),fzrq1 VARCHAR(200),fzrq2 VARCHAR(200),fhlfsbh VARCHAR(200),fhlfs VARCHAR(200),fhlffbh VARCHAR(200),fhlff VARCHAR(200),fqtype VARCHAR(200),fqt VARCHAR(200),fqn VARCHAR(200),fqm VARCHAR(200),fqall VARCHAR(200),fqallbh VARCHAR(200) )ENGINE=OLAP
DUPLICATE KEY(fid, fprn)
DISTRIBUTED BY HASH(fid, fprn,ftimes) BUCKETS 8
PROPERTIES(“replication_num” = “2”);
数据量大小：
ba_sa_tpatientvisit – 539323
ba_sa_toperation – 361716
ba_sa_tworkroom – 269
ba_sa_bi_dim_operation – 21266
ba_sa_tdiagnose – 1579778
bi_dim_disease_risk – 115
ba_sa_tknubcard – 388025
执行sql查询语句
SELECT count(distinct fprn_ftimes) as b
from (select a.fprn as inhosp_code,
b.ftimes as visit_code,
concat((concat(coalesce(a.fprn , ‘0’), coalesce(b.ftimes , ‘0’))),coalesce(b.fzyid , ‘0’)) as fprn_ftimes,
b.fzyid as serial_code,
b.fcydate as discharge_time,
concat(substr(b.fcydate,6,2),‘月’) mm,
case when quarter(b.fcydate)=1 then ‘1-3月’ when quarter(b.fcydate)=2 then ‘1-6月’ when quarter(b.fcydate)=3 then ‘1-9月’ when quarter(b.fcydate)=4 then ‘1-12月’ end as jidu,
left(b.fcydate, 10) discharge_time_ymd,
left(b.fcydate, 7) discharge_time_ym,
left(b.fcydate, 4) discharge_time_y,
t3.fkh as discharge_dept_code,
b.frydate as admit_time,
left(b.frydate, 10) admit_time_ymd,
left(b.frydate, 7) admit_time_ym,
left(b.frydate, 4) admit_time_y,
t4.fkh as admit_dept_code,
a.fopdate as operation_time,
a.fopcode as operation_code,
t5.operation_name,
t5.operation_level_name,
t5.operation_type_name,
t5.minimally_flag,
a.fzqssbh as quiet_flag,
b.flyfs as discharge_way_name,
t6.ficdm as discharge_clinic_code,
(case when t6.ficdm like ‘c0%’ or t6.ficdm like ‘c1%’ or t6.ficdm like ‘c2%’ or
t6.ficdm like ‘c3%’ or t6.ficdm like ‘c4%’ or t6.ficdm like ‘c5%’ or t6.ficdm like ‘c6%’ or
t6.ficdm like ‘c70%’ or t6.ficdm like ‘c71%’ or t6.ficdm like ‘c72%’ or t6.ficdm like ‘c73%’ or
t6.ficdm like ‘c74%’ or t6.ficdm like ‘c75%’ then ‘患者人次’ end) zlbs,
(case when t3.fksname like ‘%xser%’ or t3.fksname like ‘%nicu%’ then ‘xser’ end) xsbs ,
t5.hospital_level_name,
t7.disease_risk,
(case when t8.fqtype <> ‘’ then ‘tnm分期数’ end) tnm分期标识,
(case when b.fxyf > 0 or b.fzyf > 0 or b.fzchyf > 0 or b.fzcyf > 0 then ‘费用标识’ end) as fybs ,
b.fdays as inhosp_day
from ba_sa_tpatientvisit as b
left outer join ba_sa_toperation as a
on a.fprn = b.fprn
and b.ftimes = a.ftimes
left outer join ba_sa_tworkroom as t3
on b.fcytykh = t3.ftykh
left outer join ba_sa_tworkroom as t4
on b.frytykh = t4.ftykh
left join ba_sa_bi_dim_operation t5
on (case when b.fcydate < ‘2021-01-01’ then concat(‘旧_’,a.fopcode) else concat(‘新_’, a.fopcode) end) = t5.operation_code_new
left outer join ba_sa_tdiagnose as t6
on b.fprn = t6.fprn
and b.ftimes = t6.ftimes
left outer join bi_dim_disease_risk as t7
on t6.ficdm like concat(t7.icd10_code,’%’)
left join ba_sa_tknubcard t8
on t8.fprn = b.fprn
and t8.ftimes = b.ftimes)a
where discharge_time_ymd>=‘2021-11-06’ and discharge_time_ymd<‘2021-11-08’
执行结果：
