MySQL - 我如何自动执行从最近日期到前一天减去指标的视图查询?日期戳最新数据 [英] MySQL - How Can I Automate a View Query That Subtracts Metrics From the Most Recent Date To The Previous Day & Date Stamps The Most Recent Data
问题描述
我有一个每日 API 调用,它为我提供唯一 ID 的 LIFETIME 数据,并在每天午夜对数据批次进行日期标记.此数据附加到 MySQL 中的数据库,但我需要以一种方式转换它,以便我可以获取每个指标的唯一 id 的每日数据.
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.
我在 MySQL 中的表:
期望输出:
对于 id 3,我采用了前一天 (7/3/2017) 和最近日期 (7/4/2017) 各自的指标(展示次数、点击次数)的差异,并将该行项目的时间戳记为 7/4/2017 年.我需要通过 id 为多个 id 和指标处理这种类型的转换.谢谢!
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!
根据 Jacob 的反馈更新我的查询:
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
推荐答案
这里的关键是将表重新加入到自身中.在下面的查询中,我将重新加入您的表,但对其进行了设置,以便时间戳是连接的限定符.这将加入前一天的记录,并允许您简单地减去它们.
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
这篇关于MySQL - 我如何自动执行从最近日期到前一天减去指标的视图查询?日期戳最新数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!