代码之家 › 专栏 › 技术社区 › Pentium10

在Google Bigquery中运行不同GROUP_NCAT的语法

google-bigquery

Pentium10 · 技术社区 · 11 年前

我有这个疑问:

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;

我希望运行一个不同的GROUP_NCAT,因为现在输出中重复相同的条目。

更新

我已将您的解决方案扩展到:

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;

但现在我得到了utm_campaign的重复值。

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

这是子查询的原始输出,在qroup by之前

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

1 回复 | 直到 11 年前

N.N. 11 年前

使用子查询进行分组并获得不同的值。类似于:

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;

少数聚合字段的另一种选择是分阶段执行。。。

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
    ;

推荐文章