Return-Path: X-Original-To: apmail-commons-user-archive@www.apache.org Delivered-To: apmail-commons-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id E0B8A11B2E for ; Tue, 26 Aug 2014 16:37:27 +0000 (UTC) Received: (qmail 96339 invoked by uid 500); 26 Aug 2014 16:37:26 -0000 Delivered-To: apmail-commons-user-archive@commons.apache.org Received: (qmail 96230 invoked by uid 500); 26 Aug 2014 16:37:26 -0000 Mailing-List: contact user-help@commons.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: "Commons Users List" Delivered-To: mailing list user@commons.apache.org Received: (qmail 96219 invoked by uid 99); 26 Aug 2014 16:37:26 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 26 Aug 2014 16:37:26 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=5.0 tests=RCVD_IN_DNSWL_LOW,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (athena.apache.org: local policy) Received: from [209.85.216.54] (HELO mail-qa0-f54.google.com) (209.85.216.54) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 26 Aug 2014 16:37:22 +0000 Received: by mail-qa0-f54.google.com with SMTP id k15so13751084qaq.41 for ; Tue, 26 Aug 2014 09:37:01 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:date:from:to:cc:subject:message-id:in-reply-to :references:mime-version:content-type:content-transfer-encoding; bh=vV/E96lTHbTw48euBKxhL4llxkmVAaeg731xPrlYrWQ=; b=KI3sc5zzBibX9Yr/oojSZuGsGSkrB4lUcIx2fvCDbLXcW4+ijosHYXax+ySOlp1rca AlGVtifdGlo0IpEYXkNK5BSeDsJ7sL9mWGXQFvJmdxdcJHBJvAL5LAeX8UlovNYQChNW wf9QKo9pHtzkbP/ZIHLp7/aSa8kl33+VFpaCTL4E40wXpkN9EkoLXGbhwFEEOmrudNsj ACAIaBCJu56eIx/q4Y2Copa6+6/jHoOIC2f5aiLYetufIuYNVamWMsX2pxa3NwelypQM hclXZdDCHt5E2JVGiMFW+DpOLhFhHf7zKwoXLqS5/0jFnb83hQgS8TvK/DiLYptszv3T TUiQ== X-Gm-Message-State: ALoCoQnLSBn8QPrTmAoil2uNcoPEHA86l8yv6coq4R4wJjpbDn/I0l/qj+oW9hJeo6mT0kdEVPFI X-Received: by 10.224.30.139 with SMTP id u11mr46122338qac.77.1409071021065; Tue, 26 Aug 2014 09:37:01 -0700 (PDT) Received: from localhost (HSI-KBW-109-193-068-033.hsi7.kabel-badenwuerttemberg.de. [109.193.68.33]) by mx.google.com with ESMTPSA id 4sm10803760qax.48.2014.08.26.09.36.59 for (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 26 Aug 2014 09:37:00 -0700 (PDT) Date: Tue, 26 Aug 2014 18:36:57 +0200 From: Bernd Eckenfels To: Commons Users List Cc: "Kulpanowski, David" Subject: Re: [math] JDBC output to generate statistical results. Message-ID: <20140826183657.000010d2.ecki@zusammenkunft.net> In-Reply-To: References:

<53fc9c94.d4568c0a.56e5.ffffd21d@mx.google.com>

MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Virus-Checked: Checked by ClamAV on apache.org Hello David, if you download and include commons-lang3.jar in your classpath Eclipse will recognize ArrayUtils and allow you to import org.apache.commons.lang3. Here is the Javadoc for it: http://commons.apache.org/proper/commons-lang/javadocs/api-release/index.ht= ml Greetings Bernd BTW: Commons Developers: I do wonder if this would be a good feature for dbutils. It has currently a RowProcessor, but that works either in Object[] or needs to map to beans. Returning a simple type array for a single column might be usefull? Am Tue, 26 Aug 2014 11:37:12 -0400 schrieb "Kulpanowski, David" : > Messrs. Worden and Eckenfels: >=20 > Thank you both for your kind assistance.=20 >=20 > Mr. Worden:=20 > your solution works perfectly. This is exactly what I am looking for.=20 >=20 > Mr. Eckenfels: > Please excuse my lack of java coding skills. I am working on it by > taking on projects at my job. I think your solution will work and I > want to use it in my code because I am now going to use Apache > Commons Math for more sophisticated statistics such as regression and > hypothesis testing. For example, is the mean average ambulance > response time in Cape Coral the statistically significantly different > from the mean average response time in Fort Myers. I anticipate > needing your code so I need to ask for additional help: >=20 > In the final line of code Eclipse is putting a red underline under > ArrayUtils.=20 >=20 > ArrayList times =3D new ArrayList<>(); > while (rset.next()) > { > times.add(Double.valueOf(rset.getDouble("M_SecondsAtStatus"))); > } > double timesArray[] =3D ArrayUtils.toPrimitive(times.toArray()); >=20 > My mouse hovers over it and the message is: "ArrayUtils cannot be > resolved". Eclipse offers nine quick fixes: > 1.) create class ArrayUtils. > 2.) create constant ArrayUtils > 3.) create local variable ArrayUtils > 4.) change to ArgUtils > 5.) change to Array > 6.) change to Arrays > 7.) create field ArrayUtils > 8.) create parameter ArrayUtils > 9.) fix project set up >=20 > Which one should I use to output my data in a format Apache Commons > Math will utilize in its functions? >=20 >=20 > -----Original Message----- > From: Brent Worden [mailto:brent.worden@gmail.com]=20 > Sent: Tuesday, August 26, 2014 11:00 AM > To: Commons Users List > Subject: Re: [math] JDBC output to generate statistical results. >=20 > Another alternative is to use a > org.apache.commons.math3.stat.descriptive.DescriptiveStatistics > object to collect all the data and then use it to compute the summary > statistics you need. Using it alleviates the need for doing all > explicit type casting and conversion: >=20 > DescriptiveStatistics ds =3D new DescriptiveStatistics(); > while(rset.next()) { > int observation =3D rset.getInt("M_SecondsAtStatus"); > ds.addValue(observation); > } >=20 > System.out.println("min: " + ds.getMin()); > System.out.println("max: " + ds.getMax()); ... >=20 > HTH, >=20 > Brent >=20 >=20 > On Tue, Aug 26, 2014 at 9:41 AM, Bernd Eckenfels > wrote: >=20 > > Hello, > > > > First of all: Your DBMS might have SQL methods to calculate typical=20 > > aggregates. This is not only easier to program, but also most > > likely faster and less resource intensive than doing it in an extra > > application. > > > > But since this is the commons list: If You want to use the Commons=20 > > Math functions you have to present the set of values (in your case > > as an array). And since there is no adapter for result sets (I > > think) building the array would be done inside the loop. The most > > natural thing is to use an ArrayList to append the values in the > > loop, but then you have to convert the resulting Double[] into > > double[]. The ArrayUtils in Apache Commons Lang could do that (but > > if you need to process millions of numbers it is not the most > > efficient way to do it). > > > > untested: > > > > ArrayList times =3D new ArrayList<>(); > > while(rset.next()) { > > times.add(Double.valueOf(rset.getDouble(T)); > > } > > double timesArray[] =3D ArrayUtils.toPrimitive(times.toArray()); > > > > And then you can use this array for the Math statistics. > > > > Gruss > > bernd > > > > > > -- > > http://bernd.eckenfels.net > > > > ----- Urspr=C3=BCngliche Nachricht ----- > > Von: "Kulpanowski, David" > > Gesendet: =E2=80=8E26.=E2=80=8E08.=E2=80=8E2014 15:55 > > An: "Commons Users List" > > Betreff: RE: [math] JDBC output to generate statistical results. > > > > Thank you Mr. Ritter: > > > > Two issues: > > 1.) I am attempting to obtain univariate statistics from thousands > > of ambulance responses. For example, ambulance responses (in > > seconds) 534, 678, 943, 194 would be a mean of 587 seconds. Not by > > row, but rather as summary statistics. > > 2.) It appears that Apache Commons Math is needing a Double value. > > So I change it as shown below. > > Note on 2) Even though I am needing summary statistics I move the=20 > > lines of code into the loop just to see what would happen.I just > > want to get it to work because it appears the problem is the type > > of variable (int, double, array). > > > > while (rset.next()) > > { > > double values =3D=20 > > rset.getDouble("M_SecondsAtStatus"); > > System.out.println(values); > > System.out.println("min: " +=20 > > StatUtils.min(values)); > > System.out.println("max: " +=20 > > StatUtils.max(values)); > > System.out.println("mean: " +=20 > > StatUtils.mean(values)); > > System.out.println("product: " +=20 > > StatUtils.product(values)); > > System.out.println("sum: " +=20 > > StatUtils.sum(values)); > > System.out.println("variance: " +=20 > > StatUtils.variance(values)); > > } > > > > A red underline in Eclipse shows up and my mouse hovers over it. > > The error message is the following: > > > > "The method min(double[]) in the type StatUtils is not applicable > > for the arguments (double)" > > > > I then change the values variable to double[] as shown below: > > > > "double[] values =3D rset.getDouble("M_SecondsAtStatus");" > > > > java doesn't like this either. It gives a red underlined error > > message: "Type mismatch: cannot convert from double to double[]" > > > > > > I guess this boils down to two questions: > > 1.) How do I output a double[] array from database output? > > 2.) How do I output this double[] into a variable that Apache > > Commons Math will accept? > > ok, maybe three questions: > > 3.) Other people are using Apache Commons Math to understand their=20 > > database data better. How are they doing it? A lot of guys have=20 > > massive mainframe databases filled with health care data etc. They > > are doing sophisticated math with their data. How are they doing it? > > > > -----Original Message----- > > From: Benedikt Ritter [mailto:britter@apache.org] > > Sent: Tuesday, August 26, 2014 9:15 AM > > To: Commons Users List > > Subject: Re: [math] JDBC output to generate statistical results. > > > > > In you're code the variable values is defined within the scope of=20 > > > the > > while loop. > > > > D'oh worst of typos... should be "in your code" of corse ;-) > > > > 2014-08-26 15:13 GMT+02:00 Benedikt Ritter : > > > > > Hello David, > > > > > > the problem you're encountering is a problem with scopes. A > > > variable is only available in the scope it was defined. In you're > > > code the variable values is defined within the scope of the while > > > loop. This means, that the variable is only defined between the > > > curly brackets of > > the while loop. > > > > > > Your System.out statements try to access the values variable, > > > which is no longer accessible, since the flow of control has > > > already left the scope it was definied in (by finishing the > > > iteration over the ResultSet). > > > > > > What you need to do is move the other System.out statements into > > > the loop like so: > > > > > > > > > while (rset.next()) > > > { > > > int values =3D > > > rset.getInt("M_SecondsAtStatus"); System.out.println(values); > > > > > > > > > // I am hoping to derive useful > > > statistics from my database, such as > > > // the following.this uses Jakarta > > > Commons Math System.out.println("min: " +=20 > > > StatUtils.min(values)); > > > System.out.println("max: " +=20 > > > StatUtils.max(values)); > > > System.out.println("mean: " +=20 > > > StatUtils.mean(values)); > > > System.out.println("product: " +=20 > > > StatUtils.product(values)); > > > System.out.println("sum: " +=20 > > > StatUtils.sum(values)); > > > System.out.println("variance: " +=20 > > > StatUtils.variance(values)); > > > } > > > > > > > > > This way statistics will be printed for each row in the result > > > set. > > > > > > Regards, > > > Benedikt > > > > > > P.S.: Jakarta is an old name, that is not used any more. The name > > > of the project now is simple Apache Commons and you're using > > > Apache Commons > > Math. > > > > > > > > > 2014-08-26 15:03 GMT+02:00 Kulpanowski, David > > > : > > > > > > Using jdbc I am querying my database of ambulance response times. > > > My goal > > >> is to take the output and process it into statistics using > > >> Jakarta Commons Math library. So far I am successful in querying > > >> my database and outputting the response times to the console. My > > >> next step is to process this output statistically, such as mean, > > >> medians, mode, etc. > > This is where I am stuck. > > >> What I can't figure out is how to get my database output into a=20 > > >> format for Commons Math to generate a statistical analysis. In=20 > > >> other words, I have > > >> 100,000 ambulance responses, now I want to do more advanced=20 > > >> statistical analysis with this data. > > >> Shown below is my code. > > >> > > >> package javaDatabase; > > >> > > >> import java.sql.*; > > >> import org.apache.commons.math3.stat.StatUtils; > > >> > > >> public class javaConnect4 > > >> { > > >> public static void main(String[] args) > > >> { > > >> Connection conn =3D null; > > >> Statement stmt =3D null; > > >> try > > >> { > > >> conn =3D DriverManager > > >> > > >> > > .getConnection("jdbc:sqlserver://myServerAddress;database=3DmyDatabase;i > > ntegratedsecurity=3Dfalse;user=3DmyUser;password=3DmyPassword"); > > >> stmt =3D conn.createStatement(); > > >> String strSelect =3D "SELECT M_SecondsAtStatus=20 > > >> FROM MManpower WHERE M_tTime > 'august 25, 2014' AND M_Code =3D=20 > > >> 'USAR'"; > > >> > > >> ResultSet rset =3D > > >> stmt.executeQuery(strSelect); > > >> > > >> while (rset.next()) > > >> { > > >> int values =3D > > rset.getInt("M_SecondsAtStatus"); > > >> System.out.println(values); > > >> } > > >> > > >> // I am hoping to derive useful statistics > > >> from my database, such as > > >> // the following.this uses Jakarta Commons > > >> Math System.out.println("min: " + StatUtils.min(values)); > > >> System.out.println("max: " + > > >> StatUtils.max(values)); System.out.println("mean: " + > > StatUtils.mean(values)); > > >> System.out.println("product: " +=20 > > >> StatUtils.product(values)); > > >> System.out.println("sum: " + > > >> StatUtils.sum(values)); System.out.println("variance: " +=20 > > >> StatUtils.variance(values)); > > >> > > >> } catch (SQLException ex) > > >> { > > >> ex.printStackTrace(); > > >> } finally > > >> { > > >> try > > >> { > > >> if (stmt !=3D null) > > >> stmt.close(); > > >> if (conn !=3D null) > > >> conn.close(); > > >> } catch (SQLException ex) > > >> { > > >> ex.printStackTrace(); > > >> } > > >> } > > >> } > > >> } > > >> > > >> > > >> An error message pops up in Eclipse and the variable "values" is=20 > > >> red underlined; "values cannot be resolved to a variable". > > >> I am not sure how to get this to work. > > >> I don't understand how to output my ambulance response times > > >> from the database into something Apache Commons math will > > >> understand. How can I get Apache Commons math to take the output > > >> from my database and generate a statistical result?. > > >> > > >> > > >> NOTES: > > >> 1.) I have cross-posted this question on StackOverflow.com but > > >> have not resolved the issue. > > >> 2.) I have verified that Apache Commons Math is registered in my=20 > > >> project by hand coding a small array and using Commons Math to=20 > > >> generate > > statistics. > > >> So Apache Math works and my database output goes to the console=20 > > >> window, so it works also. But how do you get them to work > > >> together? 3.) I am a geographer, not a computer programmer. > > >> Believe me, you cannot make it simple enough. Please be explicit > > >> in your answers. > > >> > > >> David Kulpanowski > > >> Database Analyst > > >> Lee County EMS > > >> PO Box 398 > > >> Fort Myers, FL 33902-0398 > > >> 239-533-3962 > > >> DKulpanowski@Leegov.com > > >> Longitude: -81.861486 > > >> Latitude: 26.528843 > > >> > > >> > > >> ________________________________ > > >> Please note: Florida has a very broad public records law. Most=20 > > >> written communications to or from County Employees and officials=20 > > >> regarding County business are public records available to the=20 > > >> public > > and media upon request. > > >> Your email communication may be subject to public disclosure. > > >> > > >> Under Florida law, email addresses are public records. If you do=20 > > >> not want your email address released in response to a public=20 > > >> records request, do not send electronic mail to this entity.=20 > > >> Instead, contact this office by phone or in writing. > > >> > > > > > > > > > > > > -- > > > http://people.apache.org/~britter/ > > > http://www.systemoutprintln.de/ > > > http://twitter.com/BenediktRitter > > > http://github.com/britter > > > > > > > > > > > -- > > http://people.apache.org/~britter/ > > http://www.systemoutprintln.de/ > > http://twitter.com/BenediktRitter > > http://github.com/britter > > >=20 > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-unsubscribe@commons.apache.org > For additional commands, e-mail: user-help@commons.apache.org >=20 --------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscribe@commons.apache.org For additional commands, e-mail: user-help@commons.apache.org