我正在尝试从数据库中计算数据,但首先我注意到我得到的结果中的奇怪行为,其次,我无法提出考虑重新填充的请求。
我有一张桌子:名称 - 日期时间 - 内容
我想按天对行进行分组,并选择数字的差异以获得消耗。例如:
Name - DateTime - Content
Foo - 22-04-2018 6:00 - 120
Foo - 22-04-2018 10:00 - 119
Foo - 22-04-2018 16:00 - 118
内容已减少,结果应为 -2。我的请求输出 = -2
另一个例子:
Name - DateTime - Content
Foo - 23-04-2018 6:00 - 50
Foo - 23-04-2018 10:00 - 90
Foo - 23-04-2018 16:00 - 120
在这里我们可以注意到这个数字增加了。这意味着我们重新填充了储备,而不是消耗,并且内容增加了。结果应为 -70。我的请求输出:30
我的要求 :
SELECT day,
Abs(Sum(diffn)) AS totN
FROM (SELECT Date(datetime) AS day,
Max(content) - Min(content) AS diffN
FROM logs
WHERE NAME = 'Foo'
AND datetime >= '2018-04-22 00:00:00'
AND datetime <= '2018-04-23 00:00:00'
GROUP BY Date(datetime)) a
GROUP BY day;
但是对于第二个例子,我的结果是 30 而不是 70,我不知道为什么......我希望您帮助更改我的请求并考虑补充,以便我得到我想要的结果。
谢谢!
您需要通过比较最高值和最低值(包括时间(小时((来确定前缀。我在这里使用带有两个子查询的"CASE"函数。
也许你需要把年-月-日颠倒过来,因为我使用的是德语日期时间格式。
SET @datetime = '2018-04-22';
SELECT date(datetime) as day
,(CASE WHEN
(SELECT content FROM logs WHERE date(datetime) = @datetime ORDER BY datetime LIMIT 1)
>
(SELECT content FROM logs WHERE date(datetime) = @datetime ORDER BY datetime desc LIMIT 1)
THEN min(content) - max(content)
ELSE max(content) - min(content) END) as diffN
FROM logs
WHERE Name = 'Foo' AND date(datetime) = @datetime
GROUP BY day(datetime)
ORDER BY datetime
;
这应该可以完成这项工作:
SELECT day(datetime) as day, max(content) - min(content) as diffN
FROM logs
WHERE Name = 'Foo'
AND datetime >= '2018-04-23 00:00:00'
AND datetime <= '2018-04-24 00:00:00'
GROUP BY day(datetime)
此外,更改日期筛选器,它应该介于 23 和 24 之间。
建立第一个和最后一个日期时间及其关联内容。例如
drop table if exists t;
create table t (name varchar(3), dt datetime, content int);
insert into t values
('Foo' , '2018-04-22 06:00:00', 120),
('Foo' , '2018-04-22 10:00:00', 119),
('Foo' , '2018-04-22 16:00:00', 118),
('Foo' , '2018-04-23 06:00:00', 50),
('Foo' , '2018-04-23 10:00:00', 90),
('Foo' , '2018-04-23 16:00:00', 120);
select s.name,lastinday,firstinday,lastinday - firstinday
from
(
select name,dt, content lastinday
from t
where dt = (Select max(dt) from t t1 where t1.name = t.name and date(t1.dt) = date(t.dt))
) s
join
(
select name,dt, content firstinday
from t
where dt = (Select min(dt) from t t1 where t1.name = t.name and date(t1.dt) = date(t.dt))
) t
on t.name = s.name and date(t.dt) = date(s.dt);
+------+-----------+------------+------------------------+
| name | lastinday | firstinday | lastinday - firstinday |
+------+-----------+------------+------------------------+
| Foo | 118 | 120 | -2 |
| Foo | 120 | 50 | 70 |
+------+-----------+------------+------------------------+
2 rows in set (0.00 sec)
为什么要第二次分组:
理想情况下,这应该有效:
SELECT Date(datetime) AS day,
Max(content) - Min(content) AS diffN
FROM logs
WHERE NAME = 'Foo'
AND datetime >= '2018-04-22 00:00:00'
AND datetime <= '2018-04-23 00:00:00'
GROUP BY Date(datetime)
此查询的结果将仅包含 2 行 - 1 表示第 22 天,1 表示第 23 天。无需按天再次分组