0

I am trying to figure out how I can output any machine that has had the latest 3 backups be larger than 10GB. My SQL Table looks similar to the below table:

ComputerID | BackupImageName | WindowsVolumeGUID | FileSize | TimeCreated
1234       | PCBackup.spi    | kjsdfai-dadad-ddd | 10400    | 05-22-20
1234       | PCBackup1.spi   | kjsdfai-dadad-ddd | 10500    | 05-23-20
1234       | PCBackup2.spi   | kjsdfai-dadad-ddd | 10300    | 05-24-20
1234       | PCBackup3.spi   | kjsdfai-dadad-ddd | 10900    | 05-25-20
1225       | PCBackup4.spi   | kjsdfa5-dadad-ddd | 10600    | 05-22-20
1225       | PCBackup5.spi   | kjsdfa5-dadad-ddd | 5000     | 05-23-20
1225       | PCBackup6.spi   | kjsdfa5-dadad-ddd | 10000    | 05-24-20
1225       | PCBackup7.spi   | kjsdfa5-dadad-ddd | 4588     | 05-25-20

I need to output all machines (computerID's) or Volumes (WindowsVolumeGUID) that have their latest 3 files in the list greater than 10000 file size. So from the above table it would output the computerID of 1234 because its latest 3 are greater than 10000 file size and even though the computerID 1225 has 2 files greater than 10000 it would not output the result because there is a break in-between the two large files. The search will need to take into account the WindowsVolumeGUID since some machines have more than one volume and I need to take into account the individual volume backups and count the latest entries based on that.

Alternatively, I can also accomplish the same thing if there is a way to output the top 3 results for each unique WindowsVolumeGUID and not just across the board.

Any help would be greatly appreciated!

2
  • Is TimeCreated actually a date format? If not, fix this first. – danblack May 29 '20 at 2:58
  • The MySQL version is 5.7 and the date is in the correct format, I just built that table by hand to hopefully explain my issue. The filesize is also in bytes and not MB but either way I should be able to modify some ideas to come up with a viable solution. – Rmuell01 May 29 '20 at 13:29
0
  1. Incoming schema and test data

    CREATE TABLE `db_info` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `ComputerID` int(10) unsigned NOT NULL,
      `BackupImageName` varchar(32) NOT NULL,
      `WindowsVolumeGUID` varchar(32) NOT NULL,
      `FileSize` int(10) unsigned NOT NULL,
      `TimeCreated` timestamp NOT NULL DEFAULT current_timestamp(),
      PRIMARY KEY (`id`),
      KEY `ComputerID` (`ComputerID`),
      KEY `WindowsVolumeGUID` (`WindowsVolumeGUID`),
      KEY `FileSize` (`FileSize`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `db_info` (`ComputerID`, `BackupImageName`, `WindowsVolumeGUID`, `FileSize`, `TimeCreated`) VALUES
    (1234, 'PCBackup.spi ', 'kjsdfai-dadad-ddd', 10400, '2020-05-22'),
    (1234, 'PCBackup1.spi', 'kjsdfai-dadad-ddd', 10500, '2020-05-23'),
    (1234, 'PCBackup2.spi', 'kjsdfai-dadad-ddd', 10300, '2020-05-24'),
    (1234, 'PCBackup3.spi', 'kjsdfai-dadad-ddd', 10900, '2020-05-25'),
    (1225, 'PCBackup4.spi', 'kjsdfa5-dadad-ddd', 10600, '2020-05-22'),
    (1225, 'PCBackup5.spi', 'kjsdfa5-dadad-ddd', 5000, '2020-05-23'),
    (1225, 'PCBackup6.spi', 'kjsdfa5-dadad-ddd', 10000, '2020-05-24'),
    (1225, 'PCBackup7.spi', 'kjsdfa5-dadad-ddd', 4588, '2020-05-25');
    
  2. Requested Query

    SET @n := 0, @lastComputerId := NULL;
    SELECT ComputerId, SUM(IF(FileSize >= 10000, 1, 0)) AS large_backups_count
    FROM (
        SELECT ComputerId, FileSize
        FROM `db_info`
        WHERE (@n:=(@n * IF(@lastComputerId <=> ComputerID, 1, (@lastComputerId := ComputerID) AND 0) + 1)) < 4
        ORDER BY ComputerID, TimeCreated DESC
    ) AS tmp
    GROUP BY ComputerId
    HAVING large_backups_count >= 3;
    

    Result:

    +------------+---------------------+
    | ComputerId | large_backups_count |
    +------------+---------------------+
    |       1234 |                   3 |
    +------------+---------------------+
    
  3. Alternative query based on total size of recent three backups :

    SET @n := 0, @lastComputerId := NULL;
    SELECT ComputerId, SUM(FileSize) AS sum_of_recent_three_backups
    FROM (
        SELECT ComputerId, FileSize
        FROM `db_info`
        WHERE (@n:=(@n * IF(@lastComputerId <=> ComputerID, 1, (@lastComputerId := ComputerID) AND 0) + 1)) < 4
        ORDER BY ComputerID, TimeCreated DESC
    ) AS tmp
    GROUP BY ComputerId
    HAVING sum_of_recent_three_backups >= 30000;
    

    Result:

    +------------+-----------------------------+
    | ComputerId | sum_of_recent_three_backups |
    +------------+-----------------------------+
    |       1234 |                       31200 |
    +------------+-----------------------------+
    
0

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.