#sql #postgresql #ahoy
#sql #postgresql #эй
Вопрос:
Я работаю над созданием представления базы данных, которое связывает онлайн-заказ с предыдущими посещениями веб-сайта пользователями. Это для веб-сайта электронной коммерции, поэтому один пользователь может посетить и заказать несколько раз.
Я уже присоединился к таблице посещений и таблице заказов в user_id и связал ближайшее время, меньшее, чем время сеанса, со временем заказа. Теперь я надеюсь сказать, что каждое посещение до тех пор, пока заказ № 1 не будет равен «1», а затем после последующего посещения до тех пор, пока заказ № 2 не будет равен «2». Кроме того, если для этого конкретного пользователя нет order_id, я бы хотел вернуть «0». Смотрите скриншоты, связанные ниже для справки.
Я уже пытался использовать dense_rank, но он ранжирует только те строки, в которых присутствует order_id . Я хочу перенести эти ранги.
SELECT v.id AS visit_id,
v.user_id,
v.started_at AS visit_date,
dense_rank() OVER (PARTITION BY v.user_id ORDER BY v.started_at) AS visit_number,
dense_rank() OVER (PARTITION BY v.user_id ORDER BY o.id) AS order_number,
o.id AS order_id,
o.created_at AS order_date
FROM visits v
FULL JOIN orders o ON v.user_id = o.user_id AND v.started_at < o.created_at AND o.created_at < (( SELECT min(visits.started_at) AS min
FROM visits
WHERE visits.user_id = v.user_id AND visits.started_at > v.started_at)) AND (v.started_at '24:00:00'::interval) > o.created_at
GROUP BY v.id, v.user_id, v.started_at, o.id, o.created_at
ORDER BY v.started_at;
Комментарии:
1. Во-первых, кажется, что у вас есть посещение для каждого заказа, поэтому
left join
более уместно, чемfull join
. Во-вторых, каковы цифры посещений после последнего заказа?2. Существует несколько заказов, которые не связаны с посещением веб-сайта, поэтому мы не хотели их исключать (например, ввод вручную и тому подобное). Все посещения, которые произошли после последнего заказа, должны отображаться как «null», пока не произойдет следующий заказ.
Ответ №1:
Это GROUP BY
кажется ненужным, но я оставлю это. В основном вам нужна совокупная сумма.
Я бы присвоил всем посещениям перед конкретным заказом номер заказа:
SELECT v.id AS visit_id, v.user_id,
v.started_at AS visit_date,
dense_rank() OVER (PARTITION BY v.user_id ORDER BY v.started_at) AS visit_number,
dense_rank() OVER (PARTITION BY v.user_id ORDER BY o.id) AS order_number,
o.id AS order_id,
o.created_at AS order_date,
count(o.id) over (partition by v.user_id order by v.started_at) as order_number
FROM visits v FULL JOIN
orders o
ON v.user_id = o.user_id AND
v.started_at < o.created_at AND
o.created_at < (SELECT min(visits.started_at)
FROM visits v2
WHERE v2.user_id = v.user_id AND
v2.started_at > v.started_at) AND
(v.started_at '24:00:00'::interval) > o.created_at
GROUP BY v.id, v.user_id, v.started_at, o.id, o.created_at
ORDER BY v.started_at;
Я думаю, что это та логика, которую вы хотите:
SELECT v.id AS visit_id, v.user_id,
v.started_at AS visit_date,
dense_rank() OVER (PARTITION BY v.user_id ORDER BY v.started_at) AS visit_number,
dense_rank() OVER (PARTITION BY v.user_id ORDER BY o.id) AS order_number,
o.id AS order_id,
o.created_at AS order_date,
MIN(o.order_number) OVER (PARTITION BY v.user_id ORDER BY v.started_at DESC) as order_number
FROM visits v FULL JOIN
(SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.id) as order_number
FROM orders o
) o
ON v.user_id = o.user_id AND
v.started_at < o.created_at AND
o.created_at < (SELECT min(visits.started_at)
FROM visits v2
WHERE v2.user_id = v.user_id AND
v2.started_at > v.started_at) AND
(v.started_at '24:00:00'::interval) > o.created_at
GROUP BY v.id, v.user_id, v.started_at, o.id, o.created_at
ORDER BY v.started_at;
Однако он может создавать NULL
s там, где вы хотите 0
s.
Комментарии:
1. Спасибо за этот ответ, Гордон. У меня есть предложение group by, потому что есть несколько агрегатов, которые я опустил в этом примере кода (для краткости). После тестирования вашего второго фрагмента кода единственная проблема, с которой я столкнулся, заключается в том, что строка, для которой есть номер заказа, не принимает правильное значение. Например, все посещения, ведущие к первому заказу, показывают «0», как и ожидалось, но затем строка с деталями заказа также показывает «0». Кроме того, все посещения после последнего заказа показывают номер заказа, если он еще не был размещен.
Ответ №2:
Используется lag
для проверки, не является ли предыдущая строка ненулевой, чтобы ее можно было пометить для начала новой группы. Как только флаг установлен, вы можете использовать текущую сумму для определения групп.
SELECT T.*,
1 SUM(FLAG) OVER(PARTITION BY user_id ORDER BY visit_date) AS order_number
FROM (
SELECT v.id AS visit_id,
v.user_id,
v.started_at AS visit_date,
dense_rank() OVER (PARTITION BY v.user_id ORDER BY v.started_at) AS visit_number,
o.id AS order_id,
o.created_at AS order_date,
--conditioncheck with lag
case when lag(o.id) over(partition by v.user_id order by v.started_at) is not null then 1 else 0 end as flag
FROM visits v
FULL JOIN orders o ON v.user_id = o.user_id AND v.started_at < o.created_at AND o.created_at < (( SELECT min(visits.started_at) AS min
FROM visits
WHERE visits.user_id = v.user_id AND visits.started_at > v.started_at)) AND (v.started_at '24:00:00'::interval) > o.created_at
) T
Комментарии:
1. Спасибо Vamsi. Это почти решает мою проблему. Одна вещь, которую я изменил в вашем коде, — это разделение на v.user_id в вашей проверке условий. К сожалению, это продолжает добавлять 1 к номеру заказа, даже если другого заказа еще не было. Например, если я посещаю веб-сайт после моего последнего заказа, но я не сделал еще один заказ, я бы в идеале хотел, чтобы это было указано как «null», пока они не разместят свой следующий заказ. Я предполагаю, что мне понадобится другое условие, но я не уверен, как это сделать.