library(odbc)
library(DBI)
library(dplyr)
library(ggplot2)
library(corrr)
con <- dbConnect(odbc(), Driver = "ODBC Driver 17 for SQL Server", 
                 Server = "REX\\SQLEXPRESS", Database = "VYUKA_DATABAZE", 
                 Trusted_Connection = "yes",
                 encoding = "CP1250")

sectors<-tbl(con, "sectors") 

my_cor<-sectors %>% 
  select(-country) %>% 
  correlate(quiet=TRUE)

stretch(my_cor)
 
my_cor %>% shave()
my_cor %>% rplot() +
  theme(axis.text.x = element_text(angle = 90, vjust = 0.5, hjust=1))
my_cor %>% network_plot()

my_cor %>%focus(Industry)%>%arrange(desc(Industry))


library(modeldb)
library(tidypredict)

computers<-tbl(con, "Computers") 
n_total<-pull(count(computers))
n<-as.integer(round(n_total)*0.25)

index<-c(rep(T,n),rep(F,n_total-n))
index<-index[order(runif(n_total))]
test_id<-(1:n_total)[index]
train_id<-(1:n_total)[!index]

data<-test_sample<-computers %>%
  select(id,speed,hd,ram,price,cd) %>%
  mutate(price=log(price),cd=as.integer(cd=="yes")) 
  
test_sample<-data %>%
  filter(id %in% test_id) %>%
  select(-id)
count(test_sample)  

train_sample<-data %>%
  filter(id %in% train_id) %>%
  select(-id)

model<-test_sample %>% linear_regression_db(price,n)
pm<-as_parsed_model(model)
tidypredict_fit(pm)
result<-test_sample %>% tidypredict_to_column(pm) %>% select(price, fit, everything())

result %>% ggplot(data=.)+geom_point(aes(x=fit,y=fit-price))

result %>% select(price,fit) %>% correlate(quiet=T) %>% filter(!is.na(fit)) %>% 
  mutate(r2=fit^2) %>% select(r2)
mean_price<-pull(result %>% summarise(mean(price,na.rm=T)))
result %>% select(price,fit) %>% mutate(e=(fit-price)^2,t=(price-mean_price)^2) %>% 
  summarise(s1=sum(e,na.rm=T), s2=sum(t,na.rm=T)) %>% mutate(r_2=1-s1/s2)
  
result<-train_sample %>% tidypredict_to_column(pm) %>% select(price, fit, everything())
result %>% select(price,fit) %>% correlate(quiet=T) %>% filter(!is.na(fit)) %>% 
  mutate(r2=fit^2) %>% select(r2)
mean_price<-pull(result %>% summarise(mean(price,na.rm=T)))
result %>% select(price,fit) %>% mutate(e=(fit-price)^2,t=(price-mean_price)^2) %>% 
  summarise(s1=sum(e), s2=sum(t)) %>% mutate(r_2=1-s1/s2)