Оконная функция PostgreSQL для суммы за интервал

Если вы хотите избежать оконных функций (что возможно из-за их когнитивной нагрузки), вы также можете решить проблему, используя предложение WITH (также известное как CTE). Поскольку запросу на самом деле не требуется ничего, кроме одного порога для каждого идентификатора продукта, вы можете выразить фильтрацию в условии соединения с помощью CTE следующим образом:

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;

Но является ли это решение оптимальным?

Нет, если вам просто нужно «суммировать продажи quantity за последние 3 дня по каждому product_id».

Есть ли способ решить проблему, используя оконную функцию без подзапроса?

Обычно вы можете обменять оконные функции на коррелированные подзапросы, lateral или скаляры, но есть более простой способ ускорить ваш запрос и исправить его. Он выполняет дополнительную работу, пытаясь получить скользящую/ступенчатую сумму; окно не пытается получить 3 последних дня для каждого product_id.

Вместо этого для каждой строки просматриваются строки с тем же product_id за 2 дня до этого. Позже вы выберете трехдневный период с наибольшей суммой quantity, который не обязательно будет тремя самыми последними датами.

На 400 тысячах выборок ваш запрос принимает целое 1.0s без индекса, 0.7s с индексом покрытия , и вы можете перейти от этого к 0.4s без индекса покрытия или 0.1s с индексом покрытия. Вам просто нужно спросить только сумму за 3 последних даты для каждой product_id: демо на 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;

Хитрость здесь в том, что оконная функция будет выполняться с Run Condition: (3 >= row_number() OVER (?)), что означает, что она просто возьмет 3 самых последних и завершит работу. Он даже может вывести их прямо из верхней части индекса покрытия, даже не посещая таблицу.
Ваш исходный запрос должен сканировать все целиком (либо всю таблицу, либо весь индекс, если он доступен), а затем отсортировать его, чтобы получить max().

Как вы отметили, при этом учитываются любые последние 3 даты для каждого product_id, которые не обязательно должны быть последовательными, а самая последняя не обязательно должна быть сегодня или вчера.
Не последние 3 дня вообще, если считать от текущей даты.
Не последние 3 даты подряд для каждого product_id.

Если вам это нужно, последние три из сегодняшних реализуются тривиально:

select product_id,sum(quantity) as quantity_last_3_days
from sales
where sales_date>='2023-01-03'::date--this would be current_date or now()
                  -'2d'::interval
group by product_id;

и 3 последних последовательных product_id одинаковы

with cte as (
  select distinct on (product_id) *
  from sales s1
  order by product_id,sales_date desc)
select *,(select sum(quantity)
          from sales s2
          where s1.product_id=s2.product_id
          and s1.sales_date-'2 days'::interval >= s2.sales_date) 
          as quantity_last_3_days
from cte s1;

Мой ответ. В частности, он изучает Зегарека и ответы grundb:

select product_id, quantity_last_3_days
  FROM
     (SELECT product_id,
        CASE 
          WHEN
             ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sales_date DESC) < 2
          THEN
             SUM(quantity) OVER (PARTITION BY product_id ORDER BY sales_date RANGE BETWEEN INTERVAL '2 days'
            PRECEDING AND CURRENT ROW) 
          ELSE
             NULL
        END quantity_last_3_days
       FROM sales) as s
 WHERE  quantity_last_3_days IS NOT NULL;

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