我有三个表:monthly_revenue、currencies 和 foreign_exchange。

monthly_revenue 表

|------------------------------------------------------|
| id | product_id | currency_id | value | month | year |
|------------------------------------------------------|
| 1  | 1          | 1           | 100   | 1     | 2015 |
| 2  | 1          | 2           | 125   | 1     | 2015 |
| 3  | 1          | 3           | 115   | 1     | 2015 |
| 4  | 1          | 1           | 100   | 2     | 2015 |
| 5  | 1          | 2           | 125   | 2     | 2015 |
| 6  | 1          | 3           | 115   | 2     | 2015 |
|------------------------------------------------------|

foreign_exchange 表

|---------------------------------------|
| id | base | target | rate | rate_date |
|---------------------------------------|
| 1  | GBP  | USD    | 1.6  |2015-01-01 |
| 2  | GBP  | USD    | 1.62 |2015-01-15 |
| 3  | GBP  | USD    | 1.61 |2015-01-31 |
| 4  | EUR  | USD    | 1.2  |2015-01-01 |
| 5  | EUR  | USD    | 1.4  |2015-01-15 |
| 6  | EUR  | USD    | 1.4  |2015-01-31 |
| 7  | GBP  | EUR    | 1.4  |2015-01-01 |
| 8  | GBP  | EUR    | 1.45 |2015-01-15 |
| 9  | GBP  | EUR    | 1.44 |2015-01-31 |
|---------------------------------------|

由此，我们可以看到平均外汇汇率:

• 英镑 > 1 月美元为 1.61
• 1 月欧元 > 美元为 1.33
• GBP > 1 月欧元为 1.43

没有以美元为基础货币的汇率，也没有 2 月的汇率。

货币表

|-----------|
| id | name |
|-----------|
| 1  | GBP  |
| 2  | USD  |
| 3  | EUR  |
|-----------|

我想要实现的目标

monthly_revenue 表中的每一行都可以有不同的 currency_id，因为所下的订单是不同的货币。我想以通用货币查看给定月份的所有收入。因此，与其以英镑查看 1 月份的所有收入，然后以美元分别查看 1 月份的所有收入，我想获得 1 月份所有收入的一个值 - 转换为美元(例如)。

可以使用以下公式为每一行计算(本示例使用一月):

收入值 x 1 月份基础货币和目标货币之间的平均汇率

如果我在 1 月份有 50 个订单，使用 4 种不同的货币，这让我可以查看任何单一货币的所有收入。

示例 - 获取 1 月份的所有收入，以美元为单位

这应该返回:

|------------------------------------------------------|
| id | product_id | currency_id | value | month | year |
|------------------------------------------------------|
| 1  | 1          | 1           | 100   | 1     | 2015 |
| 2  | 1          | 2           | 125   | 1     | 2015 |
| 3  | 1          | 3           | 115   | 1     | 2015 |
|------------------------------------------------------|

但是，第 1 行和第 3 行不是美元(分别是英镑和欧元)。

我希望看到的是返回的每一行都包含转换为的平均外汇汇率，以及一个 converted 列。例如:

|-------------------------------------------------------------------------|
| id | prod_id | currency_id | value | month | year | fx_avg | converted  |
|-------------------------------------------------------------------------|
| 1  | 1       | 1           | 100   | 1     | 2015 | 1.61   | 161        |
| 2  | 1       | 2           | 125   | 1     | 2015 | 1      | 125        |
| 3  | 1       | 3           | 115   | 1     | 2015 | 1.33   | 152.95     |
|-------------------------------------------------------------------------|

我在哪里

我目前可以使用以下查询完成基本计算，但缺少几个关键功能:

• 如果没有可用的汇率(例如对于当然没有汇率的 future 日期)，则忽略整行。在这种情况下，我希望使用最近一个月的平均值。

• 如果在目标货币与基础货币相同的情况下执行计算，则忽略整行(因为 FX 表中没有基础等于目标的记录)。在这种情况下，速率应该被硬定义为 1。

查询至今

SELECT 
    r.value * IFNULL(AVG(fx.rate),1) as converted, AVG(fx.rate) as averageFx, 
    r.*, fx.*
FROM 
    foreign_exchange fx, monthly_revenue r, order_headers h
WHERE 
    fx.base IN (SELECT name FROM currencies WHERE id = r.currency_id) AND 
    r.order_header_id = h.id AND 
    fx.target = 'USD' AND 
    MONTH(fx.rate_date) = r.month AND
    YEAR(fx.rate_date) = r.year AND
    r.year = 2015
GROUP BY r.id
ORDER BY month ASC

如果没有可用于 FX 的记录，看起来应该执行一个单独的子查询来获取最近一个月的平均汇率。

任何意见将不胜感激。如果需要任何进一步的信息，请发表评论。

谢谢。

编辑 Here is a SQFiddle其中包含示例架构和突出问题的代码。

最佳答案

这是一个近似函数，用于计算您兑换给定货币和月初的时间:

DELIMITER //
CREATE FUNCTION MonthRate(IN _curr CHAR(3) CHARACTER SET ascii,
                          IN _date DATE)
    RETURNS FLOAT
    DETERMINISTIC
BEGIN
    -- Note:  _date must be the first of some month, such as '2015-02-01'
    DECLARE _avg FLOAT;
    DECLARE _prev FLOAT;
    -- First, try to get the average for the month:
    SELECT AVG(rate) INTO _avg FROM foreign_exchange
        WHERE base = _curr
          AND target = 'USD'
          AND rate_date >= _date
          AND rate_date  < _date + INTERVAL 1 MONTH;
    IF _avg IS NOT NULL THEN
        RETURN _avg;
    END;
    -- Fall back onto the last rate before the month:
    SELECT rate INTO _prev
        FROM foreign_exchange
        WHERE base = _curr
          AND target = 'USD'
          AND rate_date < _date
        ORDER BY _date
        LIMIT 1;
    IF _prev IS NOT NULL THEN
        RETURN _prev;
    END;
    SELECT "Could not get value -- ran off start of Rates table";
END;
DELIMITER ;

可能存在语法错误等。但希望你能处理它。

应该很容易从其余代码中调用该函数。

对于性能，这将是有益的:

INDEX(base, target, rate_date, rate)

关于如果计数为零，MySQL 执行子查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32051465/