Couchbase/N1QL：从参数提供的值列表中选择

作为后续从集合中按“类别”获取顶部行 https://stackoverflow.com/questions/72447539/get-top-rows-by-category-from-a-collection我仍然想获取每个类别 ID 的前 5 个产品，但我想提供与我相关的类别 ID 的预选列表。

从...开始vsr 的回答 https://stackoverflow.com/a/72447949/218472从最初的问题来看，我可以这样做：

SELECT u.*
FROM (SELECT DISTINCT RAW p.categoryId
      FROM products AS p
      WHERE p.categoryId IN $categoryIds) AS c
UNNEST (SELECT p1.*
    FROM products AS p1
    WHERE p1.categoryId = c
    ORDER BY p1.categoryId, p1.price DESC
    LIMIT 5) AS u;

其中命名参数$categoryIds将作为数组提供['cat1', 'cat2'].

感觉做起来效率有点低SELECT DISTINCT RAW p.categoryId FROM products AS p WHERE p.categoryId IN $categoryIds，只是为了取回一些东西，这本质上又是我提供的categoryIds列表。

我确信有更有效的方式来表达这一点。就像是：

SELECT u.*
FROM (VALUES IN $categoryIds) AS c
UNNEST ...;

CREATE INDEX ix1 ON products(categoryId, price DESC);

因此，Unnest 中的以下子查询使用索引顺序并仅检索每个类别的前 5 个条目，而不管特定类别中的条目数量有多少

如果 $categoryIds 包含唯一条目

SELECT u.*
FROM $categoryIds AS c
UNNEST (SELECT p1.*
       FROM products AS p1
       WHERE p1.categoryId = c
       ORDER BY p1.categoryId, p1.price DESC
       LIMIT 5) AS u;

对于非唯一条目

SELECT u.*
FROM (SELECT DISTINCT RAW c1
      FROM $categoryIds AS c1 ) AS c
UNNEST (SELECT p1.*
    FROM products AS p1
    WHERE p1.categoryId = c
    ORDER BY p1.categoryId, p1.price DESC
    LIMIT 5) AS u;