Comment associer une commande en ligne à plusieurs visites précédentes sur le site Web à l'aide d'une fonction de rang dans PostgreSQL ?

Question

Je travaille à la création d'une vue de base de données qui associe une commande en ligne aux précédentes visites d'un utilisateur sur le site Web. Il s'agit d'un site de commerce électronique où un utilisateur peut visiter et commander plusieurs fois.

J'ai déjà joint la table des visites et la table des commandes sur le nom de l'utilisateur et j'ai associé l'heure inférieure à la session la plus proche à l'heure de la commande. Maintenant, j'espère pouvoir dire que chaque visite jusqu'à la commande n°1 est égale à "1", et que chaque visite suivante jusqu'à la commande n°2 est égale à "2". En outre, s'il n'y a pas d'ID de commande pour cet utilisateur spécifique, j'aimerais renvoyer "0". Voir les captures d'écran liées ci-dessous pour référence.

J'ai déjà essayé d'utiliser dense_rank, mais il ne classe que les lignes dans lesquelles un order_id est présent. Je veux reporter ces classements.

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;

Answer 1

Utilice lag pour vérifier si la ligne précédente n'est pas nulle, afin de la signaler pour un nouveau départ de groupe. Une fois l'indicateur activé, vous pouvez utiliser une somme courante pour définir les groupes.

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

Answer 2

El GROUP BY semble inutile, mais je vais le laisser. Vous avez essentiellement besoin d'une somme cumulée.

J'attribuerais à toutes les visites avant une commande particulière le numéro de commande :

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;

Je pense que c'est la logique que vous voulez :

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;

Il pourrait produire NULL où vous voulez 0 cependant.