按月份和年份对日期字段进行分组

Question

以下表格中的数据Loan_Amendment:

    ID     |     Amount     | AddDeduct | AmendDate  
-----------+----------------+-----------+------------
 LT0000160 | 1000.000000000 | 2         | 2018-02-08
 LT0000170 | 1500.00000000  | 1         | 2018-02-10
 LN0000577 | 1000.000000000 | 1         | 2018-03-20
 LN0000587 | 2000.000000000 | 2         | 2018-03-20

我试图选择具有相同年份和月份的AmendDate的Amount字段的总和。

因此，我尝试使用以下SQLALchemy查询：

AmendObj = db.session.query(func.sum(Loan_Amendment.Amount).label('Amount'),
                            Loan_Amendment.AmendDate).\
    group_by(Loan_Amendment.AmendDate).\
    first()

然而，根据我尝试的查询，我只能按照AmendDate的精确日期（年、月和日）进行分组。

更新：

我的模型如下所示：

class Loan_Amendment(Object):
    ID              =   db.Column(db.String(13), primary_key=True)
    AddDeduct       =   db.Column(db.String(1))
    Amount          =   db.Column(db.Numeric(25, 9))
    AmendDate       =   db.Column(db.String(20))

目前，我正在使用Flask-SQLAlchemy==2.0和SQLAlchemy==0.9.9，数据库类型为postgresql。

我该如何选择仅按月份和年份对AmendDate进行汇总和分组？

- Houy Narun

---

你使用哪个数据库？ - Ilja Everilä

我正在使用 postgresql 数据库。谢谢。 - Houy Narun

Answer 1

使用date_trunc()函数将日期截断到月份精度：

month = func.date_trunc('month', Loan_Amendment.AmendDate)

AmendObj = db.session.query(func.sum(Loan_Amendment.Amount).label('Amount'),
                            month).\
    group_by(month).\
    first()
    # Did you mean all()?

另一个不太依赖于数据库的选择是使用EXTRACT()函数：

from sqlalchemy import extract

AmendObj = db.session.query(func.sum(Loan_Amendment.Amount).label('Amount'),
                            extract('year', Loan_Amendment.AmendDate),
                            extract('month', Loan_Amendment.AmendDate)).\
    group_by(extract('year', Loan_Amendment.AmendDate),
             extract('month', Loan_Amendment.AmendDate)).\
    first()

---

由于您的日期列实际上是文本，因此首先需要进行类型转换。如果您使用的SQLAlchemy版本具有1.0.7或更高版本的ColumnElement.cast()快捷方式，则只需

func.date_trunc('month', Loan_Amendment.AmendDate.cast(Date))

但如果没有，那么...

from sqlalchemy import cast

func.date_trunc('month', cast(Loan_Amendment.AmendDate, Date))

自然地，如果使用EXTRACT，同样适用。

---

考虑到您的模型和数据，一个巧妙的解决方案是简单地取包含年份和月份部分的子字符串并进行分组：

month = func.substr(Loan_Amendment.AmendDate, 1, 7)

- Ilja Everilä

---

我按照上面的第一个选项操作时，出现了错误信息：ProgrammingError: (ProgrammingError) function date_trunc(unknown, character varying) does not exist LINE 1: ...T sum("Loan_Amendment"."Amount") AS "Amount", date_trunc... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. 'SELECT sum("Loan_Amendment"."Amount") AS "Amount", date_trunc(%(date_trunc_2)s, "Loan_Amendment"."AmendDate") AS date_trunc_1... 我确定我已经from sqlalchemy import *，谢谢。 - Houy Narun

从选项1开始，我尝试将日期转换为字符串，就像这样month = func.date_trunc('month', str(Loan_Amendment.AmendDate))，但是我得到了ProgrammingError: (ProgrammingError) function date_trunc(unknown, unknown) is not unique。出了什么问题？谢谢。 - Houy Narun

你没有提到你的日期列实际上是文本。添加一个转换为日期的语句：Loan_Amendment.AmendDate.cast(Date)。str(Loan_Amendment.AmendDate)不是“转换为文本”。它只是在Python中生成给定对象的字符串表示形式。 - Ilja Everilä

是的，我的日期字段是文本类型。尝试强制转换，如上所示Loan_Amendment.AmendDate.cast(Date)，仍然报错AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Loan_Amendment.AmendDate has an attribute 'cast' :( - Houy Narun

请在问题中包含您正在使用的SQLAlchemy版本和实际模型。同时，使用sqlalchemy.sql.expression.cast()构造来解决错误：cast(Loan_Amendment.AmendDate, Date)。 - Ilja Everilä