Bigquery表潜在客户列值基于日期 [英] Bigquery Table Lead Column Values Based On Date

问题描述

我想复制(收入)列并将其转移一年，以便进行同比比较.目前正在寻求根据特定日期在Big Query表中获取潜在客户的价值，以实现这一目标，但仍然存在.

I want to duplicate a (revenue) column and shift it one year in order to make YoY comparisons. Currently looking to lead values in a Big Query table based on a specific date to achieve this but stuck.

我使用DATE_ADD创建了一个新列以获取去年的日期，但现在我想在其旁边添加一个列，其中包含基于DATE_ADD日期的收入.

I used DATE_ADD to create a new column to get the date of last year but now I want to get a column next to it with the revenue based on the DATE_ADD date.

一个问题是，并非所有地点都包含相同的日期，这就是为什么更难进行轮班的原因.

One problem is that not all locations include the same date, that's why it's harder to make the shift.

由于无法正确格式化表格，因此我在此处具有预期结果的图像.基本上，Revenue_last_year应该填写与date_add列和正确位置相对应的Revenue列的值.

There is no way to properly format a table so I have an image of the intended result here. Where basically the revenue_last_year should fill in with the value of the revenue column corresponding to the date_add column and the right location.

就我所能进行的查询如下:

The query below is as far as I've been able to go:

SELECT  
Date, 
location, 
revenue,
DATE_ADD(date, INTERVAL -1 YEAR) AS DateAdd,
LEAD(revenue, ##OFFSET## ) OVER (PARTITION BY location ORDER BY date DESC) AS revenue_last_year
FROM
`dataset.table1`

有人对如何将偏移值与正确的日期相关联提出建议吗?还是应该以一种完全不同的方式来处理这个问题?

Does anyone have a suggestion on how to relate the offset value to the right date? Or should I approach this in a completely different way?

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

#standardSQL
SELECT 
  a.date, a.location, a.revenue, 
  DATE_SUB(a.date, INTERVAL 1 YEAR) date_last_year, 
  IFNULL(b.revenue, 0) revenue_last_year 
FROM `project.dataset.table` a
LEFT JOIN `project.dataset.table` b
ON a.location = b.location
AND DATE_SUB(a.date, INTERVAL 1 YEAR) = b.date

您可以像下面的示例一样使用虚拟数据来测试，玩游戏

You can test, play with above using dummy data as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATE '2018-02-20' `date`, 'A' location, 1 revenue UNION ALL
  SELECT '2018-02-20', 'B', 2 UNION ALL
  SELECT '2018-02-21', 'A', 3 UNION ALL
  SELECT '2018-02-22', 'B', 4 UNION ALL
  SELECT '2019-02-20', 'A', 5 UNION ALL
  SELECT '2019-02-20', 'B', 6 UNION ALL
  SELECT '2019-02-21', 'A', 7 UNION ALL
  SELECT '2019-02-21', 'B', 8 UNION ALL
  SELECT '2019-02-22', 'A', 9 UNION ALL
  SELECT '2019-02-22', 'B', 10 
)
SELECT 
  a.date, a.location, a.revenue, 
  DATE_SUB(a.date, INTERVAL 1 YEAR) date_last_year, 
  IFNULL(b.revenue, 0) revenue_last_year 
FROM `project.dataset.table` a
LEFT JOIN `project.dataset.table` b
ON a.location = b.location
AND DATE_SUB(a.date, INTERVAL 1 YEAR) = b.date
-- ORDER BY a.date, a.location  

有结果

Row date        location    revenue date_last_year  revenue_last_year    
1   2018-02-20  A           1       2017-02-20      0
2   2018-02-20  B           2       2017-02-20      0
3   2018-02-21  A           3       2017-02-21      0
4   2018-02-22  B           4       2017-02-22      0
5   2019-02-20  A           5       2018-02-20      1    
6   2019-02-20  B           6       2018-02-20      2    
7   2019-02-21  A           7       2018-02-21      3    
8   2019-02-21  B           8       2018-02-21      0
9   2019-02-22  A           9       2018-02-22      0
10  2019-02-22  B           10      2018-02-22      4    

这篇关于Bigquery表潜在客户列值基于日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！