我需要你的帮助。我正在努力实现以下目标：
获取过去一周内每天的提款和存款利润。
所以我希望得到的价值观：天，存款利润，提款利润。然而，问题是，一天是自定义的一天，意思是：一天介于yyyy-mm-dd 13:00:00和yyyy-mm-dd 13:00:00。因此，按日期分组是不够的。
我尝试过的查询是：
SELECT submit_date,
MAX(deposit_amount) - MIN(deposit_amount) AS deposit,
SUM(withdrawal_amount * withdrawal_percentage) as withdrawal
FROM `pro_Profits`
WHERE account_id = '{C795E1D2-452A-DEE8-A800-02E94332114A}'
AND submit_datetime >= NOW() - INTERVAL 1 WEEK
GROUP BY submit_date
ORDER BY `submit_datetime` DESC
表：
CREATE TABLE IF NOT EXISTS `pro_Profits` (
`id` varchar(512) NOT NULL,
`account_id` varchar(512) NOT NULL,
`submit_date` date NOT NULL,
`submit_time` time NOT NULL,
`submit_datetime` datetime NOT NULL,
`deposit_amount` bigint(20) NOT NULL,
`withdrawal_amount` bigint(20) NOT NULL,
`deposit_percentage` double NOT NULL DEFAULT '1',
`withdrawal_percentage` double NOT NULL DEFAULT '0.4',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `id_2` (`id`),
KEY `account_id` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
您基本上需要做的是每天轮班13个小时。你可以在MySQL中使用一个函数：
TIMESTAMPDIFF(HOUR,13,submit_date)
在您的SQL查询中，它看起来像这样：
SELECT
TIMESTAMPDIFF(HOUR,13,submit_date) as shifted_submit_date,
MAX(deposit_amount)-MIN(deposit_amount) AS deposit,
SUM(withdrawal_amount*withdrawal_percentage) as withdrawal
FROM
pro_Profits
WHERE
account_id = '{C795E1D2-452A-DEE8-A800-02E94332114A}' AND
submit_datetime >= NOW()-INTERVAL 1 WEEK
GROUP BY
shifted_submit_date
ORDER BY
submit_datetime DESC
可能需要一些实验才能得到你想要的东西。我觉得奇怪的是，你按一件事分组，按另一件事排序。
您可以尝试以下操作：
SELECT
FLOOR(TIME_TO_SEC(TIMEDIFF(DATE_ADD(Date(NOW()), INTERVAL 13 Hour),submit_datetime))/86400.00) as Diff,
MAX(deposit_amount)-MIN(deposit_amount) AS deposit,
SUM(withdrawal_amount*withdrawal_percentage) as withdrawal
FROM
pro_Profits
WHERE account_id='{C795E1D2-452A-DEE8-A800-02E94332114A}'
and submit_datetime >= DATE_ADD(Date(NOW()), INTERVAL 13 Hour)-INTERVAL 1 WEEK
GROUP BY
Diff
ORDER BY
Diff
- DATE_ADD（DATE（NOW（）），间隔13小时：您想从今天13:00开始，返回一周
- TIME_TO_SEC（TIMEDIFF（DATE_ADD（日期（NOW（）），INTERVAL 13小时），submit_datetime）/86400.00：计算我们的日期和"submit_detetime"之间的差异（以秒为单位）
- FLOOR（…）：我们得到了这个差异的上限来创建我们的一天"桶"
注意："buckets"的计数实际上是8，如果在13:00之后提交查询，您也可以找到"-1"。您可以轻松地编辑上面的查询以删除这些结果。