Друзья, помогите решить проблему.
Пытаюсь построить воронку по URL, запрос выдает следующее: "Error: Cannot group by an aggregate."
Данные беру из Google Analytics. Вот сам запрос:
SELECT
COUNT(DISTINCT s1.clientId ) AS s1.User,
s1.page.pagePath
FROM (
SELECT
COUNT(DISTINCT clientId ) as User,
page.pagePath
FROM
[89437389479847487]
GROUP BY user,page.pagePath
HAVING
page.pagePath = 'URL 1'
AND page.pagePath != 'utm'
) AS s1
INNER JOIN (
SELECT
COUNT(DISTINCT clientId ) AS User,
page.pagePath
FROM
[89437389479847487]
GROUP BY user,page.pagePath
HAVING
page.pagePath = 'URL 2'
AND page.pagePath != 'utm') AS s2
ON
s1.User=s2.User
GROUP BY s1.user,s1.page.pagePath
Очень буду признателен за ответ.
Чтоб работало по идее нужно выкинуть из вложенных select`ов группировку по user. Ну и обилие DISTINCT это плохо. Как минимум можно убрать DISTINCT из первого SELECT`а мне так кажется
SELECT COUNT(s1.clientId ) AS s1.User, s1.page.pagePath
FROM (
SELECT
COUNT(DISTINCT clientId ) as User,
page.pagePath
FROM
[89437389479847487]
GROUP BY page.pagePath
HAVING
page.pagePath = 'URL 1'
AND page.pagePath != 'utm'
) AS s1
INNER JOIN (SELECT COUNT(DISTINCT clientId ) AS User, page.pagePath
FROM
[89437389479847487]
GROUP BY page.pagePath
HAVING page.pagePath = 'URL 2' AND page.pagePath != 'utm') AS s2
ON s1.User=s2.User
GROUP BY s1.user,s1.page.pagePath
В итоге вот так заработало:
SELECT
s1.page,
COUNT(s1.clientId ) AS step1,
COUNT(s2.clientId ) AS step2
FROM (
SELECT
page.pagePath AS page,
clientId
FROM
[limetest.limetableempty]
WHERE
page.pagePath CONTAINS 'url'
AND page.pagePath != 'utm_'
GROUP BY 1, 2
) s1
LEFT JOIN (
SELECT
clientId
FROM
[limetest.limetableempty]
WHERE
page.pagePath CONTAINS 'url'
AND page.pagePath != 'utm_'
GROUP BY 1
) s2
ON s1.clientId = s2.clientId
GROUP BY s1.page
Простой
