construct a query for a currency rates table 2

[jpadie]

i have a table as follows
rate varchar(10)
symbol varchar(5)
xDate int(15)

essentially it holds currency conversion rates for a given day (expressed as a unix timestamp)

not all currency conversion rates are published each day and i am currently use this query to retrieve two rows with the appropriate rates

select rate,symbol,max(xDate) from tablename where symbol in ('EUR', 'GBP') and xDate <=123456770

the trouble is that the date for the published rate may be different between the two rows.

how may i alter the query so that it will return the rows for the latest day (up to and including a specified date) on which both currencies have published?

many thanks

 

[jpadie]

oops. that query was not the one i was using in production code.

select rate, symbol, max(xDate) from currencyTable group by xDate, symbol, rate having symbol in ('EUR', 'GBP') and xDate <= 1246399200

but hopefully the invalid sql did not throw anyone of the scent of my underlying question.

 

[tsuji]

The reasoning may be working like this if repeating the derived table may look clumsy, candidate for simplification(?).
[tt]
[blue]select a.rate, a.symbol, max(a.xDate) from [/blue]
[green](select b.* from currencyTable b group by b.xDate, b.symbol, b.rate having symbol in ('EUR') and b.xDate <= 1246399200) as a
inner join
(select d.* from currencyTable d group by d.xDate, d.symbol, d.rate having symbol in ('GBP') and d.xDate <= 1246399200) as c
on
DATE(FROM_UNIXTIME(a.xDate))=DATE(FROM_UNIXTIME(c.xDate))[/green]
union all
[blue]select c.rate, c.symbol, max(c.xDate) from [/blue]
[green](select b.* from currencyTable b group by b.xDate, b.symbol, b.rate having symbol in ('EUR') and b.xDate <= 1246399200) as a
inner join
(select d.* from currencyTable d group by d.xDate, d.symbol, d.rate having symbol in ('GBP') and d.xDate <= 1246399200) as c
on
DATE(FROM_UNIXTIME(a.xDate))=DATE(FROM_UNIXTIME(c.xDate))[/green]
;[/tt]

 

[jpadie]

tsuji

that's a surprise! i was aware that the query was non-trivial (otherwise i hope i could have worked it out myself), but i had not assumed that the result would be a union between two self-joined tables.

i will compare the performance results of this query against a programmatic solution (which uses three separate but non-join queries.

thanks
Justin

 

[jpadie]

i'm actually running this query on a sqlite installation. i'm getting between 7 and 10 secs for the query execution (without the date transformations - pure unix compares). for the programmatic approach i get results in the region of 50ms for all three queries and the intervening php.

that's annoying, i was hoping that this was resolvable by a single query without a performance hit.

i can't immediately think of a way to improve the structure without going for a cartesian pairing of all available currencies. given that i am handling 50 or so currencies for each day, and i am holding data going back to 2000, i have a horrible feeling that this will become rather large rather quickly! I would be delighted to receive your expertise to the contrary though.

 

[tsuji]

jpadie, no claim of sql-expertise here. I hope r937 can intervene and come up with an optimal solution. (Can you tell me if that sql works in the direction of what you set out to get amid slow? If yes, that's comforting - I want to know - because that's all brain code.)

 

[jpadie]

tsuji

yes the code works and retrieves the currency pair that were expected. this is the exact query i used

select a.rate, a.symbol, max(a.xDate) from 
(select b.* from currency b group by b.xDate, b.symbol, b.rate having symbol in ('EUR') and b.xDate <= 1246399200) as a
inner join
(select d.* from currency d group by d.xDate, d.symbol, d.rate having symbol in ('GBP') and d.xDate <= 1246399200) as c
on
(a.xDate=c.xDate)
union all
select c.rate, c.symbol, max(c.xDate) from 
(select b.* from currency b group by b.xDate, b.symbol, b.rate having symbol in ('EUR') and b.xDate <= 1246399200) as a
inner join
(select d.* from currency d group by d.xDate, d.symbol, d.rate having symbol in ('GBP') and d.xDate <= 1246399200) as c
on
(a.xDate = c.xDate)

the only mods were, as said, the removal of the date and unixtime transformations.

 

[tsuji]

Thank you for the info and the vote.

I was visualizing that the data are fed from some automatic quoting m/c from the market with tic counts at the closing period etc so that xDate (if in unix timestamp) would have hh:mm:ss slight variation on the number (int(15)): that's why I join them on market date without ambiguity. If that piece of data contains uniform hh:mm:ss, a direct comparison would then make much sense.

I'll think about optimization of it as something to look-into. Thanks for the question.

 

[jpadie]

the dates are derived from programmatic code rather than on return data. i nearly always handle dates using unixtime within my applications.

the code i am using is work in progress but here it is if you're interested. i have not been able to find a decent supported (and free) alternative API on the 'net that supports historic interest rates.

the initial load is slow. one web request for every day, in fact. if you're testing this you might want to change the firstSupportedDay property to something more recent.

i'll publish this more widely when i polish it up.

<?php
/*
Plugin Name: Currency Exchanger
Plugin URI: [URL unfurl="true"]http://rathercurious.net[/URL]
Description: currency exchange rate class
Version: 0.1.0
Author: Justin Adie
Author URI: [URL unfurl="true"]http://rathercurious.net[/URL]
*/
class exchangeRates{
	private $fields = array('from', 'to', 'date', 'amount');
	private $table = 'currency';
	private $dbFile = './currencyExchangeRates2.sqlite';
	private $firstSupportedDay = 1167606000 ; //1st Jan 2007
	
	public function __construct(){
		if (!file_exists($this->dbFile)){
			$this->pdo = new PDO ("sqlite:{$this->dbFile}");
			$this->init();
		} else {
			$this->pdo = new PDO ("sqlite:{$this->dbFile}");
		}
		$this->today = strtotime('today');
		if (empty($this->date)){
			$this->date = $this->today;
		}
		$this->addRates();
		if (isset($this->statement)) $this->statement= null;
	}
	
	public function convert($from, $to, $amount, $date=null, $format=false){
		global $wpdb;
		if(empty($date)){
			$date = $this->today;
		}
		foreach (array($from, $to) as $symbol){
			$sql = "Select xDate as xDate from $this->table where symbol=? and xDate <= ? order by xDate desc limit 5";
			$statement = $this->pdo->prepare($sql);
			if ($statement === false){
				die (print_r($this->pdo->errorinfo(), true));
			}
			$result = $statement->execute(array($symbol, $date));
			if ($result === false){
				die (print_r($result->errorinfo(), true));
			}
			while ($row = $statement->fetchObject()){
				$results[$symbol][] = $row->xDate;
			}
		}
		$dates = array_intersect($results[$from], $results[$to]);
		if (count($dates) == 0){
			die ('We do not have a currency exchange rate pair listed for the requested day nor for any day close thereto');
		}
		//will be the first that has the latest date
		$sql = "select rate, symbol from $this->table where symbol in (?, ?) and xDate=?";
		$statement = $this->pdo->prepare($sql); 
		$statement->execute(array($to, $from, $dates[0]));
		$results = $statement->fetchAll(PDO::FETCH_OBJ);
		//convert to USD
		foreach($results as $pair){
			if ($pair->symbol == $from){
				$this->fromRate = $pair->rate;
			} else {
				$this->toRate = $pair->rate;
			}
		}
		$usd = $amount / $this->fromRate;
		$conversion = $usd * $this->toRate;
		if ($format) {
			return $this->getSymbol($to) .  $this->formatted($conversion);
		} else {
			return $conversion;
		}
	}
	
	private function getSymbol($iso){
		//will perform lookup of currency symbols.  for the time being return the ISO symbol
		return $iso;
	}
	private function formatted ($number){
		return number_format($number, $this->decPlaces, $this->decSeparator, $this->kSeparator);
	}
	
	public function setFormat($decPlaces=2, $decSeparator='.', $kSeparator=','){
		list($this->decPlaces, $this->decSeparator, $this->kSeparator) = func_get_args();
	}
	private function incrementDay(){
		$this->day = strtotime('+1 day', $this->day);
	}

	private function addRates(){
		global $wpdb;
		$query = "select max(xdate) as mD from {$this->table}";
		$s = $this->pdo->query($query);
		$obj = $s->fetchObject();
		$day = $obj->mD;
		unset($s);
		if ($day < $this->firstSupportedDay){
			$day = $this->firstSupportedDay;
		}
		$this->day = $day;
		if ($this->day < $this->today){
			while ($this->day <= $this->today){
				if ($this->day !== $this->firstSupportedDay){ //increment the day to avoid duplication
					$this->incrementDay();
				}
				set_time_limit(30);
				$this->getDailyRates($this->day);
				$this->incrementDay();
			}
			if (!empty($this->parser)){
				xml_parser_free($this->parser);
				unset($this->parser);
			}
		}
	}
	
	private function getDailyRates($day){
		$date = date('Ymd', $day);
		$url = "[URL unfurl="true"]http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote;date=$date;currency=true?view=basic&format=xml&callback=currencyConverter.addConversionRates";[/URL]
		$ch = curl_init();
		curl_setopt($ch, CURLOPT_URL, $url);
		curl_setopt($ch, CURLOPT_HEADER, false);
		curl_setopt($ch, CURLOPT_FRESH_CONNECT, true);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
		curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
		$response = curl_exec($ch);
		$tdb = $this->getRatePairs($response);
		$this->writeDB($tdb, $day);
	}
	
	private function prepareInsertStatement(){
		$sql = "insert into $this->table (xdate, rate, symbol) values (?, ?, ?)";
		$statement =  $this->pdo->prepare($sql);
		if ($statement === false){
			die (print_r($this->pdo->errorinfo(), true));
		}
		$this->insertStatement = $statement;
		
	}
	
	private function writeDB($obj, $date){
		if (empty($obj->symbol)) return;
		if (empty($this->insertStatement )){
			$this->prepareInsertStatement();
		}
		$result = $this->insertStatement->execute(array($date, $obj->price, $obj->symbol));	
		if ($result === false){
			die (print_r($this->insertStatement->errorInfo(), true));
		}
	}
	
	private function getRatePairs($xml){
		if (empty($this->parser)){
			$this->parser = xml_parser_create();
		    xml_parser_set_option($this->parser, XML_OPTION_CASE_FOLDING, 0);
		    xml_parser_set_option($this->parser, XML_OPTION_SKIP_WHITE, 1);
		}
	    xml_parse_into_struct($this->parser, $xml, $values, $tags);
	   
	    // loop through the structures
	    foreach ($tags as $key=>$val) {
	        if ($key == "resource") {
	            $dataRanges = $val;	
	            for ($i=0; $i < count($dataRanges); $i+=2) {
	                $offset = $dataRanges[$i] + 1;
	                $len = $dataRanges[$i + 1] - $offset;
	                $obj = $this->parseFields(array_slice($values, $offset, $len));
					$this->writeDB($obj, $this->day);
	            }
	        } else {
	            continue;
			}
		}
	}
	
	private function parseFields($fields) {
		$d = array('symbol', 'price');
		$obj = new stdClass();
	    foreach ($fields as $r){
	    	//$item = array();
	    	if ($r['type'] == 'complete'){
	    		if (in_array($r['attributes']['name'], $d)){
	    			$obj->$r['attributes']['name'] = $r['value'];
	    		}
	    	}
	    }
		if (isset($obj->symbol)){
			$obj->symbol = str_replace('=X', '', $obj->symbol);
		}
		return $obj;
	}
	
	private function init(){
		$query[] = <<<SQL
CREATE TABLE if not exists $this->table (
 symbol text ,
 rate float ,
 xdate int,
PRIMARY KEY (symbol, xdate) on conflict REPLACE)
SQL;

		foreach ($query as $q) {
			$result = $this->pdo->exec($q);
			if ($result === false){
				die (print_r($this->pdo->errorinfo(), true));
			}
		}
	}
}

class timer{
	public function start(){
		$this->start = microtime(true);
	}
	private function stop(){
		$this->stop = microtime(true);
	}
	public function report($format = true){
		$this->stop();
		$this->diff();
		printf ("<hr/>Script took %s milliseconds", number_format($this->diff/1000, 8)); 
	}
	private function diff(){
		$this->diff = $this->stop - $this->start;
	}
}
$timer = new timer();
$timer->start();
$xR = new exchangeRates();
$xR ->setFormat(2,'.', ',');
echo "100  GBP is " . $xR->convert('GBP', 'EUR', '100', null, true) ;
$timer->report();
?>

by way of benchmarking, with the database fully loaded i get execution times of about 0.6 microseconds.  three years of data took about 10 minutes to load up.

 

[tsuji]

Thanks again for sharing your work. I am sure I will learn something out of it too.

 

[jpadie]

thank tsuji. the point of posting it was to see whether anyone could come up with optimisations to the sql given the constraints of the data feed. i'm sorry i did not make that clear so that it may have seemed that i was otherwise pointlessly posting code to blow my own trumpet!

 

[tsuji]

No worry about that at all.

 

[ChrisHunt]

Hmmm...

I don't have a MySQL database to hand to test this, but how about something like this?

SELECT t.rate,t.symbol,t.xDate
FROM   tablename as t
INNER JOIN (SELECT max(t1.xDate) as maxDate
            FROM   tablename t1
            INNER JOIN tablename t2
            ON t2.xDate = t1.xDate
            AND t2.symbol = 'EUR'
            WHERE  t1.xDate <= 123456770
            AND    t1.symbol = 'GBP') AS m
ON m.maxDate = t.xDate
WHERE t.symbol in ('EUR', 'GBP')

The subquery finds the latest date on which both currencies were published, and the main query pulls out the rates on this date.

But maybe that's not what you really wanted? Maybe you want to retrieve the rate for each currency on the latest date when it was published (this date maybe being different for each currency). In that case, do something like this:

SELECT t.rate,t.symbol,t.xDate
FROM   tablename as t
INNER JOIN (SELECT t2.symbol, max(t2.xDate) as maxDate
            FROM   tablename t2
            WHERE  t2.xDate <= 123456770
            AND    t2.symbol in ('EUR', 'GBP')
            GROUP BY t2.symbol) AS m
ON m.symbol = t.symbol
AND m.maxDate = t.xDate
WHERE t.symbol in ('EUR', 'GBP')

Either way, you shouldn't need massed ranks of UNIONs to get this job done.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[jpadie]

sqlite barfs a bit at that syntax (the table names from the inner join are not carried through to the main query), but otherwise that looks spot on! thanks!

this is the query as used (very little difference)

SELECT t.rate,t.symbol,t.xDate
FROM   currency t
INNER JOIN (SELECT symbol, max(xDate) as maxDate
            FROM   currency
            WHERE  xDate <= 1246312800
            AND    symbol in ('EUR', 'GBP')
            GROUP BY symbol) AS m
ON (m.symbol = t.symbol
AND m.maxdate = t.xDate)
WHERE t.symbol in ('EUR', 'GBP')