分市州用户管理

Sys.setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
library(ROracle)
library(tidyverse)
library(dbplyr)
library(knitr)
library(magrittr)
library(stringr)
library(lubridate)
library(janitor)

drv <- dbDriver("Oracle")
connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.12)(PORT=1521))",
  "(CONNECT_DATA=(SERVICE_NAME=JZDB1)))", sep = "")
con <- dbConnect(drv, username = "", password = "",
                 dbname = connect.string)

sqltxt <-"select * from sys_user"
user<- tbl(con,sql(sqltxt)) %>%
  collect() %>%
  clean_names()%>%
  mutate(shi=str_sub(jgbm,1,4)) %>%
  group_by(shi,jlzt) %>%
  summarise(n=n())
  spread(jlzt,n)

tmp <- con %>%
  tbl(in_schema('ipvsdb','SYS_XZQH_ZZJG')) %>%
  select(DZMC,DZBM) %>%
  filter(str_length(DZBM)==4) %>%
  collect() %>%
  clean_names() %>%
  right_join(user,by=c('dzbm'='shi')) %>%
  drop_na(dzmc) %>%
  arrange(dzbm)

ggplot(tmp,aes(x=factor(dzmc,levels = unique(tmp$dzmc)),y=n,fill=jlzt))+
  geom_bar(stat="identity",position=position_dodge())+
  labs(x='地区',y='计数')+
  scale_fill_manual(values=c("#00AFBB", "#E7B800", "#FC4E07"),labels=c('废弃','正常','停用'))+
  theme_classic()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1),legend.title = element_blank())

ggplot(tmp,aes(x=dzbm,y=n,fill=jlzt))+
  geom_bar(stat="identity")+
  scale_x_discrete("地区", labels = c("6201" = "兰州市","6202" = "嘉峪关","6203" = "金昌市","6204" = "白银市","6205" = "天水市"
                                    ,"6206" = "武威市","6207" = "张掖市","6208" = "平凉市","6209" = "酒泉市","6210" = "庆阳市"
                                    ,"6211" = "定西市","6212" = "陇南市","6229" = "临夏州","6230" = "甘南州","6232" = "兰州新区"))+
  theme_classic()

write.csv(tmp,'~//My Pictures/tmp.csv')