求助：MySQL按contract_address计算7天内price的百分比变化

需求说明 #

需要获取每个contract_address在7天内基于created_at字段的价格百分比变化，仅统计event为"SOLD"的交易记录。

数据表结构与测试数据 #

-- Adminer 4.8.1 MySQL 5.5.5-10.4.10-MariaDB dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `nft_market_trade`;
CREATE TABLE `nft_market_trade` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_address` varchar(100) DEFAULT NULL,
  `token_id` int(11) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `type` enum('F','A') DEFAULT NULL,
  `from_address` varchar(100) DEFAULT NULL,
  `to_address` varchar(100) DEFAULT NULL,
  `block_number` mediumtext DEFAULT NULL,
  `tx_hash` varchar(100) DEFAULT NULL,
  `event` varchar(100) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp(),
  `timestamp` mediumtext DEFAULT NULL,
  `fee_artist_portion` double DEFAULT NULL,
  `fee_treasury_portion` double DEFAULT NULL,
  `auction_id` mediumtext DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `nft_market_trade` (`id`, `contract_address`, `token_id`, `price`, `amount`, `type`, `from_address`, `to_address`, `block_number`, `tx_hash`, `event`, `created_at`, `updated_at`, `timestamp`, `fee_artist_portion`, `fee_treasury_portion`, `auction_id`) VALUES
(342,   '0xeed69d5ac882bbecb7449fe31e916a3c69b3c27a',   3,  0.000003,   1,  'F',    '0x68cb4d2da9323586c11d58cc3c22f96282319050',   '0x4091320130802794fc301642b8d61d090e419477',   '97325503', '0x0648cf42ae8683baedda860c0b64fa061abc06d3353f898d042e118fb8537b48',   'SOLD', '2022-08-27 06:14:54',  '2022-07-27 06:14:54',  '1658902087',   0.00000006, 0.00000006, NULL),
(343,   '0xeed69d5ac882bbecb7449fe31e916a3c69b3c27a',   3,  0.000003,   1,  'F',    '0x68cb4d2da9323586c11d58cc3c22f96282319050',   '0xb7204b9862d2302d2109d38d548111c966ef78ee',   '97322182', '0x83ae44b08d1be5060a1a7d0bd6b82878b2a08734adacc392afdf57f32dde8046',   'PLACE',    '2022-08-27 06:14:56',  '2022-07-27 06:14:56',  '1658898762',   0,  0,  NULL),
(344,   '0xeed69d5ac882bbecb7449fe31e916a3c69b3c27a',   3,  3.5,    1,  'F',    '0x4091320130802794fc301642b8d61d090e419477',   '0xb7204b9862d2302d2109d38d548111c966ef78ee',   '97326062', '0xf9ccc91d53f696594ab04e6782a30c83a6e11663e641acf2999ca5236df710ad',   'PLACE',    '2022-08-27 06:17:42',  '2022-07-27 06:17:42',  '1658902646',   0,  0,  NULL),
(345,   '0xeed69d5ac882bbecb7449fe31e916a3c69b3c27a',   3,  3.5,    1,  'F',    '0x4091320130802794fc301642b8d61d090e419477',   '0x68cb4d2da9323586c11d58cc3c22f96282319050',   '97326730', '0xab4db021609514cb50846baa08124667634b80902cb9d1f3bb9189239980d218',   'SOLD', '2022-08-27 06:28:54',  '2022-07-27 06:28:54',  '1658903314',   0.07,   0.07,   NULL),
(346,   '0xeed69d5ac882bbecb7449fe31e916a3c69b3c27a',   3,  3.5,    1,  'F',    '0x68cb4d2da9323586c11d58cc3c22f96282319050',   '0x2b08e3ca40d615606c5068db8b66f41f460da811',   '97328265', '0xa252f96edb41a0c6c7c66d7316b40cf9101b629e6342f4043ffce9c1e9322b86',   'PLACE',    '2022-08-27 06:54:15',  '2022-07-27 06:54:15',  '1658904850',   0,  0,  NULL)

尝试的SQL语句 #

SELECT a.contract_address, a.price, a.event, a.created_at, max(price) - min(price) / min(price) * 100 as percentchange
FROM `nft_market_trade` a
WHERE a.event = "SOLD"
AND created_at >= now() - interval 7 day
group by a.contract_address;

存在的问题 #

1. 计算逻辑错误：运算符优先级导致结果偏差，max(price) - min(price) / min(price) * 100会先执行除法和乘法，实际计算的是max(price) - 100，并非正确的涨幅比例。
2. 分组字段不符合规范：分组后选取的a.price、a.event、a.created_at是非聚合字段，会返回分组内随机的一条数据，无实际业务意义。
3. 执行顺序误解：WHERE过滤是在聚合计算之前执行的，实际问题并非过滤顺序错误，而是计算逻辑和字段选取的问题。

修正后的SQL方案 #

方案1：基于7天内首次与最后一次成交的价格变化 #

适合查看价格随时间的涨跌趋势：

SELECT 
    contract_address,
    ROUND((last_price - first_price) / first_price * 100, 2) AS percent_change
FROM (
    SELECT 
        contract_address,
        FIRST_VALUE(price) OVER (PARTITION BY contract_address ORDER BY created_at) AS first_price,
        LAST_VALUE(price) OVER (PARTITION BY contract_address ORDER BY created_at ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_price
    FROM nft_market_trade
    WHERE event = 'SOLD'
        AND created_at >= NOW() - INTERVAL 7 DAY
) AS price_data
GROUP BY contract_address, first_price, last_price;

方案2：基于7天内最高价与最低价的变化 #

适合计算区间内价格的最大涨幅：

SELECT 
    contract_address,
    ROUND((MAX(price) - MIN(price)) / MIN(price) * 100, 2) AS percent_change
FROM nft_market_trade
WHERE event = 'SOLD'
    AND created_at >= NOW() - INTERVAL 7 DAY
GROUP BY contract_address;

修正说明 #

• 修正运算符优先级：用括号包裹价格差计算(MAX(price) - MIN(price))，确保先算差值再计算比例。
• 移除无意义字段：仅保留分组字段和计算结果，避免返回随机无效数据。
• 明确执行逻辑：WHERE先过滤出7天内的有效成交记录，再进行分组聚合，确保统计数据的准确性。

内容的提问来源于stack exchange，提问作者muya.dev