如何返回一年中每天的记录数，而不必在循环中进行代价高昂的查询？

Question

在数据库表中，我们可以找到具有文件路径的记录、处理文件的员工以及处理文件时的时间戳。 表“Log”只包含每个员工几千条记录。每个员工每天都有多条记录带有时间戳（但不是唯一的时间戳）。 我想提取一个元组列表，其中包含一个日期以及在该日期生成的表条目的数量

我提供的代码可以工作，但速度非常慢。2300条记录的22秒计算时间是荒谬的。 我已经将问题缩小到for循环中的行“logs\u per\u day=（query.select（）.where（fn.date（cls.datetime）==checkday.count（））”。 我知道在一个循环中执行许多查询可能不是很好。此外，datetime对象到日期的转换可能也没有帮助。。。 能告诉我一个更好的方法吗

import datetime
import os

from peewee import *

db = SqliteDatabase('logs.db')
# db = SqliteDatabase(':memory:')
now = datetime.datetime.now()

class BaseModel(Model):

    class Meta:
        database = db


class Log(BaseModel):

    log_ID = AutoField()
    datetime = DateTimeField()
    letter = CharField()
    disk_path = CharField()
    ftp_path = CharField()
    out = BooleanField()
    employee = CharField(null=True)

    class Meta:
        table_name = 'log'

    @classmethod
    def get_histo_data(cls, employee="Some Dude", year=None):
        """returns a list with sublists (datetime object, integer)"""
        if not year: # if no year was provided the query return all entries from the employee
            query = cls.select().where(cls.employee == employee).order_by(cls.datetime)
            print(employee, len(query), " entries")

            firstday = query.order_by(cls.datetime).get().datetime.date()
            lastday = query.order_by(cls.datetime.desc()).get().datetime.date()

        else: # returns all entries in the given year
            query = (cls
                        .select()
                        .where(cls.employee == employee, cls.datetime.year == year)
                        .order_by(cls.datetime)
                    )
            print("{} has {} entries in the year {}".format(employee, len(query), year))

            firstday = datetime.date(year, 1, 1)
            lastday = datetime.date(year, 12, 31)

        print("first day sent: ", firstday)
        print("last day sent: ", lastday)

        daydelta = (lastday-firstday).days
        sendList = []

        for i in range(daydelta+1):   ### FIXME: This is extremely slow!!!
            checkday = firstday + datetime.timedelta(days=i)
            logs_per_day = (query
                                .select()
                                .where(fn.date(cls.datetime) == checkday)
                                .count()
                                )

            # print(checkday, "*** logs that day: ", logs_per_day)
            sendList.append([checkday, logs_per_day])

        return sendList

def initialize():
    db.connection()
    db.create_tables([Log], safe=True)
    db.close()

if __name__ == '__main__':
    initialize()

    Log.get_histo_data(employee="Mr Someone", year=2018)

输出应该类似于“[（2018-11-12157），（2018-11-13,12），（2018-11-14,0）…]

Answer 1

我自己发现的：

def get_histo_data(cls, employee="Some dude", year=None):
    """returns a list of tuples (datetime object, integer)"""
    if not year: # if no year was provided the query return all entries from the employee
        query = cls.select().where(cls.employee == employee).order_by(cls.datetime)
        print(employee, len(query), " entries")

        firstday = query.order_by(cls.datetime).get().datetime.date()
        lastday = query.order_by(cls.datetime.desc()).get().datetime.date()

    else: # returns all entries in the given year
        query = cls.get_query_by_year(employee, year)
        print("{} has {} entries in the year {}".format(employee, len(query), year))

        firstday = datetime.date(year, 1, 1)
        lastday = datetime.date(year, 12, 31)

    ### count the entries
    logDict = dict()
    for record in query:
        date = record.datetime.date()
        if date not in logDict:
            logDict[date] = 1
        else:
            logDict[date] += 1
    ### fill the null days
    daydelta = (lastday-firstday).days
    for i in range(daydelta+1):
        checkday = firstday + datetime.timedelta(days=i)
        if checkday not in logDict:
            logDict[checkday] = 0
        else:
            continue

    return list(sorted(logDict.items()))