EAV - 对有和没有 NULL 的列和行值进行 COUNT()/SUM()

您好，欢迎到论坛！

您的架构需要彻底彻底改革。这张图片给出了一些关于它有什么问题的想法：

您使用了 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.color

SET 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 中看到这一点。

我还留给您为大型数据集的必要字段添加索引 - 如果您遇到问题，请在此处返回一个新问题，其中包含指向此问题的链接！EXPLAIN ANALYZE在这方面应该给出一些提示！

SELECT t2.data Color, SUM(t1.data IS NOT NULL) PickedUp, COUNT(*) Total
FROM demo t1
JOIN demo t2 USING (userID)
WHERE t1.title = 'DATE'
  AND t2.title = 'COLOR'
GROUP BY t2.data;

小提琴