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

我正在嘗試執行以下操作：

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

我想從 FileItems 中選擇最後一個 /n/ 項，按降序排列，行由. 上面的查詢出錯了。我需要該值才能獲取此查詢的輸出，並將其放在源表上以獲取我所在的其餘列。DISTINCT``seriesName``ERROR: column "fileitems.dbid" must appear in the GROUP BY clause or be used in an aggregate function``dbid``JOIN

請注意，這基本上是以下問題的格式塔，為了清楚起見，刪除了許多無關的細節。

---

原始問題

我有一個從 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 中出現錯誤並顯示錯誤 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這是一個糟糕的分頁機制，讓我無需將滾動游標專用於每個連接即可逃脫，我可能會在某個時候重新審視它。

---

一個月前我問的Ref- Quest 導致了這個查詢。

---

好的，更多註釋：

   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 中是如何工作的。我哪裡錯了？我的印像是子查詢基本上只是一個內聯函式，結果只是輸入到主查詢中。

一致的行

重要的問題似乎還沒有出現在您的雷達上：

從每組相同的行中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：

• 在每個 GROUP BY 組中選擇第一行？

撇開：應該是retrievalTime，或者更好的是：retrieval_time。不帶引號的混合大小寫標識符是 Postgres 中常見的混淆來源。

使用 rCTE 獲得更好的性能

由於我們在這里處理的是一個大表，因此我們需要一個可以使用索引的查詢，而上述查詢並非如此（除了WHERE sourceSite = 'mk'）

仔細檢查後，您的問題似乎是鬆散索引掃描的特例。Postgres 本身不支持鬆散索引掃描，但可以使用遞歸 CTE來模擬它。Postgres Wiki 中有一個簡單案例的程式碼範例。

看：

• SELECT DISTINCT 在我的 PostgreSQL 表上比預期的要慢
• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄

不過，您的情況更複雜。我想我找到了一個變種讓它為你工作。建立在這個索引上（沒有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。更快，但是。顯然，需要更頻繁地刷新。

此處和此處的手冊中的詳細資訊。

引用自：https://dba.stackexchange.com/questions/74773