Блог

У меня есть электронная коммерция, и я хочу измерить удержание после 60 дней первой продажи. Сохраненный пользователь совершил по крайней мере одну покупку в течение следующих 60 дней.

У меня есть таблица продаж со следующей информацией:

 Sale ID | Customer ID | Date
1       |  405        | 2021-03-05
2       |  408        | 2021-03-06
3       |  231        | 2021-03-07
 

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

Я пытаюсь использовать этот запрос:

 SELECT
         "Customer ID",
         COUNT("Sale ID") OVER(
            PARTITION BY "Customer ID"
            ORDER BY "Date" ASC
            RANGE BETWEEN CURRENT ROW AND INTERVAL '60 days' FOLLOWING
            ) AS "60 days Sales"
            
         FROM "Sales"
      GROUP BY "Customer ID"
 

Попробовав это, я получаю эту ошибку:
ERROR: Column "Date" must appear in the GROUP BY clause or be used in aggregate

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

Что я делаю не так? Я не уверен, почему «Дата» должна быть сгруппирована: я ожидаю, что результат будет сгруппирован по идентификатору клиента.

Спасибо!

Проблема в том, что оконные функции оцениваются позже GROUP BY , поэтому PostgreSQL не знает, какую из "Date" одной группы вы имеете в виду.

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

 SELECT "Customer ID",
       COUNT("Sale ID")
          FILTER (WHERE "Date" BETWEEN current_timestamp
                               AND current_timestamp   INTERVAL '60 days')
          AS "60 days Sales"
FROM "Sales"
GROUP BY "Customer ID";
 

 select "Customer ID",
    case when max("60 days Sales") > 1 then 'Retained' else 'Churned' end as Status
from (
    SELECT
        "Customer ID",
        COUNT("Sale ID") OVER(
            PARTITION BY "Customer ID"
            ORDER BY "Date" ASC
            RANGE BETWEEN CURRENT ROW AND INTERVAL '60 days' FOLLOWING
        ) AS "60 days Sales"            
    FROM "Sales"
) t
group by "Customer ID"
 

Сначала посмотрите на каждую продажу. Затем решите, есть ли у этого клиента продажа, соответствующая вашим 60-дневным условиям. Если ваша логика требует сосредоточиться на первоначальной продаже, вам придется сделать что-то другое, например:

 select "Customer ID",
    case when count(case when "Date" > firstDate then 1 end) > 0 then 'Retained' else 'Churned' end as Status
from (
    SELECT
        "Customer ID", "Date",
        min("Date") over (partition by "Customer ID") AS firstSale            
    FROM "Sales"
) t
where "Date" between firstSale and firstDate   interval '60 days'
group by "Customer ID"
 

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