如何使用sqlite3模块计算SQLite中最近N个条目的平均值？

sshcrbum 于 2022-12-19 发布在 SQLite

关注(0)|答案(2)|浏览(210)

我使用Python 3.11编写代码，使用tkinter和sqlite3包，生成了一个包含四列的数据库，其中一列名为weight，其值定义为real我想做的是使用cursor.execute编写一个函数，“选择”weight列中最近的7个条目，计算并返回这7个值的平均值。
我知道SQLite3有内置函数AVG()，我也尝试过使用它，但是该函数取weight列中所有条目的平均值，我还没有找到一种方法来指示它只取N个最近的条目。
我也知道Sqlite3有cursor.fetchmany(7)的能力，但是Sqlite3把所有数据都变成元组。所以当我fetchmany(7)并硬编码它来产生平均值时，它会抛出元组无法与int/str/floats交互的错误。下面是我的函数到目前为止的样子。当我执行这个函数时，我实际得到的是列中所有条目的平均值。而不是最后7个。

def average_query():
    
    #Create a database or connect to one
    conn = sqlite3.connect('weight_tracker.db')
    #Create cursor
    c = conn.cursor()
    
    my_average = c.execute("SELECT round(avg(weight)) FROM weights ORDER BY oid DESC LIMIT 7")
    my_average = c.fetchall()
    my_average = my_average[0][0]
    
    #Create labels on screen
    average_label = Label(root,text=f"Your average 7-day rolling weight is {my_average} pounds.")
    average_label.grid(row=9, column=0, columnspan=2)

    #Commit changes
    conn.commit()
    #Close connection
    conn.close()

sqlite

来源：https://stackoverflow.com/questions/74804779/how-to-calculate-the-average-of-the-n-most-recent-entries-in-sqlite-using-the-sq

2条答案

按热度按时间

aydmsdu91#

你可以抽取出前5名，然后在Python中求平均值：

res = c.execute('SELECT weight FROM weights ORDER BY oid DESC LIMIT 7')
rows = res.fetchall()
# E.G. [(40,), (0,), (0,), (2500,), (1500,), (144,), (999,)]

avg = sum(r[0] for r in rows) / len(rows)
# 740.4285714285714

也可以使用嵌套查询来执行平均：

res = c.execute('SELECT ROUND(AVG(*)) FROM ( SELECT weight FROM weights ORDER BY oid DESC LIMIT 7 );')

rows = res.fetchall()
# [(740.4285714285714,)]

avg = rows[0][0]
# 740.4285714285714

赞(0）回复(0）举报 2022-12-19

wgx48brx2#

嘿👋，我建议你尽量把数据保存在SQL中，并在那里计算平均值。SQL就是为这种操作而设计和优化的。
为了达到最后N个条目的平均值，你可以依靠window functions。它们需要一段时间来适应，但一旦你弄清楚它们，它们就超级强大了。
应该给予结果的SQL查询如下所示：

SELECT
  AVG(weight) OVER (
    ORDER BY oid DESC
    ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
  ) as avg_weight
FROM
  weights

如果你想测试的话，我把这些放在一个简单的python脚本中，注意数据和列的名字是不同的，但是它应该告诉你在python中是如何做的。

import sqlite3

connection = sqlite3.connect("demo.db")
cursor = connection.cursor()

## Setup database for testing ##
cursor.execute(
    """
    CREATE TABLE IF NOT EXISTS example (
        weight INT,
        timestamp INT
    )
"""
)

cursor.execute(
    """
    INSERT INTO example (weight, timestamp)
    VALUES 
        (10, 0), 
        (13, 1), 
        (5, 2), 
        (6, 3), 
        (10, 4), 
        (3, 5), 
        (10, 6), 
        (13, 7), 
        (5, 8), 
        (6, 9)
"""
)

## Get rolling average ##

cursor.execute(
    """
    SELECT
        AVG(weight) OVER (
            ORDER BY timestamp
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
        )
    FROM
        example
"""
)

print(cursor.fetchall())

赞(0）回复(0）举报 2022-12-19

我来回答

如何使用sqlite3模块计算SQLite中最近N个条目的平均值？

2条答案

相关问题

热门标签

最新问答