分市州计算每电子监管码接种剂次数
xzqh<- con %>%
tbl(in_schema('ipvsdb','SYS_XZQH_ZZJG')) %>%
select(DZMC,DZBM) %>%
filter(str_length(DZBM)==4) %>%
collect() %>%
clean_names()
sql_txt <- "select grda_code,ym_bm,ym_mc,xt_djjgdm,jz_sj,jz_zc,dzjgm,substr(xt_djjgdm,1,4) as shi from INOC_JZJL
where jz_sj>=to_date('2018-01-01','yyyy-mm-dd')
and jz_sj<=to_date('2019-12-31','yyyy-mm-dd') and ym_bm='0311'"
sunhao <- tbl(con,sql(sql_txt)) %>%
collect() %>%
clean_names() %>%
filter(!is.na(dzjgm)) %>%
mutate(year=str_sub(jz_sj,1,4)) %>%
group_by(shi,year) %>%
summarise(count=n(),dzn=n_distinct(dzjgm)) %>%
mutate(jici=round(count/dzn,2)) %>%
left_join(xzqh,by = c("shi"="dzbm"))
tbl(in_schema('ipvsdb','SYS_XZQH_ZZJG')) %>%
select(DZMC,DZBM) %>%
filter(str_length(DZBM)==4) %>%
collect() %>%
clean_names()
sql_txt <- "select grda_code,ym_bm,ym_mc,xt_djjgdm,jz_sj,jz_zc,dzjgm,substr(xt_djjgdm,1,4) as shi from INOC_JZJL
where jz_sj>=to_date('2018-01-01','yyyy-mm-dd')
and jz_sj<=to_date('2019-12-31','yyyy-mm-dd') and ym_bm='0311'"
sunhao <- tbl(con,sql(sql_txt)) %>%
collect() %>%
clean_names() %>%
filter(!is.na(dzjgm)) %>%
mutate(year=str_sub(jz_sj,1,4)) %>%
group_by(shi,year) %>%
summarise(count=n(),dzn=n_distinct(dzjgm)) %>%
mutate(jici=round(count/dzn,2)) %>%
left_join(xzqh,by = c("shi"="dzbm"))
评论
发表评论