如何根据前面的查询过滤最近7天?bigquery

Question

你好，我只是想问一下如何解决这个问题。

如下所示的查询示例。

在我准备的下一个查询中，我想过滤交货日期的最后7天。不要使用current_date，因为最大日期非常晚。假设当前日期为7/12/2022，但查询显示的最大日期为7/07/2022。如何将日期从7/1/2022筛选到7/07/2022?

, Datas1 as 
(select distinct (delivery_due_date) as delivery_date 
, Specialist  
, Id_number
, Staff_Total as Total_Items 
from joining
where Delivery_Due_Date is not null 
)

实际上我在where中使用了max函数，但我得到了一个错误。请帮帮我。

Answer 1

在第一个块中创建了这些数据的示例。在第二个块中对该数据执行选择。在第三区块提取了最大交付数据。从第3个区块收集的数据在7天内限制最后一个区块。

WITH joining AS(
SELECT '2022-07-01' AS delivery_due_date, 'ABC' as Specialist,222 as Id_number, 21 as Staff_Total union all
SELECT '2022-07-07' AS delivery_due_date, 'ABC2' as Specialist,223 as Id_number, 01 as Staff_Total union all
SELECT '2022-07-15' AS delivery_due_date, 'ABC4' as Specialist,212 as Id_number, 25 as Staff_Total union all
SELECT '2022-07-20' AS delivery_due_date, 'AB5C' as Specialist,224 as Id_number, 15 as Staff_Total union all
SELECT '2022-07-05' AS delivery_due_date, 'ABC7' as Specialist,226 as Id_number, 87 as Staff_Total ),

Datas1 as (select distinct (delivery_due_date) as delivery_date , Specialist
, Id_number , Staff_Total as Total_Items from joining where Delivery_Due_Date is not null ),
Datas2 as ( 
select max(delivery_date) as  ddd from Datas1)
select Datas1.* from Datas1,Datas2 where date(delivery_date) between date_sub(date(Datas2.ddd), interval 7 day) and date(Datas2.ddd)