Mysql Compare for the change in the price for today and yesterday data

Question

I have MySQL table like below with sample data

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

I'm running the below query today morning to get data

 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;

I'm not sure this is correct, I know this has to do with an inner join but it is taking a long time to get data and can somebody highlight if I'm missing anything or correct if I'm doing wrong.

I want to get data by aggregator id to see a change in the cost for today and yesterday data

Here is the sample insert queries

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
)

Mysql Compare for the change in the price for today and yesterday data

0 Answers