从去年获取数据

Question

我想要从bigquery中给定日期的1年前获取数据。 date(DATE_ADD(date(DATE_ADD(timestamp(New_date),(DATEDIFF(timestamp(New_date), CURRENT_DATE())-1), "DAY")), -354, "DAY"))

上面的查询工作，但是当我尝试把它放在case语句中时，我得到null。

sum(case when date(New_day) = date(DATE_ADD(date(DATE_ADD(timestamp(New_date),(DATEDIFF(timestamp(New_date), CURRENT_DATE())-1), "DAY")), -354, "DAY")) then 'this is working' else null end) as lastyeardata

我如何在同一天做了比较同比为1年前的数据？

编辑@米哈伊尔的建议： #standardSQl WITH `yourTable` AS ( SELECT 1 AS id, '2017-08-14' AS New_date, '1234' AS volume UNION ALL SELECT 2 AS id, '2017-08-13' AS New_date, '2345' AS volume UNION ALL SELECT 3 AS id, '2017-08-14' AS New_date, '3456' AS volume UNION ALL SELECT 4 AS id, '2017-08-14' AS New_date, '4567' AS volume UNION ALL SELECT 5 AS id, '2016-08-14' AS New_date, '5678' AS volume UNION ALL SELECT 6 AS id, '2016-08-13' AS New_date, '6789' AS volume UNION ALL SELECT 7 AS id, '2016-08-12' AS New_date, '6789' AS volume UNION ALL SELECT 8 AS id, '2016-08-11' AS New_date, '1011' AS volume ) select New_date ,volume as thisyeardata ,Case WHEN PARSE_DATE('%Y-%m-%d', New_date) = DATE_ADD(PARSE_DATE('%Y-%m-%d', New_date), INTERVAL -1 YEAR) THEN volume ELSE NULL end as lastyearvolume ,DATE_ADD(PARSE_DATE('%Y-%m-%d', New_date), INTERVAL -1 YEAR) as lastyear FROM `yourTable`

出于某种原因lastyearvolume是给我空。

Answer 1

下面是BigQuery的标准SQL（这是真的建议BigQuery的团队使用与传统之一）

#standardSQl 
WITH `yourTable` AS (
    SELECT 1 AS id, '2017-08-14' AS New_date, '2016-08-14' AS New_day UNION ALL 
    SELECT 2 AS id, '2017-08-13' AS New_date, '2016-08-13' AS New_day UNION ALL 
    SELECT 3 AS id, '2017-08-14' AS New_date, '2016-08-12' AS New_day UNION ALL 
    SELECT 4 AS id, '2017-08-14' AS New_date, '2016-08-11' AS New_day 

) 
SELECT 
    COUNT(
    CASE 
     WHEN PARSE_DATE('%Y-%m-%d', New_day) = DATE_ADD(PARSE_DATE('%Y-%m-%d', New_date), INTERVAL -1 YEAR) 
     THEN 'this is working' 
     ELSE NULL 
    END 
) AS lastyeardata 
FROM `yourTable` 

正如你所期望的，因为他们有NEW_DATE和New_day相隔一年两行只是计数，其余的都没有

上面的例子假设你的日期字段STRING类型 的。如果他们是日期类型的 - 你只需要省略PARSE_DATE功能

DATE_ADD有对应 - DATE_SUB - 因此它可以用来代替基于更新的例子DATE_SUB(PARSE_DATE('%Y-%m-%d', New_date), INTERVAL 1 YEAR)

更新：

简单快速的方式来调整你的查询将不起作用：

#standardSQl 
WITH `yourTable` AS (
SELECT 1 AS id, '2017-08-13' AS New_date, '1234' AS volume UNION ALL 
SELECT 2 AS id, '2017-08-14' AS New_date, '2345' AS volume UNION ALL 
SELECT 3 AS id, '2017-08-15' AS New_date, '3456' AS volume UNION ALL 
SELECT 4 AS id, '2017-08-16' AS New_date, '4567' AS volume UNION ALL 
SELECT 5 AS id, '2016-08-13' AS New_date, '5678' AS volume UNION ALL 
SELECT 6 AS id, '2016-08-14' AS New_date, '6789' AS volume UNION ALL 
SELECT 7 AS id, '2016-08-15' AS New_date, '6789' AS volume UNION ALL 
SELECT 8 AS id, '2016-08-16' AS New_date, '1011' AS volume 
) 
SELECT 
    this_year.New_date 
    ,this_year.volume AS thisyeardata 
    ,last_year.volume AS lastyeardata 
FROM `yourTable` AS this_year 
JOIN `yourTable` AS last_year 
ON PARSE_DATE('%Y-%m-%d', last_year.New_date) = DATE_ADD(PARSE_DATE('%Y-%m-%d', this_year.New_date), INTERVAL -1 YEAR)