Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Whether to perl or oracle

by tradez (Pilgrim)
on Feb 27, 2002 at 17:25 UTC ( #147977=perlquestion: print w/ replies, xml ) Need Help??
tradez has asked for the wisdom of the Perl Monks concerning the following question:

Fellow Monks, I come to you with limited sql power and mediocre perl knowledge asking you for your help in finishing this question. I am attempting to get a count of users in a database that fall into one of three categories. It is a decibel recording DB that keeps track at the levels at which modems are speaking on a RF network, in dB of course. The 3 groups are less then 30, 30-40, and 40 plus dB.
The current way I am doing it is with a mixture of the following sql:
my $sql = "select power_level, decode(power_level,null,'NA',decode(sig +n(power_level-30),-1,'1', decode(sign(power_level-40),1,'2','3'))) from wbr_power where host_name in ( select host_name from hosts where market_id = '$market' )";
And the following perl doing simple iterations on the array returned :
my ($lessThan, $inBetween, $greaterThan) = 0; foreach my $code (@powerCodes) { if ($code->[1] eq '1'){ $lessThan++; }elsif ($code->[1] eq '2'){ $inBetween++; }elsif ($code->[1] eq '3'){ $greaterThan++; } }
There must be someway through joins and count() or sum() to do this all on the oracle end. But like I said, still pretty wet in the SQL relm. Please help.


Tradez
"Never underestimate the predicability of stupidity"
- Bullet Tooth Tony, Snatch (2001)

Comment on Whether to perl or oracle
Select or Download Code
Re: Whether to perl or oracle
by VSarkiss (Monsignor) on Feb 27, 2002 at 18:17 UTC

    Personally, I wouldn't put any of the binning logic in the SQL at all. In other words, retrieve the power levels unchanged from the database, then calculate the subtotals in Perl. You already have code in your Perl program to count the number of occurrences, except now it's much harder to see what's going on, because the logic behind the calculation is split between the Perl and the SQL (and is rather obscure in the SQL, IMHO).

    It's hard to say whether you could improve the SQL without knowing more about the database design. My first impulse was to recommend joining wbr_power and hosts rather than using a subquery, but that may get you multiple rows if the hosts table is not unique on host_name. More information about the data model would help to tune up your SQL without introducing errors. ;-)

    HTH

      Sorry about not giving more info on the particular normilization, I know what it is like to try and answer these obscure questions without all the info. It is infact unique on the host_name, but I think the subquery is a simple and efficient way of doing what I need. Thanks for the input though!

      Tradez
      "Never underestimate the predicability of stupidity"
      - Bullet Tooth Tony, Snatch (2001)
Re: Whether to perl or oracle
by mr.dunstan (Monk) on Feb 27, 2002 at 18:46 UTC
    I end up getting stuck on these all the time. The people at my work who are SQL gurus tell me to use SQL, to "leverage the power of the database" - but the perl folk say "use perl, it's way easier, plus you can do offline analysis of the data and take some of the work -off- the database."

    I say use perl!

    -mr.dunstan
      Yeah, I am always a big fan of simple perl. Thanks for your thought.

      Tradez
      "Never underestimate the predicability of stupidity"
      - Bullet Tooth Tony, Snatch (2001)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://147977]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (15)
As of 2014-10-24 15:44 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (132 votes), past polls