在Postgres中有效地计算行之间关系的强度

Question

我有一个看起来类似的表：

 session_id | sku 
------------|-----
     a      |  1
     a      |  2
     a      |  3
     a      |  4
     b      |  2
     b      |  3
     c      |  3

我想将其转换为类似于以下的表格：

 sku1 | sku2 | score
------|------|------
  1   |  2   |   1
  1   |  3   |   1
  1   |  4   |   1
  2   |  3   |   2
  2   |  4   |   1
  3   |  4   |   1

这个想法是存储一个非规范化的表，允许用户查找给定的sku，其他哪些sku与它相关的会话相关，以及两个sku与同一会话相关的次数。

你可以建议在PostgreSQL或其他技术中实现这一点的算法、模式或策略是什么？

我意识到这种查找可以使用计数在原始表上进行，也可以使用分面搜索引擎。然而，我想让阅读更具性能，只想保留总体统计数据。其想法是，我将定期对第一个表中最新的几千行执行此透视，然后将结果存储在第二个表中。我只关心第二张表的大概统计数字。

我有一些SQL可以工作，但速度很慢。也在研究使用某种图形数据库的潜力，但希望避免为应用程序的一小部分添加另一种技术。

更新：下面的SQL似乎足够高性能了。在我的iMac上，我可以在大约5分钟内将第一个表（标签）中的120万行转换为第二个表（product_relations）中的250k行，其中sku的变体约为2-3k。事实上，我每天只会去规范化多达10k行。问题是，这是否真的是最好的方法。我觉得有点脏。

BEGIN;
    CREATE
    TEMPORARY TABLE working_tags(tag_id int, session_id varchar, sku varchar) ON COMMIT DROP;
    INSERT INTO working_tags
    SELECT id,
           session_id,
           sku
    FROM tags
    WHERE time < now() - interval '12 hours'
      AND processed_product_relation IS NULL
      AND sku IS NOT NULL LIMIT 200000;
    CREATE
    TEMPORARY TABLE working_relations (sku1 varchar, sku2 varchar, score int) ON COMMIT DROP;
    INSERT INTO working_relations
    SELECT a.sku AS sku1,
           b.sku AS sku2,
           count(DISTINCT a.session_id) AS score
    FROM working_tags AS a
    INNER JOIN working_tags AS b ON a.session_id = b.session_id
    AND a.sku < b.sku
    WHERE a.sku IS NOT NULL
      AND b.sku IS NOT NULL
    GROUP BY a.sku,
             b.sku;
    UPDATE product_relations
    SET score = working_relations.score+product_relations.score
    FROM working_relations
    WHERE working_relations.sku1 = product_relations.sku1
      AND working_relations.sku2 = product_relations.sku2;
    INSERT INTO product_relations (sku1, sku2, score)
    SELECT working_relations.sku1,
           working_relations.sku2,
           working_relations.score
    FROM working_relations
    LEFT OUTER JOIN product_relations ON (working_relations.sku1 = product_relations.sku1
                                          AND working_relations.sku2 = product_relations.sku2)
    WHERE product_relations.sku1 IS NULL;
    UPDATE tags
    SET processed_product_relation = TRUE
    WHERE id IN
        (SELECT tag_id
         FROM working_tags);
  COMMIT;

Answer 1

如果我正确解释了你的意图（根据评论），这应该可以做到：

SELECT
  s1.sku AS sku1,
  s2.sku AS sku2,
  count(session_id)
FROM session s1
INNER JOIN session s2 USING (session_id)
WHERE s1.sku < s2.sku
GROUP BY s1.sku, s2.sku
ORDER BY 1,2;

请参阅：http://sqlfiddle.com/#！15/2e2/1

换言之：自加入会话，然后为每个会话ID找到SKU的所有配对，不包括左边大于或等于右边的配对，以避免重复配对-如果我们有(1,2,count)，我们也不想要(2,1,count)。然后按SKU配对进行分组，并计算每个配对的行数。

如果您的SKU配对可以重复，并且您希望排除重复项，则可能需要count(distinct session_id)。可能会有更有效的方法来做到这一点，但这是最简单的。

至少session_id上的索引将非常有用。您可能还想篡改计划器成本参数，以确保它选择了一个好的计划——特别是，确保effective_cache_size是准确的，并且random_page_cost与seq_page_cost反映了您的缓存和I/O成本。最后，尽可能多地投入work_mem。

如果您正在创建物化视图，只需CREATE UNLOGGED TABLE whatever AS SELECT ...。这样可以最大限度地减少写入/重写/重写次数。