|char type||datetime type||Column 3|
Because there are too many records
If there are multiple records per uuid in a short time, I would like to thin it out
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.
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
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?
- i want to send data to mysql with laravel
- mysql - i want to display the data list on the transitioned page
- vba - i want to pass class data to the user form
- c # - i want to reflect object data in view
- php - [wp] i want to save the data sent from the public page on the mw wp form with the image in the post
- i want to display db data with jsp
- python - i want to turn data call with for in django
- mysql - i want to link primary key and foreign key
- java - i want to display all db data in jsp
- mysql - i want to know how to use sql bit
- i want to add data to java web app db
- spring boot - i want to insert data using h2, but i can not do it well
- i want to scrape jsp data in python
- i want to extract the data by extracting the url from a text area etc (php)
- kotlin - i want to display api data in recyclerview
- ruby - i want to retrieve the data from the json type file sent from the api
- python - data storage in mysql
- mysql - in sql case when, i want to name more than one same as ~
- i want to put out mr with gitlab
- i want to add new data to the data acquired from json
- python 3x - typeerror: 'method' object is not subscriptable
- python - you may need to restart the kernel to use updated packages error
- xcode - pod install [!] no `podfile 'found in the project directory
- vuejs - [vuetify] unable to locate target [data-app] i want to unit test to avoid warning
- android studio - emulator: dsound: could not initialize about the error message directsoundcapture
- android studio - unresolved reference comes out in kotlin
- mysql startup failed [error] innodb: the innodb_system data file 'ibdata1' must be writable
- django - oserror: [winerror 123] the file name, directory name, or volume label syntax is incorrect : '<frozen importlib_boot
- python - importerror: cannot import name md5 error cannot be resolved