clickhouse 查询sql性能优化

优化前2.594s

SELECT rate*`data` as lastread ,* FROM
(
SELECT cg_code,MAX(dev_time) as nt FROM ecms_renter.device_collection_data_log
where node_id =
AND node_type =
AND company_code =
AND dev_time >=toStartOfDay(subtractDays(now(),2))
AND dev_time < toStartOfDay(subtractDays(now(),1))
AND cg_code IN ()
GROUP BY cg_code
) lastreadtable
LEFT JOIN ecms_renter.device_collection_data_log t
on t.cg_code = lastreadtable.cg_code  AND t.dev_time  = lastreadtable.nt
where node_id =
AND node_type =
AND company_code =

优化后 662ms

SELECT * FROM
ecms_renter.device_collection_data_log  as tableA,
(
SELECT cg_code,MAX(dev_time) as mt FROM ecms_renter.device_collection_data_log
where node_id =
AND node_type =
AND company_code =
AND dev_time >=toStartOfDay(subtractDays(now(),2))
AND dev_time < toStartOfDay(subtractDays(now(),1))
AND cg_code IN
GROUP BY cg_code
) as tableB
where dev_time = mt
AND cg_code = tableB.cg_code