MySQL - how can I automate a view query that subtracts metrics from the most recent date to previous dates and dates? Most recent data
I have a daily API call that gives me LIFETIME data for a unique ID and date, which puts a data packet every day at midnight. This data is added to the database in MySQL, but I need to transform it in such a way that I can get daily data for this unique ID for each metric.
My table in MySQL:
Desired output:
For id 3, I took the previous day (3/7/2017) and the most recent date (4/7/2017) the difference for its respective metrics (impressions, clicks) and the timestamp at which this position is 7/4/2017. I need to handle this type of id conversion for multiple IDs and metrics. Thank!
UPDATE to my request 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
source to share
The key point here is to rejoin the table to itself. In the query below, I am returning to your table, but setting it up so that the timestamp is the classifier in the join. This will join the previous day's entries 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
source to share
It looks like the grouping function might be useful as it looks like you want to aggregate impressions and clicks by date and ID. Also, you didn't mention that this data requires any joins (yet), so this is assumed 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
source to share