BigQuery 中的动态日期

Question

无需手动将日期更改为当前日期，我想要一些代码来帮助自动更改日期或在大查询中将日期自动增加一天 0000hrs

AND ((call_date >= "2022-10-01") AND (call_date <= "2022-10-12"))

下面是完整的代码。

WITH_0 AS ( SELECT *, FROM employee_calldata),
_1 AS (
SELECT
call_date AS __call_date__1,
sub_queue AS __sub_queue__1,
sum(call_count) as callstaken,
mode AS __mode__1, `FROM _0 AS _t
WHERE
(NOT ((call_type) IS NULL)))
AND ((call_date >= "2022-10-01") AND (call_date <= "2022-10-12"))
AND (sub_queue = "Customer_Complaints")
GROUP BY __call_date__1, __sub_queue__1, __mode__1)
SELECT * FROM _1

'

Answer 1

DATE((DATETIME_ADD(('2022-10-03 00:00:00'), INTERVAL 100 HOUR)))

=> 2022-10-07

Answer 2

我认为您要问的是，如何创建随着时间推移而递增的滚动窗口。

相当于：

AND ((DATE(call_date) >= "2022-10-01") AND (call_date <= "2022-10-12"))

是：

AND ((DATE(call_date) >= DATETIME_SUB(CURRENT_DATE(), INTERVAL 2 DAY) AND (DATE(call_date) <= DATETIME_ADD(CURRENT_DATE(), INTERVAL 9 DAY)

这些值将根据当前日期而更改，更改DATETIME_SUB and DATETIME_ADD间隔以更改与当前日期的差异。

还有关于您的代码的其他一些一般性评论。

• WHERE条件不需要括号。

• (NOT ((call_type) IS NULL)))可以写成call_type IS NOT NULL.

• 您不需要您的第一SELECT *, FROM employee_calldata或您的SELECT * FROM _1，因为它们不会做任何额外的事情。

这意味着您的最终查询可以编写为：

SELECT
call_date AS __call_date__1,
sub_queue AS __sub_queue__1,
sum(call_count) as callstaken,
mode AS __mode__1
FROM 
employee_calldata AS _t
WHERE
call_type IS NOT NULL
AND DATE(call_date) >= DATETIME_SUB(CURRENT_DATE(), INTERVAL 2 DAY) 
AND DATE(call_date) <= DATETIME_ADD(CURRENT_DATE(), INTERVAL 9 DAY)
AND sub_queue = "Customer_Complaints"
GROUP BY 
__call_date__1, 
__sub_queue__1,
__mode__1