Скачать или смотреть How to Load Data Infile in MySQL While Ignoring Records with Short Time Differences

Learn how to effectively load data into MySQL while avoiding duplicated entries based on a set time difference. This guide explains a systematic approach to managing your data import.
---
This video is based on the question https://stackoverflow.com/q/67891856/ asked by the user 'Leonardo Quatrocchi' ( https://stackoverflow.com/u/16032796/ ) and on the answer https://stackoverflow.com/a/67895728/ provided by the user 'Leonardo Quatrocchi' ( https://stackoverflow.com/u/16032796/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to Load data infile in MySQL avoiding records with 5 minutes of difference of the same client? (I can do it on excel easily)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Load Data Infile in MySQL While Ignoring Records with Short Time Differences

Importing data into MySQL can sometimes present unique challenges, especially when you want to avoid inserting duplicate records based on specific conditions. A common scenario is managing records with time differences, such as skipping entries that fall within a certain time frame from a previous record. In this guide, we’ll address how to load data using the LOAD DATA INFILE command while avoiding entries that are too close in time (specifically, with less than a 5-minute difference) based on a client's information.

The Problem

In a situation where you have a dataset with several columns including CLIENT_NUMBER, SUBACCOUNT, DATE, and TIME, and you want to ensure that records for the same client and subaccount are inserted only if they are spaced out by more than 5 minutes, you might find that simply using LOAD DATA INFILE won't cut it. The challenge arises in dynamically checking the time difference between records during the insert process.

Sample Dataset

Let’s consider the following structure and sample data that create this scenario:

Columns:

CLIENT_NUMBER (VARCHAR(8))

SUBACCOUNT (VARCHAR(2))

DATE (VARCHAR(8)) in DDMMYYYY format

TIME (VARCHAR(6)) in HHMMSS format

Example Rows:

[[See Video to Reveal this Text or Code Snippet]]

The Solution

Realizing that this cannot be completed in a single SQL command, we will use a strategy of Divide & Conquer to process the data. Here's how you can manage it step-by-step:

Step 1: Data Preparation

Order Data: Ensure your data file is ordered by CLIENT_NUMBER, SUBACCOUNT, and the combined EPOCH representation of DATE and TIME. This will facilitate easy comparison.

Step 2: Detect Duplicates

Read Through Data: Loop through the ordered data line by line. For each current line (n), check if it matches the next line (n+ 1):

Condition 1: CLIENT_NUMBER of line n equals CLIENT_NUMBER of line n+ 1 and SUBACCOUNT of line n equals SUBACCOUNT of line n+ 1.

Condition 2: The time difference must be less than 300 seconds (5 minutes) between the two entries:

(epoch time of line n+ 1 - epoch time of line n) < 300

Step 3: Mark Duplicates

Marking Duplicates: All lines that meet the criteria should be flagged with a row_id indicating they are deemed as a duplicate:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Query for Unique Records

Select Valid Records: Finally, execute a query to select all records where the duplicated flag is not set. This will give you all the data without the unwanted duplicated entries:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By following the steps mentioned above, you can effectively manage your data import into MySQL while ensuring that entries that could be viewed as duplicates (based on client number, subaccount, and time differences) are identified and handled appropriately. There may not be a one-liner SQL solution for this problem, but with a well-structured strategy and clear logic, you can achieve the desired results effectively.

If you ever find yourself in a similar predicament, remember that breaking down the problem into smaller, manageable pieces is often the best approach!