MySQL - Как я могу автоматизировать запрос представления, который вычитает метрики от самой последней даты к предыдущему дню, а метки даты - самые последние данные

Question

У меня есть ежедневный вызов API, который дает мне данные LIFETIME для уникального идентификатора, а также отметки даты для пакета данных каждый день в полночь. Эти данные добавляются в базу данных в MySQL, но мне нужно преобразовать их таким образом, чтобы я мог получать ежедневные данные для этого уникального идентификатора для каждой метрики.

Моя таблица в MySQL:

Желаемый результат:

Для идентификатора 3 я взял разницу предыдущего дня (03.07.2017) и самой последней даты (04.07.2017) для ее соответствующих показателей (показы, клики) и пометил время для этой позиции как 04.07.2017. Мне нужно обработать этот тип преобразования по идентификатору для нескольких идентификаторов и метрик. Спасибо!

ОБНОВЛЕНИЕ моего запроса на основе отзывов Джейкоба:

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  

Answer 1

Ключ здесь состоит в том, чтобы снова присоединиться к столу. В приведенном ниже запросе я воссоединяюсь с вашей таблицей, но настраиваю ее так, чтобы метка времени была квалификатором в соединении. Это объединит записи предыдущего дня и позволит вам просто вычесть их.

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

Answer 2

Похоже, что функция группировки может быть полезна, поскольку вы хотите объединить показы и клики по дате и идентификатору. Кроме того, вы не упомянули, что эти данные требуют каких-либо объединений (пока), поэтому предполагается, что они находятся в одной таблице. Ниже в 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