英文:
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`;
查询结果
-
MAX 返回聚合组中的最大非-NULL值。
英文:
You can use MAX analytic function.
SELECT *, MAX(Supplier) OVER (PARTITION BY LEFT(`Order`, 3)) Sup_fixed
FROM `Database`;
Query results
-
MAX returns the maximum non-NULL value in an aggregated group.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论