SQLite操作sakila.db时RANGE INTERVAL窗口函数出现语法错误

问题重现 #

执行以下Python代码操作sakila.db时触发SQL语法错误：

pd.read_sql_query('''
                SELECT date(payment_date),
                        sum(amount) as total_date,
                      avg(sum(amount)) over (order by date(payment_date)
                         RANGE BETWEEN INTERVAL '2'  day PRECEDING AND INTERVAL  '2' day FOLLOWING) rolling_7_days
                FROM payment
                WHERE payment_date BETWEEN '2005-07-01' AND '2005-09-01'
                GROUP BY date(payment_date)

                ''', conn)

错误提示：

': near "'2'": syntax error

错误原因 #

SQLite不支持INTERVAL '2' day这类日期间隔语法，这是部分其他SQL数据库（如PostgreSQL）的写法。在SQLite的窗口函数RANGE BETWEEN子句中，当ORDER BY字段为日期类型时，无法直接用INTERVAL定义范围，需改用数值化的日期或其他SQLite支持的日期处理方式。

解决方案 #

将日期转换为儒略日（julianday）数值，通过数值范围实现前后2天的滚动窗口，修改后的代码如下：

pd.read_sql_query('''
                SELECT date(payment_date) as payment_day,
                        sum(amount) as total_date,
                        avg(sum(amount)) over (
                            order by julianday(date(payment_date))
                            RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
                        ) rolling_5_days
                FROM payment
                WHERE payment_date BETWEEN '2005-07-01' AND '2005-09-01'
                GROUP BY payment_day
                ''', conn)

关键说明 #

• julianday()函数将日期转换为连续的数值，相邻日期的数值差为1，因此RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING正好对应前后2天的范围。
• 原语句中的rolling_7_days命名不符合实际范围：前后2天+当天共5天，若需要7天滚动（前后3天+当天），需将范围改为RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING。

内容的提问来源于stack exchange，提问作者Tam Anh Phan