sql - 加入两个表并正确分组结果

Question

我正在使用 SQL 数据库来存储一些视频分析的人数统计结果。
计数 表可以总结如下:

rowid (auto increment int)  |     created_at (date)   |   type (int)  | count_value (int)
----------------------------+-------------------------+---------------+-------------

后来，我想为检测到的人添加一些属性，仍然使用图像处理。
我本可以更改 计数 表，但是对于添加的每个 now 属性，我都必须手动更改 计数 table 。我想要一个动态的解决方案。
创建了第二个表，名为 属性 ，这很简单。

rowid (auto increment int)  |     key (string)   |   value (string)  | row_id_counting (int)
----------------------------+--------------------+-------------------+-------------

例如，如果我的算法检测到 30 岁男性，以下行将出现在 计数 和 属性 表:

COUNTING :

rowid |     created_at    | type | count_value 
------+-------------------+------+-------------
 150  | 2019.10.18  08:50 |  1   |      1


ATTRIBUTES :

rowid  |     key   |   value  | row_id_counting
-------+-----------+----------+---------------
  120  | "gender"  |  "M"     |      150 
  121  |  "age"    |  "30"    |      150

如果有一天我的 计数 表必须存储其他类型的数据(例如猫或车辆)，我可以在 type 下指定它列，我不必为这些数据存储无用的属性，因为属性存储在另一个表中，并且是动态创建的。

我还可以轻松地从 中检索一些数据计数 在我的 SQL 请求中使用 JOIN 的特定属性。

现在，我想每小时汇总所有具有相同属性的数据，以便从我的“计数”表中创建一些统计数据。我的第一个解决方案是:

SELECT SUM(count_value), attr.key, attr.value, strftime('%Y-%m-%d %H:00:00',created_at, 'localtime') as timestr 
    FROM counting cnt 
    LEFT JOIN attributes attr ON cnt.rowid = attr.row_id_counting
    WHERE datetime(created_at,'localtime') BETWEEN '2019-10-16 22:00:0.000000' AND '2019-10-17 22:00:00.000000' 
    AND cnt.type = 1
    GROUP BY timestr,attr.key, attr.value

结果起初看起来很有希望，这个请求甚至为没有属性的计数行返回一个值。
但是一组简单的数据可以很容易地显示出局限性:

一名50岁，性别不明

一名20岁男性

一名女性，年龄不详

一个人，年龄性别不详

结果:

 SUM(count_value)   key       value       timestr
 1                  (null)    (null)      2019-10-18 10:00:00
 1                  age       20          2019-10-18 10:00:00
 1                  age       50          2019-10-18 10:00:00
 1                  gender    F           2019-10-18 10:00:00
 1                  gender    M           2019-10-18 10:00:00

即使我将 (null) (null) 行添加到年龄属性或性别属性的总和中，我也只计算 3 人而不是 4 人。年龄总和的失踪人员是年龄未知的女性，而失踪的人是性别总和是50岁的未知性别的人。

是否有任何方法可以更改此 SQL 请求，以便改为具有以下行:

 SUM(count_value)   key       value       timestr
 2                  age       (null)      2019-10-18 10:00:00
 1                  age       20          2019-10-18 10:00:00
 1                  age       50          2019-10-18 10:00:00
 2                  gender    (null)      2019-10-18 10:00:00
 1                  gender    F           2019-10-18 10:00:00
 1                  gender    M           2019-10-18 10:00:00

Answer 1

问题是这样的:数据库应该如何知道类型 1 是一个人，而你期待他们的性别和年龄，类型 2 是一只猫，你期待尾部的颜色？

如果您知道这一点，您可以尝试构建一个将其考虑在内的查询，即。

select type, name, (select color from <the other table> where <the attributes match>),
from first table where <conditions>

但恐怕这几乎是你唯一的选择