Thread: simple SQL question

simple SQL question

From

"Kevin Duffy"

Date:

20 March 2009, 19:06:22

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hello All:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I would like your input on how I should approach a problem.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Say I have a table of companies and one attribute is the market capitalization of these
companies.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">I have another table (definition below) and it contains capitalization levels. </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">For example Micro Cap, Mid Cap, and Large Cap.    However, the table </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">CAPITALIZATIONLEVEL, only contains the upper cutoff of the levels.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The question is: What is the most efficient way to assign/join the capitalization levels to the
companies?</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I could create a function that given a market cap in millions would return the matching cap
level,</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">by using a cursor to step through CAPITALIZATIONLEVEL  from lowest to highest. </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">This function would be declared STABLE.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">-or maybe-</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">a function that RETURNS SETOF and the rows in the set returned would contain both the lower and
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">upper limits of the cap level.  The lower limit would be calc’ed  by using a cursor to step through
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">CAPITALIZATIONLEVEL  from lowest to highest.  This function would be declared
STABLE.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Which method would execute more efficiently?  </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks for considering my issue.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">KevinDuffy</span></font><p class="MsoNormal"><font face="Times New Roman"
size="3"><spanstyle="font-size: 
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt"> </span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">CREATE TABLE capitalizationlevel</span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial">(</span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">  capitallevelkey serial NOT NULL,</span></font><p class="MsoNormal"><font face="Arial"
size="3"><spanstyle="font-size:12.0pt; 
font-family:Arial">  caplevelname character(10) NOT NULL,</span></font><p class="MsoNormal"><font face="Arial"
size="3"><spanstyle="font-size:12.0pt; 
font-family:Arial">  caplevelmillions integer NOT NULL,     </span></font><font face="Wingdings"><span
style="font-family:Wingdings">ß</span></font><fontface="Arial"><span style="font-family:Arial"> this is the upper
limit</span></font><pclass="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">  CONSTRAINT pk_capitalizationlevel PRIMARY KEY (capitallevelkey)</span></font><p
class="MsoNormal"><fontface="Arial" size="3"><span style="font-size:12.0pt; 
font-family:Arial">)</span></font><p class="MsoNormal"><font face="Arial" size="3"><span style="font-size:12.0pt;
font-family:Arial">WITH (OIDS=FALSE);</span></font><p class="MsoNormal"><font face="Arial" size="3"><span
style="font-size:12.0pt;
font-family:Arial">ALTER TABLE capitalizationlevel OWNER TO postgres;</span></font></div>

Re: simple SQL question

From

"Kevin Duffy"

Date:

20 March 2009, 21:20:53

Mr. McFadyen:

Thanks for your quick reply.

The coloumns in the SECURITY Table of interest would be:

securitykey serial NOT NULL,

securitytypekey integer,

securitydesc character varying(125),

marketcap_usd numeric(19,6)

where marketcap_usd would be used to join into capitalizationlevel.

I was hoping to create something, how shall we say, tidy”.

So I could write something like

Select SECURITY.* , getcaplevelkey( marketcap_usd ) From security

Select security.*, caplevelkey, caplevelname

From SECURITY, getcaplevel() as gcl

Where marketcap_usd between ( gcl.caplow and gcl.caphigh )

I would expect that in the first option the function would be called for every security and would have to loop through a cursor for each call.

In the second option where the function is returning a SETOF, how many times would it be called?

If I put it in parentheses, how many times would it be called?

Any comments from the PostgresSQL gurus out there?

Happy Friday.

From: Dan McFadyen [mailto:danm@cryptocard.com]
Sent: Friday, March 20, 2009 3:51 PM
To: Kevin Duffy
Subject: RE: [SQL] simple SQL question

Hi again,

You got me curious so I went into my SQL browser and got cracking:

SELECT companyName, capName FROM

(

SELECT companyName, MIN(capitalizationlevel.caplevelmillions) FROM companies, capitalizationlevel WHERE companymillions < capitalizationlevel.caplevelmillions GROUP BY companyName

) as something JOIN capitalizationlevel ON something.min= capitalizationlevel.caplevelname

I don’t know what your company table looks like, so that’s the closest I can get.

Enjoy

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Kevin Duffy
Sent: March 20, 2009 3:06 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] simple SQL question

Hello All:

I would like your input on how I should approach a problem.

Say I have a table of companies and one attribute is the market capitalization of these companies.

I have another table (definition below) and it contains capitalization levels.

For example Micro Cap, Mid Cap, and Large Cap. However, the table

CAPITALIZATIONLEVEL, only contains the upper cutoff of the levels.

The question is: What is the most efficient way to assign/join the capitalization levels to the companies?

I could create a function that given a market cap in millions would return the matching cap level,

by using a cursor to step through CAPITALIZATIONLEVEL from lowest to highest.

This function would be declared STABLE.

-or maybe-

a function that RETURNS SETOF and the rows in the set returned would contain both the lower and

upper limits of the cap level. The lower limit would be calc’ed by using a cursor to step through

CAPITALIZATIONLEVEL from lowest to highest. This function would be declared STABLE.

Which method would execute more efficiently?

Thanks for considering my issue.

Kevin Duffy

CREATE TABLE capitalizationlevel

(

capitallevelkey serial NOT NULL,

caplevelname character(10) NOT NULL,

caplevelmillions integer NOT NULL, ß this is the upper limit

CONSTRAINT pk_capitalizationlevel PRIMARY KEY (capitallevelkey)

)

WITH (OIDS=FALSE);

ALTER TABLE capitalizationlevel OWNER TO postgres;

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. Please see our legal details at http://www.cryptocard.com
CRYPTOCard Inc. is registered in the province of Ontario, Canada with Business number 80531 6478. CRYPTOCard Europe is limited liability company registered in England and Wales (with registered number 05728808 and VAT number 869 3979 41); its registered office is Eden Park, Ham Green, Bristol, BS20 0EB