在连接到BigQuery的Google工作表中将表连接到自己

Question

我对SQL非常陌生，不过，无论如何，我在Google查询方面有相当多的经验。我最近已经毕业于在BigQuery中托管我的数据，我需要创建一个表的简单支点，然后将它加入到原来的表中。

我的目标是找到客户最初购买的月份/年，然后创建一个列，其中数据将分配给他们所做的每一个订单。(分配给他们一个采购队列)

我已经研究过如何将数据转换为输出所需的表，但是，我不知道如何在不实际将其写入BigQuery中的另一个表的情况下将其重新连接到原始表。

这是我目前所做的，至少这是我最近的尝试.

CREATE TABLE Cohort_List AS (
    SELECT
      MIN(CONCAT(CAST(EXTRACT(YEAR from datecreated) as string), LPAD(CAST(EXTRACT(MONTH from datecreated) as string),2,'0'))) as Cohort_Date,
      customerid as CL_Customer_ID,
    FROM `fishbowl_raw_data.fishbowl_so`
    GROUP BY customerid
    );
    
    SELECT
      DATE(datecreated) as SO_Date,
      customerid as Customer_ID,
      totalprice as SO_Total,
    FROM `fishbowl_raw_data.fishbowl_so`
    JOIN Cohort_List
    ON `fishbowl_raw_data.fishbowl_so`.`customerid`=Cohort_List.CL_Customer_ID

Answer 1

通过创建一个示例数据集，我成功地解决了这个问题：

​

​

使用下面的查询，我创建了一个表Cohort_List，其中包含将显示每个customerid的COHORT_DATE的Common Table Expression (CTE)。使用CTE，我设法将它与sample_data连接起来，以获得每个customerid的COHORT_DATE。

CREATE TABLE `my-project.mydataset.Cohort_List` AS (
WITH CTE AS (
SELECT
  MIN(datecreated) OVER (PARTITION BY customerid) as COHORT_DATE,
  customerid
FROM `my-project.mydataset.sample_data`
)
SELECT 
  DISTINCT a.customerid as Customer_ID,
  a.datecreated as SO_Date,
  totalprice as SO_Total,
  CONCAT(CAST(EXTRACT(YEAR from b.COHORT_DATE) AS STRING), LPAD(CAST(EXTRACT(MONTH from b.COHORT_DATE) AS STRING), 2, '0')) as Cohort_Date
FROM `my-project.mydataset.sample_data` a
LEFT JOIN CTE b
ON a.customerid = b.customerid
ORDER BY a.customerid, a.datecreated
)

查询my-project.mydataset.Cohort_List时的输出

​

​