BigQuery的标准SQL中的日期和时间函数

Question

我试图在BigQuery的新标准SQL功能上使用我的MSSQL查询中的一个，因为它需要特殊的JOIN条件。但是，该引用不包含BigQuery标准SQL中日期时间函数的任何解释。

到目前为止，我设法我DATE_ADD声明 从转换：DATE_ADD（ '2015年1月1日'，-13， '月'） 到：DATE_ADD（ '2015年1月1日'，间隔-13 MONTH）

我还将YEAR（x）转换为EXTRACT（YEAR FROM x）。两者似乎都是MySQL类似的语句。

最后，我注意到大多数日期函数的输出必须使用TIMESTAMP转换，然后才能比较它们。

但现在我的查询终于想运行我得到一个“内部错误”。任何人都知道什么可能是错的？这是查询：

SELECT 
    M, 
    Bought12MBeforeM, 
    Bought12MBeforeAndInM, 
    (Bought12MBeforeAndInM * 1.0)/(Bought12MBeforeAndInM + Bought12MBeforeM) AS RepurchaseRate 
FROM (
    SELECT 
    M, 
    COUNT(DISTINCT CASE WHEN MaxM < TIMESTAMP(M) THEN user_id ELSE NULL END) AS Bought12MBeforeM, 
    COUNT(DISTINCT CASE WHEN MaxM >= TIMESTAMP(M) 
     AND MinM < TIMESTAMP(M) THEN user_id ELSE NULL END) AS Bought12MBeforeAndInM 
    FROM (
    SELECT 
     M, 
     user_id, 
     MAX(cart_creation_date) AS MaxM, 
     MIN(cart_creation_date) AS MinM 
    FROM (
     SELECT 
     user_id, 
     cart_creation_date 
     FROM 
     `vex-eu-cloud-sql-001.work_user.flat_orders` 
     WHERE 
     cart_creation_date >= TIMESTAMP(DATE_ADD('2015-01-01', INTERVAL -13 MONTH)) 
     GROUP BY 
     user_id, 
     cart_creation_date) UniqueCarts 
    INNER JOIN (
     SELECT 
     DATE(Day) AS M 
     FROM 
     `vex-custom.wouter.days_2007_2020` 
     WHERE 
     EXTRACT(YEAR 
     FROM 
      Day) >= 2015 
     AND EXTRACT(DAY 
     FROM 
      Day) = 1 
     AND Day < TIMESTAMP(CURRENT_DATE())) Months 
    ON 
     cart_creation_date > TIMESTAMP(DATE_ADD(M, INTERVAL -13 MONTH)) 
     AND cart_creation_date < TIMESTAMP(DATE_ADD(M, INTERVAL 1 MONTH)) 
    GROUP BY 
     M, 
     user_id) BoughtInM 
    GROUP BY 
    M) Results 

Answer 1

错误是由于在标准SQL的WHERE子句中处理TIMESTAMP的错误。目前的解决方法是UNIX_MICROS功能来包装它从TIMESTAMP到INT64翻译，那么你的查询工作：

SELECT 
    M, 
    Bought12MBeforeM, 
    Bought12MBeforeAndInM, 
    (Bought12MBeforeAndInM * 1.0)/(Bought12MBeforeAndInM + Bought12MBeforeM) AS RepurchaseRate 
FROM (
    SELECT 
    M, 
    COUNT(DISTINCT CASE WHEN MaxM < TIMESTAMP(M) THEN user_id ELSE NULL END) AS Bought12MBeforeM, 
    COUNT(DISTINCT CASE WHEN MaxM >= TIMESTAMP(M) 
     AND MinM < TIMESTAMP(M) THEN user_id ELSE NULL END) AS Bought12MBeforeAndInM 
    FROM (
    SELECT 
     M, 
     user_id, 
     MAX(cart_creation_date) AS MaxM, 
     MIN(cart_creation_date) AS MinM 
    FROM (
     SELECT 
     user_id, 
     cart_creation_date 
     FROM 
     `vex-eu-cloud-sql-001.work_user.flat_orders` 
     WHERE 
     UNIX_MICROS(cart_creation_date) >= UNIX_MICROS(TIMESTAMP(DATE_ADD('2015-01-01', INTERVAL -13 MONTH))) 
     GROUP BY 
     user_id, 
     cart_creation_date) UniqueCarts 
    INNER JOIN (
     SELECT 
     DATE(Day) AS M 
     FROM 
     `vex-custom.wouter.days_2007_2020` 
     WHERE 
     EXTRACT(YEAR FROM Day) >= 2015 
     AND EXTRACT(DAY FROM Day) = 1 
     AND UNIX_MICROS(Day) < UNIX_MICROS(TIMESTAMP(CURRENT_DATE()))) Months 
    ON 
     cart_creation_date > TIMESTAMP(DATE_ADD(M, INTERVAL -13 MONTH)) 
     AND cart_creation_date < TIMESTAMP(DATE_ADD(M, INTERVAL 1 MONTH)) 
    GROUP BY 
     M, 
     user_id) BoughtInM 
    GROUP BY 
    M) Results