如何在大查询中操作SAFE_ORDINAL

Question

再次UPDATED....TRYING：

我正在处理这个查询：

Select FirstName, LastName, MiddleName, Gender, Age, DOB, Address, Address2, City, State, Zip, Zip4, TimeZone, Income, HomeValue, Networth, MaritalStatus, IsRenter, HasChildren, CreditRating, Investor, LinesOfCredit, InvestorRealEstate, Traveler, Pets, MailResponder, Charitable, PolicalDonations, PoliticalParty, Attom_ID, GEOID, Score, Score1, Score2, Score3, Score4, Score5, Latitude, Longitude, Email[SAFE_ORDINAL(1)] Email1, Email[SAFE_ORDINAL(2)] Email2, Email[SAFE_ORDINAL(3)] Email3
from (

Select
  P.FirstName, P.LastName, MiddleName, Gender, Age, DOB, P.Address, Address2, P.City, P.State, P.Zip, Zip4, TimeZone, Income, HomeValue, Networth, MaritalStatus, IsRenter, HasChildren, CreditRating, Investor, LinesOfCredit, InvestorRealEstate, Traveler, Pets, MailResponder, Charitable, PolicalDonations, PoliticalParty, Attom_ID, GEOID, Score, Score1, Score2, Score3, Score4, Score5, Latitude, Longitude, E.Email
from `mother-216719.People.PEOPLE` P
join `mother-216719.People.EMAIL_STAGE` E on 
  P.FirstName = E.FirstName
  and P.LastName = E.LastName
  and P.Address = E.Address
  and P.Zip = E.Zip
 ) a

这是一个人与EMAIL_STAGE之间的直接连接。所有不带别名的P.*字段都来自人，而电子邮件则以字符串的形式来自EMAIL_STAGE。

EMAIL_STAGE表有名、姓、地址、城市、州、Zip和电子邮件。光是那七个领域。PEOPLE表还有其他字段，还有几个不需要的字段。

我加入电子邮件附加电子邮件到人们通过地址匹配。这里的关键是这种关系是一对多的，可能会有很多电子邮件给人们，我想把EMAIL1，EMAIL2，EMAIL3添加到人们身上。我希望这些电子邮件来自电子邮件阶段的一对多的人际关系。

我的第一个建议是使用NTH()执行类似于枢轴的策略，但是后来有人告诉SAFE_ORDINAL[]是我想要的Standard术语。上面的查询是结果，查询中的结构错误是我要解决的问题。

从连接中，我如何将发现的前三封电子邮件(订单不耐)转到EMAIL1、EMAIL2、EMAIL3中？

谢谢。

更新了#2.

STAGE_EMAIL表中的示例数据集：

FirstName|LastName|Address|City|State|Zip|Email

Jael Baird区616龙虾区Melipilla连Santiago|4513|vulputate.ullamcorper.magna@Crasvulputate.edu Yvette x Ellison\P.O.方框847,5270 Ut Rd.|Gasteiz|Euskadi|549851|Quisque.porttitor.eros@Duissit.org Lacota Head Head P.O.方框161,7347 Elit St..Coutisse x NA E5R 7B5|metus.vitae@egestasa.com Victor Hensley 398-3949 Eget，St..Lagos Lagos LI8 8 2ND|rhoncus.Proin@Phasellus.org

Answer 1

下面是用于BigQuery标准SQL的

#standardSQL
SELECT FirstName, LastName, MiddleName, Gender, Age, DOB, Address, Address2, City, State, Zip, Zip4, TimeZone, Income, HomeValue, Networth, MaritalStatus, IsRenter, HasChildren, CreditRating, Investor, LinesOfCredit, InvestorRealEstate, Traveler, Pets, MailResponder, Charitable, PolicalDonations, PoliticalParty, Attom_ID, GEOID, Score, Score1, Score2, Score3, Score4, Score5, Latitude, Longitude, 
  Emails[SAFE_ORDINAL(1)] Email1, Emails[SAFE_ORDINAL(2)] Email2, Emails[SAFE_ORDINAL(3)] Email3
FROM (
  SELECT
    P.FirstName, P.LastName, MiddleName, Gender, Age, DOB, P.Address, Address2, P.City, P.State, P.Zip, Zip4, TimeZone, Income, HomeValue, Networth, MaritalStatus, IsRenter, HasChildren, CreditRating, Investor, LinesOfCredit, InvestorRealEstate, Traveler, Pets, MailResponder, Charitable, PolicalDonations, PoliticalParty, Attom_ID, GEOID, Score, Score1, Score2, Score3, Score4, Score5, Latitude, Longitude, E.Email
  FROM `mother-216719.People.PEOPLE` P
  JOIN (
    SELECT FirstName, LastName, Address, Zip, ARRAY_AGG(Email) Emails
    FROM `mother-216719.People.EMAIL_STAGE`
    GROUP BY FirstName, LastName, Address, Zip
  ) E 
  ON P.FirstName = E.FirstName
  AND P.LastName = E.LastName
  AND P.Address = E.Address
  AND P.Zip = E.Zip
) a

注意:没有测试，所以让我们知道是否仍然存在问题；此外，E的子选择可能会被调整到您需要从EMAIL_STAGE中选择的任何字段--我让您根据需要完成此操作。