假设我在这样的表上有数据：
id | other_id | category | amount
--------------------------------
1 | abc | widget | 100
2 | abc | widget | 200
3 | def | widget | 100
4 | ghi | gadget | 100
5 | ghi | gadget | 100
6 | jkl | gadget | 100
7 | jkl | gadget | 100
我想查询该表以返回
(other_id, category, sum_of_amount)
哪里sum_of_amount是amount同一列中所有行的列总和other_id。除了这个，我要排除其中的组合的元组category和sum_of_amount不是唯一的。因此查询应返回元组：
(abc, widget, 300)
(def, widget, 100)
而不是任何gadget行，因为组合(gadget, 200)不是唯一的。
到目前为止，我有这个：
with session_scope() as db_session:
query = db_session.query(
ModelClass.other_id,
ModelClass.category,
label('sum_of_amount', func.sum(ModelClass.amount))
).group_by(
ModelClass.other_id,
ModelClass.category
)
该查询未过滤掉任何内容。我想我需要使用独特的方式，但我不知道。
您可以(ohter_id, category, sum_of_amount)根据查询生成查询的结果集：
=# SELECT other_id, category, SUM(amount) AS sum_of_amount
FROM test
GROUP BY other_id, category;
other_id │ category │ sum_of_amount
──────────┼──────────┼───────────────
abc │ widget │ 300
ghi │ gadget │ 200
jkl │ gadget │ 200
def │ widget │ 100
(4 rows)
然后，您必须排除(category, sum_of_amount)不唯一的行。在上述确定每一行唯一性的结果集中，您可以添加新列，其中包含具有相同行数的行，(category, sum_of_amount)如下所示：
=# SELECT other_id, category, SUM(amount) AS sum_of_amount,
COUNT(*) OVER (PARTITION BY category, SUM(amount)) AS duplicates
FROM test
GROUP BY other_id, category;
other_id │ category │ sum_of_amount │ duplicates
──────────┼──────────┼───────────────┼───────
ghi │ gadget │ 200 │ 2
jkl │ gadget │ 200 │ 2
def │ widget │ 100 │ 1
abc │ widget │ 300 │ 1
(4 rows)
正如您在上面的演示中所看到的，您手中有行列式。现在，您可以通过WHERE使用duplicates列添加子句来生成要查找的结果集。由于WHERE子句中不允许使用窗口函数（列的OVER一部分duplicates），并且我们必须在计算总金额后评估窗口函数的结果，因此必须使用子查询。
=# SELECT other_id, category, sum_of_amount
FROM (
SELECT other_id, category, SUM(amount) AS sum_of_amount,
COUNT(*) OVER (PARTITION BY category, SUM(amount)) AS duplicates
FROM test
GROUP BY other_id, category
) d
WHERE duplicates = 1
ORDER BY other_id, category;
other_id │ category │ sum_of_amount
──────────┼──────────┼───────────────
abc │ widget │ 300
def │ widget │ 100
(2 rows)
以上SQL的SQLAlchemy查询表达式可以是：
from sqlalchemy import func, over
sum_of_amount = label('sum_of_amount', func.sum(ModelClass.amount))
duplicates = over(func.count('*'),
partition_by=(ModelClass.category, sum_of_amount))
query = db_session.query(
ModelClass.other_id,
ModelClass.category,
sum_of_amount,
duplicates,
).group_by(
ModelClass.other_id,
ModelClass.category
).from_self(
ModelClass.other_id,
ModelClass.category,
sum_of_amount
).filter(duplicates == 1)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句