rodbc_with_analysis.R

#install.packages("RODBC")
library(RODBC)
odbcDataSources()

##                                              dBASE Files 
##    "Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)" 
##                                              Excel Files 
## "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)" 
##                                       MS Access Database 
##               "Microsoft Access Driver (*.mdb, *.accdb)" 
##                                             PostgreSQL30 
##                           "PostgreSQL ODBC Driver(ANSI)" 
##                                                   ajaydb 
##                           "PostgreSQL ODBC Driver(ANSI)"

chan=odbcConnect('PostgreSQL30','postgres;Password=root;Database=kush')
class(chan)

## [1] "RODBC"

sqlTables(chan)

##   TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
## 1      kush      public     cities      TABLE        
## 2      kush      public    sales77      TABLE        
## 3      kush      public    weather      TABLE

sqlFetch(chan,'sales77',max=5)

##   customer_id sales       date product_id
## 1       10001   845 2017-07-05        407
## 2       10002  2370 2015-11-18        617
## 3       10003  5744 2017-02-10        928
## 4       10004  3230 2017-01-13        500
## 5       10005  8781 2017-04-23        555

#sqlColumns(chan,'sales77')

sales=sqlFetch(chan,'sales77',max=5000)
str(sales)

## 'data.frame':    500 obs. of  4 variables:
##  $ customer_id: int  10001 10002 10003 10004 10005 10006 10007 10008 10009 10010 ...
##  $ sales      : int  845 2370 5744 3230 8781 2544 217 306 8720 6137 ...
##  $ date       : Date, format: "2017-07-05" "2015-11-18" ...
##  $ product_id : int  407 617 928 500 555 316 187 880 900 110 ...

library(RColorBrewer)
hist(sales$sales,col=brewer.pal(5,"Blues"))
library(lubridate)

## 
## Attaching package: 'lubridate'

## The following object is masked from 'package:base':
## 
##     date

sales$month=month(sales$date)
#install.packages("Hmisc")
library(Hmisc)

## Loading required package: lattice

## Loading required package: survival

## Loading required package: Formula

## Loading required package: ggplot2

## 
## Attaching package: 'Hmisc'

## The following objects are masked from 'package:base':
## 
##     format.pval, round.POSIXt, trunc.POSIXt, units

attach(sales)

## The following object is masked _by_ .GlobalEnv:
## 
##     sales

summarize(sales$sales,sales$month,sum)

##    sales$month sales$sales
## 1            1      287623
## 5            2      188986
## 6            3      261182
## 7            4      183638
## 8            5      246474
## 9            6      244724
## 10           7      197330
## 11           8      251764
## 12           9      185211
## 2           10       96029
## 3           11      178954
## 4           12      141284

a=summarize(sales$sales,sales$month,sum)
names(a)

## [1] "sales$month" "sales$sales"

#plot(a[1],a[2])
plot(summarize(sales$sales,sales$month,sum))