SQLAlchemy - 如何对多列进行去重计数

Question

SQLAlchemy - 如何对多列进行去重计数

5

我有这个查询：

SELECT COUNT(DISTINCT Serial, DatumOrig, Glucose) FROM values;

我尝试使用SQLAlchemy这种方式重新创建它：

session.query(Value.Serial, Value.DatumOrig, Value.Glucose).distinct().count()

但这个代码将被翻译成这个样子:

SELECT count(*) AS count_1
    FROM (SELECT DISTINCT 
           values.`Serial` AS `values_Serial`, 
           values.`DatumOrig` AS `values_DatumOrig`,
           values.`Glucose` AS `values_Glucose`
          FROM values)
    AS anon_1

这种方法不是直接调用计数函数，而是将 select distinct 包装成一个子查询。

我的问题是：使用SQLAlchemy，有哪些不同的方法可以计算多列上的不重复选择，并且它们会转化为什么？

是否有任何解决方案可以转换为我的原始查询？是否存在性能或内存使用方面的显著差异？

- MattSom

2

你是否已经要求MySQL解释这两个查询，以查看结果是否有显著不同？ - Gord Thompson

@GordThompson 目前还没有，这是个好主意。但是我知道，即使只运行子查询中的 select distinct 部分，MySQL 也会失去连接。这表明 SQLAlchemy 版本过于拖累我的 MySQL 服务器，而第一个本地版本在一分钟内返回（约 3200 万条记录）。 - MattSom

1

有任何一列可以为空吗？如果是这样，那么两个查询之间至少有一个区别，即“本地”查询（带有count(distinct ...)）不计算其中serial、datumorig或glucose为空的行。后者计算子查询生成的所有行，包括具有NULL值的（不同的）行。虽然这并没有解释性能差异，但很可能是原因。 - Ilja Everilä

1

从 https://www.db-fiddle.com/f/tTEQytCcsv8Xu1CGVPt1k4/0 看来，“子查询计数”会导致使用临时表，即使对于如此小的数据量，如果这是一个磁盘上的临时表，那么它可能至少部分解释了由于您的大型数据集而产生的性能差异。 - Ilja Everilä

@IljaEverilä 是的，我刚刚也对它们运行了 Explain。有趣的是，即使是子查询对我来说也失去了连接。其中的区别在于两种情况都使用了索引，但是在使用 select distinct(...) 时，它会显示 Using index; Using temporary，而在使用 count(distinct(...)) 版本时，它是 Using index for group-by (scanning)。 - MattSom

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ilja Everilä · Accepted Answer

首先，我认为COUNT(DISTINCT)支持多于一个表达式的功能是MySQL的扩展。您可以在例如PostgreSQL中使用ROW值来实现类似的效果，但是关于NULL的行为不同。在MySQL中，如果任何值表达式计算为NULL，则该行不符合条件。这也导致了问题中两个查询之间的不同：

如果COUNT(DISTINCT)查询中的Serial，DatumOrig或Glucose中有任何一个为NULL，则该行不符合条件，换句话说不计入。
COUNT(*)是子查询anon_1的基数，换句话说是行数的计数。 SELECT DISTINCT Serial，DatumOrig，Glucose 将包括（不同的）带有NULL的行。

查看2个查询的EXPLAIN输出，看起来子查询会导致MySQL使用临时表。这很可能会导致性能差异，特别是如果它在磁盘上被实现。

在SQLAlchemy中生成多值COUNT(DISTINCT)查询有点棘手，因为count()是一个通用函数，实现更接近SQL标准。它只接受单个表达式作为其（可选的）位置参数，distinct()也是如此。如果其他所有方法都失败了，您可以始终回到text()片段，就像在这种情况下一样：

# NOTE: text() fragments are included in the query as is, so if the text originates
# from an untrusted source, the query cannot be trusted.
session.query(func.count(distinct(text("`Serial`, `DatumOrig`, `Glucose`")))).\
    select_from(Value).\
    scalar()

那段代码远非易读易维护的代码，但可以暂时完成任务。另一种选择是编写一个自定义结构来实现MySQL扩展，或者像你尝试过的那样重写查询。形成一个自定义结构以生成所需SQL的方法如下：

from itertools import count
from sqlalchemy import func, distinct as _distinct

def _comma_list(exprs):
    # NOTE: Magic number alert, the precedence value must be large enough to avoid
    # producing parentheses around the "comma list" when passed to distinct()
    ps = count(10 + len(exprs), -1)
    exprs = iter(exprs)
    cl = next(exprs)
    for p, e in zip(ps, exprs):
        cl = cl.op(',', precedence=p)(e)

    return cl

def distinct(*exprs):
    return _distinct(_comma_list(exprs))

session.query(func.count(distinct(
    Value.Serial, Value.DatumOrig, Value.Glucose))).scalar()