google-cloud-platform - 数据变化时查询

Question

我有一张包含一年中每一天的产品、成本和日期的表格，例如

prod1, 1, 01/01/2020    
prod1, 1, 02/01/2020    
prod1, 2, 03/01/2020   
prod1, 2, 04/01/2020   
prod1, 1, 05/01/2020  
prod1, 1, 06/01/2020

我如何生成一个查询来为我生产产品、成本和日期，每个产品都按顺序出现在每次更改的第一个和最后一个，即不是

prod1, 1, 01/01/2020 first, 06/01/2020 last 
prod1, 2, 03/01/2020 first, 04/01/2020 last 

但

prod1, 1, 01/01/2020 first, 01/01/2020 last 
prod1, 2, 02/01/2020 first, 04/01/2020 last 
prod1, 1, 05/01/2020 first, 06/01/2020 last 

我知道这一定与 OVER 子句有关，但我正在为它对我的工作方式而苦苦挣扎

注意有多种产品

非常感谢任何帮助

标签： google-cloud-platformgoogle-bigquery

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
select product, cost, min(date) first_date, max(date) last_date
from (
  select product, cost, date, 
    countif(cost_changed) over(partition by product order by date) grp
  from (
    select product, cost, date, 
      cost != lag(cost) over(partition by product order by date) cost_changed 
    from `project.dataset.table`
  )
)
group by product, cost, grp

如果应用于您问题的样本数据 - 输出是

您可以使用下面的 CTE 测试、玩上面

with `project.dataset.table` as (
  select 'prod1' as product, 1 as cost, DATE '2020-01-01' as date union all
  select 'prod1', 1, '2020-01-02' union all
  select 'prod1', 2, '2020-01-03' union all
  select 'prod1', 2, '2020-01-04' union all
  select 'prod1', 1, '2020-01-05' union all
  select 'prod1', 1, '2020-01-06'
)