我已经获得了以下代码来运行一个查询,该查询根据最新的时间戳按group_status分组的connector_pk的数量:
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`
不幸的是,这需要大约30分钟的时间,所以我希望得到一个优化的解决方案。
示例表
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
所需的输出如下所示:
group_Status | Count of status
Available | 0
Charging | 1
Not Available | 2
对于我最初的问题,我被指向以下问题(和答案(:
为每组分组的SQL结果获取具有最大值的记录
我想创建一个带有输出的视图
是否也可以将以下内容添加到查询中以包括在视图中:
SELECT status, = IF(status = 'charging', 'Charging', if(status = 'Not
Occupied','Available', 'Occupied') AS group_status FROM
connector_status_report
我使用以下方法加快了查询速度:
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
如果有人能帮助插入创建"Group_Status"列的查询,将不胜感激