gpt4 book ai didi

sql-server - 在 bigquery 中交叉应用等效的 MSSQL 运算符

转载 作者:行者123 更新时间:2023-12-04 12:43:21 24 4
gpt4 key购买 nike

在 Sql server syntaxe 或 bigquery syntaxe 中是否有等效的交叉应用。

我需要在没有交叉应用的情况下编写这个查询:

   select *
from t1
cross apply (select top 1 col1,col2,col3
from t2
where col1 = t1.col1
and (col2 = '0' or col2 = t1.col2)
and (col3 = '0' or (col3 = left(t1.col3) and t1.col4 = 'fr'))
order by col2 desc, col3 desc)

名为“Delivery”的 t1 包含交付信息:

Delivery_ID,Delivery_ShipmentDate,Country_Code,address_ZipCode and the providerservice_id

名为“ProviderService”的 t2 包含有关 providerservice 的信息:

Providerservice_id,DCountry_ID , DZone_Code  as well as a numeric ProviderService_DeliveryTime.

所以每个 Delivery_ID 都有一个 ProviderService_ID对于同一个 Providerservice,我们可以根据此表中的其他列(DCountry_ID,DZone_Code)有几个不同的 ProviderService_DeliveryTime

所以最终的查询将是:

    Select t1.Delivery_ShipmentDate,t2.ProviderService_DeliveryTime
from Delivery t1
cross apply (select top 1 ProviderService_DeliveryTime,DCountry_ID , DZone_Code
from ProviderService
where ProviderService_id = t1.ProviderService_id
And (DCountry_ID = '0' or DCountry_ID = t1.Country_Code)
And (DZone_Code = '0' or (DZone_Code = left(t1.address_ZipCode,2) and t1.Country_Code='FR'))
order by t2.DCountry_ID desc, t2.DZone_Code desc)sq

事实上,我需要先在 sql server syntaxe 中编写相同的查询,然后在 Bigquery 中编写它,因为 BigQuery 不识别“交叉应用”运算符。

我试着用 row_number 窗口函数来做,但没有成功:

    with cte as (Select t1.Delivery_ShipmentDate,t2.ProviderService_DeliveryTime,row_number() over (partition by t2.providerservice_id order by t2.DCountry_ID desc, t2.DZone_Code desc) as num
from Delivery t1
inner join providerservice t2 on t1.providerservice_id = t2.providerservice_id
And (DCountry_ID = '0' or DCountry_ID = t1.Country_Code)
And (DZone_Code = '0' or (DZone_Code = left(t1.address_ZipCode,2) and t1.Country_Code='FR')))


select * from cte where num = 1

它仅在我按 delivery_id 过滤时有效:

 with cte as (Select t1.Delivery_ShipmentDate,t2.ProviderService_DeliveryTime,row_number() over (partition by t2.providerservice_id order by t2.DCountry_ID desc, t2.DZone_Code desc) as num
from Delivery t1
inner join providerservice t2 on t1.providerservice_id = t2.providerservice_id
And (DCountry_ID = '0' or DCountry_ID = t1.Country_Code)
And (DZone_Code = '0' or (DZone_Code = left(t1.address_ZipCode,2) and t1.Country_Code='FR'))
where delivery_id = xxxx)


select * from cte where num = 1

有人可以帮我吗?谢谢!!

最佳答案

以下是 BigQuery 标准 SQL

#standardSQL
SELECT * EXCEPT(pos)
FROM (
SELECT *
ROW_NUMBER() OVER(PARTITION BY TO_JSON_STRING(t1) ORDER BY t2.col2 DESC, t2.col3 DESC) pos
FROM t1 INNER JOIN t2
ON t2.col1 = t1.col1
AND (t2.col2 = '0' OR t2.col2 = t1.col2)
AND (t2.col3 = '0' OR (t2.col3 = left(t1.col3) AND t1.col4 = 'fr'))
)
WHERE pos = 1

我希望你给了我们一些数据来玩 - 但没有它 - 以上只是供你尝试的快速拍摄

我对照带有客户和订单表的经典示例检查了这种方法(在 BigQuery 中实现 CROSS APPLY),它有效

关于sql-server - 在 bigquery 中交叉应用等效的 MSSQL 运算符,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55355582/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com