1

I am using Couchbase and N1QL.

Let's say I have a table of products. Products have a categoryId and a price among other attributes:

id name categoryId price
1 product 1 cat1 5
2 product 2 cat2 100
3 product 3 cat1 25

I know how to get the 5 most expensive products for a single category. I would do something like this:

SELECT * FROM products
WHERE categoryId = 'cat1'
ORDER BY price DESC
LIMIT 5

But how would I query for the top 5 most expensive products per category? I know I can do a WHERE categoryId IN ['cat1', 'cat2'] but this would result in the top5 products among those categories, and not per category.

Any couchbase or N1QL experts that can help me out?

Thanks in advance!

DiegoFrings
  • 3,043
  • 3
  • 26
  • 30

1 Answers1

2

Use WINDOW functions

SELECT p1.*, MISSING AS r
FROM (SELECT p.*, 
      RANK() OVER (PARTITION BY p.categoryId ORDER BY p.price DESC) AS r
      FROM products AS p
      WHERE p.categoryId IS NOT NULL) AS p1
WHERE p1.r < 6;

Non-Window functions (UNNEST should perform better)

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

SELECT p2.*, MISSING AS r
FROM (SELECT p.*,
      (SELECT RAW 1+COUNT(1)
       FROM products AS p1
       WHERE p1.categoryId = p.categoryId AND p1.price > p.price)[0] AS r
      FROM products AS p
      WHERE p.categoryId IS NOT NULL) AS p2
WHERE p2.r < 6;

OR

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

OR

WITH categoryIds AS (SELECT DISTINCT RAW p.categoryId
                     FROM products AS p
                     WHERE p.categoryId IS NOT NULL)
SELECT c AS categoryId,
       (SELECT p1.*
        FROM products AS p1
        WHERE p1.categoryId = c
        ORDER BY p1.categoryId, p1.price DESC
        LIMIT 5) AS top5
FROM categoryIds AS c ;
vsr
  • 7,149
  • 1
  • 11
  • 10
  • That looks very useful. I will look into it. Thank you for the quick reply. – DiegoFrings May 31 '22 at 12:48
  • 1
    _Window function is enterprise level feature_ :\ ... Unfortunately I am not able to use this. – DiegoFrings May 31 '22 at 13:17
  • Checkout https://stackoverflow.com/questions/46856267/implement-rank-without-using-analytic-function – vsr May 31 '22 at 16:49
  • 1
    Thanks @vsr that almost perfect ... I posted a follow-up question: https://stackoverflow.com/questions/72461950/couchbase-n1ql-select-from-list-of-values-provided-by-parameter – DiegoFrings Jun 01 '22 at 12:32