CODEWITHSUNDEEP
mysql
noMAD
noMAD

Reputation: 7844

Complex conditional SQL query

Say I have the following table:

| Visitor | url | time |

I am trying to write the following query:

for every visitor:
  check if visitor has visited /my_url
  if yes:
    check if visitor visited /my_url/cool url within next 5 min

Then I would have something like:

| visitor | url          |
| xyz     | /my_url      |
|         | /my_url/cool |
| abc     | /my_url      |
|         | /my_url/cool |

Am not sure where exactly to even start. Any help would be appreciated.

All I got till now is:

select url, time, visitor 
from my_table
where url like '%my_url%'
group by visitor;

Upvotes: 0

Views: 42

Answers (2)

symcbean
symcbean

Reputation: 48357

Not a very complex query.

For every visitor

Does that mean you want the visitors whom have not visited the URL to be included in the output? (I don't find your description nor example clear). Assuming this is the case...

Presumably you have many more records in your table than unique visitors, hence the solution would be a lot more efficient if you could do a join to a table of visitors, but in its absence, you can use:

SELECT DISTINCT visitor
FROM yourtable

Check if visitor has visited [url1]

SELECT ilv.visitor, u1.time
FROM (
   SELECT DISTINCT visitor
   FROM yourtable
) AS ilv
LEFT JOIN yourtable AS u1
ON u1.visitor=ilv.visitor
AND u1.url=[url1]

check if visitor visited [url2] within next 5 min

SELECT ilv.visitor, u1.time,
  (SELECT MIN(time)
  FROM yourtable u2
  WHERE u2.visitor=u1.visitor
  AND u2.url=[url2]
  AND u2.time between u1.time
            AND u1.time + INTERVAL 5 MINUTES
  )
FROM (
   SELECT DISTINCT visitor
   FROM yourtable
) AS ilv
LEFT JOIN yourtable AS u1
ON u1.visitor=ilv.visitor
AND u1.url=[url1];

But if you just want a list of visitors whom fulfill both criteria

SELECT visitor
FROM yourtable u1
JOIN yourtable u2
ON u1.visitor=u2.visitor
AND u2.time between u1.time
            AND u1.time + INTERVAL 5 MINUTES
WHERE u1.url=[url1]
AND u2.url=[url2]

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31792

Try something like this:

select distinct v1.Visitor
from visits v1
join visits v2
  on  v2.Visitor = v1.Visitor
  and v2.time >  v1.time
  and v2.time <= v1.time + interval 5 minute
where v1.url = '/my_url'
  and v2.url = '/my_url/cool'

Since you know the urls while constructing the query, there is no need to select them.

Upvotes: 1

Related Questions