Блог

Главная - Вопросы по программированию - Функция аналитики не разрешена в группе By

Функция аналитики не разрешена в группе By

#sql #google-bigquery #left-join

#sql #google-bigquery #левое соединение

Вопрос:

У меня есть следующий запрос:

 SELECT t.date, 
t.transactionId,
t.channelGrouping, 
tp.itemRevenue,
t.transactionItemQuantity, 
t.company, 
t.campaign, 
ifnull(replace((regexp_extract(tp.productCategory, r"(^[a-zA-Z0-9_.amp;;  -] )")),"amp;amp;", "amp;"), "N/A") as productCategory,
ifnull(sum(a.adCost),0) as adCost
FROM `kpi.TransactionsByChannel` t left join kpi.TransactionProducts tp
on tp.transactionId = t.transactionId
left join kpi.Ads a
on a.company = t.company and a.date = t.date and a.campaign = t.campaign group by 1,2,3,4,5,6,7,8

которая возвращает мне результат:

введите описание изображения здесь

Однако, как вы можете видеть, adCost неверен и дублируется. Мне нужно получить ее соотношение на основе itemRevenue или просто написать первую строку, а остальные строки должны быть равны 0.

Я попробовал следующий запрос:

 SELECT t.date, 
t.transactionId,
t.channelGrouping, 
tp.itemRevenue,
t.transactionItemQuantity, 
t.company, 
t.campaign, 
ifnull(replace((regexp_extract(tp.productCategory, r"(^[a-zA-Z0-9_.amp;;  -] )")),"amp;amp;", "amp;"), "N/A") as productCategory,
round(adCost*(itemRevenue/SUM(itemRevenue) over (partition by tp.company, tp.productCategory, tp.date)),2) as adCost_adjusted,
FROM kpi.TransactionsByChannel t left join kpi.TransactionProducts tp
on tp.transactionId = t.transactionId
left join kpi.Ads a
on a.company = t.company and a.date = t.date and a.campaign = t.campaign group by 1,2,3,4,5,6,7,8,9

но я получаю сообщение об ошибке:

 Column 9 contains an analytic function, which is not allowed in GROUP BY at [13:99]

Любая помощь была бы действительно оценена!

Комментарии:

1. Вы не можете GROUP BY a SUM() . Весь смысл аналитических функций заключается в том, чтобы вычислять что-либо по группе строк, выбранных GROUP BY . Каков желаемый результат?

2. Просто удалите столбец, который вы вставили SUM() из вашего GROUP BY предложения.

3. @Kaii Но я хочу получить соотношение доходов и разделить стоимость рекламы на основе этого. Чтобы вычислить это, мне нужна сумма. Как я должен с этим разобраться?

4. @Kaii желаемый результат: вместо того, чтобы повторять adCost 4 раза (63,87), разделить 63,87 на 4 на основе коэффициента дохода.

Ответ №1:

Попробуйте что-то вроде этого подхода:

 with ttr as
(
    select 
        t.transactionId,
        sum(tp.itemRevenue) as TotalItemRevenue
    from 
        `kpi.TransactionsByChannel` t 
    inner join 
        kpi.TransactionProducts tp
    on 
        tp.transactionId = t.transactionId
    group by 
        t.transactionId
)

SELECT 
    t.date, 
    t.transactionId,
    t.channelGrouping, 
    tp.itemRevenue,
    t.transactionItemQuantity, 
    t.company, 
    t.campaign, 
    ifnull(replace((regexp_extract(tp.productCategory, r"(^[a-zA-Z0-9_.amp;;  -] )")),"amp;amp;", "amp;"), "N/A") as productCategory,
    ifnull(a.adCost,0) * tp.itemRevenue / ttr.TotalItemRevenue as adCost_adjusted
FROM 
    `kpi.TransactionsByChannel` t 
inner join
    ttr
on
    t.transactionId = ttr.transactionId
inner join 
    kpi.TransactionProducts tp
on 
    tp.transactionId = t.transactionId
left join 
    kpi.Ads a
on 
    a.company = t.company
    and a.date = t.date 
    and a.campaign = t.campaign