CI CARRIERID CDMASERVINGONEWAYDELAY
10101 1 19
10101 2 19
10101 2 19
10101 1 20
10101 1 20
10101 1 20
10101 1 20
10123 2 20
10101 2 20
10123 2 20
10101 2 20
10123 1 21
10101 1 21
10101 1 21
10101 1 21
10101 2 21
10101 2 21
10101 2 34
10101 2 21
10101 2 21
......
以上表,有900万行数据,我想按CI,CARRIERID分组,提取CDMASERVINGONEWAYDELAY排序前95%的的数据,以下远行的速度相当慢,1个小时都没有运行完,SQL语句如何写,谢谢!SELECT * FROM WY_WYPT.DBO.CDT AS A
WHERE CDMASERVINGONEWAYDELAY IN(SELECT TOP 95 PERCENT CDMASERVINGONEWAYDELAY
FROM WY_WYPT.DBO.CDT AS B WHERE B.CI=A.CI AND A.CarrierID=B.CarrierID ORDER BY CDMASERVINGONEWAYDELAY DESC)
10101 1 19
10101 2 19
10101 2 19
10101 1 20
10101 1 20
10101 1 20
10101 1 20
10123 2 20
10101 2 20
10123 2 20
10101 2 20
10123 1 21
10101 1 21
10101 1 21
10101 1 21
10101 2 21
10101 2 21
10101 2 34
10101 2 21
10101 2 21
......
以上表,有900万行数据,我想按CI,CARRIERID分组,提取CDMASERVINGONEWAYDELAY排序前95%的的数据,以下远行的速度相当慢,1个小时都没有运行完,SQL语句如何写,谢谢!SELECT * FROM WY_WYPT.DBO.CDT AS A
WHERE CDMASERVINGONEWAYDELAY IN(SELECT TOP 95 PERCENT CDMASERVINGONEWAYDELAY
FROM WY_WYPT.DBO.CDT AS B WHERE B.CI=A.CI AND A.CarrierID=B.CarrierID ORDER BY CDMASERVINGONEWAYDELAY DESC)
WHERE CDMASERVINGONEWAYDELAY not IN(SELECT TOP 5 PERCENT CDMASERVINGONEWAYDELAY
FROM WY_WYPT.DBO.CDT AS B WHERE B.CI=A.CI AND A.CarrierID=B.CarrierID ORDER BY CDMASERVINGONEWAYDELAY )换种方式试试
FROM (
SELECT RowID=Row_Number() OVER(PARTITION BY a.CI,a.CarrierID ORDER BY a.CDMASERVINGONEWAYDELAY DESC)
,RowIDMax=COUNT(*) OVER(PARTITION BY a.CI,a.CarrierID)
,a.CI,a.CarrierID,a.CDMASERVINGONEWAYDELAY
FROM WY_WYPT.DBO.CDT AS a
) AS a
where (RowID+0.0)/RowIDMax*100<=95