用於程序員表達預測的聚合採樣器功能

是否有任何 SQL 標准或 DBMS 實現為可預測的重複列提供“樣本聚合器”？

我會解釋的，請看最後的粗體字。

PS：這個問題一般是關於大表中的“小性能增益”，以及SQL 語言中的“語義增強” 。

---

v1作為典型的表，我們可以想像一些數據的 SQL 視圖：

Organization   | City           |  Country | Info1 | Info2
---            | ---            | ---      | ---   | ---
LocalOrg1      | San Francisco  |  US      | 10    | 23
LocalOrg1      | San Francisco  |  US      | 2     | 24
Armarinhos Fer | São Paulo      |  BR      | 11    | 55
Armarinhos Fer | São Paulo      |  BR      | 12    | 56

我（程序員）的預測是，所有組織v1都只存在於一個城市（例如，只有一個“舊金山市政廳”）。所以，根據我的預測，查詢

SELECT organization, city, country, 
      sum(info1) as tot1, avg(info2) as avg2, count(*) as n
FROM v1
GROUP BY organization, city, country  -- city,country predicted repeat

有一些冗餘和一些語義限制：分組標準是 organization，不需要花費 CPU 檢查city 和country。我可以說“嘿，約翰，看看那個按組織分組的查詢”，因為當他有相同的預測時，對另一個人說“組織、城市和國家”是沒有意義的。這是 SQL 義務，但不是查詢的語義本質。

當然，使用GROUP by organization, 2,3語法糖或使用max(),語義可能會更好

SELECT organization, max(city) as city, max(country) as country, 
      sum(info1) as tot1, avg(info2) as avg2, count(*) as n
FROM v1
GROUP BY organization  -- better semantic, worse performance 

但是max()浪費了“什麼都不比較”的時間（因為正如我預測的那樣，只有重複的東西），而且查詢成本可能會比第一次查詢大。

一些 DBMS 還提供first()，last()並且可能更有效（！）max()，我想這是當今最簡單的解決方案：使用某種first()功能優化性能和語義，根據我的預測，城市和國家在按組織分組時會重複.

但無論重複列的樣本是來自第一行還是最後一個採樣行。first/last 也可以是內部優化選擇，因此，在這種情況下，真正需要的是一種aggsample()函式：

SELECT organization, 
      aggsample(city), aggsample(country) -- better semantic and perfornace
      sum(info1) as tot1, avg(info2) as avg2, count(*) as n
FROM v1
GROUP BY organization  -- better performance 

所以，在這個詳細的上下文中：是否有任何SQL 語言變體定義了這種函式 ( aggsample)？

---

在評論和一些作業後編輯…

筆記

候選人和限制：

• PostgreSQL 的DISTINCT ON. 似乎解決了類似的問題，但沒有解決通常的 GROUP BY 摘要中的使用。
• MySQL 的ANY_VALUE，正如@AndriyM 所建議的那樣。看起來很完美（！），但我從未使用過……稍後會嘗試，安裝 mysql 並進行一些測試。

類似的討論：

• https://stackoverflow.com/a/8373384/287948
• https://stackoverflow.com/q/36134657/287948
• … https://stackoverflow.com/a/20347763/287948

試

CREATE TABLE v1 (
 Organization text,  City text,
 Country text, Info1  int, Info2 int
);
INSERT INTO v1 VALUES 
('LocalOrg1', 'San Francisco', ' US', '10  ', '23'),
('LocalOrg1', 'San Francisco', ' US', '2   ', '24'),
('Armarinhos Fer', 'São Paulo', ' BR', '11  ', '55'),
('Armarinhos Fer', 'São Paulo', ' BR', '12  ', '56');

Extrange，PostgreSQL 提供有效語法“DISTINCT ON + GROUP BY”，

SELECT DISTINCT ON (organization) organization, city, country, 
      sum(info1) as tot1, avg(info2) as avg2, count(*) as n
FROM v1
GROUP BY organization, city, country

但效果不是更好（性能最差），並且僅使用 ou 分組是無效的GROUP BY organization。

…沒有其他測試。

PS：postgresql 唯一有效的語法（和結果）DISTINCT ON是

SELECT DISTINCT ON (organization) organization, City, Country, 
   sum(Info1) OVER w AS tot1,
   avg(info2) OVER w AS avg2,
   count(*)   OVER w as n
FROM v1
WINDOW w AS (PARTITION BY organization);

如我們所見，語法醜陋，性能最差。

感謝@AndriyM，MySQL 5.7中這個新特性的連結讓我找到了這個**ANY_VALUE()函式**，這正是我的問題的“aggsample”！

• 性能結論：ANY_VALUE()函式的使用代表了性能的一些提升，數千個 MySQL 數據庫的實際案例證明了這一點，並且決定在當今的 MySQL 版本中保留這一優勢，這為我們帶來了這個新函式。
• 可用性（友好性）結論：使子句ANY VALUE()中的語義增益（去掉非分組列）成為可能。GROUP BY
• 哲學結論：這是程序員表達他/她對重複值的預測的方式。

所以，ANY VALUE()對於任何其他 SQL 語言都是一個很好的推薦！

---

筆記

要測試您需要 MySQL 5.7+，並檢查SELECT @@GLOBAL.sql_mode或將模式設置為ONLY_FULL_GROUP_BY.

查詢工作正常，

SELECT organization, any_value(city) AS city, any_value(country) as country,
      sum(info1) as tot1, avg(info2) as avg2, count(*) as n 
FROM v1 GROUP BY organization;

MySQL 中另一個有趣的行為是讓使用者處於友好模式，這any_value()不是必需的——因此，也可以避免“AS 名稱”無聊的語法。在這種特殊（非標準）模式下，以下查詢具有相同的行為，

SELECT organization, city, country,
      sum(info1) as tot1, avg(info2) as avg2, count(*) as n 
FROM v1 GROUP BY organization;

框架挑戰：如果正確規範化數據庫，就不會有這個問題。因此，這個問題應該在設計階段就已經解決了，您不需要發明語義模糊的聚合函式來解決它。

如果 , 和 的組合Organization保證Info1是Info2唯一的（我很確定不是這種情況），那麼這些屬性形成一個複合候選鍵。隨後，您的關係不在 2NF 中，因為City並且Country僅在功能上依賴於候選鍵 ( Organization) 的子集，並且需要處於不同的關係中。

如果 、 和 的組合Organization不能Info1保證Info2是唯一的，那麼這個關係是完全非規範化的（即實際上不是一個關係），您需要從頭開始設計。

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