snowflake-cloud-data-platform - 为雪花中多个枢轴具有相同名称的列写别名

Question

我的查询是

SELECT *

FROM  (

WITH as1 AS (
SELECT
    DISTINCT a.COMPANY_NAME,
    b.industry_sector,
    b.INDUSTRY_GROUP,
    c.VERTICAL,
    D.VCROUND,
    D.VCROUND AS VCROUND1,
    D.VCROUND AS VCROUND2,
    D.VCROUND AS VCROUND3,
    D.VCROUND AS VCROUND4,
    D.Deal_date,
    e.EMPLOYEE_COUNT ,
    a.REVENUE,
    a.TOTALRAISED ,
    D.Premoney_Valuation
FROM
    DAT.PIK.PB_C a
LEFT JOIN D.PIT.PB_COMPANY_INDUS b ON
    a.C_ID = b.C_ID
LEFT JOIN D.P.PB_COMPANY_V c ON
    c.C_ID = b.C_ID
    AND a.C_ID = c.C_ID
LEFT JOIN DA.PIT.PB_DEAL D ON
    D.C_ID = c.C_ID
    AND D.c_id = b.c_id
    AND D.C_ID = a.c_id
LEFT JOIN DA.PITCHBOOK.PB_COMP e ON
    e.C_ID = D.C_ID
    AND e.C_ID = c.C_ID
    AND e.C_ID = b.C_ID
    AND e.C_ID = a.C_ID
     where a.COMPANY_NAME ='11Sight'
                                
)
                                
SELECT
    *
FROM
    as1
    pivot(sum(TOTALRAISED) FOR VCROUND IN ('Angel', '1st Round', '2nd Round', '3rd Round', '4th Round', '5th Round')) AS p (
                                                                                                COMPANY_NAME,industry_sector,INDUSTRY_GROUP,VERTICAL,Angel_EmployeeCount,
                                                                                                R1_EmployeeCount,R2_EmployeeCount,R3_EmployeeCount,
                                                                                                R4_EmployeeCount,R5_EmployeeCount)
     pivot(sum(EMPLOYEE_COUNT) FOR VCROUND1 IN ('Angel', '1st Round', '2nd Round', '3rd Round', '4th Round', '5th Round')) 
     AS q 
    

     
    ).

我得到'天使'，'第一轮'，'第二轮'，'第三轮'，'第四轮'，'第五轮'，。能否请您告诉我如何区分从顶部枢轴到底部枢轴的列，这给出了相同的列。

Answer 1

所以通过这个精简的 SQL 和 fake_data:

WITH fake_data AS (
    SELECT *, VCROUND AS VCROUND1 FROM VALUES
        ('comp1', 'ind_1', 'group_1', 'virt_1', 'Angel', 1, 1000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '1st Round', 10, 100000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '2nd Round', 11, 200000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '3rd Round', 12, 300000)
        v(COMPANY_NAME, industry_sector, INDUSTRY_GROUP, VERTICAL, VCROUND, EMPLOYEE_COUNT, TOTALRAISED)
)                            
SELECT *
FROM fake_data fd
    pivot(sum(TOTALRAISED) FOR VCROUND IN ('Angel', '1st Round', '2nd Round', '3rd Round')) AS p 
    (
        COMPANY_NAME,
        industry_sector,
        INDUSTRY_GROUP,
        VERTICAL,
        Angel_EmployeeCount,
        R1_EmployeeCount,
        R2_EmployeeCount,
        R3_EmployeeCount,
        R4_EmployeeCount,
        R5_EmployeeCount
    )
    pivot(sum(EMPLOYEE_COUNT) FOR VCROUND1 IN ('Angel', '1st Round', '2nd Round', '3rd Round')) 
     AS q ;

我得到:

<表类="s-表"><头>COMPANY_NAMEINDUSTRY_SECTORINDUSTRY_GROUP垂直“‘天使’”“‘第一轮’”“‘第二轮’”“‘第三轮’”'天使''第一轮''第二轮''第三轮'<正文>comp1ind_1组_1virt_11,0001comp1ind_1组_1virt_1100,00010comp1ind_1组_1virt_1200,00011comp1ind_1组_1virt_130万12

所以同意这令人困惑..

因此远离 PIVOT，因为它没有增加值(value)，让我们手写如下:

WITH fake_data AS (
    SELECT * FROM VALUES
        ('comp1', 'ind_1', 'group_1', 'virt_1', 'Angel', 1, 1000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '1st Round', 10, 100000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '2nd Round', 11, 200000),
        ('comp1', 'ind_1', 'group_1', 'virt_1', '3rd Round', 12, 300000)
        v(company_name, industry_sector, industry_group, vertical, vcround, employee_count, totalraised)
)  
SELECT 
    company_name,
    industry_sector,
    industry_group,
    vertical,
    SUM(IFF(vcround='Angel', totalraised, null)) AS angel_totalraised,
    SUM(IFF(vcround='1st Round', totalraised, null)) AS r1_totalraised,
    SUM(IFF(vcround='2nd Round', totalraised, null)) AS r2_totalraised,
    SUM(IFF(vcround='3rd Round', totalraised, null)) AS r3_totalraised,

    SUM(IFF(vcround='Angel', EMPLOYEE_COUNT, null)) AS angel_employeecount,
    SUM(IFF(vcround='1st Round', EMPLOYEE_COUNT, null)) AS r1_employeecount,
    SUM(IFF(vcround='2nd Round', EMPLOYEE_COUNT, null)) AS r2_employeecount,
    SUM(IFF(vcround='3rd Round', EMPLOYEE_COUNT, null)) AS r3_employeecount
FROM fake_data
GROUP BY 1,2,3,4;

给出:

<表类="s-表"><头>COMPANY_NAMEINDUSTRY_SECTORINDUSTRY_GROUP垂直“‘天使’”“‘第一轮’”“‘第二轮’”“‘第三轮’”'天使''第一轮''第二轮''第三轮'<正文>comp1ind_1组_1virt_11,000100,000200,00030万1101112

我认为您正在寻找的更多内容。