Planning Stage - Filling in Blank Data

[iwpg]

I have several hundred thousand records that will add a monthly db row if new data exists. I plan on working this info into a chart, but I see no reason to add values that contain 0. For 350,000 records, doing it this way reduces new monthly records to about 8,500, vs 350,000 - a huge performance improvement if this is possible.

 

I figured that an array will help solve this.

 

For example, looping over the data in SQL, will return something like:

 

while ($r = $result->fetch_assoc()){

$id[] = $r["id"];

$revenue[] = $r["revenue"]; // Revenue for example

$date[] = $r["date"]; // 2016-07

}

 

So, the result will have the dates, id's and revenue stored as an array. Using ksort() will sort the dates, however is there a plausible way to add missing dates (that have 0 values)?

 

Sorry for not being more clear, this is the best way I could describe this. I thank you for your help. DB storage is limited and expensive, and this is a critical step on the long term project.

[benanamen]

 I for one dont understand what your doing. Forget your attempted solution for a minute. Try to describe better what you have and what it needs to do.

 

I have several hundred thousand records that will add a monthly db row if new data exists.

 

This reads like you have all those records that will add a row, as in a single row. That doesn't make sense at the moment. Where is the hundred thousand records coming from?  Best I can tell is you have three columns, an id, revenue, and date. If the dates have a zero value what date are you going to replace it with? Why do some have a zero for a date in the first place?

Edited February 2, 2017 by benanamen

[iwpg]

Hi Benanamen,

 

I need this data to use with highCharts.js, and will plot the data by month. So for example:

2016-05 Revenue: $20

2016-08 Revenue: $10

2016-11 Revenue: $50

 

How can I populate the empty data where no month exists in between?

To chart this out in a way that makes sense, I need this:

 

2016-05 Revenue: $20

2016-06 Revenue: $0

2016-07 Revenue: $0

2016-08 Revenue: $10

2016-09 Revenue: $0

2016-10 Revenue: $0

2016-11 Revenue: $50

2016-12 Revenue: $0 // This month, or months after last pulled record up until now

 

The records are coming from traffic to a website. There are 350,000 original database rows for traffic sources. I am trying to collect archived info on a monthly level.

 

I hope that this clarifies my original post.

2016-05 Revenue: $20

Edited February 2, 2017 by iwpg

[benanamen]

That is very much clearer and easily understandable. Unfortunately I am done for the night so I can't help you any further right now. I am sure someone will probably give you direction before I can get back to you.

 

In the meantime, get familiar with the XY Problem in my signature. It will help you and us in the future.

Edited February 2, 2017 by benanamen

[Barand]

When I need to extract chart data covering a range of dates I find it easier to create a temporary table containing all the dates that I need to show on the chart. I use a DatePeriod object to create the range of dates EG

mysql> select * from tempdate;
+---------+
| month   |
+---------+
| 2016-01 |
| 2016-02 |
| 2016-03 |
| 2016-04 |
| 2016-05 |
| 2016-06 |
| 2016-07 |
| 2016-08 |
| 2016-09 |
| 2016-10 |
| 2016-11 |
| 2016-12 |
+---------+

So if I have a revenue table like yours ...

mysql> select * from revenue;
+---------+---------+
| month   | revenue |
+---------+---------+
| 2016-05 |      20 |
| 2016-08 |      10 |
| 2016-11 |      50 |
+---------+---------+

... I can fill in the gaps with

SELECT t.month
  , IFNULL(r.revenue, 0) as revenue
FROM tempdate t 
LEFT JOIN revenue r USING (month);

+---------+---------+
| month   | revenue |
+---------+---------+
| 2016-01 |       0 |
| 2016-02 |       0 |
| 2016-03 |       0 |
| 2016-04 |       0 |
| 2016-05 |      20 |
| 2016-06 |       0 |
| 2016-07 |       0 |
| 2016-08 |      10 |
| 2016-09 |       0 |
| 2016-10 |       0 |
| 2016-11 |      50 |
| 2016-12 |       0 |
+---------+---------+

[iwpg]

Thank you Barand, this is a great solution. Problem solved.

 

I certainly appreciate it!