讓 CASE .. END 在 ORDER BY 中有意義嗎？

像這樣的查詢SELECT * FROM t ORDER BY case when _parameter='a' then column_a end, case when _parameter='b' then column_b end是可能的，但是：這是一個好習慣嗎？

通常在查詢的 WHERE 部分使用參數，並在 SELECT 部分有一些計算列，但參數化 ORDER BY 子句並不常見。

假設我們有一個列出二手車的應用程序（à la CraigsList）。汽車列表可以按價格或顏色排序。我們有一個函式，給定一定數量的參數（例如價格範圍、顏色和排序標準）返回一組記錄和結果。

為了使其具體化，我們假設cars都在下表中：

CREATE TABLE cars
(
 car_id serial NOT NULL PRIMARY KEY,  /* arbitrary anonymous key */
 make text NOT NULL,       /* unnormalized, for the sake of simplicity */
 model text NOT NULL,      /* unnormalized, for the sake of simplicity */
 year integer,             /* may be null, meaning unknown */
 euro_price numeric(12,2), /* may be null, meaning seller did not disclose */
 colour text               /* may be null, meaning unknown */
) ;

該表將具有大多數列的索引…

CREATE INDEX cars_colour_idx
 ON cars (colour);
CREATE INDEX cars_price_idx
 ON cars (price);
/* etc. */

並且有一些商品列舉：

CREATE TYPE car_sorting_criteria AS ENUM
  ('price',
   'colour');

…和一些樣本數據

INSERT INTO cars.cars (make, model, year, euro_price, colour)

VALUES 
   ('Ford',   'Mondeo',   1990,  2000.00, 'green'),
   ('Audi',   'A3',       2005,  2500.00, 'golden magenta'),
   ('Seat',   'Ibiza',    2012, 12500.00, 'dark blue'),
   ('Fiat',   'Punto',    2014,     NULL, 'yellow'),
   ('Fiat',   '500',      2010,  7500.00, 'blueish'),
   ('Toyota', 'Avensis',  NULL,  9500.00, 'brown'), 
   ('Lexus',  'CT200h',   2012, 12500.00, 'dark whitish'), 
   ('Lexus',  'NX300h',   2013, 22500.00, NULL) ;

我們要進行的查詢類型如下：

SELECT
   make, model, year, euro_price, colour
FROM
   cars.cars
WHERE
   euro_price between 7500 and 9500 
ORDER BY
   colour ;

我們想在函式中查詢這種風格：

CREATE or REPLACE FUNCTION get_car_list
  (IN _colour    text, 
   IN _min_price numeric, 
   IN _max_price numeric, 
   IN _sorting_criterium car_sorting_criteria) 
RETURNS record AS
$BODY$
     SELECT
         make, model, year, euro_price, colour
     FROM
         cars
     WHERE
          euro_price between _min_price and _max_price
          AND colour = _colour
     ORDER BY
         CASE WHEN _sorting_criterium = 'colour' THEN
           colour
         END,
         CASE WHEN _sorting_criterium = 'price' THEN
           euro_price
         END 
$BODY$
LANGUAGE SQL ;

代替這種方法，這個函式中的 SQL 可以作為字元串動態生成（在 PL/pgSQL 中），然後執行。

我們可以感覺到任何一種方法的一些限制、優點和缺點：

1. 在一個函式中，很難找到某個語句的查詢計劃（如果可能的話）。然而，當我們經常使用某些東西時，我們傾向於使用函式。
2. 靜態 SQL 中的錯誤將（大部分）在編譯函式或首次呼叫時被擷取。
3. 動態 SQL 中的錯誤只會在函式編譯後才被擷取（moslty），並且所有執行路徑都已檢查（即：對函式執行的測試次數可能非常多）。
4. 像公開的那樣的參數查詢可能比動態生成的查詢效率低；然而，執行者將有更難的工作解析/製作查詢樹/每次決定（這可能會影響相反方向的效率）。

題：

如何“兩全其美”（如果可能）？

$$ Efficiency + Compiler checks + Debugging easily + Optimizing easily $$ 注意：這將在 PostgreSQL 9.6 上執行。

一般回答

首先，我想解決前提中的歧義：

通常在查詢的 WHERE 部分使用參數，並在 SELECT 部分有一些計算列，但參數化 ORDER BY 子句並不常見。

該部分中的計算列SELECT幾乎與查詢計劃或性能無關。但是*“在WHERE部分”*是模棱兩可的。

在子句中參數化值是很常見的WHERE，這適用於準備好的語句。（並且 PL/pgSQL 在內部使用準備好的語句。）無論提供的值如何，通用查詢計劃通常都是有意義的。也就是說，除非表的數據分佈非常不均勻，但由於 Postgres 9.2 PL/pgSQL 重新計劃查詢幾次以測試通用計劃是否足夠好：

• PostgreSQL 儲存過程性能

但是在子句中參數化整個謂詞（包括標識符）並不常見WHERE，這對於一開始就準備好的語句是不可能的。您需要帶有 的動態 SQL EXECUTE，或者您在客戶端中組裝查詢字元串。

動態ORDER BY表達式介於兩者之間。您可以使用表達式來做到這一點CASE，但這通常很難優化。Postgres 可能會使用帶有 plain 的索引ORDER BY，但不能使用CASE隱藏最終排序順序的表達式。計劃者很聰明，但不是人工智慧。根據查詢的其餘部分（ORDER BY可能與計劃相關或不相關 - 在您的範例中相關），您可能最終得到一個次優的查詢計劃。

另外，您添加了CASE表達式的次要成本。在您的特定範例中，也適用於多個無用的ORDER BY列。

通常，動態 SQLEXECUTE會更快或更快。

如果您在函式體中保持清晰易讀的程式碼格式，可維護性應該不是問題。

修復展示功能

問題中的功能已損壞。返回類型定義為返回匿名記錄：

RETURNS record AS

但是查詢實際上返回了一組記錄，它必須是：

RETURNS SETOF record AS

但這仍然無濟於事。您必須在每次呼叫時提供一個列定義列表。您的查詢返回已知類型的列。相應地聲明返回類型！我在這裡猜測，使用返回的列/表達式的實際數據類型：

RETURNS TABLE (make text, model text, year int, euro_price int, colour text) AS

為方便起見，我使用相同的列名。RETURNS TABLE子句中的列實際上是OUT參數，在主體中的每個 SQL 語句中可見（但在內部不可見EXECUTE）。因此，在函式體的查詢中對列進行表限定以避免可能的命名衝突。展示功能將像這樣工作：

CREATE or REPLACE FUNCTION get_car_list (
   _colour            text, 
   _min_price         numeric, 
   _max_price         numeric, 
   _sorting_criterium car_sorting_criteria) 
 RETURNS TABLE (make text, model text, year int, euro_price numeric, colour text) AS
$func$
     SELECT c.make, c.model, c.year, c.euro_price, c.colour
     FROM   cars c
     WHERE  c.euro_price BETWEEN _min_price AND _max_price
     AND    c.colour = _colour
     ORDER  BY CASE WHEN _sorting_criterium = 'colour' THEN c.colour     END
             , CASE WHEN _sorting_criterium = 'price'  THEN c.euro_price END;
$func$  LANGUAGE sql;

不要像Evan 在他的回答中所做的那樣RETURNS，將函式聲明中的關鍵字與 plpgsqlRETURN命令混淆。細節：

• 返回下一個和返回記錄的區別

範例查詢的一般難度

某些列上的謂詞（更糟糕的是：範圍謂詞）， 中的其他列ORDER BY，這已經很難優化了。但你在評論中提到：

實際結果集可能是幾 1.000 行的順序（因此，在伺服器端以較小的塊進行排序和分頁）

因此，您將向這些查詢添加LIMIT和OFFSET，首先返回n 個“最佳”匹配項。或者一些更智能的分頁技術：

• 使用來自許多表的列提高 order by 的性能

您需要一個匹配的索引來加快速度。我不明白這怎麼可能CASE與ORDER BY.

考慮：

• 優化具有小 LIMIT 的查詢，以一列為謂詞並按另一列排序
• 空間索引能否幫助“範圍-按-限制”查詢

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