数据浏览器中的语法错误 [英] syntax error in Data Explorer

问题描述

Stack Exchange数据资源管理器允许对Stack Exchange数据库进行SQL查询.我尝试过这个

The Stack Exchange Data Explorer allows SQL queries against a Stack Exchange database. I tried this one —

select
  month(CreationDate) month,
  year(CreationDate) year,
  sum(lower(left(Title,2))='wh')/count(*) wh,
  (select sum(Score)/count(*)
   from Posts u
   where month(CreationDate)=month(t.CreationDate)
     and year(CreationDate)=year(t.CreationDate)
     and lower(left(Title,2))='wh'
     and PostTypeId=1 -- question
  ) wh_score,
  sum(Score)/count(*) score,
  (select sum(AnswerCount)/count(*)
   from Posts u
   where month(CreationDate)=month(t.CreationDate)
     and year(CreationDate)=year(t.CreationDate)
     and lower(left(Title,2))='wh'
     and PostTypeId=1 -- question
  ) wh_answers,
  sum(AnswerCount)/count(*) answers
from Posts t
where PostTypeId=1 -- question
group by month,year;

—但该网站告诉我

— but the site told me

')'附近的语法不正确. 'wh_score'附近的语法不正确. "wh_answers"附近的语法不正确.

Incorrect syntax near ')'. Incorrect syntax near 'wh_score'. Incorrect syntax near 'wh_answers'.

，我不知道为什么.有人可以帮忙吗?

and I cannot figure out why. Can anyone help, please?

我尝试过的事情无济于事:

Things I've tried, to no avail:

• datepart(month,CreationDate)而不是month(CreationDate)(对于year同样)
• 明确使用as别名(然后，三个错误中的后两个抱怨"at"而不是别名)
• 不是内置函数名称的别名
• left(Title,2)而不是lower(left(Title,2))
• 在由and s连接的四个事物中的前两个和后两个周围加上括号.
• 对子查询中的列名称明确表示u.

• datepart(month,CreationDate) instead of month(CreationDate) (and likewise for year)
• explicit as for aliases (then the latter two of the three errors complained about 'at' rather than about the aliases)
• aliases that aren't built-in function names
• left(Title,2) instead of lower(left(Title,2))
• putting parentheses around the first two, and around the last two, of the four things joined by ands
• explicit u. for column names in the subqueries

推荐答案

1. 您不能按别名分组，需要指定计算列
2. 不允许这样做:sum(lower(left(Title,2))='wh').您必须将其转换为CASE WHEN运算符.

1. You can't group by an alias, you need to specify the computed column
2. This is not allowed : sum(lower(left(Title,2))='wh'). You have to convert it into a CASE WHEN operator.

这里有一个更正的查询(给出了超时):

Here a corrected query (that gives a timeout):

select
    month(CreationDate) month
  , year(CreationDate) year
  , sum(case when lower(left(Title,2))='wh' then 1 else 0 end)/count(*) wh
  , (select sum(Score)/count(*)
   from Posts u
   where month(CreationDate)=month(t.CreationDate)
     and year(CreationDate)=year(t.CreationDate)
     and lower(left(Title,2))='wh'
     and PostTypeId=1 -- question
  ) wh_score,
  sum(Score)/count(*) score,
  (select sum(AnswerCount)/count(*)
   from Posts u
   where month(CreationDate)=month(t.CreationDate)
     and year(CreationDate)=year(t.CreationDate)
     and lower(left(Title,2))='wh'
     and PostTypeId=1 -- question
  ) wh_answers,
  sum(AnswerCount)/count(*) answers
from Posts t
where PostTypeId=1 -- question
group by month(CreationDate), year(CreationDate);

您要如何处理此查询?

这篇关于数据浏览器中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！