Geek asks and answers

SQL - warning if a new unique record is added in the last hour

I am trying to find an elegant solution in the form of an SQL query for the following problem.

New entries will be inserted into the log table. I need to detect any new entries (inserted at the last hour) that I have not seen before, and create a warning (for example, # from these entries> 0)

ID, Url, DOB
1, site1.com/page1, "5/06/2012 20:01"
2, site2.com/page2, "5/06/2012 21:20"
3, site1.com/page1, "6/06/2012 10:05"

If "now" - 06/06/2012 10:40 - I see that 1 new record has been added (id = 3), but I do not want to generate a warning because we saw this URL before (id = 1) .

if we have 4, site3.com/pageX, "06/06/2012 10:08" then I want to create a warning (return count = 1), because this line was inserted in the last hour, and we don’t seen before.

What is the best way to implement it? ideally without nested queries

+5
source share
5 answers

I think this is what you need. This will result in new entries in the last hour (if a new one means that the same URL was not visited in the last hour)

SELECT  *
FROM    Log
WHERE   DOB > DATEADD(HOUR, -1, CURRENT_TIMESTAMP)
AND     NOT EXISTS
        (   SELECT  1
            FROM    Log T1
            WHERE   T1.URL = Log.URL 
            AND     T1.DOB < DATEADD(HOUR, -1, CURRENT_TIMESTAMP)
        )

SQL Fiddle Working Example

EDIT

Just looked at the comment that you only need to count:

SELECT  COUNT(*)
FROM    Log
WHERE   DOB > DATEADD(HOUR, -1, CURRENT_TIMESTAMP)
AND     NOT EXISTS
        (   SELECT  1
            FROM    Log T1
            WHERE   T1.URL = Log.URL 
            AND     T1.DOB < DATEADD(HOUR, -1, CURRENT_TIMESTAMP)
        )

EDIT 2

I'm not sure why there is a requirement for only one choice, however the closest I can get to one choice is:

SELECT  COUNT(*)
FROM    (   SELECT  *, MIN(DOB) OVER(PARTITION BY URL) [FirstViewed]
            FROM    Log
        ) Log
WHERE   FirstViewed >= DATEADD(HOUR, -1, CURRENT_TIMESTAMP)

This will still return 2 if the same page has been visited twice in the last hour.

http://sqlfiddle.com/#!3/5a8bc/1

+5
source

- , URL-, , .

SELECT x1.*
FROM
  (SELECT URL,
          COUNT(ID) AS urlcount,
          MAX(DOB) AS uniqueurl
   FROM Log
   GROUP BY URL HAVING count(ID) = 1
   OR MIN(DOB) > dateadd(HOUR ,-1 , CURRENT_TIMESTAMP)) AS x1
WHERE x1.uniqueurl > dateadd(HOUR ,-1 , CURRENT_TIMESTAMP);

http://sqlfiddle.com/#!3/250e0/45/0

, , , , , ,

+2

(SQLFiddle):

SELECT COUNT(DISTINCT T0.URL) 
FROM Log AS T0 
LEFT OUTER JOIN Log AS T1 ON 
    T1.URL = T0.URL 
    AND T1.DOB < DATEADD(HOUR, -1, CURRENT_TIMESTAMP) 
WHERE 
    T0.DOB > DATEADD(HOUR, -1, CURRENT_TIMESTAMP) 
    AND T1.ID IS NULL

, GarethD, .

+1

:

SELECT DISTINCT a.id, a.url, a.dob
FROM Log a JOIN Log b ON (a.url = b.url)
WHERE UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(a.DOB)<=3600 
  AND UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(b.DOB)>3600;

, , .

, UNIX_TIMESTAMP , , , . 3600 .

:

The sentence has been corrected. But this is for MySQL (I have not seen the sql-server2005 tag)

0
source 
select distinct(a.url) from tbl a, tbl b where a.dob>(now-hour) and b.dob<=(now-hour) and a.url=b.url;

(replace the time with manipulations with something from your db of choice), index urls and dob)

We also hope that your database is smart enough to dob comparisons before concatenating and concatenating using indexes.

-1
source

More articles:


All Articles