主页 / dba / 问题 / 74773
Accepted
Fake Name
Asked: 2014-08-24 22:44:27 +0800 CST

GROUP BY 一列，同时在 PostgreSQL 中按另一列排序

  • 772

我怎样才能一列，而只GROUP BY按另一列排序。

我正在尝试执行以下操作：

SELECT dbId,retreivalTime 
    FROM FileItems 
    WHERE sourceSite='something' 
    GROUP BY seriesName 
    ORDER BY retreivalTime DESC 
    LIMIT 100 
    OFFSET 0;

我想从 FileItems 中选择最后一个 /n/ 项，按降序排列，行由. 上面的查询出错了。我需要该值才能获取此查询的输出，并将其放在源表上以获取我所在的其余列。DISTINCTseriesNameERROR: column "fileitems.dbid" must appear in the GROUP BY clause or be used in an aggregate functiondbidJOIN

请注意，这基本上是以下问题的格式塔，为清楚起见，删除了许多无关的细节。

原始问题

我有一个从 sqlite3 迁移到 PostgreSQL 的系统，因为我在很大程度上已经超过了 sqlite：

    SELECT
            d.dbId,
            d.dlState,
            d.sourceSite,
        [snip a bunch of rows]
            d.note

    FROM FileItems AS d
        JOIN
            ( SELECT dbId
                FROM FileItems
                WHERE sourceSite='{something}'
                GROUP BY seriesName
                ORDER BY MAX(retreivalTime) DESC
                LIMIT 100
                OFFSET 0
            ) AS di
            ON  di.dbId = d.dbId
    ORDER BY d.retreivalTime DESC;

基本上，我想选择数据库中的最后n 个DISTINCT项目，其中不同的约束位于一列上，排序顺序位于另一列上。

不幸的是，上面的查询，虽然它在 sqlite 中运行良好，但在 PostgreSQL 中出现错误并带有 error psycopg2.ProgrammingError: column "fileitems.dbid" must appear in the GROUP BY clause or be used in an aggregate function。

不幸的是，虽然添加dbId到 GROUP BY 子句可以解决问题（例如GROUP BY seriesName,dbId），但这意味着查询结果的不同过滤不再起作用，因为dbid是数据库主键，因此所有值都是不同的。

通过阅读Postgres 文档，有SELECT DISTINCT ON ({nnn})，但这要求返回的结果按{nnn}.

因此，要通过 执行我想要的操作SELECT DISTINCT ON，我必须查询 allDISTINCT {nnn}和它们，然后按then再次MAX(retreivalTime)排序，然后取最大的 100 并使用这些对表进行查询以获取其余行，我'想避免，因为数据库在列中有 ~175K 行和 ~14K 不同的值，我只想要最新的 100 个，而且这个查询对性能有些关键（我需要查询时间 < 1/2 秒）。retreivalTime{nnn}seriesName

我在这里的天真假设基本上是数据库需要以 的降序遍历每一行retreivalTime，并且一旦看到LIMIT项目就停止，所以全表查询是不理想的，但我不会假装真正了解数据库系统内部优化，我可能完全错误地接近这个。

FWIW，我偶尔会使用不同的OFFSET值，但是对于偏移 > ~500 的情况，查询时间很长是完全可以接受的。基本上，OFFSET这是一个糟糕的分页机制，让我无需将滚动光标专用于每个连接就可以逃脱，我可能会在某个时候重新审视它。

参考问题我在一个月前提出的导致此查询的问题。

好的，更多注释：

    SELECT
            d.dbId,
            d.dlState,
            d.sourceSite,
        [snip a bunch of rows]
            d.note

    FROM FileItems AS d
        JOIN
            ( SELECT seriesName, MAX(retreivalTime) AS max_retreivalTime
                FROM FileItems
                WHERE sourceSite='{something}'
                GROUP BY seriesName
                ORDER BY max_retreivalTime DESC
                LIMIT %s
                OFFSET %s
            ) AS di
            ON  di.seriesName = d.seriesName AND di.max_retreivalTime = d.retreivalTime
    ORDER BY d.retreivalTime DESC;

如所描述的那样对查询正常工作，但如果我删除该GROUP BY子句，它会失败（它在我的应用程序中是可选的）。

psycopg2.ProgrammingError: column "FileItems.seriesname" must appear in the GROUP BY clause or be used in an aggregate function

我想我根本不了解子查询在 PostgreSQL 中是如何工作的。我哪里错了？我的印象是子查询基本上只是一个内联函数，结果只是输入到主查询中。

postgresql performance

3 个回答

  1. Best Answer

    一致的行

    重要的问题似乎还没有出现在您的雷达上：
    从每组相同的行中seriesName，您是否想要一行的列，或者只是来自多行的任何值（可能来自同一行，也可能不来自同一行)?

    您的答案是后者，您将最大值dbid与最大值结合起来retreivaltime，这可能来自不同的行。

    要获得一致的行，请使用DISTINCT ON并将其包装在子查询中以对结果进行不同的排序：

    SELECT * FROM (
   SELECT DISTINCT ON (seriesName)
          dbid, seriesName, retreivaltime
   FROM   FileItems
   WHERE  sourceSite = 'mk' 
   ORDER  BY seriesName, retreivaltime DESC NULLS LAST  -- latest retreivaltime
   ) sub
ORDER BY retreivaltime DESC NULLS LAST
LIMIT  100;

    详细信息DISTINCT ON：

    撇开：应该是retrievalTime，或者更好的是：retrieval_time。不带引号的混合大小写标识符是 Postgres 中常见的混淆来源。

    使用 rCTE 获得更好的性能

    由于我们在这里处理的是一个大表，因此我们需要一个可以使用索引的查询，而上述查询并非如此（除了WHERE sourceSite = 'mk'）

    仔细检查后，您的问题似乎是松散索引扫描的特例。Postgres 本身不支持松散索引扫描，但可以使用递归 CTE来模拟它。Postgres Wiki 中有一个简单案例的代码示例。

    看：

    不过，您的情况更复杂。我想我找到了一个变种让它为你工作。建立在这个索引上（没有WHERE sourceSite = 'mk'）

    CREATE INDEX mi_special_full_idx ON MangaItems
(retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST, dbid)

    或（与WHERE sourceSite = 'mk'）

    CREATE INDEX mi_special_granulated_idx ON MangaItems
(sourceSite, retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST, dbid)

    第一个索引可用于两个查询，但在附加WHERE条件下效率不高。第二个索引对第一个查询的使用非常有限。由于您有两种查询变体，请考虑创建两个索引。

    我dbid在最后添加了允许仅索引扫描。

    这个带有递归 CTE 的查询使用了索引。我使用Postgres 9.3进行了测试，它对我有用：没有顺序扫描，所有仅索引扫描：

    WITH RECURSIVE cte AS (
   (
   SELECT dbid, seriesName, retreivaltime, 1 AS rn, ARRAY[seriesName] AS arr
   FROM   MangaItems
   WHERE  sourceSite = 'mk'
   ORDER  BY retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT i.dbid, i.seriesName, i.retreivaltime, c.rn + 1, c.arr || i.seriesName
   FROM   cte c
   ,      LATERAL (
      SELECT dbid, seriesName, retreivaltime
      FROM   MangaItems
      WHERE (retreivaltime, seriesName) < (c.retreivaltime, c.seriesName)
      AND    sourceSite = 'mk'  -- repeat condition!
      AND    seriesName <> ALL(c.arr)
      ORDER  BY retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST
      LIMIT  1
      ) i
   WHERE  c.rn < 101
   )
SELECT dbid
FROM   cte
ORDER  BY rn;

    您需要包含seriesName in ORDER BY，因为retreivaltime它不是唯一的。“几乎”独特仍然不是独特的。

    解释

    • 非递归查询从最新的行开始。

    • 递归查询添加下一个最新的行，其中 aseriesName不在列表中，等等，直到我们有 100 行。

    • 基本部分是JOIN条件(b.retreivaltime, b.seriesName) < (c.retreivaltime, c.seriesName)和ORDER BY从句ORDER BY retreivaltime DESC NULLS LAST, seriesName DESC NULLS LAST。两者都匹配索引的排序顺序，这使得魔法发生。

    • 收集seriesName在一个数组中以排除重复项。成本b.seriesName <> ALL(c.foo_arr)随着行数的增加而逐渐增加，但对于 100 行来说仍然很便宜。

    • 只是dbid按照评论中的说明返回。

    部分索引的替代方案：

    我们之前一直在处理类似的问题。这是一个基于部分索引和循环函数的高度优化的完整解决方案：

    如果操作正确，可能是最快的方法（物化视图除外）。但更复杂。

    物化视图

    由于您没有很多写入操作，并且它们不是评论中所述的性能关键（应该在问题中），因此将前 n 个预先计算的行保存在物化视图中，并在相关更改后刷新它基础表。而是将您的性能关键查询基于物化视图。

    • 可能只是最新的 1000dbid左右的“薄” mv。在查询中，连接到原始表。例如，如果内容有时会更新，但前 n 行可以保持不变。

    • 或返回整行的“胖” mv。更快，但是。显然，需要更频繁地刷新。

    此处和此处的手册中的详细信息。

    • 12

  2. 好的，我已经阅读了更多文档，现在我至少对这个问题有了更好的理解。

    基本上，发生的事情是聚合dbid的结果有多个可能的值。GROUP BY seriesName使用 SQLite 和 MySQL，显然数据库引擎只是随机选择一个（这在我的应用程序中绝对没问题）。

    然而，PostgreSQL 更加保守，所以与其选择一个随机值，不如说它会引发错误。

    使此查询工作的一种简单方法是将聚合函数应用于相关值：

    SELECT MAX(dbid) AS mdbid, seriesName, MAX(retreivaltime) AS mrt
    FROM MangaItems 
    WHERE sourceSite='mk' 
    GROUP BY seriesName
    ORDER BY mrt DESC 
    LIMIT 100 
    OFFSET 0;

    这使得查询输出完全合格，并且查询现在可以工作。

    • 8

  3. 好吧，我实际上最终使用了数据库之外的一些程序逻辑来完成我想做的事情。

    基本上，99% 的时间，我想要最后 100 200 个结果。查询计划器似乎没有为此优化，如果 的值OFFSET很大，我的程序过滤器会慢得多。

    无论如何，我使用命名游标手动迭代数据库中的行，以几百个为一组检索行。然后，我在我的应用程序代码中过滤它们以获得不同，并在我积累了我想要的不同结果的数量后立即关闭光标。

    mako代码（基本上是python）。剩下很多调试语句。

    <%def name="fetchMangaItems(flags='', limit=100, offset=0, distinct=False, tableKey=None, seriesName=None)">
    <%
        if distinct and seriesName:
            raise ValueError("Cannot filter for distinct on a single series!")

        if flags:
            raise ValueError("TODO: Implement flag filtering!")

        whereStr, queryAdditionalArgs = buildWhereQuery(tableKey, None, seriesName=seriesName)
        params = tuple(queryAdditionalArgs)


        anonCur = sqlCon.cursor()
        anonCur.execute("BEGIN;")

        cur = sqlCon.cursor(name='test-cursor-1')
        cur.arraysize = 250
        query = '''

            SELECT
                    dbId,
                    dlState,
                    sourceSite,
                    sourceUrl,
                    retreivalTime,
                    sourceId,
                    seriesName,
                    fileName,
                    originName,
                    downloadPath,
                    flags,
                    tags,
                    note

            FROM MangaItems
            {query}
            ORDER BY retreivalTime DESC;'''.format(query=whereStr)

        start = time.time()
        print("time", start)
        print("Query = ", query)
        print("params = ", params)
        print("tableKey = ", tableKey)

        ret = cur.execute(query, params)
        print("Cursor ret = ", ret)
        # for item in cur:
        #   print("Row", item)

        seenItems = []
        rowsBuf = cur.fetchmany()

        rowsRead = 0

        while len(seenItems) < offset:
            if not rowsBuf:
                rowsBuf = cur.fetchmany()
            row = rowsBuf.pop(0)
            rowsRead += 1
            if row[6] not in seenItems or not distinct:
                seenItems.append(row[6])

        retRows = []

        while len(seenItems) < offset+limit:
            if not rowsBuf:
                rowsBuf = cur.fetchmany()
            row = rowsBuf.pop(0)
            rowsRead += 1
            if row[6] not in seenItems or not distinct:
                retRows.append(row)
                seenItems.append(row[6])

        cur.close()
        anonCur.execute("COMMIT;")

        print("duration", time.time()-start)
        print("Rows used", rowsRead)
        print("Query complete!")

        return retRows
    %>

</%def>

    这目前在115 ~ 80 毫秒内检索最新的100 200 个不同的系列项目（较短的时间是使用本地连接而不是 TCP 套接字时），同时处理大约 1500 行。

    来评论：

    • 以 250 个为单位读取行。
    • buildWhereQuery是我自己的动态查询生成器。是的，这是一个可怕的想法。是的，我知道 SQLalchemy 等人。我自己写是因为 A. 这是一个我不希望在家庭 LAN 之外使用的个人项目，以及 B. 这是学习 SQL的好方法。
    • 我可以考虑在两种查询机制之间切换取决于偏移量的值。看起来当偏移量 > 1000 并且我正在过滤不同的项目时，这种方法开始超过像@ErwinBrandstetter 的答案中那样的程序所需的时间。
    • @ErwinBrandstetter 的回答仍然是一个更好的 通用解决方案。这仅在一种非常特殊的情况下更好。
    • 出于某种奇怪的原因，我不得不使用两个游标。除非您在事务中，否则您无法创建命名游标，但您无法在没有游标的情况下启动事务（注意 - 这是autocommitmode off）。我必须实例化一个匿名游标，发出一些 SQL（只是一个BEGIN, 这里），创建我的命名游标，使用它，关闭它，最后使用匿名游标提交。
    • 这可能完全在 PL/pgSQL 中完成，结果可能会更快，但我更了解 python。
    • 1

相关问题