Enrich website log data with user id in SQL

Assume that we have typical session log from the website with cookies and user id (in case he/she provided credentials):

cookie id timestamp af312b452c . 31-03-11 1:44 PM af312b452c . 31-03-11 1:46 PM af312b452c . 31-03-11 1:48 PM af312b452c . 31-03-11 1:51 PM af312b452c . 31-03-11 1:53 PM af312b452c . 31-03-11 1:56 PM af312b452c 1 31-03-11 1:58 PM af312b452c 1 31-03-11 2:01 PM

31-03-11 1:58 PM - user logged-in, so since then we are tracking his/her activity alongside with user_id.

I want to enrich such data by filling missing fields backwards to have complete information what particular user has seen on the webpage:

cookie id timestamp af312b452c 1 31-03-11 1:44 PM af312b452c 1 31-03-11 1:46 PM af312b452c 1 31-03-11 1:48 PM af312b452c 1 31-03-11 1:51 PM af312b452c 1 31-03-11 1:53 PM af312b452c 1 31-03-11 1:56 PM af312b452c 1 31-03-11 1:58 PM af312b452c 1 31-03-11 1:58 PM

What will be the best way to do it in SQL?

Notes:

  • whole table has many rows,
  • users can have multiple cookies.
  • a particular cookie value may have been used for a different user at some point in the past

Thank you all in advance.

-------------Problems Reply------------

I would start by creating a function that would get me the most recent logged in ID of a cookie given that cookie and the timestamp you logged that cookie (psuedocode off the top of my head, apologies if something is off):

CREATE FUNCTION getFutureLogin(in_cookie VARCHAR, in_timestamp DATETIME) RETURNS INT AS
BEGIN
DECLARE logged_in_id IN

SELECT logged_in_id = TOP 1 id
FROM T_NAME
WHERE cookie = in_cookie
AND timestamp > in_timestamp
AND id IS NOT NULL
ORDER BY timestamp ASC

RETURN logged_in_id
END

From there, it's a simple update that you'd probably schedule a job to do sometime.

UPDATE T_NAME SET id = getFutureLogin(cookie, timestamp) WHERE id IS NULL

May not be the most straightforward or fastest way, but it would work.

Category:sql Views:0 Time:2011-05-14
Tags: sql cookies

Related post

  • Logging data without using a normal SQL database? 2011-03-02

    I am currently logging every "failure" on my site (login/signup/etc) to a database so I can monitor what is giving my users a hard time - or which ips/users are doing suspicious things. However, I find that I only really need the data for about a wee

  • How much allocated space for log/data is too much? (SQL Server 2008) 2011-05-06

    This is a somewhat unusual question... Is there such a thing as too big of an allocation for data and log files for SQL Server? Please note, that I am NOT talking about running out of space. Let's assume for the moment that there is infinite storage,

  • Python, web log data mining for frequent patterns 2010-05-27

    I need to develop a tool for web log data mining. Having many sequences of urls, requested in a particular user session (retrieved from web-application logs), I need to figure out the patterns of usage and groups (clusters) of users of the website. I

  • Whats a better strategy for storing log data in a database? 2009-12-06

    Im building an application that requires extensive logging of actions of the users, payments, etc. Am I better off with a monolithic logs table, and just log EVERYTHING into that.... or is it better to have separate log tables for each type of action

  • write /read log data into file in android 2010-03-13

    how to read/write log data into a text file in android that file should be res folder . --------------Solutions------------- You could take a look at microlog4android. They have a solution ready to log to a file. http://code.google.com/p/microlog4and

  • Publicly available Web proxy forward cache logs/data sets 2010-06-28

    I'm looking to do some analysis on HTTP requests that occur between clients and web servers. Are there any recent (at least within last 4 years) publicly available data sets of web proxy forward cache logs, such as those recorded by a Squid proxy? I'

  • Clearing Magento Log Data 2010-09-03

    I have a question regarding clearing of the log data in Magento. I have more than 2.3GB of data in Magento 1.4.1, and now I want to optimize the database, because it's too slow due to the size of the data. I checked the log info (URL,Visitors) and it

  • How do you turn off the Action Log Data Collector in Microsoft Test Manager 2010? 2010-09-24

    Microsoft Test Manager 2010 does not support Silverlight applications, so when I navigate from a test to my Silverlight 4 app running in IE, I get a popup notifying me that there is a compatibility issue. This is really annoying. Doing some Googling,

  • Displaying log data in latest-first format 2010-10-19

    I like having log data in a last-first form (the same way most blogs and news sites organize their posts). The languages I'm most comfortable in are C++ and Python: is there a way to output log data either to the screen (stdout) or a file with the mo

  • Access Crash logs data on the iPhone 2010-11-12

    I'm looking for the way to get crash logs data on the iPhone OS. I found getting the logs this way: 1) Sync your iPhone 2) Browse to the following folders. Note that DEVICE_NAME will be the name if your iPhone as shown in iTunes. Mac OS X : /Library/

  • Most efficient method of logging data to MySQL 2011-02-02

    We have a service which sees several hundred simultaneous connections throughout the day, peeking at about 2000, for about 3 million hits a day, and growing. With each request I need to log 4 or 5 pieces of data to MySQL, we originally used the loggi

  • SQL, Is it bad to concat() log data in longtext field? 2011-03-30

    I'm building my own authentication system. Right now I have my database setup to log each login timestamp by: $query = 'UPDATE `users` SET login_log = concat(login_log, ?) WHERE userKey = ? LIMIT 1 '; $vars = array(time().',', $this->userKey); $QH

  • Is HBase right for storing and querying log data? 2011-04-19

    I'm thinking of using HBase to store logs (web log data), each log would have about 20 different values (let's say columns), I want to run queries that filter results based on those columns. My initial idea was to save each log (cell) multiple times

  • Selection appropriate STL container for logging Data 2011-04-20

    I require logging and filtering mechanism in my client server application.where client may request log data based on certain parameter. log will have MACID,date and time,command type and direction as field. server can filter log data based on these p

  • How does Facebook store it's log data? 2011-04-29

    I recently started using Scribe, Facebooks solution for transferring and collecting log data from many different servers. What I could not find is how Facebook stores the huge amounts of log data it gets (according to a presentation it was 25TB per d

  • DB design : Config Data, Actual Data, Log Data 2011-06-02

    I want to know if there is any typical approach to differenciate this kind of data I have to listing devices (for example) in a db, ane everyone will have Configuration data Actual data Log data I commonly mix Config/Actual Data in the same table and

  • Send a cross-domain request to log data 2011-06-24

    I'm working on an ad platform. When someone clicks on an image, I want to send a request back to my server to log this action. I have a pre-generated url for this. If I send a request to this url, it will log the data. My issue is that the log url is

  • How to log data changed in sql server tables. Which approach is better 2011-09-01

    Just wanted to know about logging data modification. I have seen to ways of tracking data change (DML). Using Triggers Keeping columns in same table for Added Date, Added By, Modified Date, Modified By. Using approach (1), I can write triggers for In

  • Android perimision "user's private information", "your personal information" , "read sensitive log data" 2011-09-08

    Well I found this texts on many applications, that is when I install some app the require the android.permission.READ_LOGS permission, they show some text like "permission for reading read sensitive log data..." the thing I do not understood is what

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.067 (s). 11 q(s)