3

I have a daily API call that gives me LIFETIME data for a unique ID and date stamps the data batch every day at midnight. This data is appended to a database in MySQL, but I need to transform it in a way where I can get daily data for that unique id for every metric.

My Table in MySQL:

enter image description here

Desired Output:

enter image description here

For id 3 I took the previous day (7/3/2017) and most recent date (7/4/2017) difference for it's respective metrics (Impressions, Clicks) and time stamped that line item as 7/4/2017. I need to process this type of transformation by id for multiple ids and metrics. Thank you!

UPDATE of My Query Based on Jacob's Feedback:

CREATE VIEW `facebook_insights` AS  
SELECT  
  t1.id  
, t1.timestamp  
, t1.message  
, t1.posted  
, t1.permalink_url  
, t1.caption  
, t1.link  
, t1.type  
, t1.post_impressions - t2.post_impressions as Impressions  
, t1.post_impressions_organic - t2.post_impressions_organic as Post_Impressions_Organic  
, t1.post_impressions_paid - t2.post_impressions_paid as Post_Impressions_Paid  
, t1.post_engaged_users - t2.post_engaged_users as Post_Engaged_Users  
, t1.post_consumptions - t2.post_consumptions as Post_Consumptions  
, t1.post_negative_feedback - t2.post_negative_feedback as 
Post_Negative_Feedback  
, t1.post_negative_feedback_unique - t2.Post_Negative_Feedback_Unique as 
Post_Negative_Feedback_Unique  
, t1.post_impressions_fan - t2.post_impressions_fan as Post_Impressions_Fan  
, t1.post_impressions_fan_paid - t2.post_impressions_fan_paid as 
Post_Impressions_Fan_Paid  
, t1.post_engaged_fan - t2.Post_Engaged_Fan as Post_Engaged_Fan  
, t1.post_video_complete_views_organic - 
t2.post_video_complete_views_organic as Post_Video_Complete_Views_Organic  
, t1.post_video_complete_views_paid - t2.post_video_complete_views_paid as 
Post_Video_Complete_Views_Paid  
, t1.post_video_views_10s - t2.post_video_views_10s as Post_Video_Views_10s  
, t1.post_video_views_10s_unique - t2.post_video_views_10s_unique as 
Post_Video_Views_10s_Unique  
, t1.post_video_views_organic - t2.post_video_views_organic as 
Post_Video_Views_Organic  
, t1.post_video_views_paid - t2.post_video_views_paid as 
Post_Video_Views_Paid  
, t1.post_video_views_clicked_to_play - t2.post_video_views_clicked_to_play 
as Post_Video_Views_Clicked_to_Play  

FROM  
unpaid_media.facebook_insight t1  
JOIN unpaid_media.facebook_insight t2  
ON t1.id = t2.id   
and t1.timestamp  = t2.timestamp + INTERVAL 1 DAY
Ulises Sotomayor
  • 159
  • 2
  • 14

2 Answers2

3

The key here is to rejoin the table to itself. In the query below, I'm rejoining your table, but setting it up so that the timestamp is a qualifier on the join. This will join the previous day's records and allow you to simply subtract them.

select
  t1.id
  , t1.timestamp
  , t1.impressions - t2.impressions as impressions
  , t1.clicks - t2.clicks as clicks
from
  table t1
  join table t2
    on t1.id         = t2.id
    and t1.timestamp = t2.timestamp + INTERVAL 1 DAY
Jacobm001
  • 4,431
  • 4
  • 30
  • 51
  • That doesn't look very difficult – Strawberry Jul 05 '17 at 21:37
  • @Strawberry: It's not very difficult per say, but is less convenient than simply using an analytic function... A lot of people have trouble wrapping their heads around a self join – Jacobm001 Jul 05 '17 at 21:38
  • Thanks @Jacobm001 , but now i'm getting this error: **illegal mix of collations (latin1_swedish_ci implicit) and (utf8_general_ci coercible for operation '='** – Ulises Sotomayor Jul 05 '17 at 23:33
  • The table i'm calling from has the collation set to latin1 - default collation – Ulises Sotomayor Jul 05 '17 at 23:36
  • @UlisesSotomayor: this should solve that problem stackoverflow.com/a/1008336/1142368 – Jacobm001 Jul 06 '17 at 02:24
  • @Jacobm001 I changed the collation to 'utf8_general_ci' and I still get the error. Do I need to apply this to my view query? – Ulises Sotomayor Jul 06 '17 at 16:22
  • @UlisesSotomayor: I don't think so, but I think you're better off asking a second question about this particular issue. I'm not as familiar with MySQL as I am with other RDMSs. Sorry :( – Jacobm001 Jul 06 '17 at 16:54
  • @Jacobm001, solved! Thanks for all the help. It turns out that i had my timestamp column set as text. https://stackoverflow.com/questions/8544438/mysql-now-1-day – Ulises Sotomayor Jul 06 '17 at 22:38
  • @UlisesSotomayor: That would do it! Glad to hear it's working :) – Jacobm001 Jul 06 '17 at 22:40
  • @UlisesSotomayor: I only mention it since you're new and I notice you haven't accepted any answers before... Generally speaking, it's a good idea to accept and/or an answer(s) that helps solve your problem. There's a checkmark under the votes icon to accept a question, and the arrows indicate usefulness. Accepting answers gives both you and the answer rep, and upvoting will give the person who asks/answers a rep :) – Jacobm001 Jul 06 '17 at 22:44
  • @Jacobm001 , thanks! I didn't know this. Will do moving forward. – Ulises Sotomayor Jul 06 '17 at 22:52
0

It looks like a grouping function can be useful as it looks like you want to aggregate the impressions and clicks by date and ID. Also, you didnt mention this data required any joins (yet), so this is assuming it's in a single table. Below in T-SQL: 

USE DatabaseName
GO

SELECT T.ID, 
       (CAST(T.TimeStamp) as Date) as Date, 
       SUM(T.Impressions) as TotalImpressionsPerDay, 
       SUM(T.Clicks) as TotalClicksPerDay
FROM TableName as T
GROUP BY ID, (CAST(T.TimeStamp) as Date)
ORDER BY ID ASC
Eric
  • 1
  • The OP has a running total by day. He wants to use that to generate output that shows the daily values. – Jacobm001 Jul 05 '17 at 21:42
  • @Jacobm001 I see, the difference is the per day growth. Strange that the API would spitout lifetime data in such a fashion instead of appending with the fresh data. – Eric Jul 05 '17 at 21:56