Shulou(Shulou.com)06/01 Report--

This article mainly explains how MySQL calculates the difference between two adjacent rows. Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how MySQL calculates the difference between two adjacent rows.

Background

We preset the function of regularly reporting GPS data in the driver's mobile phone APP. The function is set to APP to collect GPS location addresses every 15 seconds, and then report them to the server for persistence every 10 times. However, because APP is not integrated with Android, but provided and designed by us, the collection of GPS data may cause positioning deviation or failure to obtain positioning due to the human operation of drivers or other networks. Now our server has such a requirement to analyze whether the APP installed in the driver's mobile phone is collected to the GPS location on a regular basis.

In order to ensure that the GPS location data can be uploaded to the server smoothly, after each APP request, only when the APP receives a correct response from the server can we indicate that the uploaded data is normal and accurate, and then clear the last recorded GPS location data on the APP side. However, if APP is unable to report the last collected data to the server for various reasons, and fails after another attempt, it will be reported next time together with the next batch of collected data, through this mechanism to ensure the stability of data transmission.

Analysis.

If the APP in the driver's phone is timed for 15 seconds to collect the GPS address, then the time difference between the two records in the database in the order of acquisition time should be 15 seconds. If it is more than 15 seconds, then there is a problem with timing acquisition. So what we need to do now is to screen out whether the time difference between the two records before and after obtaining the GPS position is more than 15 seconds. The method to calculate the time difference has already been found in MySQL, that is, TimeDiff (I have a summary of the previous blog post for the small pit used by TimeDiff in Java. Interested friends can Mark it and take the time to have a look later. There seems to be no contact with how to calculate the difference between the two records before and after calculation, so this article will solve this problem.

Solve

First of all, the blogger has a table on the server to record the GPS click information reported by the driver. The table structure is as follows:

1mura-driver GPS collection form

2CREATETABLEcaptainad_driver_gps_position (

3idBIGINTNOTNULLautoincrementCOMMENT` primary key

4 businessman BIGINTDEFAULTNULCOMMENT' business ID'

5device_macVARCHAR (64) DEFAULTNULLCOMMENT' device MAC address'

6device_imeiVARCHAR (64) DEFAULTNULLCOMMENT' device IMEI'

7lat_lngVARCHAR (64) DEFAULTNULLCOMMENT' Latitude and Longitude'

8captureproof timeTIMESTAMPDEFAULTCURRENTTIMESTAMPCOMMENT`capture time'

9createroomtimeTIMESTAMPDEFAULTCURRENTTIMESTAMPCOMMENT` creation time

10 updatekeeper timeTIMESTAMPDEFAULTCURRENTTIMESTAMPONUPDATECURRENTTIMESTAMPCOMMENT`

11PRIMARYKEY (id)

12KEY`idx _ business_ id` (`KEY`id`) USINGBTREE

13) ENGINE=INNODBDEFAULTCHARSET=utf8COMMENT=' driver GPS Collection'

The data recorded in the table is roughly as follows:

Now calculate the difference between the two records after the capture_time is sorted by time when the GPS location is obtained. In order to calculate the difference between the two, then we must need to get the first and the last two records, here we can skillfully use a variable to record the number of rows of the current row, and then stack the number of rows each time with the circular query to achieve the purpose of row recording. in this way, we can know which two records are one after the other.

The SQL statement that prints the line number:

1SELECT

2 (@ rownum:=@rownum+1) ASrownum

3tab.business_id

4tab.device_mac

5tab.capture_time

6FROM

7captainad_driver_gps_positiontab

8 (SELECT@rownum:=0) rmuri-declare variables

9WHERE

101 to 1

11ANDDATE_FORMAT (

12tab.capture_time

1300% Ymuri% mmi% d'

14) = '2019-06-28'

15ORDERBY

16tab.capture_time

Based on this, we write the target SQL. Here, I sort out the statement according to our actual business. The script is roughly as follows:

1SELECT

2t.business_id

3t.device_mac

4t.capture_time

5t.tdiff

6FROM

7 (

8SELECT

9r1.business_id

10r1.device_mac

11r1.capture_time

12TIMEDIFF (

13r2.capture_time

14r1.capture_time

15) AS'tdiff'

16FROM

17 (

18SELECT

19 (@ rownum:=@rownum+1) ASrownum

20tab.business_id

21tab.device_mac

22tab.capture_time

23FROM

24captainad_driver_gps_positiontab

25 (SELECT@rownum:=0) r

26WHERE

271-1

28ANDDATE_FORMAT (

29tab.capture_time

30% Ymuri% mmi% d'

31) = '2019-06-28'

32ORDERBY

33tab.capture_time

34) R1

35LEFTJOIN (

36SELECT

37 (@ INDEX:=@INDEX+1) ASrownum

38tab.business_id

39tab.device_mac

40tab.capture_time

41FROM

42captainad_driver_gps_positiontab

43 (SELECT@INDEX:=0) r

44WHERE

451-1

46ANDDATE_FORMAT (

47tab.capture_time

48% Ymuri% mmi% d'

49) = '2019-06-28'

50ORDERBY

51tab.capture_time

52) r2ONr1.business_id=r2.business_id

53ANDr1.device_mac=r2.device_mac

54ANDr1.rownum=r2.rownum-1

55) t

56WHERE

57t.tdiff > '00VOG 00RU 15'

In the above code, we use r1.rownum=r2.rownum-1 to determine whether the two records are forward and backward, and then use the TIMEDIFF function to calculate the time difference, and our goal is achieved.

At this point, I believe you have a deeper understanding of "how MySQL calculates the difference between two adjacent rows". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.