best strategy for histogram preparation

[Franco Vaccari]

Excellent! Will look into it

Thanks

Franco

On Jul 12, 2013, at 1:51 PM, Bart Pietercil <bart.pietercil at cognosis.be> wrote:

> Hi Franco,
> 
> without spending too much time on this case, it think you need to take a look here:
> 
> http://www.valentina-db.com/docs/dokuwiki/v5/doku.php?id=valentina:vcomponents:vsql:reference:pivot_table_commands&s[]=pivot
> 
> hth
> 
> Bart
> 
> 
> On 12 Jul 2013, at 12:45, Franco Vaccari <vaccari at dst.units.it> wrote:
> 
>> I'd like to ask what is the best strategy to extract data from a database for producing a histogram. I'm using Xojo ADK.
>> 
>> The way I do it now is to call  SQLselect  in a loop to extract subsets of data and count the records of each subset (see the part // loop over histogram classes in the code below). For histograms made on the values stored in field M1, statements in the loop come out like this:
>> 
>> …
>> …
>> 
>> select recID,M1,catLink from "Data" where "catLink" = 6 and "M1" >= 5 and "M1" < 5.5
>> select recID,M1,catLink from "Data" where "catLink" = 6 and "M1" >= 5.5 and "M1" < 6
>> select recID,M1,catLink from "Data" where "catLink" = 6 and "M1" >= 6 and "M1" < 6.5
>> …
>> …
>> 
>> For each query I count the records of the resulting cursor, and use the counts for each range to produce the histogram (e.g. 134, 72, 88, etc, plotted as bars).
>> 
>> It works, but it requires a lot of queries if the number of histogram classes is large. Is there a single SQL command that can produce the same result more efficiently? I mean, return a cursor with  as many fields as the number of histogram classes are, each field containing the count of samples falling in the range? Of course it should be possible to adjust the range definition (i.e. divide the population into 10, 50, 100 etc classes). 
>> 
>> Thanks for any hint
>> 
>> Franco  
>> 
>> =========
>> 
>> Code:
>> 
>> // inSQLWhereString: the part of the SQL statement that select the ranges of time, coords and depth
>> // inMmin: the minimum amplitude from where to start the grouping
>> // inMmax: the maximum amplitude for the grouping
>> // inMstep: the step of the grouping
>> 
>> dim ds(-1) as DataSet1D
>> dim tmpCursor as vCursor
>> …
>> …
>> 
>> // build the SQL part for selecting values of M1 within ranges
>> 
>>        dim j, jStart, jStop,jStep as integer
>> 
>>        jStart=round(inMmin*100)
>>        jStop=round(inMmax*100)
>>        jStep=round(inMstep*100)
>> 
>> // loop over histogram classes
>> 
>>        for j = jStart to jStop step jStep
>> 
>>          SQLstring=SQLbaseString+ SQLcatLinkString+_
>>            " and " + DoubleQuoted("M1") + " >= " + str(j*0.01)+_
>>            " and " + DoubleQuoted("M1") + " < " + str((j+jStep)*0.01) // interval number
>> 
>>          tmpCursor=mDatabase.SqlSelect(SQLstring,EVCursorLocation.kServerSide,EVLockType.kNoLocks,EVCursorDirection.kRandom)
>> 
>>          dim n as integer = tmpCursor.RecordCount
>>          tmpCursor=nil
>> 
>>          ds(UBound(ds)).AppendValue n
>>          ds(UBound(ds)).xLabel.append str(j*0.01)
>> 
>>        next // loop over histogram classes
>> 
>>      end if
>> …
>> …
>> 
>> 
>> return ds
>> _______________________________________________
>> Valentina mailing list
>> Valentina at lists.macserve.net
>> http://lists.macserve.net/mailman/listinfo/valentina
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina