How to ensure that a ResultSet includes rows for "missing" observations

Question

I have a ResultSet table shown below:

+------------+--------------------+--------------------+---------+-----------------------+
| test_date  | upload_kbps        | download_kbps      | latency | network_operator_name |
+------------+--------------------+--------------------+---------+-----------------------+
| 2017-04-02 |  19.12741903076923 | 44.614721153846155 | 32.1250 | Alcatel               |           
| 2017-03-31 |  18.30683616557377 | 44.294387978142076 | 34.7432 | Alcatel               |
| 2017-03-31 | 20.643555595555555 |  50.99801587301587 | 32.1640 | Vodafone              |

I want to modify the ResultSet for further use where while I add a row into the ResultSet like so:

    +------------+--------------------+--------------------+---------+-----------------------+
    | test_date  | upload_kbps        | download_kbps      | latency | network_operator_name |
    +------------+--------------------+--------------------+---------+-----------------------+
    | 2017-04-02 |  19.12741903076923 | 44.614721153846155 | 32.1250 | Alcatel               |   
    | 2017-04-02 |  0                 | 0                  | 0       | Vodafone              |        
    | 2017-03-31 |  18.30683616557377 | 44.294387978142076 | 34.7432 | Alcatel               |
    | 2017-03-31 | 20.643555595555555 |  50.99801587301587 | 32.1640 | Vodafone              |

The logic behind this is to basically add a null row for that telecom where on that day, a speedtest was not done for it. For further clarification: the reason i need to do this is because the table in MySQL db does not record a row/entry for tests not done, hence the lack of a row in my original ResultSet, hence the need for me to add a 'NULL/0' row to reflect the lack of test for that telco, on that day. I don't have direct access to that database to modify the entries currently so this was the best I can think of.

Any idea how I can do this? Appreciate the help!

Answer 1

It sounds like you want to add rows to the ResultSet after the fact. AFAIK, we can't do that. Instead we need to construct our SQL query so that it will produce the "extra" rows we need.

So if we have a table named "test" and

SELECT * FROM test
ORDER BY test_date DESC, network_operator_name

produces

test_date   upload_kbps       download_kbps     latency  network_operator_name
----------  ----------------  ----------------  -------  ---------------------
2017-04-02  19.1274190307692  44.6147211538461   32.125  Alcatel              
2017-03-31  18.3068361655737   44.294387978142  34.7432  Alcatel              
2017-03-31  20.6435555955555  50.9980158730158   32.164  Vodafone             

then we can start with a query to produce a row for every combination of test_date and network_operator_name

SELECT test_date, network_operator_name
FROM
    (SELECT DISTINCT network_operator_name FROM test) unique_operators
    CROSS JOIN
    (SELECT DISTINCT test_date FROM test) unique_dates

which gives us

test_date   network_operator_name
----------  ---------------------
2017-03-31  Alcatel              
2017-03-31  Vodafone             
2017-04-02  Alcatel              
2017-04-02  Vodafone             

Then we can LEFT JOIN that query with the actual table

SELECT 
    required_rows.test_date,
    COALESCE(test.upload_kbps, 0) AS upload_kbps,
    COALESCE(test.download_kbps, 0) AS download_kbps,
    COALESCE(test.latency, 0) AS latency,
    required_rows.network_operator_name
FROM 
    (
        SELECT test_date, network_operator_name
        FROM
            (SELECT DISTINCT network_operator_name FROM test) unique_operators
            CROSS JOIN
            (SELECT DISTINCT test_date FROM test) unique_dates
    ) required_rows
    LEFT JOIN
    test
        ON required_rows.test_date = test.test_date 
            AND required_rows.network_operator_name = test.network_operator_name
ORDER BY required_rows.test_date DESC, required_rows.network_operator_name

producing

test_date   upload_kbps       download_kbps     latency  network_operator_name
----------  ----------------  ----------------  -------  ---------------------
2017-04-02  19.1274190307692  44.6147211538461   32.125  Alcatel              
2017-04-02                 0                 0        0  Vodafone             
2017-03-31  18.3068361655737   44.294387978142  34.7432  Alcatel              
2017-03-31  20.6435555955555  50.9980158730158   32.164  Vodafone