语法在Google BigQuery中运行不同的GROUP_CONCAT [英] Syntax to run a distinct GROUP_CONCAT in Google Bigquery

问题描述

  SELECT campaign.id AS campaign_id，
 GROUP_CONCAT（utm.campaign）AS utm_campaign 
 FROM [email_event] 
 WHERE（TIMESTAMP BETWEEN SEC_TO_TIMESTAMP（1412136000）和SEC_TO_TIMESTAMP（1414814340））
 GROUP BY campaign_id; 
  

我喜欢运行一个独特的GROUP_CONCAT，因为现在在输出中重复了相同的条目。 / p>

更新

我已将您的解决方案扩展至此：

  SELECT campaign.id AS campaign_id，
 GROUP_CONCAT（utm.campaign）作为utm_campaign，
 GROUP_CONCAT（utm.content） AS utm_content 
 FROM 
（SELECT * 
 FROM 
（SELECT 507 AS campaign.id，
'remarketingemail'AS utm.campaign，
'newsletter_feb' AS utm.content），
（SELECT 508 AS campaign.id，
'remarketingemail'AS utm.campaign，
'newsletter_jan'AS utm.content），
（SELECT 508 AS campaign.id，
'remarketingemail'AS utm.campaign，
'newsletter_feb'AS utm.content），
（SELECT 508 AS campaign.id，
'adwordscamp'AS utm.campaign，
 'cyber_monday'as utm.content））
 GROUP BY campaign_id; 
  

但是现在我重复了utm_campaign的值。

  + ----- + ------------------------------- ----------- + -------------------------------------- + 
 | 507 | remarketingemail | newsletter_feb | 
 | 508 | remarketingemail，remarketingemail，adw ... | newsletter_jan，newsletter_feb，cyb ... | 
 + ----- + --------------------------------------- --- + -------------------------------------- + 
  
 
 
 $ b 这是子查询上的原始输出， p>  + ----- + ----------------------------------- + ------------------------------- + 
 | 507 | remarketingemail | newsletter_feb | 
 | 508 | remarketingemail | newsletter_jan | 
 | 508 | remarketingemail | newsletter_feb | 
 | 508 | adwordscamp | cyber_monday | 
 + ----- + ----------------------------------- + --- ---------------------------- + 
  




解决方案

使用子查询进行分组并获取不同的值。像这样：

  SELECT campaign.id AS campaign_id，
 GROUP_CONCAT（utm.campaign）AS utm_campaign 
 FROM 
（选择campaign.id，utm.campaign 
 FROM [email_event] 
 WHERE（TIMESTAMP BETWEEN SEC_TO_TIMESTAMP（1412136000）和SEC_TO_TIMESTAMP（1414814340））
 GROUP EACH BY运动。 id，utm.campaign）
 GROUP BY campaign_id; 
  

少数汇总字段的另一个选项是分阶段执行...

 选择campaign_id，
 GROUP_CONCAT（utm_campaign）作为utm_campaign，
 utm_content 
从
（选择活动。 id AS campaign_id，
 utm.campaign as utm_campaign，
 GROUP_CONCAT（utm.content）AS utm_content 
 FROM 
（
 SELECT * 
 FROM 
（SELECT 507 AS campaign.id，
'remarketingemail'AS utm.campaign，
'newsletter_feb'AS utm.content），
（SELECT 507 AS campaign.id，
 'remarketingemail2'as utm.campaign，
'newsletter_feb'AS utm.content），
（SELECT 508 AS campaign.id，
'remarketingemail'AS utm.campaign，
' newsletter_jan'AS utm.content），
（SELECT 508 AS campaign.id，
'remarketingemail'AS utm.campaign，
'newsletter_feb'AS utm.conte nt）
）
 GROUP BY utm_campaign，campaign_id）
 GROUP BY utm_content，campaign_id 
; 
  




I have this query:

SELECT campaign.id AS campaign_id,
       GROUP_CONCAT(utm.campaign) AS utm_campaign
FROM [email_event]
WHERE (TIMESTAMP BETWEEN SEC_TO_TIMESTAMP(1412136000) AND SEC_TO_TIMESTAMP(1414814340))
GROUP BY campaign_id;

And I would love to run a distinct GROUP_CONCAT, as now same entries are repeated in the output.

UPDATE

I've extended your solution to this:

SELECT campaign.id AS campaign_id,
       GROUP_CONCAT(utm.campaign) AS utm_campaign,
       GROUP_CONCAT(utm.content) AS utm_content
FROM
  (SELECT *
   FROM
     (SELECT 507 AS campaign.id,
             'remarketingemail' AS utm.campaign,
             'newsletter_feb' AS utm.content),
     (SELECT 508 AS campaign.id,
             'remarketingemail' AS utm.campaign,
             'newsletter_jan' AS utm.content),
     (SELECT 508 AS campaign.id,
             'remarketingemail' AS utm.campaign,
             'newsletter_feb' AS utm.content),
     (SELECT 508 AS campaign.id,
             'adwordscamp' AS utm.campaign,
             'cyber_monday' AS utm.content) )
GROUP BY campaign_id;

But now I got repeated values for utm_campaign.

+-----+------------------------------------------+--------------------------------------+
| 507 | remarketingemail                         | newsletter_feb                       |
| 508 | remarketingemail,remarketingemail,adw... | newsletter_jan,newsletter_feb,cyb... |
+-----+------------------------------------------+--------------------------------------+

this is the raw output on the subquery, before qroup by

+-----+-----------------------------------+-------------------------------+
| 507 | remarketingemail                  | newsletter_feb                |
| 508 | remarketingemail                  | newsletter_jan                |
| 508 | remarketingemail                  | newsletter_feb                |
| 508 | adwordscamp                       | cyber_monday                  |
+-----+-----------------------------------+-------------------------------+

解决方案

Use a subquery to group by and get distinct values. something like this:

SELECT campaign.id AS campaign_id,
       GROUP_CONCAT(utm.campaign) AS utm_campaign
FROM
    (Select campaign.id,utm.campaign
    FROM [email_event]
    WHERE (TIMESTAMP BETWEEN SEC_TO_TIMESTAMP(1412136000) AND SEC_TO_TIMESTAMP(1414814340))
    GROUP EACH BY campaign.id,utm.campaign)
    GROUP BY campaign_id;

another option for few aggregated fields is to do it in stages...

SELECT campaign_id ,
       GROUP_CONCAT(utm_campaign) as utm_campaign,
       utm_content
       From
(SELECT campaign.id AS campaign_id,
       utm.campaign as utm_campaign,
       GROUP_CONCAT(utm.content) AS utm_content
FROM
    (
SELECT *
FROM
  ( SELECT 507 AS campaign.id,
           'remarketingemail' AS utm.campaign,
           'newsletter_feb' AS utm.content),
  ( SELECT 507 AS campaign.id,
           'remarketingemail2' AS utm.campaign,
           'newsletter_feb' AS utm.content),
  (SELECT 508 AS campaign.id,
          'remarketingemail' AS utm.campaign,
          'newsletter_jan' AS utm.content),
  (SELECT 508 AS campaign.id,
          'remarketingemail' AS utm.campaign,
          'newsletter_feb' AS utm.content)
      )
    GROUP BY utm_campaign,campaign_id)
    GROUP BY utm_content,campaign_id
    ;

这篇关于语法在Google BigQuery中运行不同的GROUP_CONCAT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！