窗口函数在pd.read_sql中不起作用；它显示错误

我目前在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中看到的那样，他们在此版本中做的第一件事是：

1. 添加对窗口函数的支持

这是我以前遇到过的问题，诀窍是更新您的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。