如何按列值按查詢結果壓縮/分組？

我有下表（MySQL 版本 >= 5.7.X，使用 DBeaver 21.1.0 管理數據庫）：

我想要類似的東西（我不一定需要將分組列命名為 actions_completed_by_unique_user，這樣會很好）：

我的查詢是：

SELECT n_brand , population, actions_completed_by_unique_user
FROM agg_table
WHERE n_brand = 'pepsico'
AND actions_completed_by_unique_user > 0
GROUP BY population

但它只需要每個人口的第一次出現並且不會對值求和，是否可以僅使用 SQL 查詢來做到這一點，還是我必須以程式方式做到這一點？

為了解決這個問題，我做了以下事情。下面的所有程式碼都可以在這裡找到。

我又看了一遍，意識到我的第一個答案（見編輯歷史）只是“給了你一條魚”，而不是“教你如何釣魚（*）”！

概述：

您在這裡有兩個問題，一個很容易發現和處理 - 聚合 - 在這種情況下，SUM()-ing over 然後GROUP-ingBY某些欄位（參見this和this） - （"compact"-ing 在問題中），另一個問題更多微妙的：

• 在這種情況下， MySQL無法GROUP BY正確執行！“什麼？？” ，我聽你說，“最流行的開源伺服器不能做簡單的查詢？？” . 答案是，在這種情況下，可悲的是，“不，它不能！” .

第 1 部分 - 澄清 MySQL 的 GROUP BY：

後一個問題可能更加有害和混亂，在回答問題之前需要澄清，所以我會先處理它。

首先要做的是創建一個test表並用您的數據填充它：

CREATE TABLE test
(
 n_brand VARCHAR (25) NOT NULL,
 population VARCHAR (25) NOT NULL,
 actions_u_user INTEGER NOT NULL
);

數據：

INSERT INTO test VALUES
('pepsico', 'pepsicoeur',   1),
('pepsico', 'pepsicoeur',   1),
('pepsico', 'pepsicoeur',   1),
('pepsico', 'pepsicousa',   1),
('pepsico', 'pepsicousa',   2),
('pepsico', 'pepsicousa',   2),
('pepsico', 'pepsicomex',   0),
('pepsico', 'pepsicomex',   2),
('ferrari', 'ferrarieur',   1),
('ferrari', 'ferrarieur',   1),
('ferrari', 'ferrariusa',   0),
('ferrari', 'ferrarimex',   1),
('ferrari', 'ferrarimex',   1);

如果我執行（在 dbfiddle 上）：

SHOW VARIABLES LIKE '%sql_mode%';

我得到：

Variable_name   Value
sql_mode    ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

請注意ONLY_FULL_GROUP_BY（在這種情況下，它是第一個條目，但這並不重要）。現在，如果你SHOW VARIABLES LIKE '%sql_mode%'在自己的系統上執行上面的命令，它就沒有這個ONLY_FULL_GROUP_BY位了。我怎麼知道這個？

好吧，如果我嘗試在 dbfiddle 上執行您的查詢：

SELECT n_brand , population, actions_u_user
FROM test
WHERE n_brand = 'pepsico'
AND actions_u_user > 0
GROUP BY population

我得到：

Expression #3 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'db_1748585530.test.actions_u_user'
which is not functionally dependent on columns in GROUP BY
clause; this is incompatible with sql_mode=only_full_group_by

這是因為sql_modedbfiddle.uk 上的伺服器設置已（正確）設置為包含該ONLY_FULL_GROUP_BY選項，因此您的查詢將不起作用！

您可能正在執行 x < 5 的 MySQL 5.7.x 版本 - 請參閱此處的文章並查看同一作者的文章的連結（揭穿 GROUP BY 神話）。在完成這個答案後閱讀這些 - 它們為這個令人驚訝的棘手問題提供了額外的清晰度！

這個（不是很有用）查詢將起作用：

SELECT n_brand , population, actions_u_user
FROM test
WHERE n_brand = 'pepsico'
AND actions_u_user > 0
GROUP BY n_brand, population, actions_u_user;

因為，您GROUPed BY 擁有SELECT.

結果：

n_brand     population  actions_u_user
pepsico     pepsicoeur  1
pepsico     pepsicomex  2
pepsico     pepsicousa  1
pepsico     pepsicousa  2

那麼，這告訴我們什麼？

好吧，它告訴我們我們有一組值pepsico pepsicoeur 1，一組pepsico pepsicomex 2&c。- 即，它是每個不同記錄的一個記錄 ( n_brand, population& actions_u_user)。

這個查詢：

SELECT DISTINCT n_brand, population, actions_u_user
FROM test
WHERE n_brand = 'pepsico'
 AND actions_u_user > 0
ORDER BY n_brand, population;

將給出相同的結果（可能更便宜） - 見小提琴。

您可以執行這個稍微有用的查詢（注意SUM()聚合）：

SELECT n_brand , population, actions_u_user, SUM(actions_u_user) AS sum_p
FROM test
WHERE n_brand = 'pepsico'
AND actions_u_user > 0
GROUP BY n_brand, population, actions_u_user;

結果：

n_brand     population  actions_u_user  sum_p
pepsico     pepsicoeur               1      3
pepsico     pepsicomex               2      2
pepsico     pepsicousa               1      1
pepsico     pepsicousa               2      4

稍微有用一些 - 但是= pepsico 和= pepsicousa 的總和是5 (4 + 1)，所以我們部分地正在回答這個問題（見下文）。n_brand``population

但是，要查看沒有ONLY_FULL_GROUP_BYin 的問題出在哪裡sql_mode，讓我們將其刪除！

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

所以，它被刪除了！現在，我們重新執行您的原始查詢：

SELECT n_brand , population, actions_u_user
FROM test
WHERE n_brand = 'pepsico'
AND actions_u_user > 0
GROUP BY population;

我們得到一個**“結果”（這可能會也可能**不會因您的 MySQL 版本、一天中的時間、月相和風吹的方向而異！）：

n_brand     population  actions_u_user
pepsico     pepsicoeur  1
pepsico     pepsicomex  2
pepsico     pepsicousa  1

太好了，您可能會想，“我有一個答案！” - 確實是這樣 - 但**問題是 WTF嗎？您有 3 條記錄，經檢查，它們實際上是= ‘pepsico’ 和不同記錄的第一**條記錄（按INSERT離子）。n_brand``population

MySQLPRIMARY KEY在表上添加了一個隱式（參見此處和此處的手冊）：

• 如果表沒有 PRIMARY KEY 或合適的 UNIQUE 索引，InnoDB 會在包含行 ID 值的合成列上生成一個名為 GEN_CLUST_INDEX 的隱藏聚集索引。這些行按 InnoDB 分配的行 ID 排序。行 ID 是一個 6 字節的欄位，隨著新行的插入而單調增加。因此，按行 ID 排序的行在物理上是按插入順序排列的。

這些是隱式 PK 的第一條記錄，它們為 . 的不同值返回population。但是，嘿，誰知道呢？

如果你依賴這種行為，上帝會幫助你！它沒有記錄（或者更確切地說它被記錄為“未確定”）！如果值為UPDATEd，那麼結果是什麼？DELETEd？結果呢？

到目前為止，最好有一個確定性的記錄答案——如果你將其包含ONLY_FULL_GROUP_BY在你的sql_mode.

我喜歡將其視為向伺服器提供足夠的資訊，從而為 SQL 查詢提供合理、確定的答案！如果您有任何疑問，請按照我的做法 - 並在 PostgreSQL 上進行測試，這是一個健全的 RDBMS！

所以，就是這樣！設置你sql_mode的包括在內ONLY_FULL_GROUP_BY，你就不會掉入這個特殊的焦油坑！

第 2 部分 - 問題的答案：

讓我們繼續（更有趣）聚合。五個主要的聚合函式是：

AVG() – return the average value.

* ```
COUNT() – return the number of values.

MAX() – return the maximum value.

* ```
MIN() – return the minimum value.

SUM() – return the sum of all or distinct values.

（`sql_mode`回到原來的設置——對於正確制定的查詢來說並不重要，但為什麼不呢？（見小提琴））。


所以，如果我們`SUM()`對錶中的所有值取（因為這是相關的），那就很好了，花花公子，即

SELECT SUM(actions_u_user) AS “The sum of all” FROM test;

結果：

The sum of all 14

但是，如果像現在這樣，您希望獲得`SUM()`超過 6 個不同的 `n_brand`&值，則`population`必須獲得**`GROUP BY`**這些值，否則這些`SUM()`s 對應於什麼？


所以，我們執行這個：

SELECT n_brand AS “The Brand”,

– for presentation purposes. With multi-word aliases, – you have to use double quotes! I use them all the time anyway!

population AS “Population”, SUM(actions_u_user) AS “The column name”

– or AS col_name - no quotes necessary if there are no – spaces in the column alias

FROM test GROUP BY n_brand, population ORDER BY n_brand, population;

結果：

The Brand Population The column name ferrari ferrarieur 2 ferrari ferrarimex 2 ferrari ferrariusa 0 pepsico pepsicoeur 3 pepsico pepsicomex 2 pepsico pepsicousa 5 6 rows

因此，對於給定的&值，現在我們在整個表中都有了`SUM()`of 。最後，我們可以細化這個查詢以返回所需的結果集——我們只對和在哪裡感興趣，因此：`actions_u_user``n_brand``population``pepsico``SUM() is > 0`

SELECT n_brand AS “The Brand”,

– for presentation purposes. With multi-word aliases, – you have to use double quotes!

population AS “Population”, SUM(actions_u_user) AS “The column name”

– or AS col_name - no quotes necessary if there are no – spaces in the column alias

FROM test

WHERE n_brand = ‘pepsico’ – added these two lines AND actions_u_user > 0

GROUP BY n_brand, population ORDER BY n_brand, population;

結果：

The Brand Population The column name pepsico pepsicoeur 3 pepsico pepsicomex 2 pepsico pepsicousa 5

這是想要的結果！


我會敦促您學習並掌握聚合函式——它們是 SQL 的“麵包和黃油”，並將導致諸如[視窗函式](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html)（和其他好東西）之類的東西，這些東西是移動數據的更強大的方法達到[DIKW 金字塔](https://en.wikipedia.org/wiki/DIKW_pyramid)的頂點！


最後，我敦促您在[這里](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql)和[這裡](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc/41887524)看看沒有在 MySQL 中設置的危險`ONLY_FULL_GROUP_BY`——我見過大師說在專家的手中，取消設置是可以的——就我個人而言，我不會。 ..為什麼要麻煩初學者？


查詢可以提供有意義的結果，而不必訴諸這種可憎的東西......所以我**說不**！YMMV！或者只是切換到 PostgreSQL！但是，請參閱上面引用的 Roland Bouman 的文章（[揭穿 GROUP BY 神話](https://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html)）以獲得替代觀點和簡潔的解決方法。[這](https://stackoverflow.com/questions/7594865/is-mysql-breaking-the-standard-by-allowing-selecting-columns-that-are-not-part-o/7596265#7596265)將提供進一步的解釋。


ps歡迎來到dba.se和+1 - 這個答案迫使我整理我的想法！pps 將來，在提出問題時，您能否提供一個工作小提琴 - 它有助於為問題提供一個[單一的事實](https://en.wikipedia.org/wiki/Single_source_of_truth)並消除重複勞動 - 幫助我們幫助您！

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