问查询优化:将元数据连接到值列表表
EN

Stack Overflow用户

提问于 2017-07-02 00:56:51

回答 1查看 25关注 0票数 0

我有一个包含数据表和元数据表的数据库。我想要创建一个视图，该视图选择属于某一项的某些元数据，并将其作为列列出。

视图的基本查询是：SELECT * FROM item。项目表定义为：

CREATE TABLE item (
id        INTEGER PRIMARY KEY AUTOINCREMENT
                  UNIQUE
                  NOT NULL,
traceid   INTEGER REFERENCES trace (id) 
                  NOT NULL,
freq      BIGINT  NOT NULL,
value     REAL    NOT NULL
);

要添加的元数据遵循模式“metadata.import=‘name’”

元表定义为：

CREATE TABLE metadata (
id        INTEGER PRIMARY KEY AUTOINCREMENT
                  UNIQUE
                  NOT NULL,
parameter STRING  NOT NULL
                  COLLATE NOCASE,
value     STRING  NOT NULL
                  COLLATE NOCASE,
datasetid INTEGER NOT NULL
                  REFERENCES dataset (id),
traceid   INTEGER REFERENCES trace (id),
itemid    INTEGER REFERENCES item (id) 
);

应该以这种方式选择"name“参数：

如果存在一个参数为"name“且itemid与item.id匹配的记录，那么它的值应该包含在记录中。
否则，如果存在参数为"name“、"itemid”为空且traceid与item.traceid匹配的记录，则应使用其值。
否则，结果应该为NULL，但是无论如何都应该包括来自item表的记录。

目前，我使用以下查询来实现此目标：

SELECT i.*, 
COALESCE (
  MAX(CASE WHEN m.parameter='name' THEN m.value END), 
  MAX(CASE WHEN m2.parameter='name' THEN m2.value END)
) AS itemname

FROM item i

JOIN metadata m
  ON (m.itemid = i.id AND m.parameter='name')

JOIN metadata m2 
  ON (m2.itemid IS NULL AND m2.traceid = i.traceid AND m2.parameter='name') 

GROUP BY i.id

但是，这个查询有点效率低下，因为元数据表被使用了两次，并且包含了比"name“记录更多的记录。因此，我正在寻找一种提高速度的方法，特别是在即将实现某些扩展的情况下：

应该包含第三个级别的“数据集”：如果"parameter=name“具有与项目相同的数据源(将通过搜索另一个连接traceid和数据表的项来查找项)，则应该使用"parameter=name”，如果"itemid“匹配或"traceid”匹配都不存在“parameter=name”。
更多的元数据应该由遵循相同模式的视图查询。

任何帮助都是非常感谢的。

sql

join

sqlite

query-optimization

腾讯云会员中心

加入腾讯云会员，每月可领会员专属优惠券；会员云产品免费体验；云产品会员专享价；会员签到抽奖…

回答 1

Stack Overflow用户

发布于 2017-07-02 01:10:23

首先，您可以使用一个联接而不是2，如下所示：

JOIN metadata m ON (m.parameter='name' AND (m.itemId = i.id OR (m.itemId IS NULL AND m.traceid = i.traceid)))

然后，您可以使用简单的select删除合并：

SELECT i.*, m.value as itemname

结果应该如下所示：

SELECT i.*, m.value as itemname
FROM item i

JOIN metadata m ON (m.parameter='name' AND (m.itemId = i.id OR (m.itemId IS NULL AND m.traceid = i.traceid)))

GROUP BY i.id