如何在BigQuery中传入条码-日期元组对执行精准SQL查询？

我需要查询BigQuery中的大型数据表，获取门店内指定条码对应特定日期的数据。每个条码在表中有数千条不同日期的记录，仅按条码查询效率太低，因此我准备了一个包含条码与对应指定日期的元组列表（仅展示子集）：

import datetime

date_and_barcode = [('A4630411929016393', datetime.date(2022, 10, 9)),
 ('A4630411929716390', datetime.date(2022, 10, 9)),
 ('A4630462735016271', datetime.date(2022, 10, 9)),
 ('A4070460677116273', datetime.date(2022, 10, 9)),
 ('A4070460701616276', datetime.date(2022, 10, 9)),
 ('A4630460194116279', datetime.date(2022, 10, 9)),
 ('A4630460205516276', datetime.date(2022, 10, 7)),
 ('A4630460214016271', datetime.date(2022, 10, 9)),
 ('A4630460280316277', datetime.date(2022, 10, 9)),
 ('A4630460281616271', datetime.date(2022, 10, 9)),
 ('A4630450353216276', datetime.date(2022, 10, 11)),
 ('A4220452268816274', datetime.date(2022, 10, 9))]

当前查询的问题 #

我当前的查询语句会返回条码与日期的所有可能组合，而非需要的一一对应组合：

from google.cloud import bigquery

query="""
select   barcode,
         storeinfo1,
         storeinfo2,
         item1

         from `project.dataset.table`
         where barcode IN UNNEST(@label_list) and date in UNNEST(@date_list)
"""

job_config = bigquery.QueryJobConfig(
query_parameters=[
         bigquery.ArrayQueryParameter("label_list", "STRING", label_list),
         bigquery.ArrayQueryParameter("date_list", "STRING", date_list),
            ]
        )
DATA = client.query(query, job_config=job_config).to_dataframe()

失败的尝试 #

我尝试了两种写法但都无法实现需求：

写法一 #

query="""
select   barcode,
         storeinfo1,
         storeinfo2,
         item1

         from `project.dataset.table`
        where barcode in {} and Date in {} 
                                )

            """.format(UNNEST(date_and_barcode)[0], UNNEST(date_and_barcode)[1])

job_config = bigquery.QueryJobConfig(
query_parameters=[
         bigquery.ArrayQueryParameter("date_and_barcode", "STRING", date_and_barcode),

            ]
        )
DATA = client.query(query, job_config=job_config).to_dataframe()

写法二 #

query="""
select   barcode,
         storeinfo1,
         storeinfo2,
         item1

         from `project.dataset.table`
        where barcode in UNNEST(@{}) and Date in UNNEST(@{})
                                )

            """.format(list(zip(*date_and_labels))[0], list(zip(*date_and_labels))[1])

job_config = bigquery.QueryJobConfig(
query_parameters=[
         bigquery.ArrayQueryParameter("date_and_barcode", "STRING", date_and_barcode),

            ]
        )
DATA = client.query(query, job_config=job_config).to_dataframe()

解决方案 #

要实现条码与日期的精确对应匹配，可以通过传递STRUCT类型的数组参数来实现，核心是让BigQuery识别条码和日期的绑定关系，而非分开查询。

方法一：使用JOIN匹配STRUCT数组 #

from google.cloud import bigquery
import datetime

# 转换元组列表为STRUCT格式的字典列表
struct_list = [{"barcode": bc, "date": dt} for bc, dt in date_and_barcode]

# 构造查询语句
query = """
SELECT barcode, storeinfo1, storeinfo2, item1
FROM `project.dataset.table`
JOIN UNNEST(@barcode_date_pairs) AS pairs
ON barcode = pairs.barcode AND date = pairs.date
"""

# 配置查询参数，定义STRUCT结构
job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ArrayQueryParameter(
            "barcode_date_pairs",
            bigquery.StructType([
                bigquery.StructField("barcode", "STRING"),
                bigquery.StructField("date", "DATE")
            ]),
            struct_list
        )
    ]
)

# 执行查询
client = bigquery.Client()
DATA = client.query(query, job_config=job_config).to_dataframe()

方法二：直接在WHERE子句匹配组合 #

这是更简洁的写法，原理和方法一一致：

# 查询语句改为WHERE子句匹配组合
query = """
SELECT barcode, storeinfo1, storeinfo2, item1
FROM `project.dataset.table`
WHERE (barcode, date) IN UNNEST(@barcode_date_pairs)
"""

# 参数配置和方法一完全相同
job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ArrayQueryParameter(
            "barcode_date_pairs",
            bigquery.StructType([
                bigquery.StructField("barcode", "STRING"),
                bigquery.StructField("date", "DATE")
            ]),
            struct_list
        )
    ]
)

DATA = client.query(query, job_config=job_config).to_dataframe()

内容的提问来源于stack exchange，提问作者Serge de Gosson de Varennes