使用一个列的子字符串来填充第二列中的空白 BigQuery SQL

huangapple go评论131阅读模式
英文:

Using substring of one column to populate blanks in a second column BigQuery SQL

问题

以下是代码的翻译部分:

WITH 
Supplier_fixed AS 
(Select 
Adb.supplier_name AS supplier_a,
Adb.order AS order_a,
Bdb.supplier AS supplier_b,
Bdb.order AS order_b
FROM `Database` AS Adb
LEFT JOIN `Database` AS Bdb
ON Substr(adb.order, 1,3) = Substr(bdb.order, 1,3)
WHERE Bdb.supplier_name IS NOT NULL)

Select DISTINCT order_a, supplier_b
FROM supplier_fixed
Order BY supplier_b

请注意,代码中的表名和字段名已被翻译为中文。如果您需要更多帮助,请告诉我。

英文:

I've got a list of suppliers and a list of their orders where some of the suppliers are null but they're identifiable by the first 3 characters of the order number, i.e a TS1 will always be Tesco and an AS2 will always be Asda.

Supplier Order
Tesco TS1123
Asda AS2123
Sainsb SB1123
TescoM TSM1123
null TS1124

I've tried to create a join where only supplier is filled in using a WHERE NOT NULL clause but it's created a massive table with tons of duplicates

WITH 
Supplier_fixed AS 
(Select 
Adb.supplier_name AS supplier_a,
Adb.order AS order_a,
Bdb.supplier AS supplier_b,
Bdb.order AS order_b
FROM `Database' AS Adb
LEFT JOIN `Database' AS Bdb
ON Substr(adb.order, 1,3) = Substr(bdb.order, 1,3)
WHERE Bdb.supplier_name IS NOT NULL)

Select DISTINCT order_a, supplier_b
FROM supplier_fixed
Order BY supplier_b

All I really want is a table that looks like this

Supplier Order Sup_fixed
Tesco TS1123 Tesco
Asda AS2123 Asda
Sainsb SB1123 Sainsb
TescoM TSM1123 TescoM
NULL TS1124 Tesco

There has to be a more efficient way of doing the above.

答案1

得分: 1

你可以使用MAX分析函数。

SELECT *, MAX(Supplier) OVER (PARTITION BY LEFT(`Order`, 3)) Sup_fixed FROM `Database`;

查询结果

使用一个列的子字符串来填充第二列中的空白 BigQuery SQL

  • MAX 返回聚合组中的最大非-NULL值。

英文:

You can use MAX analytic function.

SELECT *, MAX(Supplier) OVER (PARTITION BY LEFT(`Order`, 3)) Sup_fixed
  FROM `Database`;

Query results

使用一个列的子字符串来填充第二列中的空白 BigQuery SQL

  • MAX returns the maximum non-NULL value in an aggregated group.

huangapple
  • 本文由 发表于 2023年3月9日 20:37:22
  • 转载请务必保留本文链接:https://go.coder-hub.com/75684728.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定