Error in odbcConnectAccess("Northwind.mdb") : could not find function "odbcConnectAccess"
时间: 2024-05-29 19:14:15 浏览: 22
这个错误提示是因为R语言缺少访问Microsoft Access数据库所需的ODBC驱动程序。你需要安装Microsoft Access ODBC驱动程序。你可以在Microsoft官网上下载和安装Microsoft Access Database Engine,它包含了ODBC驱动程序。另外,你也可以尝试使用其他的ODBC驱动程序来连接Access数据库,比如Easysoft ODBC-Access Driver、MDB Tools等。
相关问题
用RODBC链接Northwind.mdb文件,从文件中提取Employees表和Orders表,根据EmployeeID合并两个数据框。
好的,我理解了您的要求,以下是可以实现您要求的R语言代码:
```r
# 安装RODBC包
install.packages("RODBC")
library(RODBC)
# 连接Northwind.mdb文件
channel <- odbcConnectAccess("Northwind.mdb")
# 从文件中提取Employees表和Orders表
Employees <- sqlQuery(channel, "SELECT * FROM Employees")
Orders <- sqlQuery(channel, "SELECT * FROM Orders")
# 根据EmployeeID合并两个数据框
merged_data <- merge(Employees, Orders, by = "EmployeeID")
# 关闭连接
odbcClose(channel)
# 输出合并后的数据框
print(merged_data)
```
以上代码会链接到Northwind.mdb文件,然后从文件中提取Employees表和Orders表,再根据EmployeeID将两个数据框进行合并。最后输出合并后的数据框。注意,这里RODBC包需要和Access一起使用,所以在您的计算机中需要安装Access软件才能够运行。
5. (2 points) Calculate the 7-day (current day and 6 previous days, skip days without orders which are weekends as you’ve already known from Q2) rolling average of sales over time for Northwind. (HINT: Use Window Function. Set rolling average of first 6 rows to be null because there are no 7-days’ worth of information. Use WITH Clause to create temporary tables.)
Assuming we have a table `orders` with columns `order_id`, `order_date`, `customer_id`, and `order_total`, we can use the following query to calculate the 7-day rolling average of sales over time for Northwind, skipping weekends:
```
WITH daily_sales AS (
SELECT order_date, SUM(order_total) AS total_sales
FROM orders
WHERE EXTRACT(DOW FROM order_date) NOT IN (0, 6) -- exclude weekends
GROUP BY order_date
), daily_sales_ranked AS (
SELECT order_date, total_sales,
RANK() OVER (ORDER BY order_date ASC) AS daily_rank
FROM daily_sales
), rolling_average AS (
SELECT order_date, total_sales,
CASE WHEN daily_rank <= 6 THEN NULL
ELSE AVG(total_sales) OVER (ORDER BY order_date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
END AS sales_moving_average_7
FROM daily_sales_ranked
)
SELECT *
FROM rolling_average
ORDER BY order_date ASC;
```
In this query, we first create a CTE called `daily_sales` that sums up the `order_total` for each `order_date` and excludes weekends using the `EXTRACT` function.
Then, we create another CTE called `daily_sales_ranked` that assigns a rank to each row based on the `order_date`.
Finally, we create a third CTE called `rolling_average` that calculates the 7-day rolling average of sales using the `AVG` window function and sets the rolling average of the first 6 rows to null.
The final SELECT statement retrieves all columns from the `rolling_average` CTE and orders the results by `order_date` in ascending order.
相关推荐
![application/x-rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![whl](https://img-home.csdnimg.cn/images/20210720083646.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)