Mysql获取按用户分组的百分比变化

Question

我不是sql的专家，我希望在获得每个contract_address的百分比变化方面得到一些帮助。

我想要实现的

使用created_at获取价格price在7天内的百分比变化。

-- 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)

以下是我尝试过的

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;

我的实现的问题是百分比是在where子句之前计算的，即我在应用where子句之前得到百分比更改。

这是我的查询的sqlfiddle链接。http://sqlfiddle.com/#！9/40809f5d/8

我很感激的帮助

Answer 1

您可以在百分比变化计算中编写一个子查询来实现这一点，然后GROUP BYcontract_address。也可以删除ROUND函数以获得更详细的值。

SELECT contract_address,
price,
event,
created_at,
round(price * 100 / (SELECT SUM(price)
FROM   nft_market_trade
WHERE  event = "SOLD"
AND created_at >= now() - interval 7 day
GROUP BY contract_address), 2)
as
percentchange
From   nft_market_trade
WHERE  event = "SOLD"
AND created_at >= now() - interval 7 day
GROUP  BY contract_address 

输出：0%

请参阅Fiddle。