Discussion :

[TarekBakri]

Bonjour,

Je ne suis pas trop doué avec tout ce qui est SQL et je dois effectuer quelques requêtes, mais je n'arrive pas à trouver une piste.

Mon problème ce résume comme suit :

J'ai deux tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE pageviews AS
(
visitorId INT
, sessionId INT
, pageInSession INT
, pageviewDate DATE
, pageviewDts TIMESTAMP
, pageType VARCHAR(50)
);

Chaque ligne de ce tableau fait référence à une vue de page d'un utilisateur.
La combinaison de visitorld, sessionId et pageInSession peut être considérée comme les principales clés de la table pageviews.
Le tableau contient des données de vue de page pour les 12 derniers mois (= ~ 15 milliards de lignes).
PageType contient les valeurs suivantes : Game, Navigation, Profil.

Deuxième table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE sessions AS
(
visitorId INT
, sessionId INT
, sessionDate DATE
, sessionDts TIMESTAMP
, deviceType VARCHAR(15)
);

Chaque ligne de cette table fait référence à une session de navigation d'un utilisateur.
La combinaison de visitorld et sessionId peut être considérée comme clé principale de la table sessions.
La table contient de données de session pour les 12 derniers mois ( =~ 5 milliards de lignes) .
deviceType contient les valeurs : Desktop, Mobile, Tablet.

Je veux savoir quelle requête SQL ( ou ensemble de requêtes) est nécessaire pour calculer le nombre des premiers utilisateurs (ceux qui utilisent l'application pour la première fois) par jour, par type de périphérique au cours des 14 derniers jours ?

Je veux aussi savoir dans un deuxième lieu quelle requête SQL ( ou ensemble de requêtes) est nécessaire pour calculer le taux de rétention moyen de 3 jours sur les pages de jeux pour les utilisateurs de Desktop au cours des 14 derniers jours ?

J'ai besoin d'aide please Help

[escartefigue]

Bonjour,


Voici une requête permettant de compter les personnes qui se sont connectées pour la première fois dans les 14 derniers jours :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select count(distinct idv)
from (select T1.visitorid  as idv
      from  sessions T1
      left  outer join session T2
        on  T2.visitorid = T1.visitorid
        and T2.sessionDate < dateadd(dd, -14, getdate())
      where T1.sessionDate > dateadd(dd, -15, getdate())
        and T2.visitorid is null)

Les fonctions de manipulation de date sont très différentes d'un SGBD à l'autre, il faudra donc adapter en fonction du votre

Par contre cette question :

Je veux aussi savoir dans une deuxième lieu quelle requête SQL ( ou ensemble de requêtes) est nécessaire pour calculer le taux de rétention moyen de 3 jours sur les pages de jeux pour les utilisateurs de Desktop au cours des 14 derniers jours?

No comprendo amigo

[TarekBakri]

Re-Bonjour ;

Je vous remercie pour votre réponse, votre requête marche sur Mysql ou pas?

En ce qui concerne la deuxième partie, désolé j'ai oublié de préciser que la définition exacte du taux de rétention de 3 jours est: "Le pourcentage d'utilisateurs actifs quotidiens qui sont encore actifs 3 jours plus tard"

[escartefigue]

Re-Bonjour ;

Je vous remercie pour votre réponse, votre requête marche sur Mysql ou pas?

Non pas en l'état, pour MySQL il faut remplacer la fonction DATEADD par DATE_ADD dont la syntaxe est différente
cf. https://dev.mysql.com/doc/refman/5.5...ction_date-add

En ce qui concerne la deuxième partie, désolé j'ai oublié de préciser que la définition exacte du taux de rétention de 3 jours est: "Le pourcentage d'utilisateurs actifs quotidiens qui sont encore actifs 3 jours plus tard"

Si il suffit de considérer qu'une personne connectée aujourd'hui s'était déjà connectée au moins une fois il y a trois jours ou plus, alors vous pouvez faire comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select sum(case when exists
                    (select 1
                     from session as S1
                     where S1.visitorId   = T1.visitorid
                       and S1.sessionDate < DATE_SUB(T1.sessionDate interval 2 day))
                then 1) end as actifs
     , count(*) as total
from sessions as T1

[SQLpro]

Si vous attendez 15 milliards de ligne par 12 mois dans votre table pageviews vous allez au devant de sacrés problèmes...
1) le type de données INT est limité à 2 milliards...
2) si vous utilisez MySQL bon courage !

A +

[tbc92]

la définition exacte du taux de rétention de 3 jours est: "Le pourcentage d'utilisateurs actifs quotidiens qui sont encore actifs 3 jours plus tard"

Ce n'est toujours pas très clair.
On a donc un univers : Ensemble des individus qui se sont connectés entre le jour A et le jour B.
Et parmi cet univers, on veut connaître ceux qui ont eu au moins 2 connexions avec maximum 3 jours d'intervalle, la 1ère connexion ayant lieu entre le jour A et B , ainsi que la 2ème. Ou un truc comme ça.

Si on considère une semaine ( Jour A = lundi, Jour B = dimanche)
Si X se connecte lundi matin , mardi matin, puis samedi matin , il est considéré comme "encore actif", ou pas ?
Idem si X se connecte lundi matin , vendredi matin et samedi matin ?

Et il y a surement plein d'autres cas à problème.

Essaie de bien recenser les cas, bien reformuler la règle. La traduction en langage informatique sera alors possible.

[SQLpro]

Voici en SQL une réponse à votre demande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH 
T0 AS
(
SELECT individuID, ROW_NUMBER() OVER(PARTITION BY individuID ORDER BY LaDate) AS N
FROM   MaTable
WHERE  LaDate BETWEEN A and B
)
SELECT 
FROM   T0 AS T1
       JOIN T0 AS T2
            ON T1.individuID,  = T2.individuID
            AND T1.LaDate < T2.LaDate
WHERE  DATEDIFF(day, T1.LaDate, T2.LaDate) <= 3

Problème MySQL ne gère pas la CTE avant la version 8 et toujours pas les fonctions de fenêtrage.

A +