时间戳

首页 > 解决方案 > 如何为输出表命名？

sql - 如何为输出表命名？

问题描述

我需要使用两个输出表进行 JOIN（这些表是使用其他表的先前操作产生的）。我想给他们具体的名字来引用他们。我尝试过 ALIAS、AS、INTO，但没有任何效果。我是 SQL 新手，我真的很想在这里得到一些帮助。我的代码如下

WITH SENDS AS (SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Sents
               FROM `linio-bi.marketing_emarsys_legacy.email_sends_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date),

    BOUNCES AS (SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Bounces
               FROM `linio-bi.marketing_emarsys_legacy.email_bounces_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date),

    OPENS AS (SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Opens
               FROM `linio-bi.marketing_emarsys_legacy.email_opens_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date),
    
    COMPLAINED AS(SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Complaints
               FROM `linio-bi.marketing_emarsys_legacy.email_complaints_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date),

    UNSUSCRIBED AS (SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Unsubscribed
               FROM `linio-bi.marketing_emarsys_legacy.email_unsubscribes_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date),

    CLICKS AS (SELECT "CL" AS Country, 
               campaign_id AS ID_Campaign, 
               FORMAT_DATE("%m/%d/%Y",DATE(event_time)) AS Launch_date,
               COUNT(campaign_id) AS Clicks
               FROM `linio-bi.marketing_emarsys_legacy.email_clicks_783270611`
               WHERE EXTRACT(YEAR FROM DATE(event_time)) >= 2019
               GROUP BY ID_Campaign,Launch_date)
   

-- TABLE 1
SELECT A.Country,
A.ID_Campaign,
A.Launch_date, 
IFNULL(A.Sents,0) Sents, 
IFNULL(B.Bounces,0) Bounces,
IFNULL(C.Opens, 0) Opens,
IFNULL(D.Complaints,0) Complaints,
IFNULL(E.Unsubscribed,0) Unsubscribed,
IFNULL(F.Clicks,0) Clicks
FROM SENDS A
LEFT JOIN BOUNCES       B ON B.ID_Campaign = A.ID_Campaign AND B.Launch_date  = A.Launch_date 
LEFT JOIN OPENS         C ON C.ID_Campaign = A.ID_Campaign AND C.Launch_date  = A.Launch_date 
LEFT JOIN COMPLAINED    D ON C.ID_Campaign = A.ID_Campaign AND D.Launch_date  = A.Launch_date 
LEFT JOIN UNSUSCRIBED   E ON C.ID_Campaign = A.ID_Campaign AND E.Launch_date  = A.Launch_date 
LEFT JOIN CLICKS        F ON C.ID_Campaign = A.ID_Campaign AND F.Launch_date  = A.Launch_date 
ORDER BY A.ID_Campaign,A.Launch_date;

-- TABLE 2
SELECT "CL" AS Country, 
T.campaign_id AS ID_Campaign,
T.name AS Campaign_name,
CONCAT(CAST(T.campaign_id as STRING) ,'-',T.name) full_campaign
FROM(SELECT T.*,
  ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY event_time DESC) as seqnum
  FROM `linio-bi.marketing_emarsys_legacy.email_campaigns_v2_783270611` T
) T
WHERE seqnum = 1

我想在 ID_Campaing 上的表 1 和表 2 之间进行 LEFT JOIN，但我也想给它们命名，当然我也想给这个 JOIN 的输出命名。有什么提示吗？

标签： sqlgoogle-bigquery

解决方案

您似乎对常用表表达式非常熟练，令人费解的是为什么您没有采取最后一步将最后两个变成 CTE：

WITH SENDS AS (...),
    BOUNCES AS (...),
    OPENS AS (...),
    COMPLAINED AS(...),
    UNSUSCRIBED AS (...),
    CLICKS AS (...),
    -- now make two more CTE's:
    TABLE1 AS (
        SELECT A.Country,
        A.ID_Campaign,
        A.Launch_date,
        IFNULL(A.Sents,0) Sents,
        IFNULL(B.Bounces,0) Bounces,
        IFNULL(C.Opens, 0) Opens,
        IFNULL(D.Complaints,0) Complaints,
        IFNULL(E.Unsubscribed,0) Unsubscribed,
        IFNULL(F.Clicks,0) Clicks
        FROM SENDS A
        LEFT JOIN BOUNCES       B ON B.ID_Campaign = A.ID_Campaign AND B.Launch_date  = A.Launch_date
        LEFT JOIN OPENS         C ON C.ID_Campaign = A.ID_Campaign AND C.Launch_date  = A.Launch_date
        LEFT JOIN COMPLAINED    D ON C.ID_Campaign = A.ID_Campaign AND D.Launch_date  = A.Launch_date
        LEFT JOIN UNSUSCRIBED   E ON C.ID_Campaign = A.ID_Campaign AND E.Launch_date  = A.Launch_date
        LEFT JOIN CLICKS        F ON C.ID_Campaign = A.ID_Campaign AND F.Launch_date  = A.Launch_date
        ORDER BY A.ID_Campaign,A.Launch_date
    ),
    TABLE2 AS (
        SELECT "CL" AS Country,
        T.campaign_id AS ID_Campaign,
        T.name AS Campaign_name,
        CONCAT(CAST(T.campaign_id as STRING) ,'-',T.name) full_campaign
        FROM(SELECT T.*,
          ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY event_time DESC) as seqnum
          FROM `linio-bi.marketing_emarsys_legacy.email_campaigns_v2_783270611` T
        ) T
        WHERE seqnum = 1)

-- and finally join them
SELECT ...
FROM TABLE1 JOIN TABLE2 ON ...

推荐阅读