窗口函数在pd.read_sql中不起作用；它显示错误 [英] Window functions not working in pd.read_sql; Its shows error
问题描述
我目前在Google Collab(Jupyter笔记本)中使用欧洲足球SQLite数据库进行数据分析。
分析的目的；对于特定的球队ex：切尔西，获取每场比赛的胜负标签(使用Case语句完成)，然后按赛季和胜负结果划分比赛计数。 这一切都是在Google Collab(Jupyter笔记本)中的pd.Read_SQL()语句中完成的。
在引入窗口函数之前，该语句运行得很好。但是查询在SQLite DB浏览器中运行得很好(附图)。我得到的主要错误是OperationalError: near "(": syntax error
以下是代码
Home_Perf = pd.read_sql(""" --- CTE to get the wins and loss as a home team
WITH Homes AS (
SELECT season, team_long_name AS HomeTeam,
home_team_goal, away_team_goal,
CASE
WHEN home_team_goal > away_team_goal THEN 'win'
WHEN home_team_goal < away_team_goal THEN 'loss'
ELSE 'Tie' END AS Win_Loss
FROM match
---Inner JOIN for getting the team name
INNER JOIN team
ON team_api_id = home_team_api_id
WHERE home_team_api_id = 8455)
SELECT season, HomeTeam,
COUNT(Win_Loss) OVER(PARTITION BY season) AS counts
FROM homes""", conn)
Home_Perf
以下是错误
ERROR:root:An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line string', (1, 38))
---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
1585 try:
-> 1586 cur.execute(*args, **kwargs)
1587 return cur
OperationalError: near "(": syntax error
The above exception was the direct cause of the following exception:
DatabaseError Traceback (most recent call last)
3 frames
<ipython-input-17-9b1c924dbbdd> in <module>()
15 SELECT season, HomeTeam,
16 COUNT(Win_Loss) OVER(PARTITION BY season) AS counts
---> 17 FROM homes""", conn)
18 Home_Perf
/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
410 coerce_float=coerce_float,
411 parse_dates=parse_dates,
--> 412 chunksize=chunksize,
413 )
414
/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, params, parse_dates, chunksize)
1631
1632 args = _convert_params(sql, params)
-> 1633 cursor = self.execute(*args)
1634 columns = [col_desc[0] for col_desc in cursor.description]
1635
/usr/local/lib/python3.6/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
1596
1597 ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")
-> 1598 raise ex from exc
1599
1600 @staticmethod
DatabaseError: Execution failed on sql ' --- CTE to get the wins and loss as a home team
WITH Homes AS (
SELECT season, team_long_name AS HomeTeam,
home_team_goal, away_team_goal,
CASE
WHEN home_team_goal > away_team_goal THEN 'win'
WHEN home_team_goal < away_team_goal THEN 'loss'
ELSE 'Tie' END AS Win_Loss
FROM match
---Inner JOIN for getting the team name
INNER JOIN team
ON team_api_id = home_team_api_id
WHERE home_team_api_id = 8455)
SELECT season, HomeTeam,
COUNT(Win_Loss) OVER(PARTITION BY season) AS counts
FROM homes': near "(": syntax error
推荐答案
tl；drGoogle Colab使用的是SQLite 3.22版，但SQLite仅支持3.25版的窗口函数。
我认为问题在于Google Colab使用的SQLlite版本过时，不支持窗口函数。谷歌不得不承认这一点！我写这篇文章是从2022年3月6日开始。
切中您的观点，更具体地说：
2018年9月15日，SQLite发布了3.25版本，正如您在发布日志here中看到的那样，他们在此版本中做的第一件事是：
- 添加对窗口函数的支持
这是我以前遇到过的问题，诀窍是更新您的SQLite3库，但是，您不是在本地设备上，而是在Google Colab上。
因此，您要做的下一件事是检查您的SQLlite版本。您可以通过运行下面这行SQL代码来实现这一点：SELECT sqlite_version();我从here那里得到了这个好东西。
由于您是在Pandas内进行查询，因此您将运行：
pd.read_sql_query("""
SELECT sqlite_version();
""", conn)
所以我今天(3/6/22)正好运行了这个程序，在我的Google Colab中得到了以下内容：
这个版本太旧了。我不知道如何更新Google Colab中的Sqlite库。所以这就是我所得到的，我认为我们目前还没有那个功能。This post可能有助于更新笔记本中的SQLite。
这篇关于窗口函数在pd.read_sql中不起作用；它显示错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
