-1

Hi I have a MySQL table of Facebook pages (fbpagesfancount) that has the total fan count by day since 01 Jan 2016.

The structure is like this:-

Pageid, Pagename, Updated_Date, Fan_Count

There are a number of specific days that are missing and do not therefore have fancount values due to Facebook API issues.

The days that are missing are usually single days, for example, there is a value for the day before and the day after.

I'd like to create a new table that has a record for every day since 01/01/2016 for each page (750 pages) and then update the days that are missing by averaging the day before and the day after the missing date.

Is this possible using MySQL only or should I write a script in PHP that performs this task and if so, any suggestions on the logic would be helpful.

Any other suggestions on how to tackle this issue would be welcome.

Thanks

Jonathan

Jonathan Lyon
  • 3,862
  • 7
  • 39
  • 52
  • any reason for the down vote? – Jonathan Lyon Apr 19 '17 at 18:28
  • Yes Possible several examples on SO. : Here's one to create missing dates between your min/max dates http://stackoverflow.com/questions/36402030/mysql-fill-missing-dates Then with that datset you can cross join to your data set filter so only nulls on your dataset remain. Then, you can use that dataset to define what records to update, join back to your dataset for daybefore/day after and get avg. Note you didn't descirbe if I have the 1st, 2nd, 3rd and 4th and lets say all dates are blank... what would be the avg of 2 and 3? 0? or should it be base don the average fo 31st and 2nd? – xQbert Apr 19 '17 at 18:28
  • Any question that contains phrasing like "I'd like to..." or "I need to..." without showing any attempt to solve the problem tends to attract a lot of negative attention. Stack Overflow is a place where people writing code can get help from others with more experience, or a different perspective on the problem. This question is really thin on examples of effort, as even a ham-fisted, wildly misguided attempt means you're committed to solving the problem and not just foisting it on the Stack Overflow community. – tadman Apr 19 '17 at 18:44

1 Answers1

0

Yes, it is possible in SQL only.
No, you should not attempt it as it is more complicated and for a single shot there's no need.
Yes, write a script in any language you know, for instance PHP.

I'm not sure why you even want to create a new table? You could add a flag to your current table saying its an origional count vs an average, and just find the missing numbers and add them in a script.

Nanne
  • 64,065
  • 16
  • 119
  • 163