SQLAlchemy仅过滤不同对中的最小字段值

Question

我有一个ProductPurchase模型，它描述了client所做的购买

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy import String

Base = declarative_base()


class ProductPurchase(Base):
   __tablename__ = "product_client"
   
   client_id = Column(String(255))
   product_id = Column(String(255))
   purchased_at = Column(DateTime(timezone=True))

我想做的是得到ProductPurchase的列表，在这个列表中，对于给定的product_id，我只会第一次购买一个client。 例如：

+-----------+------------+--------------+
| client_id | product_id | purchased_at |
+-----------+------------+--------------+
| c1        | prod1      | 2020-01-01   |
+-----------+------------+--------------+
| c1        | prod1      | 2020-01-02   |
+-----------+------------+--------------+
| c2        | prod1      | 2020-01-01   |
+-----------+------------+--------------+
| c2        | prod2      | 2020-01-01   |
+-----------+------------+--------------+

我想获得以下行：

+-----------+------------+--------------+
| client_id | product_id | purchased_at |
+-----------+------------+--------------+
| c1        | prod1      | 2020-01-01   |
+-----------+------------+--------------+
| c2        | prod1      | 2020-01-01   |
+-----------+------------+--------------+
| c2        | prod2      | 2020-01-01   |
+-----------+------------+--------------+

请注意，date2020-01-02缺少client_id=1和product_id=1对，因为它应该被过滤掉-目标是仅获得客户首次购买的产品。 如何使用sqlalchemy尝试此操作

Answer 1

以下是sql代码、sqlachemy support all over（）和PArition by语句以及cte的使用，只需遵循sqlachemy文档：

;WITH cte AS (
SELECT 
   *
  , rank() OVER (PARTITION BY p.client_id , p.product_id ORDER BY p.purchased_at ASC) rnk
 FROM 
product AS p
)

SELECT cte.client_id
     , cte.product_id
     , cte.purchased_at
FROM cte
WHERE cte.rnk = 1

Answer 2

在SQLAlchemy中，您可以按客户id和产品id使用groupy，并至少在购买时使用groupy

像这样的

from sqlalchemy import func

session.query(ProductPurchase.client_id ,ProductPurchase.purchased_id , func.min(ProductPurchase.purchased_at)).group_by(ProductPurchase.client_id ,ProductPurchase.purchased_id).all()