# Load the package into R
library(RMySQL)
library(DBI)
# Connect to your MySQL database
con <- dbConnect(MySQL(),
host = "localhost",
user = "root",
password = "Alex9297248844",
dbname = "windows_functions")retrieve the data
df <- dbGetQuery(con, "SELECT * FROM prices")head(df,10)## id item date price
## 1 1 Guitar 2023-01-01 150.0
## 2 2 Guitar 2023-01-02 50.0
## 3 3 Guitar 2023-01-03 101.5
## 4 4 Guitar 2023-01-04 99.0
## 5 5 Guitar 2023-01-05 300.0
## 6 6 Drum 2023-01-01 500.0
## 7 7 Drum 2023-01-02 198.0
## 8 8 Drum 2023-01-03 250.0
## 9 9 Drum 2023-01-04 205.0
## 10 10 Drum 2023-01-05 210.0i used the window functions in mySQL to calculate the year-to-date average and the six-day moving averages for each item.
here what i got
# Load the package into R
library(RMySQL)
library(DBI)
# Connect to your MySQL database
con <- dbConnect(MySQL(),
host = "localhost",
user = "root",
password = "Alex9297248844",
dbname = "windows_functions")Retrive the data
df2 <- dbGetQuery(con, "SELECT * FROM price_averages")head(df2 , 10)## id item date price year_to_date_avg six_day_moving_avg
## 1 1 Drum 2023-01-01 500.0 500.00 500.00
## 2 2 Drum 2023-01-02 198.0 349.00 349.00
## 3 3 Drum 2023-01-03 250.0 316.00 316.00
## 4 4 Drum 2023-01-04 205.0 288.25 288.25
## 5 5 Drum 2023-01-05 210.0 272.60 272.60
## 6 6 Guitar 2023-01-05 300.0 300.00 300.00
## 7 7 Guitar 2023-01-01 150.0 150.00 150.00
## 8 8 Guitar 2023-01-02 50.0 100.00 100.00
## 9 9 Guitar 2023-01-03 101.5 100.50 100.50
## 10 10 Guitar 2023-01-04 99.0 100.13 100.13Disconnect from the database
dbDisconnect(con)## [1] TRUE