All Forums Database
sunny.j 90 posts Joined 10/10
01 Feb 2012
optimizing the Select Query

Hi

 

i would like to re - write the below mentioned query to a single query without any datasets to create.

as it is taking so much of time to execute, any suggestion to adapt the query only ina single select sql statement

SEL a.PGW_IP_ADDRESS, a.PDN_CONNECTION_ID, b.COUNT_DISTINCT_CDR_NR, a.MAX_SEQ_NR

FROM

(

SEL

PGW_IP_ADDRESS,

PDN_CONNECTION_ID,

CDR_SEQUENCE_NR,

MAX(CDR_SEQUENCE_NR) OVER (PARTITION BY PGW_IP_ADDRESS,PDN_CONNECTION_ID) AS MAX_SEQ_NR

FROM dwhd.MEDIATED_4G_DATA

WHERE BLOCK_RATING_GROUP<>0

) a

INNER JOIN

(

SEL PGW_IP_ADDRESS,PDN_CONNECTION_ID,COUNT(DISTINCT CDR_SEQUENCE_NR) AS COUNT_DISTINCT_CDR_NR

FROM dwhd.MEDIATED_4G_DATA

WHERE BLOCK_RATING_GROUP<>0

GROUP BY 1,2

) b

ON a.PGW_IP_ADDRESS = b.PGW_IP_ADDRESS AND a.PDN_CONNECTION_ID=b.PDN_CONNECTION_ID

ORDER BY 1,2 ASC

GROUP BY 1,2,3,4

ulrich 816 posts Joined 09/09
01 Feb 2012

 

I am not sure if I oversimplyfy but isn't this the same?

 

SEL PGW_IP_ADDRESS,

       PDN_CONNECTION_ID,

       COUNT(DISTINCT CDR_SEQUENCE_NR) AS COUNT_DISTINCT_CDR_NR,

        MAX(CDR_SEQUENCE_NR) as MAX_SEQ_NR

FROM dwhd.MEDIATED_4G_DATA

WHERE BLOCK_RATING_GROUP<>0

GROUP BY 1,2

ORDER BY 1,2 ASC

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

sunny.j 90 posts Joined 10/10
01 Feb 2012

Need every combination of PGW_IP_ADDRESS,PDN_CONNECTION_ID  columns count and maximum CDR_SEQUENCE_NR .     

ulrich 816 posts Joined 09/09
01 Feb 2012

Did you run the query I suggested?

I think it will do what you want.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

sunny.j 90 posts Joined 10/10
02 Feb 2012

IF I WANT THE SECOND  MAXIMUM CDR_SEQUENCE_NR , THEN HOW TO CONVERT THIS WITHOUT ANY SUB DATASETS.

ulrich 816 posts Joined 09/09
02 Feb 2012

 

DON'T SHOUT AT PEOPLE! 

 

And I don't think this will work for the second maximum without derived tables (if it is not simply max -1).

Also this requirement is incomplete as we don't know if the CDR_SEQUENCE_NR can occur multiple times for a GW_IP_ADDRESS, PDN_CONNECTION_ID combination. You would need to give us much more infos about the table content and example data.

But maybe it is worth for you to spend some time on studying the SQL manuals of aggregate and OLAP functions.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.