Início / coding / Perguntas / 78429749
Accepted
Jelly
Asked: 2024-05-05 00:53:00 +0800 CST

Função de janela PostgreSQL para soma por intervalo

  • 772

A tabela é representada pelos seguintes scripts:

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product_id INTEGER,
  sales_date DATE,
  quantity INTEGER,
  price NUMERIC
);

INSERT INTO sales (product_id, sales_date, quantity, price) VALUES
   (1, '2023-01-01', 10, 10.00),
   (1, '2023-01-02', 12, 12.00),
   (1, '2023-01-03', 15, 15.00),
   (2, '2023-01-01', 8, 8.00),
   (2, '2023-01-02', 10, 10.00),
   (2, '2023-01-03', 12, 12.00);

A tarefa é somar a quantidade de vendas dos últimos 3 dias para cada product_id. O período deve ser contado regressivamente a partir da data máxima (última) de cada product_id. Portanto, para 1, o máximo é 2023-01-03, o mesmo para 2. Mas para product_id 2 o último dia pode ser diferente de 1 - digamos 2023-01-05.

Aplicando esta consulta com função de janela na subconsulta:

select product_id, max(increasing_sum) as quantity_last_3_days
   from 
        (SELECT product_id,
         SUM(quantity) OVER (PARTITION BY product_id ORDER BY sales_date RANGE BETWEEN INTERVAL '2 days'
                PRECEDING AND CURRENT ROW) AS increasing_sum
         FROM sales) as s
   group by product_id;

Recebo a saída esperada:

  | product_id | quantity_last_3_days |
  |____________|______________________|            
  |_____1______|___________37_________|
  |_____2______|___________30_________|

Mas será que a solução é ideal? Existe alguma maneira de resolver o problema usando a função window sem subconsulta?

sql

2 respostas

  1. Se você quiser evitar funções de janela (o que pode acontecer devido à sua carga cognitiva), você também pode resolver o problema usando uma WITHcláusula (também conhecida como CTE). Como a consulta não precisa de nada além de um único limite para cada ID de produto, você poderia expressar a filtragem em uma condição de junção com um CTE da seguinte maneira:

    WITH latest_sales AS (
  SELECT product_id, max(sales_date) AS time 
  FROM sales 
  GROUP BY product_id
)
SELECT sales.product_id, sum(sales.quantity) as quantity_last_3_days
FROM sales INNER JOIN latest_sales ON 
    sales.product_id = latest_sales.product_id 
    AND sales.sales_date >= latest_sales.time - interval '2 days'
GROUP BY sales.product_id;
    • 1
  2. Best Answer

    Mas será que a solução é ideal?

    Não, não se você precisar apenas "soma das vendas quantitydos últimos 3 dias para cada um product_id" .

    Existe alguma maneira de resolver o problema usando a função de janela sem subconsulta?

    Geralmente você pode trocar funções de janela por subconsultas correlacionadas lateralou escalares, mas há uma maneira mais fácil de acelerar sua consulta e corrigi-la. Ele está fazendo um trabalho extra tentando conseguir uma soma contínua; a janela não está tentando obter os três dias mais recentes de cada um product_id.

    Em vez disso, para cada linha, ele analisa as linhas iguais product_idaté 2 dias antes. Posteriormente, você escolherá o período de três dias com a soma mais alta de quantity, que não será necessariamente as três datas mais recentes.

    Em 400 mil amostras, sua consulta pega um todo 1.0ssem índice, 0.7scom índice de cobertura , e você pode descer para 0.4ssem ou 0.1scom índice de cobertura. Você só precisa perguntar sobre a soma das 3 datas mais recentes de cada product_id: demo em db<>fiddle

    select product_id,sum(quantity) as quantity_last_3_days
from(select *,row_number()over(PARTITION BY product_id ORDER BY sales_date DESC)
     from sales)_
where 3>=row_number
group by product_id;

    O truque aqui é que a função window será executada com a Run Condition: (3 >= row_number() OVER (?)), o que significa que ela apenas pegará as 3 mais recentes e sairá. Pode até tirá-los diretamente do topo do índice de cobertura, sem nunca ter que visitar a mesa.
    Sua consulta original precisa verificar tudo (a tabela inteira ou o índice inteiro, se estiver disponível) e, em seguida, classificá-la para obter o arquivo max().

    • 1

relate perguntas