Home>
uuid timestump hogehogea
char type datetime type Column 3
There is a mysql DB in the form of

.
Because there are too many records
If there are multiple records per uuid in a short time, I would like to thin it out

For example

uuid timestump
aaa 2017-12-7 12:01:00
aaa 2017-12-7 12:05:00
aaa 2017-12-7 12:08:00
aaa 2017-12-7 12:12:00
bbb 2017-12-7 12:01:00

↓↓↓

uuid timestump
aaa 2017-12-7 12:01:00
aaa 2017-12-7 12:12:00
bbb 2017-12-7 12:01:00

as every 10 minutes (If possible, you can change the time here)
I want to thin out.

Since there is so much data that I want to complete it with SQL, you can't consider sql statement.
If i know someone, please teach me how to write in sql statement.

Append

I added this because there were not enough words.

Please do not delete any data other than 00 minutes 10 minutes 20 minutes.
The reason is data with different record density for each user, and

uuid timestump
aaa 2017-12-7 12:01:00
aaa 2017-12-7 12:05:00
aaa 2017-12-7 12:08:00

If it is such a record, all the data cannot be acquired, and the bias will appear.

  • Answer # 1

    Search within the same uuid column in the data rounded up to the specified minute and take the earliest one to match
    I think this is possible

    However, if there are too many honest cases, it will take a lot of time to do this.

    create table test (
      tid char (3),
      tm datetime,
      hoge char (5),
      dataid int
    );
    insert into test (tid, tm, dataid)
    select 'aaa', '2017-12-7 12: 01: 00', 1;
    insert into test (tid, tm, dataid)
    select 'aaa', '2017-12-7 12: 05: 00', 2;
    insert into test (tid, tm, dataid)
    select 'aaa', '2017-12-7 12: 08: 00', 3;
    insert into test (tid, tm, dataid)
    select 'aaa', '2017-12-7 12: 12: 00', 4;
    insert into test (tid, tm, dataid)
    select 'bbb', '2017-12-7 12: 01: 00', 5;
    select *
      / *, from_unixtime (truncate (unix_timestamp (tm) div (10 * 60), 0) * (10 * 60))
      , from_unixtime (truncate (unix_timestamp (tm + INTERVAL 10 MINUTE) div (10 * 60), 0) * (10 * 60)) * /
    from test as A
    where A.dataid = (
      select B.dataid
        from test as B
        where B.tid = A.tid
          and B.tm>= from_unixtime (truncate (unix_timestamp (A.tm) div (10 * 60), 0) * (10 * 60))
          and B.tm<from_unixtime (truncate (unix_timestamp (A.tm + INTERVAL 10 MINUTE) div (10 * 60), 0) * (10 * 60))
        order by B.tm limit 1
    )

  • Answer # 2

    Is it like this?

    delete from table where (uuid, timestump) in (
    select * from (
    select uuid, timestump from table as t1 where
    not exists
    (
    select timestump-interval date_format (timestump, "% i")% 10 minute as m, uuid from table
    group by m, tid
    having uuid = t1.uuid and min (timestump) = t1.timestump
    )
    as dummy
    )

  • Answer # 3

    Minute is DELETE for data other than'00: 00&apos ;,'10: 00&apos ;,'20: 00'...'50: 00'To do. After that, check if there is any data that has been doubled with HAVING COUNT (*)>1?