mysql 矩阵运算,python和mysql中的矩阵乘法

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?

解决方案

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