mysql - SQL:如何透视列中的维度？

Question

我希望执行以下操作:

原始表:只有两篇文章、它们的流量 channel 和独特的综合浏览量

SELECT * FROM website_content_table

返回:

我想返回一个如下所示的表:

这是我当前的解决方案 - 我认为它不优雅且缓慢。在 BigQuery SQL 中肯定有一种更简单的方法来执行此操作(注意 - 我没有可以使用的数据透视函数)

SELECT pageTitle, 
      sum(unique_pageviews) AS unique_pageviews, 
      sum(organic_unique_pageviews) AS organic_unique_pageviews,
      sum(email_unique_pageviews) AS email_unique_pageviews,
      sum(paid_unique_pageviews) AS paid_unique_pageviews     
FROM (
             -- table for total unique_pageviews
            (SELECT pageTitle, 
                   sum(unique_pageviews) unique_pageviews
            FROM website_content_table 
            GROUP BY pageTitle) AS a

            -- table for organic  unique_pageviews
            LEFT JOIN 
                    (SELECT pageTitle,
                            sum(unique_pageviews) organic_unique_pageviews
                     FROM website_content_table 
                     WHERE traffic_channel = 'Organic'
                     GROUP BY pageTitle) AS organic
            ON a.pageTitle = organic.pageTitle  

            -- table for email  unique_pageviews
            LEFT JOIN 
                    (SELECT pageTitle,
                            sum(unique_pageviews) email_unique_pageviews
                     FROM website_content_table 
                     WHERE traffic_channel = 'Email'
                     GROUP BY pageTitle) AS email
            ON a.pageTitle = email.pageTitle  

            -- table for paid  unique_pageviews
            LEFT JOIN 
                    (SELECT pageTitle,
                            sum(unique_pageviews) paid_unique_pageviews
                     FROM website_content_table 
                     WHERE traffic_channel = 'Paid'
                     GROUP BY pageTitle) AS paid
            ON a.pageTitle = paid.pageTitle  
)
GROUP BY pageTitle
ORDER BY unique_pageviews

Answer 1

有一种更简单的方法可以使用带有条件聚合的数据透视查询来执行此操作:

SELECT
    pageTitle,
    SUM(unique_pageviews) AS unique_pageviews,
    SUM(CASE WHEN traffic_channel = 'Organic' THEN unique_pageviews ELSE 0 END) AS Organic,
    SUM(CASE WHEN traffic_channel = 'Email'   THEN unique_pageviews ELSE 0 END) AS Email,
    SUM(CASE WHEN traffic_channel = 'Paid'    THEN unique_pageviews ELSE 0 END) AS Paid
FROM yourTable
GROUP BY
    pageTitle;