表1 A 定位数据表,表结构:Plateno(车牌号), in_date, Speed,in_date建立的索引,并且按照in_date每天进行表分区。
表2 B 车辆基本信息表,表结构:Plateno,Companyid
表3 C 公司基本信息表,表结构:Companyid,CompanyName现在需要统计出一个公司在一天内共有多少条定位数据上传?(说明:每天的数据量在400万条数据左右)问题:我自己写了一个统计的SQL非常的慢,需要好几个小时.请各位给出一个性能比较高的统计方法。
表2 B 车辆基本信息表,表结构:Plateno,Companyid
表3 C 公司基本信息表,表结构:Companyid,CompanyName现在需要统计出一个公司在一天内共有多少条定位数据上传?(说明:每天的数据量在400万条数据左右)问题:我自己写了一个统计的SQL非常的慢,需要好几个小时.请各位给出一个性能比较高的统计方法。
select b.companyid, count(*) as gpsCount, carCount, companyname
from gps_log_hazmatcar a
left join hazmatcarinfo b on a.plateno = b.plateno
left join (select a.companyid, b.companyname, count(*) as carCount
from hazmatcarinfo a
left join company b on a.companyid = b.companyid
where a.status = 1
group by a.companyid, b.companyname) c on b.companyid =
c.companyid
where in_date > to_date('2009-06-19', 'yyyy-MM-dd')
and in_date < to_date('2009-06-20', 'yyyy-MM-dd')
group by b.companyid, carCount, companyname
SELECT X.*, Y.CARCOUNT
FROM (
SELECT B.COMPANYID
, COUNT(1) AS GPSCOUNT
, (SELECT C.COMPANYNAME FROM COMPANY C WHERE B.COMPANYID = C.COMPANYID) AS COMPANYNAME
FROM GPS_LOG_HAZMATCAR A
INNER JOIN HAZMATCARINFO B ON A.PLATENO = B.PLATENO
WHERE IN_DATE > TO_DATE('2009-06-19', 'YYYY-MM-DD')
AND IN_DATE < TO_DATE('2009-06-20', 'YYYY-MM-DD')
GROUP BY B.COMPANYID
) X , (
SELECT B.COMPANYID
, COUNT(1) AS CARCOUNT
FROM HAZMATCARINFO B
GROUP BY B.COMPANYID
) Y
WHERE X.COMPANYID = Y.COMPANYID
(select count(*) num,companyid from gps_log_hazmatcar a,hazmatcarinfo b
where a.Plateno=b.Plateno(+) and in_date>=to_date('2009-06-19', 'yyyy-MM-dd') and in_date<to_date('2009-06-19', 'yyyy-MM-dd')-1
group by companyid) c,company d
where c.companyid=d.companyid
group by CompanyName
试试这个吧,应该快一些吧
where a.plateno=b.plateno
group by b.copanyid,to_char(in_date,'YYYY-MM-DD')