J'ai une table qui enregistre l'ID utilisateur, le cours, la sessionId et le requestDate chaque fois qu'une page Web est chargée. Je veux calculer la durée par userid pour un cours donné. Il est problématique de le faire en raison de l'emploi qui se chevauchent.
Les données fournies ici devraient entraîner une durée de 10 minutes par utilisateur pour le cours 1. Je ne peux pas sembler avoir ce droit. P>
-- userid courseid sessioncount duration -- 1 1 1 10 -- 2 1 1 3 -- 3 1 1 6 -- 4 1 1 4 -- 5 1 2 10 -- 6 1 1 10 -- 7 1 1 9 -- 8 1 1 10
5 Réponses :
Désolé mais je pense que vous avez un problème de données. En ce qui concerne les échantillons de données fournis, l'utilisateur 2 est sûr de 1 pendant 12 minutes et du filid 2 pendant 2 minutes.
Êtes-vous sûr que vous avez fourni les données correctes?
Les données sont correctes, mais il est simplement difficile de le signifier pertinent. L'utilisateur 2 commence dans le cours 1, passe au cours 2 pendant deux minutes, puis revient au cours 1. Je veux le temps qu'il a passé dans le cours 1 (10 minutes). Ainsi, 12 minutes moins les 2 minutes à laquelle il passait dans un autre cours.
Il semble que tu avais raison. Mon interprétation initiale des données était défectueuse.
Ceci est aussi proche que possible. Il échoue à titre d'utilisateur 4.
Comme je l'ai dit dans mon commentaire, requestdate code> est parfois un début et parfois une fin d'un cours, et je ne peux pas voir une simple règle générale pour dériver quel rôle Il joue sur une rangée donnée. P> DECLARE @courseid INT;
SET @courseid = 1;
WITH orderCTE
AS
(
SELECT *
,ROW_NUMBER() OVER (PARTITION BY sessionid
ORDER BY id
) AS rn
FROM PageLogSample
--order by rn
)
,startendCTE
AS
(
SELECT CASE WHEN start1.rn = 1
THEN start1.courseid
ELSE end1.courseid
END courseid
,start1.sessionid
,start1.userid
,DATEDIFF(mi,start1.requestdate,end1.requestdate) duration
FROM orderCTE AS start1
JOIN orderCTE AS end1
ON end1.rn = start1.rn + 1
AND end1.sessionid = start1.sessionid
)
SELECT courseid
,COUNT(1) sessionCount
,userid
,SUM(duration) totalDuration
FROM startendCTE
WHERE courseid = @courseid
GROUP BY courseid
,userid;
J'aime l'idée de rechercher les lignes de début et de fin d'abord. Vous m'avez inspiré de prendre une nouvelle approche.
C'est assez désordonné, mais il semble fonctionner pour le traitement 1. Je n'ai pas essayé avec d'autres cours, vous pouvez donc vouloir tester cela! : D
La prémisse de base est que je reçois la durée du temps entre la première et la dernière session du filidaire cible, puis je soustraire la durée de toutes les sessions qui n'étaient pas du cours spécifié, mais où la Le temps de demande de session est tombé dans les délais de requête minimale et maximum du fil ciblé. J'espère que cela a du sens.
La requête pourrait certainement être nettoyée, éventuellement avec un CTE ou quelque chose. Question intéressante BTW! :)
Très bien, mais pas parfait encore. Lorsque vous recherchez le temps de soustraire, vous devez garder à l'esprit qu'il pourrait y avoir plus d'un «écart» avec le circuit. J'ai ajouté un utilisateur supplémentaire aux exemples de données pour le montrer.
Aha, merci d'avoir souligné cela. Je vais remplir l'utilisateur supplémentaire et lui donner un autre. :)
"Les données sont correctes, mais il est simplement difficile de le signifier pertinent."
Je suis pressé de répondre à cela une contradiction de termes. Données dont vous ne savez pas ce que cela signifie n'est pas des données.
Quant à votre question originale:
Ce dont vous avez besoin est un SGBD qui offre un support décent pour les types d'intervalles. Aucun système SQL ne joue dans cette ligue. Outre quelques systèmes de didacticiels, mon propre SGBD (ne repoussez plus que dans ce contexte, donc aucun lien) n'est le seul que je connaisse de cela offre le type de soutien qui est vraiment nécessaire pour de tels problèmes.
Si vous êtes intéressé, Google autour de "Types d'intervalles", "Formulaire normal emballé", "Données temporelles" et vous y traverserez éventuellement.
Quelques autres données d'échantillonnage et une hypothèse logique espérante de combien de temps chaque utilisateur a passé dans chaque cours.
userid courseid sessioncount duration 1 1 1 10 2 1 1 3 3 1 1 6 4 1 1 4 5 1 2 10 6 1 1 10 7 1 1 9 8 1 1 10 9 1 2 0
Belle question - en termes de script et d'échantillon pour faciliter la résolution.
La difficulté avec les données est que RequestDate n'a pas de sens cohérente. C'est parfois l'heure de début et parfois l'heure de fin d'un cours.
Bon commentaire à la belle question.
Pour m'aider à comprendre la logique de cela, pouvez-vous expliquer quelle serait la durée de l'ID utilisateur 3 si nous examinions le filidide 2 à la place? Merci!
Je me sens comme l'utilisateur 3 dépense 9 minutes du cours 2. (5 - 3) + (12 - 5) + (? - 15) = 9. Le point d'interrogation signifie que je ne sais pas combien de temps il était sur la dernière page parce que Il n'y avait pas de demande de page ultérieure. Peut-être que j'aurais dû l'avoir expliqué mieux. Ceci est le journal des demandes de page Web sur le serveur. Chaque ligne est une demande de page. Les cours sont des parties différentes du site. C'est ma tâche de trouver le temps qu'un utilisateur dépense par cours. Il y a une mise en œuvre précédente que je dois remplacer car il n'est pas assez rapide. Idéalement, mes résultats seraient égaux aux anciens implémentations.