Início / dba / Perguntas / 108999
Accepted
flyer88
Asked: 2015-08-05 10:25:08 +0800 CST

Aplicar limite e condições por dados agrupados

  • 772

Estou construindo um endpoint de API para enviar dados estatísticos que serão plotados em um gráfico. A parte mais difícil é ser a consulta que preciso executar. Resumindo, quero os primeiros nprodutos mais vendidos por dia em um período. O período, por exemplo, pode ser qualquer intervalo de datas.

Suponha que eu tenha uma salestabela com os seguintes dados (este não é o esquema real que defini):

---------------------------------
date       | product_name | count
---------------------------------
2015-01-08 | A            | 10
2015-01-08 | B            | 5
2015-01-08 | C            | 1
2015-02-08 | A            | 5
2015-02-08 | B            | 3
2015-02-08 | C            | 100

E suponha que eu queira os 2 produtos mais vendidos por dia. Eu quero uma consulta que retorne o seguinte:

---------------------------------
date       | product_name | count
---------------------------------
2015-01-08 | A            | 10
2015-01-08 | B            | 5
2015-02-08 | C            | 100
2015-02-08 | A            | 5

Note que além de Cser o produto mais vendido em todo o período, não aparece no top 2 do2015-01-08

Existe uma maneira de conseguir o que eu quero?

Obrigado :)

postgresql group-by

2 respostas

  1. Best Answer

    Uma maneira de resolver esses problemas é usar funções de janela:

    WITH cte AS
    ( SELECT date, product_name, count,
             ROW_NUMBER() OVER (PARTITION BY date
                                ORDER BY count DESC) AS rn
      FROM sales
      WHERE date >= '2015-07-01'     -- example range
        AND date < '2015-08-01'      -- 2015, July
    )
SELECT date, product_name, count
FROM cte
WHERE rn <= 2           -- max of 2 products per date
ORDER BY                     -- this does not affect the result 
    date, count DESC ;       -- only its order

    Se houver empates, eles serão resolvidos arbitrariamente e apenas 2 linhas por data serão retornadas. Se você deseja resolver empates com uma preferência específica, pode alterar a ordem dentro da OVERcláusula. Digamos que você queira que os nomes dos produtos sejam classificados em ordem alfabética quando estiverem empatados nas principais vendas, use (... ORDER BY count DESC, product_name).

    Se você quiser todos os produtos (empatados) por data, basta alterar ROW_NUMBER()para RANK().

    • 2

  2. Outra forma de resolver é usando JOIN LATERAL. Isso provavelmente é mais rápido se você tiver índices ativados (date, count DESC)e (date).

    SELECT s.date, t.product_name, t.count
FROM (SELECT DISTINCT date FROM sales) s
  CROSS JOIN LATERAL (
    SELECT s2.product_name, s2.count 
    FROM sales s2
    WHERE s2.date = s.date
    ORDER BY s2.count DESC 
    LIMIT 2) t
ORDER BY s.date
    • 2

relate perguntas