EAV - 對有和沒有 NULL 的列和行值進行 COUNT()/SUM()
假設有一張桌子
展示
使用這些數據：
如果行日期的數據是空的，顏色還沒有被拾取。
我需要在網站 php 頁面上查看的資訊概述是：
獲得的 RED：2
總 RED：2
黃色拾取：0
總黃色：1
粉紅色拾取：3
總粉紅色：3
您好，歡迎到論壇！
您的架構需要徹底徹底改革。這張圖片給出了一些關於它有什麼問題的想法：
您使用了 EAV（實體-屬性-值）數據庫設計反模式 - 搜尋Joe Celko、Bill Karwin和其他人（以及其中的連結）關於該主題的著作。
此外，非常重要的是，您應該將
VARCHAR()s 儲存為VARCHAR()s 並將DATEs 儲存為DATEs - 當您閱讀 EAV 時您會看到這一點 - 數據類型超出了視窗，這意味著查詢優化器無法生成最佳計劃。這也適用於其他類型。你可以在這裡看到這個效果：
CREATE TABLE date_test (x VARCHAR(10));INSERT INTO date_test VALUES ('15-05-2021'), ('25-10-2000');SELECT * FROM date_test ORDER BY x;結果：
x 15-05-2021 -- <<==== WRONG order! 25-10-2000正確的數據類型很重要的原因之一**！即使您必須將日期儲存為字元串（我意識到程序員會受到限制），您也應該**使用ISO 日期格式（即 YYYY-MM-DD）。
TL;DR - 答案！
如果您只想要答案，那麼它是：
SELECT dt.color AS "Color", COUNT(dt.c_date) AS "Collected", COUNT(*) AS "Total" FROM demo_tab dt GROUP BY dt.color ORDER BY dt.color;結果：
Color Collected Total PINK 3 3 RED 2 2 YELLOW 1 2它還具有成為提議中最快查詢的優點 - 請參閱
PROFILING下面的部分！處理架構：
你應該做的是（參見這裡的小提琴- 改編自@Akina’s - 我添加了一件收集的黃色服裝。我不知道該
id專欄發生了什麼，此後我忽略了它）。因此，您創建了一個
VIEW- 如果由於其他限製而無法更改架構 - 我很欣賞 DBA/程序員並不總是（完全）控制他們的環境。看法：
CREATE VIEW demo_view AS ( SELECT x.user_id AS "uid", x.data AS "color", CAST( CONCAT ( SUBSTRING(y.data, 7, 10), SUBSTRING(y.data, 3, 4), SUBSTRING(y.data, 1, 2) ) AS DATE ) AS c_date FROM demo x JOIN demo y ON x.user_id = y.user_id AND x.title = 'COLOR' AND y.title = 'DATE' );桌子：
CREATE TABLE demo_tab AS ( SELECT * FROM demo_view -- -- Could also construct it as above! -- );然後您對錶格的查詢（也可以在視圖上 - 參見小提琴）將非常簡單：
SELECT dt.color AS "Color", SUM(CASE WHEN dt.c_date IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT(*) AS "Total" FROM demo_tab dt GROUP BY dt.color;結果（所有查詢相同）：
Color Collected Total RED 2 2 YELLOW 1 2 PINK 3 3現在，為什麼，您可能會問我在這裡使用“複雜”而不是Akina 的答案
SUM(CASE...相對“簡單” 。如果您嘗試使用 PostgreSQL 執行他的小提琴（請參閱此處），它會失敗。該結構是特定於 MySQL 的，而另一個適用於 PostgreSQL、SQLServer 和 MySQL！如果有疑問 - 在 PostgreSQL 上測試 - 如果它在那裡不起作用，通常會有一些可疑的事情發生！SUM(t1.data IS NOT NULL)...``SUM(... IS NOT NULL)如果您想（或有義務）堅持使用目前的模式，請執行以下命令：
SELECT t2.data AS "Color", SUM(CASE WHEN t1.data IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT(*) Total, CAST(SUM(CASE WHEN t1.data IS NOT NULL THEN 1 ELSE 0 END ) AS FLOAT) * 100 / COUNT(*) AS "% collected" FROM demo t1 JOIN demo t2 ON t1.user_id = t2.user_id WHERE t1.title = 'DATE' AND t2.title = 'COLOR' GROUP BY t2.data ORDER BY 1;結果 - 添加了一個百分比收集欄位！
Color Collected Total % collected PINK 3 3 100 RED 2 2 100 YELLOW 1 2 50我做了一個性能分析 - 見小提琴 - 使用分析 - 檢查手冊：
SET PROFILING = 1;執行您的查詢
SHOW PROFILES;結果（顯示的典型結果 - 執行了幾次 - 可能會有所不同！查詢可能會被截斷）：
Query_ID Duration Query 1 0.00056325 SELECT dt.color AS "Color", COUNT(dt.c_date) AS "Collected", COUNT(*) AS "Total" FROM demo_tab dt GROUP BY dt.color ORDER BY dt.color Query_ID Duration Query 2 0.00064050 SELECT dt.color AS "Color", SUM(CASE WHEN dt.c_date IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT(*) AS "Total" FROM demo_tab dt GROUP BY dt.color 3 0.00074950 SELECT t2.data AS "Color", SUM(CASE WHEN t1.data IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT(*) Total, CAST(SUM(CASE WHEN t1.data IS NOT NULL THEN 1 ELSE 0 END ) AS FLOAT) * 100 / COUNT(*) AS "% collected" FROM demo t1 JOIN demo t2 ON t1.user_id = 4 0.00075000 SELECT v.color AS "Color", SUM(CASE WHEN v.c_date IS NOT NULL THEN 1 ELSE 0 END ) AS "Collected", COUNT(*) AS "Total" FROM demo_view v GROUP BY v.colorSET PROFILING = 0;基於表的查詢非常頻繁地具有最短的持續時間（大約 90% 的時間 - 我們不知道 dbfiddle 伺服器的其餘部分發生了什麼）。基於表的查詢的查詢計劃比其他查詢計劃短得多（
EXPLAIN ANALYZE- 見小提琴） - 好的經驗法則，計劃越短，查詢越快！如果你想使用標準 SQL（你應該！），你可以使用這個命令（見小提琴的底部） - 你可以發出
SET sql_mode='ANSI';命令 - 從前面提到的 Bill Karwin 的答案這裡。user_id title data w x 1 DATE 01-01-2021 2021-01-01 2021-01-01 2 DATE NULL NULL NULL 3 DATE 04-01-2021 2021-01-04 2021-01-04 4 DATE 04-01-2021 2021-01-04 2021-01-04 5 DATE 02-01-2021 2021-01-02 2021-01-02 6 DATE 08-01-2021 2021-01-08 2021-01-08 7 DATE 07-07-2027 2027-07-07 2027-07-07我把它留給讀者作為練習，將現在有效的日期放入上面的 SQL 中——它應該與 MySQL 特定的構造完全相同——你可以在 PostgreSQL fiddle here中看到這一點。
我還留給您為大型數據集的必要欄位添加索引 - 如果您遇到問題，請在此處回复我們一個包含指向此問題的連結的新問題！
EXPLAIN ANALYZE在這方面應該給出一些提示！
