Блог

У меня следующий запрос:

 WITH relationships AS (
    SELECT related_user_id, count(*) AS trade_count
    FROM trade_history
    WHERE user_id = 487834568 
    GROUP BY related_user_id
    ORDER BY trade_count DESC
)
SELECT offers.*,
       relationships.trade_count
FROM offers
         LEFT JOIN user_stock
                   ON user_stock.user_id = 487834568 and offers.product_id = user_stock.product_id
         LEFT JOIN relationships
                   ON offers.user_id = relationships.related_user_id
WHERE offers.state = 'OPEN'
  AND offers.user_id != 487834568        
  AND offers.group BETWEEN 1 AND 3 
ORDER BY offers.created_at,
         relationships.trade_count DESC,
         user_stock.amount NULLS FIRST;
  

Запрос показывает мне все предложения и упорядочивает их по:

• Сначала показывать старые предложения
• Сначала показывать предложения с более высоким количеством сделок (два пользователя торгуют друг с другом)
• Запас пользователей

Что мне нужно дополнительно, так это ограничить результаты максимум на 3 product_id . Я немного погуглил и выяснил, что это должно быть возможно с помощью оконной функции, использующей row_number() и боковые соединения. Я не хочу использовать row_number() , поскольку таблицы содержат много записей, и, скорее всего, у меня возникнут проблемы с производительностью. Я думаю, что боковые соединения — правильный инструмент (читайте здесь http://charlesnagy.info/it/postgresql/group-by-limit-per-group-in-postgresql ) но я не смог получить достойный результат с моими ограниченными знаниями SQL.

Как мне получить группировку эффективным способом?

Редактировать: чтобы сделать это более понятным, я реализовал решение с помощью оконной функции:

 WITH relationships AS (
    SELECT related_user_id, count(*) AS trade_count
    FROM trade_history
    WHERE user_id = 487834568 
    GROUP BY related_user_id
    ORDER BY trade_count DESC
)
SELECT * FROM (
    SELECT 
           offers.*,
           relationships.trade_count,
           row_number() OVER (
               PARTITION BY resource_id
               ORDER BY 
                   offers.created_at,
                   relationships.trade_count DESC,
                   user_stock.amount NULLS FIRST
        ) AS row_number
    FROM offers
             LEFT JOIN user_stock
                       ON user_stock.user_id = 487834568 and offers.product_id = user_stock.product_id
             LEFT JOIN relationships
                       ON offers.user_id = relationships.related_user_id
    WHERE offers.state = 'OPEN'
      AND offers.user_id != 487834568        
      AND offers.group BETWEEN 1 AND 3 
                  ORDER BY row_number
              ) AS ordered_offers
WHERE ordered_offers.row_number <= 3;
  

Если вы можете добавить туда некоторую группировку, добавьте HAVING count(offers.product_id) <= 3 между WHERE и ORDER BY

Собрав все это вместе, это кажется правильным. Я собираюсь попробовать аналогичную группировку на основе другой локальной базы данных и посмотреть, работает ли она так, как я ожидаю, обновится, если нет.

 WITH relationships AS (
  SELECT related_user_id, count(*) AS trade_count
  FROM trade_history
  WHERE user_id = 487834568 
  GROUP BY related_user_id
  ORDER BY trade_count DESC
)
SELECT
  offers.*,
  relationships.trade_count
FROM offers
LEFT JOIN user_stock ON
  user_stock.user_id = 487834568 AND
  offers.product_id = user_stock.product_id
LEFT JOIN relationships ON
  offers.user_id = relationships.related_user_id
LEFT JOIN (
  SELECT array_agg(offers.id) AS ids
  FROM offers
  JOIN user_stock ON
    user_stock.user_id = 487834568 AND
    offers.user_id = user_stock.user_id AND
    offers.product_id = user_stock.product_id
  GROUP BY offers.user_id
  HAVING count(offers.id) <= 3
) AS offers_limit ON
  offers.id = any(offers_limit.ids)
WHERE
  offers.state = 'OPEN' AND
  offers.user_id != 487834568 AND
  offers.group BETWEEN 1 AND 3
ORDER BY
  offers.created_at,
  relationships.trade_count DESC,
  user_stock.amount NULLS FIRST