Jump to content

Matrixkid

Members
 View Profile  See their activity

  • Posts

    57

  • Joined

  • Last visited

 Content Type 

Posts posted by Matrixkid

    Multiple Averages

    in MySQL Help

    Posted

    Hi there,

     

    I'm looking to get the AVG's of multiple rows, multiple times.

     

    I want to get the AVG(price) from the last 10 rows, 20 rows, 50 rows, 100 rows, 200 rows

     

    Is there a way to do this without having to use multiple SELECTS?

     

    I was thinking maybe theres a way to pull the results using LIMIT 200, and then for each of the AVG's use that result set?

     

    Simple table setup.

     

    ID - Big Int,20

    Price - Decimal 10,2

    Entry_Time - Timestamp

     

    Thanks,

    MK

     

    MYSQL query optimization - slow ORDER BY, or LIMIT 1

    in MySQL Help

    Posted

    Yeah, as I hit the button it occurred to me that it might be better to get rid of the option_id field and use the primary key as:

    ID,Symbol,Expiration,Strike, Category

     

    But the reason behind my current key is that option_id represents all of the above fields except for ID, and the ID field is not necessarily unique since some entries are at the exact same time.

     

    What would you suggest as the key?

     

    Here is some test data:

    INSERT INTO `chains` (`id`, `symbol`, `expiration`, `strike`, `category`, `last`, `volume`, `openint`, `stock_price`) '
VALUES
('2012-01-06 15:23:16', 'SPY', '2012-01-13', '115.00', 'C', '0.00', 0, 0, '127.88'),
('2012-01-06 15:23:16', 'SPY', '2012-01-13', '115.00', 'P', '0.02', 834, 510, '127.88'),
('2012-01-06 15:24:16', 'SPY', '2012-01-13', '115.00', 'C', '0.02', 981, 0, '127.99'),
('2012-01-06 15:24:16', 'SPY', '2012-01-13', '115.00', 'P', '0.03', 1098, 510, '127.99');

     

    MYSQL query optimization - slow ORDER BY, or LIMIT 1

    in MySQL Help

    Posted

    I'm having an issue with a SELECT statement. I find that if I have a single row of data as the result and using ORDER BY it slows it right down. As soon as I either remove the ORDER BY or add another row of data it returns it in reasonable time. My data returned for the most part will have more than 1 result (hence the LIMIT 2) but there are some cases where there is only an individual row and it's stalling things.

     

    Profiling
Status  Time
starting    0.000082
checking permissions    0.000007
Opening tables  0.000020
System lock 0.000016
init    0.000043
optimizing  0.000021
statistics  0.000016
preparing   0.000016
executing   0.000004
Sorting result  0.000005
Sending data    15.276259
end 0.000013
query end   0.056590
closing tables  0.000030
freeing items   0.000156
logging slow query  0.000008
logging slow query  0.000007
cleaning up 0.000009

     

    Showing rows 0 - 0 (1 total, Query took 15.3337 sec)

SELECT expiration, strike, category, last, volume, stock_price
FROM chains
WHERE option_id =  'AMZN^^120203P00165000'
AND DATE( id ) = DATE( NOW( ) ) 
AND TIME( id ) BETWEEN  '09:30:00' AND  '16:00:00'
ORDER BY id DESC 
LIMIT 2

     

    As soon as I insert a new row with the same option_id value and a different id(timestamp) value the results is this:

    Showing rows 0 - 1 (2 total, Query took 0.2272 sec)

     

    OR if I remove the ORDER BY id DESC it returns a similar timed result, less than a second:

    Showing rows 0 - 0 (1 total, Query took 0.5024 sec)

     

    The SQL EXPLAIN:

    id  select_type table   type    possible_keys  key      key_len ref    rows Extra
1   SIMPLE      chains  index   NULL           PRIMARY  27      NULL   2    Using where

     

    Lastly, The table structure:

    CREATE TABLE IF NOT EXISTS `chains` (
  `id` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `option_id` varchar(21) NOT NULL,
  `symbol` char(4) NOT NULL,
  `expiration` date NOT NULL,
  `strike` decimal(10,2) NOT NULL,
  `category` char(1) NOT NULL,
  `last` decimal(10,2) NOT NULL,
  `volume` int(50) NOT NULL,
  `openint` int(50) NOT NULL,
  `stock_price` decimal(12,2) NOT NULL,
  `stock_volume` int(20) NOT NULL,
  PRIMARY KEY (`id`,`option_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

     

    In the end, im trying to grab the "latest" two results.

     

    Thanks ahead of time for the help! It's much appreciated!

    MYSQL Time difference between MAX and MIN from single table

    in MySQL Help

    Posted

    Hi there,

     

    I have a table as outlined below and I'm trying to get the MAX and MIN values for a specific Id but only if the MAX entry date is greater than the MIN entry date.

     

    table:

    ID  ENTRY_DATE              NAME       PRICE
1   2012-01-23 16:09:35     MONKEY     99.33
2   2012-01-23 11:04:09     MONKEY     97.65
3   2012-01-23 09:31:19     MONKEY     93.05
4   2012-01-23 15:12:14     DICE       30.49
5   2012-01-23 12:01:24     DICE       32.00
6   2012-01-23 08:01:24     DICE       31.00

     

    So what im trying to do is grab the MAX(price) and MIN(price) when the MAX(price) entry_date is greater than the MIN(price) entry_date. From the table above, my result would be:

     

    NAME      MaxPrice     MinPrice   PriceDiff   
MONKEY    99.33        93.05      6.28

     

    'Dice' would not show up because the MAX date/time happens before the MIN.

     

    I have tried a LEFT JOIN but I cant seem to figure it out. Here is what I have right now, but it obviously grabs all results as I cant seem to figure out how to compare the entry dates...

     

    SELECT name,
MAX(price) as MaxPrice,
MIN(price) as MinPrice,
MAX(price)-MIN(price) AS PriceDiff,
FROM products
WHERE DATE(entry_date) = DATE(NOW()) 
GROUP BY name
ORDER BY PriceDiff DESC

     

    Thanks for the help!

     

    jQuery Datepicker DOM

    in Javascript Help

    Posted

    you could put the initialization of the datepicker in your ajax call function, because currently the live function of jquery only supports click, dblclick,  mousedown, mouseup, mousemove, mouseover, mouseout, keydown, keypress, keyup events, you could retain the show in the live click, also your ajax call has a callback function which you could use, which function are you using for the ajax call ? 

     

    Im currently using load to make the request. I believe load has callback functionality.

     

    Thanks!

    jQuery Datepicker DOM

    in Javascript Help

    Posted

    Hi there,

     

    Im running into an issue with the jquery ui datepicker plugin.

     

    I have it working and functioning correctly, but I need to change the way it operates.

     

    here is my current code:

    $("input.calendarSelectDate").live('click',function(){
	$(this).datepicker({inline: true, showButtonPanel: true, 
buttonImage: '/cal.gif', buttonImageOnly: false, showButtonPanel: true, 
showOn:'both'}).datepicker('show');
});

     

    the problem is is that the input field is loaded from an ajax call. and so the button doesnt appear next to the input box until the input field has been clicked.

     

    Is there a way to display the button before the click action is made? What about loading the the datepicker function with another event? Is there a way to bind it after the ajax call, so its almost like it runs on document.ready, with just $('#inputfield').datepicker(); - without the 'click' action binding it?

     

    Thanks for the help.

    MK

    Decoded String

    in PHP Coding Help

    Posted

    yep, here it is:

     

    $html = '[ "\x3cdiv class=\"medium-content-below-item\"\x3e\n  \x3cdiv class=\"desc\"\x3e\n
                    \x3ch3 class=\"medium-content-below-item-title\"\x3e\x3ca class=\"title_link\" href=\"music/albums/1633820\"\x3e\n                 
\x3cspan \x3eEmpire State Of Mind\x3c/span\x3e\n               
\x3c/a\x3e\x3c/h3\x3e\n                \n                    \x3ca href=\"music/artists/195154/jay-z\"\x3e\n             
\x3cdiv class=\"medium-content-below-item-subtitle\"\x3eJay-z\x3c/div\x3e\n               
\x3c/a\x3e\n                \n         
\x3cdiv class=\"medium-content-below-item-time\"\x3e10:32pm\x3c/div\x3e\n        \n        \n     
\x3c/div\x3e\n\x3c/div\x3e\n" ]';

		$string = str_replace("]","",$html);
		$string = str_replace("[","",$string);

		echo strip_tags(html_entity_decode($string));

    Decoded String

    in PHP Coding Help

    Posted

    Hey guys

     

    Ive been trying for a couple hours now to parse a data string but Im having no luck.

     

    I have the following:

    [ "\x3cdiv class=\"medium-content-below-item\"\x3e\n  \x3cdiv class=\"desc\"\x3e\n
                    \x3ch3 class=\"medium-content-below-item-title\"\x3e\x3ca class=\"title_link\" href=\"music/albums/1633820\"\x3e\n                  
\x3cspan \x3eEmpire State Of Mind\x3c/span\x3e\n                
\x3c/a\x3e\x3c/h3\x3e\n                \n                    \x3ca href=\"music/artists/195154/jay-z\"\x3e\n             
\x3cdiv class=\"medium-content-below-item-subtitle\"\x3eJay-z\x3c/div\x3e\n                
\x3c/a\x3e\n                \n         
\x3cdiv class=\"medium-content-below-item-time\"\x3e10:32pm\x3c/div\x3e\n        \n        \n     
\x3c/div\x3e\n\x3c/div\x3e\n" ]

     

    Ive tried preg_replace, str_replace and nothing works. if i try using ">" or "<" it doesnt replace, nor does "\x3c" or "\x3e"

     

    In the end, im looking to just replace everything so im left with

    Empire State of Mind - Jay-z - 10:32pm

     

    If someone could help me just getting a str_replace or something like that to work that would be great.

     

    Thanks

    MK

     

    SELECT not in

    in MySQL Help

    Posted

    thanks for the help Zanus, I appreciate it.

     

    Unfortunately the revision doesnt work either. I have to remove the ) as songCheck part as it returns an error.

     

    This statement returns no results:

    SELECT * FROM TESTDB WHERE song NOT IN (
     SELECT DISTINCT song FROM TESTDB WHERE weeknum <= 49 AND yearnum <= 2009
)

     

    This statement returns the correct result:

    SELECT * FROM TESTDB WHERE song NOT IN (
     SELECT DISTINCT song FROM TESTDB WHERE weeknum < 49 AND yearnum <= 2009
)

     

    I can run the NOT IN on the TESTDB and get the results i need, but as soon as I apply the query to my active db the query lags and goes nowhere. The database has over 190,000 records and grows every 3 hours.

     

    heres the error: [Err] 1053 - Server shutdown in progress

     

    Im not really sure where to go from here.

     

    Thanks again.

    MK

     

    SELECT not in

    in MySQL Help

    Posted 

    Affected rows: 0
Time: 0.032ms

     

    Nope. Should return 1 row, JVC (from the table in the first post).

     

    If I change NOT EXISTS to EXISTS, it gives me the opposite results. All the songs played prior to week 49. But as soon as I make it NOT then it returns nothing.

    SELECT not in

    in MySQL Help

    Posted

    Im trying the NOT EXISTS but keep getting 0 results.

     

    SELECT Count(song) AS CountSong, artist , song FROM TESTDB
WHERE weeknum = 49 AND yearnum = 2009 AND
NOT EXISTS (SELECT artist,song FROM TESTDB WHERE weeknum < 49 AND yearnum <= 2009 GROUP BY song)
GROUP BY song ORDER BY `CountSong` DESC

    SELECT not in

    in MySQL Help

    Posted

    you already have ? just remove the song not In ....

     

    I think you misunderstood what results im looking for.

    If i remove the not in part I am selecting all songs from week 49, in year 2009.

    I want songs only that appeared in week 49, year 2009 and NEVER before.

     

    thanks

    MK

    SELECT not in

    in MySQL Help

    Posted

    Hi there,

     

    I need help constructing a select statement that in short selects songs from a database that have not been stored in any prior weeks or years.

     

    Below is the table:

    artist   |    song  |  weeknum  |    yearnum
lg            ab           49       2009
jvc           cd           49       2009
sony          ef           49       2009
lg            ab           31       2009
song          ef           22       2008

     

    Lets assume the current week is 49.

    From the above example, my desired result would be jvc.

    Im trying to select all songs that were stored in week 49 of this year and never before. So, anything less than week 49, or any year less than 2009.

     

    I had this statement working before but because of the amount of data it no longer works.

     

    SELECT Count( song ) AS CountSong, artist , song FROM TESTDB 
WHERE weeknum =49 AND yearnum = 2009 AND
song NOT IN (SELECT song FROM TESTDB WHERE weeknum < 49 AND yearnum <= 2009) 
GROUP BY song ORDER BY `CountSong` DESC

     

    I welcome all advice and opinions.

    I am aware that the database structure isnt the greatest. Anything to make this sort of select statement work and i'll listen.

     

    Thanks

    MK

    [SOLVED] jQuery :contains

    in Javascript Help

    Posted

    Hey,

     

    I was wondering if someone could push me in the right direction with an issue im having, actually two.

     

    First of all, im trying to change the background of the 1st parent div that contains a string.

     

    Heres what im using:

    $("div:contains(' minutes ago')").css("background", "rgb(229, 128, 108)");

     

    Basically what is happening is that this is highlighting the parent, of the parent, of the parent divs. Where as I just want the first element highlighted.

    I was wondering how this could do be done?

     

    Secondly, I want to run this once my page has loaded - I have several elements that depend on a json response, and therefore when I run this jquery statement it does nothing, yet, if i run it in the javascript console in chrome or firebug, it works - since the divs are filled with information.

     

    How do I go about running the statement once the divs are filled with text from the json response?

     

    Thanks

    MK

    [SOLVED] Date Format

    in Regex Help

    Posted

    Hi there,

     

    I have a date format like this right now:

    Sat, 17 Oct 2009 17:04:00

     

    I need to turn that into:

    2009-10-17T17:04:00Z

     

    My thought process was to remove with regex "Sat," and then just split the string up.

     

    So I was wondering then what the regex would be for "Sat, "

    This would apply to all cases.

    Fri, 16 Oct 2009 15:00:00

    Thu, 15 Oct 2009 22:01:00

     

    So I just need the first 3 letters with the comma and space after the comma to be removed.

     

    thanks!

    MK

    [SOLVED] Regex for #-

    in Regex Help

    Posted

    Hi there,

     

    I have a string that looks like this;

    2-Name,3-Name,4-Name,5-Name,6-Name,7-name

     

    Im trying to preg_split the string into an array so that all im left with is name,

    I can remove the comma no problem once its split up

     

    I have tried the following:

     

    $array_name = preg_split("/[0-9][-]/",$names);
$array_name = preg_split("/[0-9-]/",$names);
$array_name = preg_split("/[0-9]-]/",$names);

     

    Thanks for your help!

    Cheers

    MK

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.