Results 1 to 10 of 10
  1. #1
    Join Date
    May 2014
    Posts
    9

    Unanswered: Select Query to add a new group field

    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)

    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

  2. #2
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    How are you planning on grouping?

    You could use a rownumber over partition by to generate your groupings and order by date, time and user id.
    70-461 SQL Certified.

  3. #3
    Join Date
    May 2014
    Posts
    9

    pointer

    err thanks, any chance of a pointer or two ?

    Regards
    John B

  4. #4
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    The numbers that are populating what is the logic behind the numbering? For instance you have two records with 1 and two more with 2 and the last set is 3 records.

    I'll need to know what logic, event will be triggering those numbers. Are the 7 records you have present only it or will there be more to come as well?
    70-461 SQL Certified.

  5. #5
    Join Date
    May 2014
    Posts
    9

    Context may help

    Ok I think some context will help, the database collects Meteor Detecion info (sorry about the length of the post)

    Each event is a unique record in the main database (mySQL) and among other things it includes

    Event_ID - Primary Key
    User_id - there are many contributors
    Date - date of the event
    time - time of the event in hh:mm:ss

    Then other info

    All this data is held in a table called Meteor_Events - For January 2017 is so far has some
    30,000 records with another 30,000 to to be added


    User can search this using varying crieria and it works fine

    Now as you probably know throughout the year there are regular shows so in Jan for eample
    there are the quadrantids with a peak between the 1st and 4th Jan

    So from the main table I run a query

    CREATE TABLE quadrantids2017

    SELECT *

    FROM
    meteor_events
    WHERE
    (meteor_events.date BETWEEN '2017/01/01'
    AND _utf8 '2017/01/04' )

    So we now have a table quadrantids2017 which is a subset with 10,000 records

    whilst the event record is unique many event are submitted by different contributors so for instance

    event_id, Contributior_id, Date, Time

    37775 2 2017-01-01 00:01:23
    52402 4 2017-01-01 00:01:23
    63369 5 2017-01-01 00:01:24
    63370 5 2017-01-01 00:22:00
    63371 5 2017-01-01 00:22:42
    63372 3 2017-01-01 00:22:44
    63373 2 2017-01-01 00:22:42
    63374 1 2017-01-01 00:22:43
    63375 5 2017-01-01 00:24:52
    63376 1 2017-01-01 00:24:52
    63377 2 2017-01-01 00:24:51

    Given that the contributors are geographicall seperated etc they may or many not pick up the start of the event at the same time

    so im looking for a query that would look for events that are + or - 1 second

    which I have

    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`
    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`

    event_id, Contributior_id, Date, Time

    37775 2 2017-01-01 00:01:23 - Match
    52402 4 2017-01-01 00:01:23 - Match

    63371 5 2017-01-01 00:22:42- Match
    63372 3 2017-01-01 00:22:44- Match
    63373 2 2017-01-01 00:22:42- Match
    63374 1 2017-01-01 00:22:43- Match

    63375 5 2017-01-01 00:24:52- Match
    63376 1 2017-01-01 00:24:52- Match
    63377 2 2017-01-01 00:24:51- Match

    So I have a new table quad2017_1 with the associated events

    Ideally I want to add two new fields
    Group
    Quality

    so it would look like this

    event_id, Contributior_id, Date, Time, group, quality

    37775 2 2017-01-01 00:01:23 1 2
    52402 4 2017-01-01 00:01:23 1 2

    63371 5 2017-01-01 00:22:42 2 4
    63372 3 2017-01-01 00:22:44 2 4
    63373 2 2017-01-01 00:22:42 2 4
    63374 1 2017-01-01 00:22:43 2 4

    63375 5 2017-01-01 00:24:52 3 3
    63376 1 2017-01-01 00:24:52 3 3
    63377 2 2017-01-01 00:24:51 3 3

    With the group just being an incremental number and the quality being the count of event in the group, the greater the quilty the more likley its a quality event

    Hope this makes sense

    John B

  6. #6
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    SELECT
    A.Event_ID
    ,A.Contributior_id
    ,A.DATE
    ,A.GROUP_ID
    ,COUNT(A.GROUP_ID) OVER (PARTITION BY A.DATE ORDER BY A.DATE) AS QUALITY_ID

    FROM
    (


    SELECT
    Event_id
    ,Contributior_id
    ,[DATE]
    ,DENSE_RANK() OVER (ORDER BY DATE) AS [GROUP_ID]

    FROM
    #quad2017_1


    ) AS A
    This should give you a good idea on how to get the ranking and counts. I would recommend placing a ranking id for your dates when they go into your second table, so you can group on those.
    70-461 SQL Certified.

  7. #7
    Join Date
    May 2014
    Posts
    9
    Thank You, I get the general idea but its still an issue with various errors, can we break into logical steps and if I need a number of query's that subsequently produce a number of tables till I get were I need to be that fine


    So first query which works fine

    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`
    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 this
    37775 2 2017-01-01 00:01:23
    52402 4 2017-01-01 00:01:23

    63371 5 2017-01-01 00:22:42
    63372 3 2017-01-01 00:22:44
    63373 2 2017-01-01 00:22:42
    63374 1 2017-01-01 00:22:43

    63375 5 2017-01-01 00:24:52
    63376 1 2017-01-01 00:24:52
    63377 2 2017-01-01 00:24:51

    needs to create an additional field lets call it cluster and keep away from the name group which could get confusing so it would look like this

    event_id, User_id, date, time, cluster
    37775 2 2017-01-01 00:01:23 <new field cluster> - 1
    52402 4 2017-01-01 00:01:23 <new field cluster> - 1

    63371 5 2017-01-01 00:22:42 <new field cluster> - 2
    63372 3 2017-01-01 00:22:44 <new field cluster> - 2
    63373 2 2017-01-01 00:22:42 <new field cluster> - 2
    63374 1 2017-01-01 00:22:43 <new field cluster> - 2

    63375 5 2017-01-01 00:24:52 <new field cluster> - 3
    63376 1 2017-01-01 00:24:52 <new field cluster> - 3
    63377 2 2017-01-01 00:24:51 <new field cluster> - 3

    and so on


    John B

  8. #8
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    Would you be able to round 5 seconds? This would give you an organic grouping which would allow ranking. You could keep the original date for reference but cluster the dates that round together.
    70-461 SQL Certified.

  9. #9
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    SELECT
    Event_ID
    ,User_ID
    ,[DATETIME]
    ,DENSE_RANK() OVER (ORDER BY ROUNDED_NUMBER) AS CLUSTER_ID

    FROM
    (

    SELECT
    Event_ID
    ,User_ID
    ,B.[DATETIME]
    ,DATEADD(SECOND, ROUND(DATEPART(SECOND,DATETIME)*2,-1) / 2-datepart(second,DATETIME), DATETIME) AS ROUNDED_NUMBER

    FROM

    (

    SELECT
    Event_ID
    ,User_ID
    ,CONVERT(DATETIME2,[DATETIME], 121) AS DATETIME
    FROM
    (

    select distinct
    event_id AS event_id
    ,user_ID AS user_ID
    ,date AS [date]
    ,Time AS [Time]
    ,CONCAT(Date,' ', Time) as [DATETIME]

    FROM #quadrantids2017
    ) AS A
    ) AS B
    ) AS C
    You'll have to group those numbers before you can rank +1 / -1 a second. What I did is apply some nested functions to get the dates to round at 5 second intervals. This should give you a nice set of groupings to keep your dates clustered.

    I had to bring the date time together then convert so the math could be applied to the datetime2 field.

    Have a nice evening.
    70-461 SQL Certified.

  10. #10
    Join Date
    May 2014
    Posts
    9
    Thanks I had a quick try but simply get errors, the server is running MySQL 5.0.92-50

    and I am using Navicat for MySQLto access and run the queries etc.

    John B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •