范围间期SQLite

jq6vz3qz 于 2023-03-23 发布在 SQLite

关注(0)|答案(1)|浏览(159)

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)

我用sqlite3对sakila.db做了测试，发现了这个问题：

': near "'2'": syntax error

按照说明操作，但不正常。https://support.workiva.com/hc/en-us/articles/360042534732-SQL-date-and-time-functions

sqlite

来源：https://stackoverflow.com/questions/75818888/range-interval-sqlite

1条答案

按热度按时间

um6iljoc1#

正如您的错误消息正确指出为syntax error;
RANGE BETWEEN INTERVAL在sqlite for Window函数中不受支持。
相反，您可以使用BETWEEN使用标准SQL语法
以下将为您工作。

pd.read_sql_query('''
SELECT
  date(payment_date),
  sum(amount) as total_date,
  (
    SELECT avg(sum(amount))
    FROM payment p2
    WHERE p2.payment_date BETWEEN date(p.payment_date, '-2 days') AND date(p.payment_date, '+2 days')
  ) as rolling_7_days
FROM payment p
WHERE payment_date BETWEEN '2005-07-01' AND '2005-09-01'
GROUP BY date(payment_date)
''', conn)

赞(0）回复(0）举报 2023-03-23

我来回答

范围间期SQLite

1条答案

相关问题

热门标签

最新问答