simple SQL question
От | Kevin Duffy |
---|---|
Тема | simple SQL question |
Дата | |
Msg-id | DFC309C8A42633419600522FA8C4AE1AB6C154@mail-01.wrcapital.corp обсуждение исходный текст |
Список | pgsql-sql |
<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>
В списке pgsql-sql по дате отправления: