在查詢中具有相同計數（*）的記錄子集之間選擇隨機數據庫記錄
我有這個簡單的查詢：
SELECT `value`, count(*) as `noHits` FROM `topic` WHERE identifier IN ('" . implode("','", array_keys($processedIdentifier)) ."') GROUP BY `value` ORDER BY count(*) DESC LIMIT 150它會生成一個記錄列表，例如：
keyword1, 100 keyword2, 90 keyword3, 40 keyword4, 40 keyword5, 40 keyword6, 40 ... keyword1500, 40我的挑戰是選擇前 150 條記錄的列表，
ordered by count(*) DESC但是當有超過 1 個值與上面的關鍵字 3 到關鍵字 1500 的列表中具有相同計數（*）時，從數據庫中隨機選擇它們。似乎從我所做的測試中選擇不是隨機的。
結果用於生成 wordcloud，當它們具有相同的計數（*）時，我不希望始終選擇相同的關鍵字（從數千個中）
為了解決這個問題，我做了以下事情（見小提琴）
創建了一個表：
CREATE TABLE test -- 22 records ( the_word TEXT, no_of_times INTEGER );用這個答案末尾的數據填充它。
然後執行以下查詢：
SELECT the_word, no_of_times FROM test ORDER BY no_of_times DESC, RAND() LIMIT 15;我對此進行了多次測試，並獲得瞭如下結果。前 6 條記錄始終相同 - 關鍵字 1-6。剩下的 9 條記錄總是從關鍵字 7-22 中隨機出現的。
結果（範例 - 顯示前 10 個）：
the_word no_of_times keyword1 100 keyword2 90 keyword3 80 keyword4 70 keyword5 60 keyword6 50 keyword17 40 keyword19 40 keyword22 40 keyword18 40這比我的第一個答案要優雅得多-猜我把事情複雜化了！:-)
============================ 數據（22 條記錄）================
INSERT INTO test VALUES ('keyword1', 100); INSERT INTO test VALUES ('keyword2', 90); INSERT INTO test VALUES ('keyword3', 80); INSERT INTO test VALUES ('keyword4', 70); INSERT INTO test VALUES ('keyword5', 60); INSERT INTO test VALUES ('keyword6', 50); INSERT INTO test VALUES ('keyword7', 40); INSERT INTO test VALUES ('keyword8', 40); INSERT INTO test VALUES ('keyword9', 40); INSERT INTO test VALUES ('keyword10', 40); INSERT INTO test VALUES ('keyword11', 40); INSERT INTO test VALUES ('keyword12', 40); INSERT INTO test VALUES ('keyword13', 40); INSERT INTO test VALUES ('keyword14', 40); INSERT INTO test VALUES ('keyword15', 40); INSERT INTO test VALUES ('keyword16', 40); INSERT INTO test VALUES ('keyword17', 40); INSERT INTO test VALUES ('keyword18', 40); INSERT INTO test VALUES ('keyword19', 40); INSERT INTO test VALUES ('keyword20', 40); INSERT INTO test VALUES ('keyword21', 40); INSERT INTO test VALUES ('keyword22', 40);
這個答案太複雜了——可能有利於了解 CTE，但請在此處查看我更優雅的答案。
這被證明是相當棘手的 - 它涉及連結
CTEs（通用表表達式 - 又名WITH clause- 一個優秀的站點）。由於不允許將 SQL 表達式放入LIMIT和OFFSET子句這一事實，這變得更加困難——解決方案本來會更容易和更優雅。我將解釋我在進行過程中使用的邏輯 - 這裡有一個可用的小提琴- 你需要
MySQL 8Window（或 Analytic）功能。正如我所提到的，我連結了一系列CTEs，因此我將逐步完成該過程。首先，我創建並填充了一個表 - 請參閱此答案的底部以獲取該數據或小提琴。
然後我執行了這個：
WITH cte1 AS ( SELECT 15 AS tot_num_recs_wanted -- the 15 could come from a table ), cte2 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY no_of_times DESC) AS rn FROM test ) SELECT * FROM cte2;結果：
the_word no_of_times rn keyword1 100 1 keyword2 90 2 keyword3 80 3 keyword4 70 4 keyword5 60 5 keyword6 50 6 keyword7 40 7 keyword8 40 8 keyword9 40 9 keyword10 40 10 -- further data snipped for brevity - see fiddle -- this could have been made much easier if -- could have used SQL in the LIMIT and OFFSET clauses -- There would have been no need to use (another) CTE -- or the ROW_NUMBER() Window function.因此，我們可以看到我們需要 15 條記錄。因此，我們需要預設選擇前 6 條記錄（no_of_times 50 - 100），然後從剩餘的 22 條記錄中隨機選擇 9 條記錄（no_of_times = 40）。
-- above snipped, again for brevity cte3 AS ( SELECT rn, no_of_times nt FROM cte2 WHERE rn = (SELECT tot_num_recs_wanted FROM cte1) ) SELECT * FROM cte3;結果：
rn nt 15 40所以，我們知道我們需要 15 條記錄，截止值為 40。
然後我跑了：
...snipped... cte4 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) AS rand_rn FROM test WHERE no_of_times = (SELECT nt FROM cte3) -- i.e. 40 ) SELECT * FROM cte4;結果：
the_word no_of_times rand_rn keyword22 40 1 keyword11 40 2 keyword9 40 3 keyword8 40 4 keyword10 40 5 keyword16 40 6 keyword7 40 7 keyword15 40 8 keyword13 40 9 keyword21 40 10 -- snipped...所以，現在我有 16 條記錄，它們的 no_of_times 等於 40，更重要
RAND()的是使用函式排序。然後，我使用以下 SQL 從該表中進行選擇：
, cte5 AS ( SELECT * FROM cte4 WHERE rand_rn <= (SELECT tot_num_recs_wanted FROM cte1) - (SELECT COUNT(*) FROM test WHERE no_of_times > (SELECT nt FROM cte3)) ) SELECT * FROM cte5; -- again, this could have been made much easier if -- could have used SQL in the LIMIT and OFFSET clauses -- There would have been no need to use (another) CTE -- or the ROW_NUMBER() Window function.結果：
the_word no_of_times rand_rn keyword9 40 1 keyword18 40 2 keyword12 40 3 keyword11 40 4 keyword13 40 5 keyword22 40 6 keyword19 40 7 keyword15 40 8 keyword10 40 9 -- **__9 rows__** --9 行 +
no_of_points大於 40 的 6 行給出了 15 行。上述SQL的關鍵部分是：
WHERE rand_rn <= (SELECT tot_num_recs_wanted FROM cte1) - (SELECT COUNT(*) FROM test WHERE no_of_times > (SELECT nt FROM cte3))這是 15 - 6 即 9 - 正是我們記錄的隨機分量所需要的。
最後，我
UNION在我的隨機記錄和no_of_points超過 40 的記錄之間執行了一個，如下所示：SELECT the_word, no_of_times FROM cte5 UNION SELECT the_word, no_of_times FROM test WHERE no_of_times > (SELECT nt FROM cte3) ORDER BY no_of_times DESC;結果：
the_word no_of_times keyword1 100 keyword2 90 keyword3 80 keyword4 70 keyword5 60 keyword6 50 keyword11 40 keyword14 40 keyword21 40 -- snipped --前 6 個關鍵字是不變的，但是如果您多次執行 fiddle，您會看到關鍵字 > 6（即
no_of_points= 40）在每次執行時都會發生變化。我不確定這個解決方案的效率如何，但合適的索引可能會有所幫助。我希望這能回答你的問題——如果不告訴我。ps 歡迎來到論壇！:-)
============================== 表和數據================= =====
CREATE TABLE test -- 22 records ( the_word TEXT, no_of_times INTEGER ); INSERT INTO test VALUES ('keyword1', 100); INSERT INTO test VALUES ('keyword2', 90); INSERT INTO test VALUES ('keyword3', 80); INSERT INTO test VALUES ('keyword4', 70); INSERT INTO test VALUES ('keyword5', 60); INSERT INTO test VALUES ('keyword6', 50); INSERT INTO test VALUES ('keyword7', 40); INSERT INTO test VALUES ('keyword8', 40); INSERT INTO test VALUES ('keyword9', 40); INSERT INTO test VALUES ('keyword10', 40); INSERT INTO test VALUES ('keyword11', 40); INSERT INTO test VALUES ('keyword12', 40); INSERT INTO test VALUES ('keyword13', 40); INSERT INTO test VALUES ('keyword14', 40); INSERT INTO test VALUES ('keyword15', 40); INSERT INTO test VALUES ('keyword16', 40); INSERT INTO test VALUES ('keyword17', 40); INSERT INTO test VALUES ('keyword18', 40); INSERT INTO test VALUES ('keyword19', 40); INSERT INTO test VALUES ('keyword20', 40); INSERT INTO test VALUES ('keyword21', 40); INSERT INTO test VALUES ('keyword22', 40);