#sql #postgresql
Вопрос:
У меня есть электронная коммерция, и я хочу измерить удержание после 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 дней для любого конкретного клиента. Таким образом, я могу использовать это позже и отфильтровать клиентов как удержанных или отторгнутых.
Что я делаю не так? Я не уверен, почему «Дата» должна быть сгруппирована: я ожидаю, что результат будет сгруппирован по идентификатору клиента.
Спасибо!
Комментарии:
1. Пожалуйста, добавьте примеры данных к вопросу.
2. После того, как вы выполнили группировку, вы можете ссылаться только на агрегаты
date
. Это, вероятно, сбивает с толку, но это часть разницы между аналитическими функциями и агрегатными функциями.3. Не имеет отношения к вашей проблеме, но: вам действительно следует избегать этих ужасных идентификаторов в кавычках. От них гораздо больше хлопот, чем они того стоят. wiki.postgresql.org/wiki/…
Ответ №1:
Проблема в том, что оконные функции оцениваются позже 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";
Ответ №2:
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"
Неясно, возможны ли две продажи в начальную дату или как бы вы к этому отнеслись.
Комментарии:
1. привет, спасибо за ваш ответ. на самом деле моя логика заключается в подсчете количества продаж, совершенных только после первой продажи. Я не смог найти способ сделать это, я пробовал вложенные запросы, но пока ничего. У вас есть какие-либо идеи о том, как этого можно достичь? Спасибо!
2. еще раз спасибо! Я попробовал второй, но он говорит мне, что «Идентификатор продажи» должен появиться в предложении GROUP BY (что странно, потому что он уже агрегирован как МИНИМУМ). Я думаю, это потому, что, как кто-то сказал ниже, ГРУППА действует до ОКОНЧАНИЯ. просто интересно, предложение WHERE будет действовать в базе данных в целом или строка за строкой? Я думал, что оконные функции были созданы именно для этих случаев, но, по-видимому, это не так. Есть какие-нибудь обходные пути? До сих пор я не мог в этом разобраться. Спасибо!
3. Это должна была быть дата, а не идентификатор продажи. Посмотрим, повезет ли тебе с этим больше. Я согласен, что ошибка не имеет смысла. Этот внутренний запрос должен выполняться сам по себе, так что попробуйте и это тоже.