Ranking in SQL statements?
Moderator: General Moderators
Ranking in SQL statements?
ok I have data from stocks in a mysql db. From each stock's data, i want to create a rank/evaluation of the stock compared to the rest of the stocks in my db to find the "alpha male" of stocks. Could anyone give me an idea of how i could start approaching this?
I have 7 criteria that I want to use to evaluate/rank the stocks by. I figure the best way to do this is to look at one criteria for all stocks, look who has the most or the best ratios and give them points. after going through all seven criteria i would add each stocks points and presto! A rank system for stocks.
Any ideas of how to approach this would help!
I have 7 criteria that I want to use to evaluate/rank the stocks by. I figure the best way to do this is to look at one criteria for all stocks, look who has the most or the best ratios and give them points. after going through all seven criteria i would add each stocks points and presto! A rank system for stocks.
Any ideas of how to approach this would help!
This isn't even close to what a point system would look like, but since i'm a noob this is as far as i can comprehend in mysql, of course borrowing code from fellow forumers on devnetwork.
what i am trying to do is filter all the raw data i have in my curldata table, insert stocks into the temporary table, ranking, by evaluating stocks that have multipliers less than or equal to 17. then from the temporary table i can evaluate stocks through a point system.
Code: Select all
$queryarray = array("create temporary table"=>
"mysql_query('CREATE TEMPORARY TABLE ranking (stockid MEDIUMINT AUTO_INCREMENT PRIMARYKEY, ticker VARCHAR(6), points MEDIUMINT) ENGINE=MEMORY'); ",
"inserting stocks into table that have multiplier less than or equal to 17"=>
"mysql_query('IF EXISTS (SELECT ticker FROM curldata WHERE multiplier<=17) THEN UPDATE ranking SET ticker=***ticker from curldata table***;ELSE INSERT INTO ranking VALUES ***ticker from curldata***; END IF;')");Ranking question again
I know my "$mysql_tickname_insert ." variable is completely off track but this is what i'm trying correct. my error begins with the regular expression.
The ultimate goal of the sql statement is to rank the stocks, in ascending order by the given arguments of the WHERE section of the statement.
Could I apply the tutorial in the regex forum into the MYSQL regexp, or is MYSQL regex different? Anyways the objective of the regular expression is to check if the dividend of a stock actually has a price (i.e. 1.20 | .05).1064 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';UPDATE curldata SET rank=ASC_ORDER WHERE dividend = ([[:digit:]].[[:digit:]]|.[' at line 1
Code: Select all
$mysql_tickname_insert="UPDATE curldata SET dividend = '".$this->dividend[1]."', curass = '".$this->curass[1]."', multiplier = '".$this->multiplier[1]."', bookval = '".$this->bookval[1]."', long_term_debt = '".$this->long_term_debt[1]."', total_cur_liabilities = '".$this->liabilities[1]."', past_5_earnings = '".$this->past_5_earnings[1]."', future_5_earnings = '".$this->future_5_earnings[1]."' WHERE ticker='$this->ticker';";
$mysql_tickname_insert .="UPDATE curldata SET rank=ASC_ORDER WHERE dividend = ([[]].[[]]|.[[]]), multiplier <= 17, multiplier*bookval<=22.5, past_5_earnings>=16.5, annsales>=100, curass/long_term_debt >= 2;";So once again lets focus on my second part of my sql statement variable, I have slightly modified it.
My goal is to have a sql statement that ranks a stock in this order. I'll do my best to elaborate:
-----------------------------------------Determinants for types of stocks I want to rank------------------------------
Stock must have a dividend:
-I figure if I just put in a regular expression, it will select stocks with dividends with prices (i.e. (1.20 | .25)). Also does the REGEX tutorial found on this forum apply to MYSQL regex or not?
Annsales (annual sales) greater than or equal to (>=) 100:
- Annual sales should be greater than 100; does not impact a stock's rank whether it is higher or lower. This is just another determinant of what stocks I want just like the dividend test.
------------------------------------------Evaluations to rank stocks-----------------------------------------------------------
Multiplier must be less than or equal to (<=) 17:
-hence the section of my sql statement contains: The lower the multiplier, the higher the rank will be for that particular stock.
Multiplier times bookval should be less than or equal (<=) to 25.5:
- I multiply the multiplier and bookval column together for each stock and test to see if it is less than or equal to 25.5; the lower the number of this value, the better:
Past_5_earnings must be greater than or equal to (>=) 16.5:
- The earnings of the stock over the past 5 years must be greater than or equal to 16.5 or better; the higher the growth number the better the rank.
Curass (current assets) divided by long_term_debt greater than or equal to (>=) two:
-the current assets divided by long_term_debt should be a ratio greater than or equal to two; the greater the number the better the rank.
NOTE: I'm not asking for people to just give me the code, I want to be able to do this myself; the trouble is that I simply do not now how to say this in sql.
Code: Select all
$mysql_tickname_insert .="UPDATE curldata SET rank=ASC_ORDER WHERE dividend = ([[]].[[]]|.[[]]), multiplier <= 17, multiplier*bookval<=25.5, past_5_earnings>=16.5, annsales>=100, curass/long_term_debt >= 2;";-----------------------------------------Determinants for types of stocks I want to rank------------------------------
Stock must have a dividend:
-I figure if I just put in a regular expression, it will select stocks with dividends with prices (i.e. (1.20 | .25)). Also does the REGEX tutorial found on this forum apply to MYSQL regex or not?
Code: Select all
([[:digit:]].[[:digit:]]|.[[:digit:]])Annsales (annual sales) greater than or equal to (>=) 100:
- Annual sales should be greater than 100; does not impact a stock's rank whether it is higher or lower. This is just another determinant of what stocks I want just like the dividend test.
Code: Select all
annsales>=100Multiplier must be less than or equal to (<=) 17:
-hence the section of my sql statement contains:
Code: Select all
multiplier <= 17Multiplier times bookval should be less than or equal (<=) to 25.5:
- I multiply the multiplier and bookval column together for each stock and test to see if it is less than or equal to 25.5; the lower the number of this value, the better:
Code: Select all
multiplier*bookval<=22.5- The earnings of the stock over the past 5 years must be greater than or equal to 16.5 or better; the higher the growth number the better the rank.
Code: Select all
past_5_earnings>=16.5-the current assets divided by long_term_debt should be a ratio greater than or equal to two; the greater the number the better the rank.
Code: Select all
curass/long_term_debt >= 2NOTE: I'm not asking for people to just give me the code, I want to be able to do this myself; the trouble is that I simply do not now how to say this in sql.
A new error
this is the updated MYSQL statementError number: 1064 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''curldata' WHERE 'dividend' REGEXP '([[:digit:]].[[:digit:]]|.[[:digit:]])', 'mu' at line 1
Code: Select all
SELECT COUNT(ticker) AS rank FROM 'curldata'
WHERE 'dividend' REGEXP '([[:digit:]].[[:digit:]]|.[[:digit:]])', 'multiplier' <= '17', 'multiplier'*'bookval'<='22.5', 'past_5_earnings'>='16.5', 'annsales'>='100','curass'/'long_term_debt' >= '2'
GROUP BY 'ticker'
ORDER BY 'rank' ASC;
Last edited by afbase on Sat Jan 13, 2007 3:25 pm, edited 1 time in total.
Are you using mysql_query()? Then you can only send one statement at a time.
Code: Select all
<?php
/** wrong */
mysql_query("
UPDATE abc ...;
UPDATE lmn ...;
UPDATE xyz ...;
");
/* only one statement per mysql_query() */
mysql_query("UPDATE abc ...");
mysql_query("UPDATE lmn ...");
mysql_query("UPDATE xyz ...");one field `rank`, many fields in curldata. What is mysql supposed to assign to `rank`?SET rank=(SELECT * FROM curldata
update
Well revision after revision, this is what I believe what I am aiming for but still errors in my sql statement.Error number: 1064 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'annsales'>='100' GROUP BY 'rank' ORDER BY 'multiplier' ASC, 'multiplier'*'book' at line 1
Code: Select all
SELECT COUNT(ticker) AS rank
FROM curldata
WHERE 'dividend' REGEXP '([[:digit:]].[[:digit:]]|.[[:digit:]])', 'annsales'>='100'
GROUP BY 'rank'
ORDER BY 'multiplier' ASC, 'multiplier'*'bookval' ASC, 'past_5_earnings' DESC,'curass'/'long_term_debt' DESC;I made that 'AND' correction and some other revisions and the issue that i am still having this issue where my rank column isn't changing by the order instructions in the Statement. Presently there are only zeros in my rank column. Another piece of info about my table is that the stock_id is the auto_incrementing primary key.
I tested to see how many stocks fit the argument of and there are less than 400 stocks that fit that description. That is completely fine by me but for some reason I'm not getting a rank for these stocks.
Code: Select all
SELECT COUNT(stock_id) AS rank FROM curldata WHERE 'annsales' >= '100' AND REGEXP '([[:digit:]].[[:digit:]]|.[[:digit:]])' GROUP BY 'stock_id' ORDER BY 'multiplier' ASC , 'multiplier' * 'bookval' ASC , 'past_5_earnings' DESC , 'curass' / 'long_term_debt' DESC;Code: Select all
'annsales' >= '100' AND REGEXP '([[:digit:]].[[:digit:]]|.[[:digit:]])'