python - Mysql比较今天和昨天数据的价格变化
问题描述
我有下面的 MySQL 表和示例数据
timestamp |aggregator_id|bare_media_cost
1589868086 | 2 | 0.2
1589868086 | 2 | 0.3
1589868086 | 1 | 2.012
1589868086 | 3 | 2.3535
1589868086 | 1 | 4.343
1589868086 | 2 | 5.43434
1589868086 | 3 | 6.0101
1589954506 | 1 | 7.222
我今天早上正在运行以下查询以获取数据
SELECT g1.aggregator_id as agid
, round(SUM(g1.bare_media_cost/1000),2) AS "cost_one($)"
, round(SUM(g2.bare_media_cost/1000),2) AS "cost_two($)"
FROM TableHourly as g1
JOIN TableHourly as g2
ON g2.aggregator_id = g1.aggregator_id
WHERE g1.timestamp > 1589868086
and g2.timestamp < 1589954506
group
by agid;
我不确定这是否正确,我知道这与内部联接有关,但获取数据需要很长时间,如果我遗漏任何内容,或者如果我做错了,有人可以强调一下。
我想通过聚合器 id 获取数据,以查看今天和昨天数据的成本变化
这是示例插入查询
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 1, 1.01);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 481, 0.35);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 1, 0);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 481, 0.35);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 522, 0.798246);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 481, 0.1558761169);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 544, 0.44005601);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 481, 0.175);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 544, 0.02596);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 481, 1.7378481169);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 1, 0.179);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 481, 0.175);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 1, 0.024);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 481, 0.115183);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 1, 0.1);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 481, 0.1125);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 1, 0.111);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 481, 0.218440364);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 1, 0.416);
insert into TableHourly (timestamp,aggregator_id,bare_media_cost) values (1589880600, 481, 0.1125);
CREATE TABLE TableHourly (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
timestamp VARCHAR(30) NOT NULL,
aggregator_id VARCHAR(30) NOT NULL,
bare_media_cost VARCHAR(30) NOT NULL
)
解决方案
推荐阅读
- reactjs - 如何在对象数组中渲染对象
- sql - postgres:如何创建具有插入和更新访问权限的角色
- pytorch - 如何在不访问类代码的情况下加载 pytorch 模型?
- c# - c#: 使用进程开始倾斜到 Win32Exception 启动 appium 服务器
- less - PhpStorm:“执行‘少’任务”一直闪烁
- python - 这个 python 机器人不会让我运行它
- c - 使用 qsort 对 C 中的结构数组进行排序
- javascript - 我试图让我的网站在每个页面上的购物篮图标上显示购物篮中的物品数量
- amazon-web-services - 使用 cdk 列出所有 LogGroup
- java - xstream.fromXML() 调用使用新版本的 xtsream jar 返回具有空值的对象