Блог

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

Я уже присоединился к таблице посещений и таблице заказов в 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;
  

Это 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.

Используется 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