Question

Im利用R绘制一些数据,从一个数据库(Stack Exchange数据倾弃 ,具体如下:

dplyr::tbl(serverfault, 
           dbplyr::sql("
select year(p.CreationDate) year,
       avg(p.AnswerCount*1.0) answers_per_question,
       sum(iif(ClosedDate is null, 0.0, 100.0))/count(*) close_rate
from Posts p
where PostTypeId = 1
group by year(p.CreationDate)
order by year(p.CreationDate)
"))

The query ,但我在R console发现这一错误:

Error: <SQL>  SELECT *
FROM (
select year(p.CreationDate) year,
       avg(p.AnswerCount*1.0) answers_per_question,
       sum(iif(ClosedDate is null, 0.0, 100.0))/count(*) close_rate
from Posts p
where PostTypeId = 1
group by year(p.CreationDate)
order by year(p.CreationDate)
) "zzz11"
WHERE (0 = 1) 
  nanodbc/nanodbc.cpp:1587: 42000: [FreeTDS][SQL Server]Statement(s) could not be prepared.

我认为,“各国无法做好准备”,这意味着服务器与盘点一样,出于某种原因。不幸的是,它并没有对什么错误表示任何担忧。在绕过对借方的询问之后,我注意到,根据错误信息,该点被打上了记分。服务器在链条的一个图书馆构造下,复制和执行全部盘问,给我这个信息更加翔实的错误信息:

按条款分列的书面记录在意见、在线功能、衍生表格、子类和共同表格表述中无效,除非指明东方木材公司、外国航天公司或意大利法郎。

Now the solution is obvious: remove (or comment out) the order by clause. But where is the detailed error message in the R console? I m using Rstudio, should that matter. If I could get the full exception right next to the code I m working on, it would help me fix bug a lot quicker. (And just to be clear, I get cryptic errors from dplyr::tbl often and typically use binary search debugging to fix them.)

Answer 1

library(dbplyr)
library(dplyr)

# Assuming `serverfault` is your database connection
query <- dplyr::tbl(serverfault, 
  dbplyr::sql("
    SELECT 
      YEAR(p.CreationDate) AS year,
      AVG(p.AnswerCount * 1.0) AS answers_per_question,
      SUM(IIF(ClosedDate IS NULL, 0.0, 100.0)) / COUNT(*) AS close_rate
    FROM Posts p
    WHERE PostTypeId = 1
    GROUP BY YEAR(p.CreationDate)
    -- ORDER BY YEAR(p.CreationDate)  -- Commented out the ORDER BY clause
  ")
)

# Display the translated SQL query
show_query(query)

# Execute the query and collect the result
result <- collect(query)

该法典包括显示“频率”功能,显示翻译的QQ。通过在Kall query中逐条评论ER项目,你可以侧重于清点和理解结构。在解决任何问题之后,你可以相应地调整问题。

友情链接