create view of count based on latest timestamp for a particular id

Question

I have been provided with the following code to run a query that counts the number of connector_pks grouped by group_status based on the latest timestamp:

SELECT
`group_status`,COUNT(*) 'Count of status '
FROM
(SELECT `connector_pk`, `group_status`, `status_timestamp` 
FROM connector_status_report t1
WHERE `status_timestamp` = (SELECT MAX(`status_timestamp`) 
                        FROM connector_status_report t2 WHERE t2.`connector_pk` = t1.`connector_pk`)) 
t3
GROUP BY `group_status`

Unfortunately this takes about 30 minutes to run so I was hoping for an optimised solution.

Example table

connector_pk    group_status    status timestamp
1               Available       2020-02-11 19:14:45
1               Charging        2020-02-11 19:18:45
2               Available       2020-02-11 19:15:45
2               Not Available   2020-02-11 19:18:45
3               Not Available   2020-02-11 19:14:45

The desired output would look like this:

group_Status      | Count of status    
Available         | 0    
Charging          | 1    
Not Available     | 2

For my original question I was pointed to the following question (and answers):

Get records with max value for each group of grouped SQL results

I would like to create a view with the output

Is it possible to also add the following to the query to include in the View:

SELECT status, = IF(status = 'charging', 'Charging', if(status = 'Not 
Occupied','Available', 'Occupied') AS group_status FROM 
connector_status_report

score 0 · Answer 1 · answered Oct 20 '20 at 11:11

I managed to speed up the query using the following:

CREATE VIEW statuscount AS
Select group_status, COUNT(*) 'Count of status'
FROM
(SELECT tt.*
FROM connector_status_report tt
INNER JOIN
  (SELECT connector_pk, MAX(status_timestamp) AS MaxDateTime
  FROM connector_status_report
  GROUP BY connector_pk) groupedtt 
ON tt.connector_pk = groupedtt.connector_pk 
AND tt.status_timestamp = groupedtt.MaxDateTime) t3
GROUP BY group_status

If anybody can help with inserting the query that creates the 'Group_Status' column it would be much appreciated

create view of count based on latest timestamp for a particular id

1 Answers1