SQLite交叉引用唯一组合

Question

我已经用给定的模式填充了两个表:

    CREATE TABLE objects
    (
        id BIGINT NOT NULL,
        latitude BIGINT NOT NULL,
        longitude BIGINT NOT NULL,
        PRIMARY KEY (id)
    )
    CREATE TABLE tags
    (
        id BIGINT NOT NULL,
        tag_key VARCHAR(100) NOT NULL,
        tag_value VARCHAR(500),
        PRIMARY KEY (id , tag_key)
    )

对象。Id和标签。引用相同的对象

我想用tag_key和tag_value的唯一组合填充第三个表。例如:

    INSERT OR REPLACE INTO objects (id) VALUES (0);
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (0, 'a', 'x');
    INSERT OR REPLACE INTO objects (id) VALUES (1);
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (1, 'a', 'y');
    INSERT OR REPLACE INTO objects (id) VALUES (2);
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (2, 'a', 'x');
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (2, 'a', 'y');
    INSERT OR REPLACE INTO objects (id) VALUES (3);
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (3, 'a', 'x');
    INSERT OR REPLACE INTO objects (id) VALUES (4);
    INSERT OR REPLACE INTO tags (id, tag_key, tag_value) VALUES (4, 'a', 'y');

应该有3个

条目

0: ([a,x]) 
1: ([a,y]) 
3: ([a,x][a,y])

目前我有:

    CREATE TABLE tags_combinations
    (
        id INTEGER PRIMARY KEY,
        tag_key VARCHAR(100) NOT NULL,
        tag_value VARCHAR(500)
    );

id不应该与对象的原始id相关，只是用于对唯一组合进行分组。

这是我到目前为止的查询:

    SELECT
        t1.tag_key, t1.tag_value
    FROM
        tags t1
    WHERE
        t1.id
    IN
        (
    /* select ids who's every tags entry is not under one id in tags_combinations */
            SELECT 
                t2.id 
            FROM
                tags t2
            WHERE
                t2.tag_key, t2.tag_value
            NOT IN
            (
            )
        );

评论的部分是我不确定的，我如何从标签中选择每个id，这些标签没有所有相应的tag_key和tag_value条目已经在tags_combination中的一个id下?

为了明确我所追求的结果:从给出的示例数据中，它应该返回4行:

row id tag_key tag_value    
 0   0   a         x
 1   1   a         y
 2   2   a         x
 3   2   a         y

Answer 1

SQL是一种基于集合的语言。如果你用集合论的语言重新表述你的问题，你可以直接把它转换成SQL:

• 您需要tags表的所有行，除了来自重复对象的行。
• 对象是重复的，如果他们有完全相同的键/值组合。然而，我们仍然希望返回这些对象中的一个，因此我们只将副本定义为不存在其他具有较小ID的重复对象的对象。
• 两个对象A和B有完全相同的键/值组合如果
  • A中的所有键/值组合在B中也存在，
  • B中的所有键/值组合也存在于a中。
• 如果A中不存在B中不存在的键/值组合，则A中的所有键/值组合在B中也存在(注意:双否定)。

SELECT id, tag_key, tag_value
FROM tags
WHERE NOT EXISTS (SELECT 1
                  FROM tags AS dup
                  WHERE dup.id < tags.id
                    AND NOT EXISTS (SELECT 1
                                    FROM tags AS A
                                    WHERE A.id = tags.id
                                      AND NOT EXISTS (SELECT 1
                                                      FROM tags AS B
                                                      WHERE B.id = dup.id
                                                        AND B.tag_key   = A.tag_key
                                                        AND B.tag_value = A.tag_value)
                                   )
                    AND NOT EXISTS (SELECT 1
                                    FROM tags AS B
                                    WHERE B.id = dup.id
                                      AND NOT EXISTS (SELECT 1
                                                      FROM tags AS A
                                                      WHERE A.id = tags.id
                                                        AND A.tag_key   = B.tag_key
                                                        AND A.tag_value = B.tag_value)
                                   )
                 )
ORDER BY id, tag_key;

Answer 2

这在SQLite中并不容易。我们想要识别一组标记键/值对。因此，我们可以按id分组并得到与group_concat相关联的对的字符串。在另一个DBMS中也是这样做的。然而，SQLite不能在group_concat中排序，所以我们可能最终得到2: 'a/x,a/y'和5: 'a/y,a/x'。

你最好的办法可能是写一个程序，迭代地找到不同的对

在SQLite中，你可能想试试这个:

insert into tags_combinations (id, tag_key, tag_value)
  select id, tag_key, tag_value
  from tags
  where id in
  (
    select min(id)
    from
    (
      select id, group_concat(tag_key || '/' || tag_value) as tag_pairs
      from
      (
        select id, tag_key, tag_value
        from tags
        order by id, tag_key, tag_value
      ) ordered_data
      group by id
    ) aggregated_data
    group by tag_pairs
  );

在应用group_concat之前对数据排序很可能使标记对排序，但不能保证 !

Answer 3

要将多行合并为一个值，需要使用类似于group_concat()的函数。

需要使用ORDER BY来确保组内的行顺序一致:

SELECT DISTINCT group_concat(tag_key) AS tag_keys,
                group_concat(tag_value) AS tag_values
FROM (SELECT id,
             tag_key,
             tag_value
      FROM tags
      ORDER BY id,
               tag_key,
               tag_value)
GROUP BY id;

如果您想让键和值交错，如问题所示，您需要执行更多的字符串连接:

SELECT DISTINCT group_concat(tag_key || ',' || tag_value, ';') AS keys_and_values
FROM (...