时间戳

首页 > 解决方案 > 制作有效的横向连接（或替代方法）

sql - 制作有效的横向连接（或替代方法）

问题描述

语境

我正在玩 PostgreSQL 的lateral加入，特别是在 group by/limit 上执行加入。

当我查找单个记录时，查询工作得非常好，但是当我们查询多条记录时，性能很快就会下降。这是有道理的，因为我们有多个子查询运行单独的收集、过滤聚合、排序。问题是，我们应该看什么 Postgres 策略，或者我们如何重构下面的查询以使其大规模执行？

询问

我们有三个主表，其中两个之间有一个联结表：

|经理| >- |专卖店| >- |店铺_产品| -< 产品

我们有给定商店记录的所有历史经理，我们有商店的完整产品目录（产品可能由多个商店携带）。

目标：给定一个Store ID，查询最近的Manager和最近售出的Product。

这是从 Store 到 Manager 和 Product 的内部连接。Manager & Product 必须按日期 desc 排序并限制为 1（至少我相信这是获取最新信息的方式）。

SELECT 
    store.id as store_id,
    manager.id as manager_id,
    *
FROM 
    Stores as store,
    LATERAL (
        SELECT 
            * 
        FROM 
            Products as product 
        INNER JOIN Stores_Products store_product on store_product.product_id = product.id
        WHERE 
            store_product.store_id = store.id
        ORDER BY 
            store.date desc
        LIMIT 1
    ) p,
    LATERAL (
        SELECT 
            * 
        FROM 
            Managers as manager
        WHERE 
            manager.store_id = store.id 
        ORDER BY
            manager.date desc
        LIMIT 1
        ) m
WHERE store.name = 'ABC retail'

当您查询单个商店时，这非常有效。但是，如果您尝试批量查询（例如WHERE store.name in [...]），查询会变得非常缓慢并且会很快消耗内存。

问题

有没有更好的方法来查询可以很好扩展的数据？

谢谢！

注意：给出的商店/产品示例只是一个传达问题的设备。实际的架构是不同的 - 所以我要求不要过多考虑这是否是规范化架构的最佳方式！谢谢 ！

标签： sqlpostgresqlquery-performancelateral

解决方案

也许窗口函数会运行得更快。在下面的代码中留下了产品订购属性，...因为在您的代码段中它们似乎是按 store.date 订购的，这看起来是错误的（它是商店的财产，而不是产品的财产，而不是商店出售的产品的财产）。

SELECT * FROM 
-- Let's rank managers within each store, giving rank=1 to the most recent
(
  SELECT id, 
         store_id, 
         RANK() OVER (PARTITION BY store_id ORDER BY date DESC) AS mgr_rank
  FROM Manager
) AS MgrRank 

JOIN

-- Let's rank products within each store, giving rank=1 to the most recent
(
  SELECT store_id,
         Products.*
         RANK() OVER (PARTITION BY store_id ORDER BY .... DESC) AS product_rank
  FROM Stores_Products JOIN Products ON product_id = Products.id
) AS ProductRank
USING(store_id) 

-- Now let's join stores themselves
JOIN Stores ON store_id = Stores.id

-- Select most recent manager and product
WHERE mgr_rank=1 AND product_rank=1 AND Stores.name='ABC retail'

请记住，此特定查询不会输出没有经理或产品的商店。您还需要使用外部连接来包含它们。

推荐阅读