All Forums Database
Shock 7 posts Joined 03/13
27 Jan 2014
RANGE in INTEGER data fields

hi, i've got this table with the field ampcputime, which is a integer and used the following text in a query

WHEN ampcputime between '0' and '999' then '0'
			WHEN ampcputime between '1000' and '1999' then '1'
			WHEN ampcputime between '2000' and '2999' then '2'
			WHEN ampcputime between '3000' and '3999' then '3'
			WHEN ampcputime between '4000' and '4999' then '4'
			WHEN ampcputime between '5000' and '5999' then '5'
			WHEN ampcputime between '6000' and '6999' then '6'
			WHEN ampcputime between '7000' and '7999' then '7'
			WHEN ampcputime between '8000' and '8999' then '8'
			WHEN ampcputime between '9000' and '9999' then '9'

 

I'm sure there's a more useful way to divide the data using INTERVAL and RANGE, but i'm not finding any example that don't use partitions.
Can someone point me in the right direction?

 

Raja_KT 1246 posts Joined 07/09
27 Jan 2014

Not able to understand your requirement. Can you please put in the same format as you put (WHEN.........'9')  and what you want in the same format.
Cheers,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Shock 7 posts Joined 03/13
27 Jan 2014
CASE WHEN ampcputime between '0' and '999' then '0'
            WHEN ampcputime between '1000' and '1999' then '1'
            WHEN ampcputime between '2000' and '2999' then '2'
            WHEN ampcputime between '3000' and '3999' then '3'
            WHEN ampcputime between '4000' and '4999' then '4'
            WHEN ampcputime between '5000' and '5999' then '5'
            WHEN ampcputime between '6000' and '6999' then '6'
            WHEN ampcputime between '7000' and '7999' then '7'
            WHEN ampcputime between '8000' and '8999' then '8'
            WHEN ampcputime between '9000' and '9999' then '9'
ELSE '-1'
END as cpusec

Instead of declaring each interval by myself, i just want to declare WHEN ampcputime between 0 and 999 (only the first), then increase the interval 1000 by 1000

dnoeth 4628 posts Joined 11/04
27 Jan 2014

What's the dataype of ampcputime?
If it's a string then this CASE will not returning the answer you expect as every value will not get past the first WHEN thus returning 0.
And if it's an INT you should compare it to numeric values instead of strings :-)
 
 
In your case you could do a simple CAST(ampcputime AS INT) / 1000
For equally sized buckets there's also:

RANGE_N (ampcputime BETWEEN 0 AND 9999 EACH 1000)
or
WIDTH_BUCKET(ampcputime_, 0,10000, 10)

will return values between 1 and 10.
Data outside of the range will be handled differenty:
WIDTH_BUCKET returns 0 for ampcputime < 0 and 11 for >= 10000
RANGE_N returns NULL
 
 
 

Dieter

Shock 7 posts Joined 03/13
29 Jan 2014

As I stated in the object of the thread, the datatype is INTEGER.
Thank you for the insight of WIDTH_BUCKET and for the RANGE_N solution :)
I wasn't sure about using RANGE_N without the involvement of partitions, but seems like it worked pretty fine
(My solution btw uses EACH 999, since i'm not counting the next thousands in the unit range)

Thank you again

You must sign in to leave a comment.