Matrix multiplication in python and mysql

Question

I have a currency exchange dictionary, as follows:

exchange_rates = {'USD': 1.00000,
                  'EUR': 1.32875,
                  'GBP': 1.56718, ...}

Then I retrieve the sales information for a product, using:

SELECT price, currency FROM sales

There are perhaps a million rows, which may look like this:

- 2.99    USD
- 3.01    EUR
- etc.

How would I do matrix multiplication to get the total sum in USD?

score 5 · Accepted Answer · answered May 08 '12 at 22:46

5

Instead of getting one million rows from the database and doing the calculation in Python, give your dictionary to the database and get the database to do the calculation and send you back the result.

You can do this by making a query similar to the following:

SELECT SUM(price * exchange_rate) AS total
FROM sales
LEFT JOIN
(
    SELECT 'USD' AS currency, 1.00000 AS exchange_rate
    UNION ALL
    SELECT 'EUR', 1.32875
    UNION ALL
    SELECT 'GBP', 1.56718
    -- ...
) AS exchange
ON exchange.currency = sales.currency

answered May 08 '12 at 22:46

Mark Byers

811,555
193
1,581
1,452

Thanks for the explanation. The one problem here is I actually don't know what will be in the exchange_rates dict. It may have up to 30 different currencies (it's a run-time calculation). – David542 May 08 '12 at 22:55
@user1383058: Generate the query at runtime. – Mark Byers May 08 '12 at 23:02
@user1383058 its no big deal to compile this query according to the exchange rates dynamically,definitely not from python I suppose. Or if you have a table for exchange rates (a much better option since exchange rates keep changing and you will have to modify and save) you can go for the query in my answer – nawfal May 08 '12 at 23:02
[I'll second the that](http://stackoverflow.com/a/7518619/939860) - meaning the answer. – Erwin Brandstetter May 08 '12 at 23:02

score 0 · Answer 2 · answered May 08 '12 at 22:44

0

Do

SELECT price FROM sales

then sum up all the items you get back, assuming the numbers in your table are in USD.

answered May 08 '12 at 22:44

moowiz2020

425
1
3
12

Presumably they aren't all in USD otherwise he wouldn't be writing this question. – Mark Byers May 08 '12 at 22:45

nawfal · Answer 3 · 2012-05-08T22:59:22.130

0

SELECT SUM(price * factor) 
FROM 
       ( 
        SELECT sales.price AS price, exchange.factor AS factor
        FROM   sales
        JOIN   exchange ON exchange.currency = sales.currency
       ) AS t

This assumes you have an exchange table

edited May 08 '12 at 22:59

answered May 08 '12 at 22:53

nawfal

70,104
56
326
368

Matrix multiplication in python and mysql

3 Answers3

Linked