时间戳

首页 > 解决方案 > 从当前日期提取前一周

google-bigquery - 从当前日期提取前一周

问题描述

我有一张表，我需要从当前日期查找前一周的真假。请参阅下表。 在此处输入图像描述

标签： google-bigquery

解决方案

以下是 BigQuery 标准 SQL

#standardSQL
SELECT 
  ReportDate, 
  ReportWeekCommencing,
  EXTRACT(WEEK FROM ReportDate) = EXTRACT(WEEK FROM ReportWeekCommencing) AS WeekToDate,
  EXTRACT(WEEK FROM DATE_SUB(ReportDate, INTERVAL 1 WEEK)) = EXTRACT(WEEK FROM ReportWeekCommencing) AS PreviousWeek
FROM `project.dataset.table`

您可以使用虚拟数据进行测试，使用上面的示例，如下例所示

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATE '2020-06-18' ReportDate, DATE '2020-06-14' ReportWeekCommencing UNION ALL
  SELECT '2020-03-31', '2020-03-29' UNION ALL
  SELECT '2020-03-29', '2020-03-22' UNION ALL
  SELECT '2020-03-24', '2020-03-22' UNION ALL
  SELECT '2020-03-27', '2020-03-10' 
)
SELECT 
  ReportDate, 
  ReportWeekCommencing,
  EXTRACT(WEEK FROM ReportDate) = EXTRACT(WEEK FROM ReportWeekCommencing) AS WeekToDate,
  EXTRACT(WEEK FROM DATE_SUB(ReportDate, INTERVAL 1 WEEK)) = EXTRACT(WEEK FROM ReportWeekCommencing) AS PreviousWeek
FROM `project.dataset.table`

带输出

Row ReportDate  ReportWeekCommencing    WeekToDate  PreviousWeek     
1   2020-06-18  2020-06-14          true            false    
2   2020-03-31  2020-03-29          true            false    
3   2020-03-29  2020-03-22          false           true     
4   2020-03-24  2020-03-22          true            false    
5   2020-03-27  2020-03-10          false           false

推荐阅读