时间戳

首页 > 解决方案 > 如何在 BIGQUERY 中查询当前和上一季度的数据

date - 如何在 BIGQUERY 中查询当前和上一季度的数据

问题描述

我正在尝试在 BIGQUERY 中获取上一季度和当前季度的记录。但无法弄清楚查询将如何构建。

背景：在第二张表中，它基于 MILESTONE 字符串（本质上是季度“2020_Q 1 _M0”的字符串（GOAL而是基于 MILESTONE 下的书面查询）提取历史成本数字（来自 XYZ2 表）。我可以创建一个动态过滤器，如果当前季度从 Q2 变为 Q3，它会使用当前季度 (Q3) 更新数字，并保留上一季度，即 Q2)

注意：SA_Date 是我的日期格式“2020-05-24”

当前脚本：

-- 1st table pulling the current cost numbers based on current date from XYZ1 table

SELECT CASE WHEN PMD_PRODUCT_NAME LIKE '%MAN%' THEN 'MAN' 
    WHEN PC.COUNTRY ='BR' THEN 'Brazil'
    ELSE GEO.COUNTRY_DESCRIPTION
    END AS Country  
  , PC.PRODUCT_STATUS AS Product_Status 
  , PC.SA_DATE AS SA_Date 
  , MILESTONE 
FROM
-- PC table
  (SELECT *, CASE WHEN SA_DATE < CURRENT_DATE() THEN 'Legacy' ELSE 'NPI' END PRODUCT_STATUS FROM [xxxx] 
  WHERE PMD_PRODUCT_STATUS ='Active') PC

-- Country since PC table has country codes and not description
  JOIN [xxxx] GEO
  ON PC.COUNTRY = GEO.COUNTRY_CODE
-- PCS table joined 
  LEFT JOIN 
    (SELECT APC_CODE 
      , COUNTRY 
      , KEY_CUSTOMER 
      , PRODUCT_VARIANT
      , TMC AS PCS_TMC 
      , BMC AS PCS_BMC
      , TMC_TARGET
    FROM [xxxx] 

-- 2nd table pulling the historical cost numbers (from XYZ2 table) based on MILESTONE string (**GOAL** instead basing on my query on MILESTONE string can I want it dynamic where if the current quarter changes from Q2 to Q3 and also retain the previous quarter which will be Q2)
 
    WHERE PMD_PRODUCT_STATUS = 'Active'
    **AND MILESTONE LIKE ('previous_qtr_modified') -- filtered to specific qtr**
    AND NOT (PMD_PROGRAM_NAME LIKE '%CANCELED%')
    AND LOCKED IS false
    ) PCS
    ON PC.APC_CODE = PCS.APC_CODE
    AND PCS.COUNTRY = GEO.COUNTRY_DESCRIPTION 
    AND PCS.KEY_CUSTOMER = PC.KEY_CUSTOMER
    AND PCS.KEY_CUSTOMER = PC.KEY_CUSTOMER
    AND PC.PRODUCT_VARIANT = PCS.PRODUCT_VARIANT
    LEFT JOIN 
    (SELECT APC_CODE 
      , COUNTRY 
      , KEY_CUSTOMER 
      , PRODUCT_VARIANT
      , TMC AS PCS_TMC1 
      , BMC AS PCS_BMC1
    FROM [xxxx] 
    WHERE PMD_PRODUCT_STATUS = 'Active'
    **AND MILESTONE LIKE ('current_qtr_modified') -- filtered to specific qtr**
    AND NOT (PMD_PROGRAM_NAME LIKE '%CANCELED%')
    AND LOCKED IS false
    ) PCS1
    ON PC.APC_CODE = PCS1.APC_CODE
    AND PCS1.COUNTRY = GEO.COUNTRY_DESCRIPTION 
    AND PCS1.KEY_CUSTOMER = PC.KEY_CUSTOMER
    AND PCS1.PRODUCT_VARIANT = PC.PRODUCT_VARIANT

    WITH date_var AS (SELECT CURRENT_DATE current_date, EXTRACT(QUARTER FROM 
    CURRENT_DATE) As current_qtr, EXTRACT(YEAR FROM CURRENT_DATE) current_year, 
    CONCAT(EXTRACT(YEAR FROM CURRENT_DATE),'_','Q', EXTRACT(QUARTER FROM 
    CURRENT_DATE),'_','M0') current_qtr_modified, CONCAT(EXTRACT(YEAR FROM 
    CURRENT_DATE),'_','Q', EXTRACT(QUARTER FROM CURRENT_DATE)-1,'_','M0') 
    previous_qtr_modified)

    SELECT current_qtr_modified, previous_qtr_modified
    FROM date_var

标签： dategoogle-bigqueryquarter

解决方案

您可以尝试将以下 cte 添加到当前查询中。

WITH
date_var
AS 
(SELECT CURRENT_DATE current_date,EXTRACT(QUARTER FROM CURRENT_DATE) As current_qtr, EXTRACT(YEAR FROM CURRENT_DATE) current_year, CONCAT(EXTRACT(YEAR FROM CURRENT_DATE),'_','Q',EXTRACT(QUARTER FROM CURRENT_DATE),'_','M0') current_qtr_modified,CONCAT(EXTRACT(YEAR FROM CURRENT_DATE),'_','Q',EXTRACT(QUARTER FROM CURRENT_DATE)-1,'_','M0') previous_qtr_modified
),

-- 1st table pulling the current cost numbers based on current date from XYZ1 table

SELECT CASE WHEN PMD_PRODUCT_NAME LIKE '%MAN%' THEN 'MAN' 
    WHEN PC.COUNTRY ='BR' THEN 'Brazil'
    ELSE GEO.COUNTRY_DESCRIPTION
    END AS Country  
  , PC.PRODUCT_STATUS AS Product_Status 
  , PC.SA_DATE AS SA_Date 
  , MILESTONE 
FROM
-- PC table
  (SELECT *, CASE WHEN SA_DATE < CURRENT_DATE() THEN 'Legacy' ELSE 'NPI' END PRODUCT_STATUS FROM [xxxx] 
  WHERE PMD_PRODUCT_STATUS ='Active') PC

-- Country since PC table has country codes and not description
  JOIN [xxxx] GEO
  ON PC.COUNTRY = GEO.COUNTRY_CODE
-- PCS table joined 
  LEFT JOIN 
    (SELECT APC_CODE 
      , COUNTRY 
      , KEY_CUSTOMER 
      , PRODUCT_VARIANT
      , TMC AS PCS_TMC 
      , BMC AS PCS_BMC
      , TMC_TARGET
    FROM [xxxx] 

-- 2nd table pulling the historical cost numbers (from XYZ2 table) based on MILESTONE string (**GOAL** instead basing on my query on MILESTONE string can I want it dynamic where if the current quarter changes from Q2 to Q3 and also retain the previous quarter which will be Q2)
 
    WHERE PMD_PRODUCT_STATUS = 'Active'
    **AND MILESTONE LIKE *previous_qtr_modified* -- filtered to specific qtr**
    AND NOT (PMD_PROGRAM_NAME LIKE '%CANCELED%')
    AND LOCKED IS false
    ) PCS
    ON PC.APC_CODE = PCS.APC_CODE
    AND PCS.COUNTRY = GEO.COUNTRY_DESCRIPTION 
    AND PCS.KEY_CUSTOMER = PC.KEY_CUSTOMER
    AND PCS.KEY_CUSTOMER = PC.KEY_CUSTOMER
    AND PC.PRODUCT_VARIANT = PCS.PRODUCT_VARIANT
    LEFT JOIN 
    (SELECT APC_CODE 
      , COUNTRY 
      , KEY_CUSTOMER 
      , PRODUCT_VARIANT
      , TMC AS PCS_TMC1 
      , BMC AS PCS_BMC1
    FROM [xxxx] 
    WHERE PMD_PRODUCT_STATUS = 'Active'
    **AND MILESTONE LIKE *current_qtr_modified* -- filtered to specific qtr**
    AND NOT (PMD_PROGRAM_NAME LIKE '%CANCELED%')
    AND LOCKED IS false
    ) PCS1
    ON PC.APC_CODE = PCS1.APC_CODE
    AND PCS1.COUNTRY = GEO.COUNTRY_DESCRIPTION 
    AND PCS1.KEY_CUSTOMER = PC.KEY_CUSTOMER
    AND PCS1.PRODUCT_VARIANT = PC.PRODUCT_VARIANT

输出：

Row  current_qtr_modified   previous_qtr_modified   
1       2020_Q3_M0              2020_Q2_M0

上面的 cte 根据当前日期计算当前季度和上一季度，然后连接 year 和 _Q 以匹配您用于过滤里程碑的格式**AND MILESTONE LIKE ('2020_Q2_M0') 使用此 cte，您可以插入current_qtr_modified并插入previous_qtr_modified您对当前和上一季度进行硬编码的位置。

编辑：我已经更新了查询。参考斜体文本。

推荐阅读