分组和旋转自定义维度 [英] Grouping and pivoting custom dimensions

问题描述

我在4个国家为4个网站的客户工作。我们的部分博客文章已翻译并发布在4个网站上。那么，发生什么事情是，我有四个不同的国家访问每个职位的唯一身份证。

I'm working for a client with 4 website in 4 country. Some of our blog posts are translated and published on the 4 website. So what happen is that I have one unique id for each post with visit in four different country.

我希望能够查询每个帖子的访问次数（所以每个职位ID）为每个国家。

I would like to be able to query the number of visit for each post (so each post id) for each country.

我运行了以下查询，但它不起作用：

I've run the following query but it doesn't work :

SELECT * FROM (
SELECT hits.customDimensions.value AS postid
FROM [storied-toolbox-115472:1127229124.ga_sessions_20161108]
WHERE hits.customDimensions.index=10), (SELECT hits.customDimensions.value AS Country,
count(totals.visits) as visit,
FROM [storied-toolbox-115472:1127229124.ga_sessions_20161108]
WHERE hits.customDimensions.index=1
group by
Country)

查询正确运行，但结果是我有帖子ID国家被引用为空。如果我有国家，帖子ID被引用为空。

The query run correctly but in the result is I have the post id the country is referenced as null. If I have the country the post id is referenced as null.

推荐答案

没有机会做任何测试，所以下面大多是盲目的拍摄，但我通常擅长这个。如果这是错误的，请不要判断：o）

Don't have chance to do any testing so below is mostly "blind" shot, but I am usually good at this. Don't judge hard if this is wrong one :o)

SELECT
  postid,
  country,
  SUM(visits) AS visits
FROM (
  SELECT 
    visitid, 
    hits.customDimensions.value AS postid
  FROM [storied-toolbox-115472:1127229124.ga_sessions_20161108]
  WHERE hits.customDimensions.index = 10
) AS posts
JOIN (
  SELECT 
    visitid, 
    hits.customDimensions.value AS country,
    SUM(totals.visits) AS visits
  FROM [storied-toolbox-115472:1127229124.ga_sessions_20161108]
  WHERE hits.customDimensions.index = 1
  GROUP BY visitid, country
) AS stats
ON posts.visitid = stats.visitid 
GROUP BY postid, country

这篇关于分组和旋转自定义维度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！