BigQuery使用日期和日期范围合并数据集 [英] BigQuery combining data sets using dates and date ranges

问题描述

是否可以通过序列号组合两个表，并使用一个表中的日期范围和另一个表中的日期范围？

我有2个表：table.events包含事件数据追踪器，而table.dates包含追踪器的运作日期。跟踪器可以多次运行，因此每次打开时都具有UID。例如。跟踪器A可以具有UID A1，A2，A3等。

如何将table.event与数据相结合

  Date，Serial，Quality，
 12/01/2019，A，1，
 12/01/2019，B，2，
 13/01/2019，C ，3，
 14/01/2019，A，4，
 15/01/2019，A，5，
 16/01/2019，B，6，
 17 / 01/2019，B，7，
 17/01/2019，C，8，
 17/01/2019，B，9 
  

with table.dates

  Start_Date，End_Date，Serial_id，
 2019年1月15日，18/01/2019，A1，
 08/01 / 2019,14 / 01/2019，A2，
 10/02 / 2019,18 / 01/2019，B1，
 13 / 01 / 2019,16 / 01/2019，C1，
 17/02 / 2019,18 / 01/2019，C2 
  

给我一​​个最终结果，例如

  Date，Serial，Serial_id，Quality，Start Date，End Date 
 12/01/2019，A，A1,1,15 / 01 / 2019,18 / 01/2019 
 12/01/2019，B，B1,2,10 / 02 / 2019,18 / 01/2019 
 13/01/2019，C，C1,3,13 / 01 / 2019,16 / 01/2019 
 14/01/2019，A，A1,4,15 / 01 / 2019,18 / 01/2019 
 15/01/2019，A，A2,5,08 / 01 / 2019,14 / 01/2019 
 16/01/2019，B，B1,6,10 / 02 / 2019,18 / 01/2019 
 17/01/20 19，B，B1,7,10 / 02 / 2019,18 / 01/2019 
 17/01/2019，C，C2,8,17 / 02 / 2019,18 / 01/2019 
 17/01/2019，B，B1,9,10 / 02 / 2019,18 / 01/2019 
  

任何帮助

编辑：

一个日期将包含多个序列号，所以我也想按序列号加入。 / p>

例如

我有跟踪器D1和E1都在2019年1月23日进行跟踪。我在table.events中有D和E的单独条目，因此我需要按日期范围和序列ID的子字符串进行匹配。

解决方案

您是否只想加入？

 选择a。 *，b。* 
来自b.serial上的连接
b 
，例如concat（a.serial，'％'）和
 a.date> = b.start_date和a.date< = b.end_date; 
  

Is there a way to combine 2 tables by serial number and using a date range from one table and dates from the other?

I have 2 tables: table.events contains event data for a tracker, and table.dates contains the operational date of a tracker. A tracker can be in operation multiple times and as such, has a UID every time it is turned on. E.g. Tracker A can have the UID A1, A2, A3 etc.

How can I combine table.events with data such as

Date,Serial,Quality,
12/01/2019,A,1,
12/01/2019,B,2,
13/01/2019,C,3,
14/01/2019,A,4,
15/01/2019,A,5,
16/01/2019,B,6,
17/01/2019,B,7,
17/01/2019,C,8,
17/01/2019,B,9

with table.dates

Start_Date,End_Date,Serial_id,
15/01/2019,18/01/2019,A1,
08/01/2019,14/01/2019,A2,
10/02/2019,18/01/2019,B1,
13/01/2019,16/01/2019,C1,
17/02/2019,18/01/2019,C2

To give me an end result such as

Date,Serial,Serial_id,Quality,Start Date,End Date
12/01/2019,A,A1,1,15/01/2019,18/01/2019
12/01/2019,B,B1,2,10/02/2019,18/01/2019
13/01/2019,C,C1,3,13/01/2019,16/01/2019
14/01/2019,A,A1,4,15/01/2019,18/01/2019
15/01/2019,A,A2,5,08/01/2019,14/01/2019
16/01/2019,B,B1,6,10/02/2019,18/01/2019
17/01/2019,B,B1,7,10/02/2019,18/01/2019
17/01/2019,C,C2,8,17/02/2019,18/01/2019
17/01/2019,B,B1,9,10/02/2019,18/01/2019

Any help would be much appreciated.

Edit:

One date will contain more than one serial number, so I want to also join by serial number.

E.g.

I have trackers D1 and E1 which both were tracking on 23/01/2019. I have seperate entries in table.events for D and E so I will need to match by date range and by a substring of serial id.

解决方案

Do you just want join?

select a.*, b.*
from a join
     b
     on b.serial like concat(a.serial, '%') and
        a.date >= b.start_date and a.date <= b.end_date;

这篇关于BigQuery使用日期和日期范围合并数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！