develooper Front page | perl.beginners | Postings from March 2002

Re: Can I do this with perl?

Thread Previous | Thread Next
From:
Jenda Krynicky
Date:
March 4, 2002 10:11
Subject:
Re: Can I do this with perl?
Message ID:
3C83C6C9.16678.18AE1BB@localhost
From: Chas Owens <cowens@intercall.com>
> On Mon, 2002-03-04 at 10:32, Brett W. McCoy wrote:
> > On Mon, 4 Mar 2002, Dave Adams wrote:
> > 
> > > I have a problem which I would like to use perl to resolve,
> > > however I'm not sure if it is possible to do.
> > >
> > > I need to scan a file and check some conditions, first if field 9
> > > is duplicated on 1 or more rows, then I need to check field 10 to
> > > see which is the greater value and then only print the whole row
> > > where field 10 is the greater, if field 9 is not a duplicate then
> > > print the whole row. An example of the data is below.
> > >
> > > 28525|U2|4CY0|50|6775.15|2002-02-07|10461|321.43|102040724|1020407
> > > 73|
> > > 28526|U2|4CY0|25|3571.78|2002-02-07|6107|167.74|102040774|10204079
> > > 8|
> > > 28527|U2|4CY0|50|6930.3|2002-02-07|11376|324.12|102040774|10204082
> > > 3|
> > > 28528|U2|4CY0|25|4640.28|2002-02-07|4800|217.43|102040824|10204084
> > > 8|
> > > 28529|U2|4CY0|50|8432.05|2002-02-07|9023|392.03|102040824|10204087
> > > 3|
> > 
> > Of course this can be done with Perl, although the algorithm will
> > take some thinking through.  You should be able to read these rows
> > into an array (using split, et al).
> > 
> > However, this will be much easier if you can get this into a real
> > database system so you can use DBI.  In fact, a text file like this
> > can even be used with DBI -- take a look at DBD:CSV.  It lets you
> > create and manipulate tables via SQL, which would make your problem
> > much simpler (although DBD::CSV may have problems enforcing unique
> > constraints). Otherwise, if you have a good bit of data to deal
> > with, take a look at a database system like PostgreSQL, which can
> > easily handle the logic you are trying to implement.
> > 
> > -- Brett
> 
> As a DBA I disagree.  Text processing is often faster and easier to
> write. 

Depends. If you can go through the data once and mungle it as you 
go it will definitely be quicker if you do it from within Perl instead of 
pushing it into database and selecting something.

In this case though it's not that easy. The problem is that the 
original request was "if field 9 is duplicated on 1 or more rows, then 
I need to check field 10 to see which is the greater value and then 
only print the whole row where field 10 is the greater, if field 9 is not 
a duplicate then print the whole row."

You could do that from within Perl, you just had to remember the 
rows you've read in a hash, check whether you've seen a row with 
the same Field_9, remember the one with greater Field_10 and go 
on. And you'd only started printing when done with reading (I'm 
assuming the data are NOT sorted on Field_9 !!!)

Or you could sort the data on Field_9 (any direction you like) and 
Field_10 (DESCending preferably) and then only print the first row 
with the same Field_9.

On the other hand if you had the data in the database you could 
write this:

SELECT Field_1, Field_2, Field_3, Field_4, Field_5, Field_6, 
Field_7, Field_8, Field_9, max(Field_10), Field_11
FROM MyData 
GROUP BY Field_1, Field_2, Field_3, Field_4, Field_5, Field_6, 
Field_7, Field_8, Field_9, Field_11

or

SELECT * 
FROM MyData, (SELECT Field_9, Max(Field_10) as Max_Field_10
	FROM MyData
	Group BY Field_9
) as Max_MyData
WHERE MyData.Field_9 = Max_MyData.Field_9
   AND MyData.Field_10 = Max_MyData.Max_Field_10

No need for cursors ... but if there is not an index on Field_9 it'll be 
terribly slow.

But still if you only need to do this it's not worth it to push the data 
into a database.

Jenda

=========== Jenda@Krynicky.cz == http://Jenda.Krynicky.cz ==========
There is a reason for living. There must be. I've seen it somewhere.
It's just that in the mess on my table ... and in my brain.
I can't find it.
					--- me

Thread Previous | Thread Next


nntp.perl.org: Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at ask@perl.org | Group listing | About