我需要你的帮助。我正在努力实现以下目标:
获取过去一周内每天的提款和存款利润。
所以我希望得到的价值观:天,存款利润,提款利润。然而,问题是,一天是自定义的一天,意思是:一天介于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"。您可以轻松地编辑上面的查询以删除这些结果。