时间戳

首页 > 解决方案 > 如何在 Google Big Query 的特定范围内查询多个日期

sql - 如何在 Google Big Query 的特定范围内查询多个日期

问题描述

我正在做很长的查询以在某些日期找到具有特定条件的客户，在这种情况下'2019-6-20'，查询是这样的

这是我的代码

select current_date() as date , count(customer_id) as cell13
from(
select customer_id, count(id) as total, string_agg(payment_state order by created_at desc limit 1) as cek
from(
select distinct(A.id), A.customer_id, extract(month from A.created_at) as months,extract(day from A.created_at) as days, extract(year from A.created_at) as years, payment_state, A.created_at, A.grandtotal_cents
from bl.orders as A
left join bl.blacklists as B
on A.customer_id = B.customer_id
where date(A.created_at) >= date_sub(date('2019-6-20') , interval 60 day) and grandtotal_cents > 0 and B.customer_id is null
)
group by customer_id
having cek = "unpaid")

这是结果

Row  date          cell13
1    2019-06-21    696

2019-03-23现在我需要在特定日期范围内查询多个日期，例如2019-06-21. 假设我怎么做，所以输出会喜欢

Row  date          cell13
1    2019-06-21    696
...
90   2019-03-23    ...

标签： sqlgoogle-bigquery

解决方案

generate_date_array()您可以使用and生成日期表，unnest()然后将其与left join.

总的来说，虽然，您的查询是一个难以理解的消息，但这是一个想法：

with dates as (
      select dte
      from (select generate_date_array('2019-03-23', '2016-06-21', interval 1 day) d
           ) d cross join
           unnest(d.d) dte
     )
select . . .
from dates left join
     bl.orders o
     on date(o.created_at) >= date_sub(dte, interval 60 day)
     . . .

推荐阅读