-2

This is our situation:

  1. We have a +50MM records.
  2. The DB structure is as follows:
id Field: int(15) auto_increment, 
DATETIME field,
METER field: varchar(15), 
RECORD_A field: float(10,2)

Each METER has a RECORD_A value that aggregates the amount every 15 minutes. For example, at 2021-07-23 11:00:00 the RECORD_A = 11,52 and at 2021-07-23 11:15:00 the RECORD_A = 12,37.

What we want to do is to have the difference or variation of RECORD_A for a METER between DATETIME every 15 minutes. In this example, our query should show 0,85 and to run the query in an "optimized" way.

Thanks in advance,

5
  • Welcome to DBA.SE! Is it MySQL or Bigtable? Your tags don't make much sense.
    – bbaird
    Jul 23 at 16:56
  • 1
    Also tag with the version of MySQL
    – bbaird
    Jul 23 at 17:27
  • In this context "50M" means "fifty million". Is that what you meant by MM?
    – Rick James
    Jul 24 at 1:01
  • Are the DATETIME column values exactly every 15:00? If so, it is easier than if the readings show up only approximately ever 15 minutes.
    – Rick James
    Jul 24 at 1:04
  • Yes, they are every 15 minutes..
    – Ivan
    Jul 24 at 15:24
2

The best thing you can do is drop the id column as it:

  1. Allows absolute junk to be inserted into your table
  2. Prevents utilizing MySQL's clustered index to optimize the query
  3. Takes up space, requiring more page reads to return the same amount of information

The primary key is (Meter,Datetime)1, so we should define it as such. This will:

  1. Prevent inconsistent data (we should not have two values for the same Meter for the same Datetime)
  2. Store the rows (more or less) according to the order of the primary key. This means fewer physical page reads, and a simple way to get the prior value without sorting the data first.

Depending on your version of MySQL, you can fulfill your request one of two ways: a join to retrieve the prior value, or a windowing function.

Using a join (works in all versions):

SELECT
  MM.Meter
 ,MM.Datetime
 ,MM.Record_A
 ,PriorMM.Record_A AS Prior_Record_A
 ,MM.Record_A - PriorMM.Record_A AS Record_A_Change
FROM
  MeterMeasurement MM
LEFT JOIN
  MeterMeasurement PriorMM
    ON PriorMM.Meter = MM.Meter
        AND PriorMM.Datetime =
          (
            SELECT
              MAX(Datetime)
            FROM
              MeterMeasurement
            WHERE
              Meter = MM.Meter
                AND Datetime < MM.Datetime
          )

Using a windowing function (supported with MySQL 8.0):

SELECT
  Meter
 ,Datetime
 ,Record_A
 ,LAG(Record_A) OVER (PARTITION BY Meter ORDER BY Datetime) AS Prior_Record_A
 ,Record_A - LAG(Record_A) OVER (PARTITION BY Meter ORDER BY Datetime) AS Record_A_Change
FROM
  MeterMeasurement

Fiddle: https://www.db-fiddle.com/f/5Qzok5RFTxwEdzB2NTqzDT/0

Which one of these will perform better depends entirely on if MySQL understands the clustered index eliminates a need for a sort when the windowing function is used.

1 I would recommend using a column name that isn't a reserved keyword in many databases - consider MeasurementTs or MeasurementDtm instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.