I'm trying to calculate variance using sql between two same days of two years

问题: I will try to be simple as possible to make my question crystal-clear. I have a table that's called 'fb_ads' (it's about different facebook compaigns for different stores i...

问题:

I will try to be simple as possible to make my question crystal-clear. I have a table that's called 'fb_ads' (it's about different facebook compaigns for different stores in USA) on BigQuery, it contains the following columns:

  • STORE : name of store
  • CLICKS: number of clicks.
  • IMPRESSIONS: number of impressions of the ad
  • COST: the ad cost
  • DATE: AAAA-MM-DD
  • Frequency: number of visitors of a store

So, I'm trying to calculate the variance between two years 2017 and 2018.

Here is the variance I'm trying to calculate:

Variance_Of_Frequency = ((Frequency in 2018 at date X) - ((Frequency in 2017 at date X))/((Frequency in 2017 at date X)

The problem is, that I'll have to compare the same day of the week close to Date X;

For example, if I have a compaign run on a Monday 2017-08-13, I'll need to compare to another monday in 2018 close to 2018-08-13 (it might be a monday on 2018-08-15 for example).

This is a daily variance!

I tried to make a weekly variance calculating and I don't know if it's correct, here is how I did it:

I first started with aggregating my daily table to a weekly tables using the following query:

creating my weekly_table

SELECT
  year_week,
  STORE,
  min(DATE ) as DATE ,
  SUM(IMPRESSIONS ) AS FB_IMPRESSIONS ,
  SUM(CLICKS ) AS FB_CLICKS ,
  SUM(COST) AS FB_COST ,
  SUM(Frequency) AS FREQUENCY,

FROM (
  SELECT
    *,
    CONCAT(cast(ANNEE as string), LPAD(cast((extract(WEEK from date)) as string), 2, '0') ) AS year_week
  FROM `fb_ads`)
GROUP BY
  year_week,
  STORE,

  ORDER BY year_week

Then I tried to calculate the variance using this:

SELECT
  base.*, (base.frequency-lw.frequency) / lw.frequency as VAR_FF
FROM
  `weekly_table` base
JOIN (
  SELECT
    * EXCEPT (date),
    DATE_ADD(DATE(TIMESTAMP(date)) , INTERVAL 1 Week)AS date
  FROM
    `weekly_table` ) lw
ON
  base.date = lw.date
  AND base.store= lw.store

Anyone has any idea how to do the daily thing or if my weekly queries are correct ?

Thanks!


回答1:

For a given date, you want to know the date of the nearest Monday to the same date in the following year...

SET @dt = '2017-08-17';
SELECT CASE WHEN WEEKDAY(@dt + INTERVAL 1 YEAR) > 3 
            THEN ADDDATE(ADDDATE(@dt + INTERVAL 1 YEAR,INTERVAL 1 WEEK),INTERVAL - WEEKDAY(@dt + INTERVAL 1 YEAR) DAY) 
            ELSE ADDDATE(@dt + INTERVAL 1 YEAR,INTERVAL - WEEKDAY(@dt + INTERVAL 1 YEAR) DAY)
            END x;

Obviously, I could remove all those + INTERVAL 1 YEAR bits by defining @dt that way to begin with.

  • 发表于 2019-03-05 00:56
  • 阅读 ( 161 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除