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.

Select Query to create a new field and populate

Discussion in 'Databases' started by johnberman, Feb 28, 2017.

  1. #1
    My query

    CREATE TABLE quad2017_1
    select distinct
    `a`.`event_id` AS `event_id`,
    `a`.`user_ID` AS `user_ID`,
    `a`.`date` AS `date`,`a`.`Time` AS `Time`,
    `a`.`Noise` AS `Noise`,`a`.`Signal` AS `Signal`
    from (`quadrantids2017` `a` join `quadrantids2017` `b`)
    where ((`a`.`date` = `b`.`date`)
    and (`a`.`user_ID` <> `b`.`user_ID`)
    and ((time_to_sec(`a`.`Time`) - time_to_sec(`b`.`Time`)) between -(1) and 1))
    order by `a`.`date`,`a`.`Time`,`a`.`user_ID`

    produces an output like so (event_id, user_id,date,time)
    SEMrush
    37776 2 2017-01-01 01:08:45
    47827 3 2017-01-01 01:09:07

    33501 1 2017-01-01 01:14:59
    47828 3 2017-01-01 01:15:00

    33503 1 2017-01-01 01:24:48
    47829 3 2017-01-01 01:24:49
    37781 2 2017-01-01 01:24:53

    Is there a way that I can get to to produce this
    (event_id, user_id,date,time) and populate a new field called group

    37776 2 2017-01-01 01:08:45 1
    47827 3 2017-01-01 01:09:07 1

    33501 1 2017-01-01 01:14:59 2
    47828 3 2017-01-01 01:15:00 2

    33503 1 2017-01-01 01:24:48 3
    47829 3 2017-01-01 01:24:49 3
    37781 2 2017-01-01 01:24:53 3

    that way when I display on line I can display in groups

    Regards
    John B
     
    johnberman, Feb 28, 2017 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,625
    Likes Received:
    719
    Best Answers:
    151
    Trophy Points:
    420
    #2
    Well, no. Not unless you can get those values from somewhere. How is the group-number decided? There seems to be no correlation between IDs / time / userID and group-id.
    Normally, you would store either the group directly into the table, or you could have another table with group-information, and then link the two on SELECT via a join. Currently, it seems your DB-setup isn't really up to scratch.
     
    PoPSiCLe, Mar 1, 2017 IP
  3. johnberman

    johnberman Peon

    Messages:
    15
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3

    Sorry I was probably not being clear

    the query returned these records and the relationship is the time as in they are events that occur within a second of each other, so the query works and creates a table for the results, somehow I wondered if I could some type of marker to enable me to relate the results so for instance the first 2 are related but without some form of marker once in a table if they were not sorted by date/time I would not know - which event was related to which event - does that make sense ?

    37776 2 2017-01-01 01:08:45
    47827 3 2017-01-01 01:08:46

    33501 1 2017-01-01 01:14:59
    47828 3 2017-01-01 01:15:00

    33503 1 2017-01-01 01:24:48
    47829 3 2017-01-01 01:24:49
    37781 2 2017-01-01 01:24:49

    John B
     
    johnberman, Mar 1, 2017 IP
  4. kodyaz

    kodyaz Greenhorn

    Messages:
    6
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #4
    Hello,
    If you are working on SQL Server, following script would work without a problem.
    On other databases systems, if you have Lag() function then it will work again.
    Please keep in mind that CTE expressions might be required to converted into SubSelect statements

    ;with cte as (
    select
        *,
        rn = ROW_NUMBER() over (order by date, time),
        lag(date,1,null) over (order by date, time) lagDate,
        lag(time,1,null) over (order by date, time) lagTime
    from myData
    ), cte2 as (
    select *, DATEDIFF(mi,lagTime,time) SSDiff,
        case
            when rn = 1 then 1
            when (Date = lagDate) and (DATEDIFF(mi,lagTime,time) <= 1) then 0
            else 1
        end as increment
    from cte
    )
    select *, grp = (select sum(t.increment) from cte2 t where cte2.rn >= t.rn)
    from cte2
    Code (SQL):
    for a very similar case you can refer to group table data using SQL Lag function example http://www.kodyaz.com/t-sql/sqlserver-lag-function-to-group-rows-on-column-value.aspx

    Output is as follows

    sql.PNG

    I hope it helps
     
    kodyaz, Mar 2, 2017 IP