带分区的MySQL LN

Question

在MySQL 5.7中，我尝试计算加密货币的每日回报。此计算为LN(Day2Close/Day1Close)。

我的表结构如下。CoinIndex在每枚硬币中都包含了日数。因此，BTC数据的第一天的coinIndex为1，ETH数据的第一天的coinIndex也为1。每天都有数据：

CREATE TABLE `histoday2` (
 `id` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
 `coinId` mediumint(7) DEFAULT NULL COMMENT 'Crypto Compare CoinID',
 `time` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
 `fsym` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
 `tsym` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
 `close` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `high` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `low` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `open` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `volumefrom` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `volumeto` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `coinIndex` int(11) DEFAULT NULL,
 `totalReturn` decimal(6,2) DEFAULT NULL,
 UNIQUE KEY `id` (`id`)
)

我的查询抛出了LN()只能包含一个操作数的错误。我不确定如何组织这个查询：

SELECT coinId, time, close, 
(SELECT close, LN(A.close/close) 
FROM histoday2 
WHERE coinIndex = A.coinIndex -1 
AND coinId = A.coinId) B 
FROM histoday2 A;

Answer 1

不能使用将两列作为表达式返回的子查询。子查询表达式最多只能返回一行和一列。

在子查询中使用JOIN，而不是返回前一天的收盘价和LN。

SELECT a.coinid, a.time, a.close AS close_yesteday, b.close AS close_today, LN(a.close/b.close) AS return_rate
FROM histoday2 AS a
JOIN histoday2 AS b ON a.coinid = b.coinid AND a.coinIndex = b.coinIndex - 1

Answer 2

我会使用左连接，以防前一天不存在...

SELECT
  A.coinId,
  A.time,
  A.close,
  B.close   AS previousClose,
  LN(A.close/B.close) AS xxx
FROM
  histoday2 A
LEFT JOIN
  histoday2 B
    ON  B.coinIndex = A.coinIndex - 1
    AND B.coinID    = A.coinID

Dbfiddle here