1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Query Help so simple I cant see it

Discussion in 'Databases' started by johnberman, Jan 20, 2017.

  1. #1
    Hi
    Im sure the answer is obvious but I cant see it
    SEMrush
    I have a table of events (they are actually radio meteor events)
    This table has amongst other things

    event_id which is unique
    Date
    time
    contributor

    in practical terms the event may be repeated several times for any given event as different contributors may have records the same event.

    So im looking for a query that will return all records were the date and time match

    Pointers really appreciated.

    John B
     
    johnberman, Jan 20, 2017 IP
    SEMrush
  2. sarahk

    sarahk iTamer Staff

    Messages:
    24,541
    Likes Received:
    3,307
    Best Answers:
    94
    Trophy Points:
    615
    #2
    I created a fiddle for your data - take a look at http://sqlfiddle.com/#!9/4d866/3/0
    but basically, this should do the trick

    select event_id, eventdate, eventtime, contributor_id
    , (select count(event_id) 
       from rme as r2 
       where rme.eventdate = r2.eventdate 
       and rme.eventtime = r2.eventtime
       group by rme.eventdate, rme.eventtime) as counter
    from rme
    having counter > 1
    Code (markup):
     
    sarahk, Jan 20, 2017 IP
  3. johnberman

    johnberman Greenhorn

    Messages:
    16
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    thank you so much - is this similar to an inner join ?

    John B
     
    johnberman, Jan 21, 2017 IP
  4. johnberman

    johnberman Greenhorn

    Messages:
    16
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #4
    sorry further to this, it works at expected but is slowww Currently I have 2000 records and it takes about 4 seconds - I fear when the record set grows it will take to long, potentially I could end up with several hundred thousand records

    John B
     
    johnberman, Jan 21, 2017 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    24,541
    Likes Received:
    3,307
    Best Answers:
    94
    Trophy Points:
    615
    #5
    It's using a subquery. I can suggest alternatives but I'm not sure what you're trying to achieve with this. If you could explain what the result is meant to mean to the people who see it I might be able to help a bit more.

    At the moment it looks like you are trying to say "We got multiple reports sent in by these guys"
    If that's the case then a simple looping through results will do the trick
    I'll check back in the morning (midnight here in NZ)
     
    sarahk, Jan 21, 2017 IP
  6. johnberman

    johnberman Greenhorn

    Messages:
    16
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #6
    ok so I record meteor events and store them in a table called events, a record looks like this

    Event_ID - 8349

    Date - 2017/01/01
    Time - 00:01:23
    Signal - -74.80
    Noise - -92.00
    Frequency - 2231
    Duration - 2.00
    Contributor - 1

    Event_ID is unique and its the primary key

    so contributor 1 may have several thousand events for say January 2017

    Now contributor 2 will alos have several thousand events for January 2017 and some will be the same as contributor 1 ie he may have this event

    Event_ID - 14367

    Date - 2017/01/01
    Time - 00:01:23
    Signal - -74.80
    Noise - -92.00
    Frequency - 2231
    Duration - 2.00
    Contributor - 2

    as the project increases there will be more and more contributors so its possible that multiple contributors will have recorded the same event

    What I want to produce is a report that shows identical events and by identical i mean were the date and time match the result using the data above would bring back 2 records but from different contributors

    Hope this makes sense
    John B
     
    johnberman, Jan 21, 2017 IP
  7. sarahk

    sarahk iTamer Staff

    Messages:
    24,541
    Likes Received:
    3,307
    Best Answers:
    94
    Trophy Points:
    615
    #7
    I'd be changing the way you store your data.

    Effectively you are deduping the database and that is always resource heavy.

    If you query the database with

    select date, time count(event_id) as counter
    from event_table 
    group by date, time
    having counter > 0
    Code (markup):
    you'll get what you want - the number of submissions for an event

    however, when you save the report you should be looking to see if the event has already been reported and create a new record in a separate table

    Events
    - event_id
    - date
    - time

    Reported By
    - report_id
    - user_id
    - event_id

    So you'd end up with only one record in Events per meteor event but lots of records in reported by

    In NZ we have a site that displays earthquakes using a feed from seismic sciencey stuff but the public can register that they "felt that". Their situation is similar to yours except that you need to create the Event as well. In fact, I'd show the list of most recent meteors and give your users the opportunity to click "I saw it too" so that reporting is quicker and simpler.
     
    sarahk, Jan 22, 2017 IP
  8. Affiliate Programs

    Affiliate Programs Greenhorn

    Messages:
    20
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    23
    #8
    Even if you can write a good query, it seems you may still have a problem. The potential problem appears to be that different contributors may record different times for the same event. Additionally, contributors in different time zones may record different dates. The date/time zone issue may not be a big one, but the possibility of different times seems to be. Is this not so?
     
    Affiliate Programs, Jan 31, 2017 IP
  9. johnberman

    johnberman Greenhorn

    Messages:
    16
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #10
    You have a point however all event are recorded in UTC and and within the same time zone so it should be ok
     
    johnberman, Feb 5, 2017 IP