Выбор минимальной цены на товар, когда в базе данных хранятся цены в разных валютах

Я собираю цены на продукты в разных интернет-магазинах, которые находятся в США (нас), Великобритании (Великобритания) и т. д., и храню их в одной ТАБЛИЦЕ mysql (com) в местной валюте. Например, для США цена указана в долларах США, для Великобритании - в фунтах стерлингов.

ТАБЛИЦА ком

 ---------------------------------------------------------------------------------------
| AUTO_INC | COUNTER |   ID |      CONC | VOLUME | PRICE |      SHOP | DATE_G | COUNTRY |
|----------|---------|------|-----------|--------|-------|-----------|--------|---------|
|   115124 |   76720 | 2399 | prod_name |     13 | 34.23 | store1.us |      3 |      us |
|   115186 |   50952 | 2399 | prod_name |     13 |    36 | store2.us |      3 |      us |
|   115187 |   45828 | 2399 | prod_name |     13 | 37.44 | store3.us |      3 |      us |
|   116448 |   73419 | 2399 | prod_name |   11.6 |    48 | store4.us |      3 |      us |
|   116449 |   73421 | 2399 | prod_name |     13 |  65.5 | store4.us |      3 |      us |
|   133334 |   22154 | 2399 | prod_name |     13 | 36.95 | store5.us |      4 |      us |
|   133386 |   31646 | 2399 | prod_name |     13 | 37.44 | store3.us |      4 |      us |
|   134828 |   54667 | 2399 | prod_name |   11.6 |    48 | store4.us |      4 |      us |
|   134929 |   54670 | 2399 | prod_name |     13 |  65.5 | store4.us |      4 |      us |
|   133337 |   22155 | 2399 | prod_name |     13 | 26.95 | store1.uk |      4 |      uk |
|   133387 |   31647 | 2399 | prod_name |     13 | 17.44 | store2.uk |      4 |      uk |
|   134829 |   54668 | 2399 | prod_name |   11.6 |    30 | store3.uk |      4 |      uk |
|   134830 |   54671 | 2399 | prod_name |     13 |  45.5 | store4.uk |      4 |      uk |
 ---------------------------------------------------------------------------------------

Чтобы конвертировать цены из местных валют в доллары США, я создал ТАБЛИЦУ my_currency.

ТАБЛИЦА my_currency

 --------------------------------------
| AUTO_INC | DOMAIN | EX_RATE | DATE_G |
|----------|--------|---------|--------|
|      235 |     uk |  0.6066 |      4 |
|      236 |     us |       1 |      4 |
|      237 |     uk |  0.6066 |      3 |
|      238 |     us |       1 |      3 |
 --------------------------------------

Если я хочу выбрать минимальные цены для наших интернет-магазинов (где COUNTRY = 'нас'), я использую следующий запрос:

SELECT t1.* FROM com as t1
INNER JOIN (
  SELECT id, conc, volume, min(price) as usd_price, date_g 
  FROM com 
  WHERE id=2399 AND date_g=4 AND country='us'
  GROUP BY conc, volume) as t2 
ON t1.conc=t2.conc and t1.volume=t2.volume and t1.id=t2.id and t1.price=t2.usd_price and t1.date_g=t2.date_g
ORDER BY conc DESC, volume DESC

И я получаю ПРАВИЛЬНЫЙ результат:

| AUTO_INC | COUNTER |   ID |      CONC | VOLUME | PRICE |      SHOP | DATE_G | COUNTRY |
|----------|---------|------|-----------|--------|-------|-----------|--------|---------|
|   133334 |   22154 | 2399 | prod_name |     13 | 36.95 | store5.us |      4 |      us |
|   134828 |   54667 | 2399 | prod_name |   11.6 |    48 | store4.us |      4 |      us |

Но теперь моя цель - выбрать минимальную цену из всех магазинов (страна в ("нас", "Великобритания")) и принять во внимание, что в базе данных хранятся цены в местных валютах: 1. конвертировать цены из местных валют в доллары США 2. выбрать минимальные цены в долларах США

Поэтому я попытался использовать следующий запрос:

SELECT t1.auto_inc, t1.id, t1.conc, t1.volume, (t1.price / my_currency.ex_rate) as sub_price, t1.date_g 
FROM com as t1
 inner join my_currency 
 ON t1.country=my_currency.domain AND t1.date_g=my_currency.date_g
inner join (
 select com.id, com.conc, com.volume, min(com.price / my_currency.ex_rate) as usd_price, com.date_g 
 from com 
 inner join my_currency 
 ON com.country=my_currency.domain AND com.date_g=my_currency.date_g
 WHERE com.id=2399 AND com.date_g=4 AND com.country in ('us', 'uk')
 GROUP BY conc, volume) as t2 
on 
t1.id=t2.id and 
t1.conc=t2.conc and 
t1.volume=t2.volume and 
(t1.price / my_currency.ex_rate)=t2.usd_price and
t1.date_g=t2.date_g 
ORDER BY conc DESC, volume DESC

Но я получаю НЕПРАВИЛЬНЫЙ результат:

| AUTO_INC |   ID |      CONC | VOLUME | SUB_PRICE | DATE_G |
|----------|------|-----------|--------|-----------|--------|
|   134828 | 2399 | prod_name |   11.6 |        48 |      4 |

ПРАВИЛЬНЫЙ результат:

| AUTO_INC |   ID |      CONC | VOLUME | SUB_PRICE | DATE_G |
|----------|------|-----------|--------|-----------|--------|
|   134828 | 2399 | prod_name |   11.6 |        48 |      4 |
|----------|------|-----------|--------|-----------|--------|
|   133387 | 2399 | prod_name |   13   | 28.750412 |      4 |

У кого-нибудь есть идеи? sqlfiddle.com


mysql currency minimum
person DEN83    schedule 26.01.2014    source источник
comment
Почему id и date_g опущены в предложении group by?   -  person Strawberry    schedule 26.01.2014
comment
ГДЕ com.id=2399 И com.date_g=4 И com.country в ('us', 'uk')   -  person DEN83    schedule 26.01.2014
comment
Да я вижу. все равно не болит!   -  person Strawberry    schedule 26.01.2014

Ответы (1)


arrow_upward
1
arrow_downward

Проблема заключается в join в вычисленном десятичном значении. Следующие работы:

SELECT t1.auto_inc, t1.id, t1.conc, t1.volume,
       (t1.price / my_currency.ex_rate) as sub_price, t1.date_g 
FROM com as t1 inner join
     my_currency 
     ON t1.country = my_currency.domain AND t1.date_g = my_currency.date_g
     inner join
     (select com.id, com.conc, com.volume, min(com.price / my_currency.ex_rate) as usd_price, com.date_g 
      from com inner join
           my_currency 
           ON com.country = my_currency.domain AND
              com.date_g = my_currency.date_g
      WHERE com.id=2399 AND com.date_g = 4 AND
            com.country in ('us', 'uk')
      GROUP BY com.id, com.conc, com.volume
     ) as t2 
     on t1.id = t2.id and 
        t1.conc = t2.conc and 
        t1.volume = t2.volume and 
        abs((t1.price / my_currency.ex_rate) - t2.usd_price) < 0.01 and
        t1.date_g = t2.date_g
ORDER BY conc DESC, volume DESC;

Однако, если вы измените условие join для price на:

        t1.price / my_currency.ex_rate =  t2.usd_price and

Тогда это не работает.

Это действительно работает, если вы возвращаете оба результата к decimal(10, 2):

        cast(t1.price / my_currency.ex_rate as decimal(10, 2)) = cast(t2.usd_price as decimal(10, 2))

Возможно, это как-то связано с этим примечанием. в документации:

При делении, выполняемом с помощью /, масштаб результата при использовании двух операндов с точным значением равен масштабу первого операнда плюс значение системной переменной div_precision_increment (которое по умолчанию равно 4). Например, результат выражения 5,05/0,014 имеет шкалу с шестью десятичными знаками (360,714286).

person Gordon Linoff    schedule 26.01.2014
comment
Большое спасибо! Вы продали мою проблему! - person DEN83; 26.01.2014