How to predict the rows of a table using machine learning?

Question

In my work, I need to manipulate lots of tables from databases. And I want to check whether the table not lost data, the basic way to do is checking the amount of rows.

For example, the amount of rows usually at about 500 million, but one day it changes to 480 million, there may some data lost.

Could anyone tell me which algorithm I should use to do this check?

I want to use history rows amount data to do a predict, and if today's data is far away from the prediction do a alarm.

I think I just have the daily data of the amount of rows, so I don't have right answer given(or I just have one feature -- amount of rows), how could I use regression to solve this?

Answer 1

I am not sure regression would help you doing this. In my opinion, the simplest way would be for you to build the histogram of the daily number of rows you have. Then fit any distribution that comes to your mind (does it look gaussian, by any chance) and build a confidence interval around the mean.

Call :

• $n_{Today}$ the number of rows observed today
• $\mu$ the average of the daily number of rows
• $\sigma$ the standard deviation of the daily number of rows

If it is gaussian, a simple rule such as $n_{Today} < \mu - 2\sigma$ could be a start.

If you have the past observations with a label "some data was lost/no data was lost" you can turn this in a classification problem. However, with one single feature, you will have trouble to find a decision rule which is not $n_{Today} < t$ where $t$ is some threshold. Maybe other features (like the day of the week, or is it a bank day ?) have an influence on the number of rows obtained it a day.