Как запустить SUM () OVER PARTITION BY для COUNT DISTINCT

Я пытаюсь получить количество отдельных пользователей для каждого события на ежедневном уровне, сохраняя при этом текущую сумму за каждый час. Я использую Athena / Presto в качестве механизма запросов.

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

SELECT
    eventname,
    date(from_unixtime(time_bucket)) AS date,
    (time_bucket % 86400)/3600 as hour,
    count,
    SUM(count) OVER (PARTITION BY eventname, date(from_unixtime(time_bucket)) ORDER BY eventname, time_bucket) AS running_sum_count
FROM (
    SELECT 
        eventname,
        CAST(eventtimestamp AS bigint) - CAST(eventtimestamp AS bigint) % 3600 AS time_bucket,
        COUNT(DISTINCT moengageuserid) as count
    FROM clickstream.moengage
    WHERE date = '2020-08-20'
    AND eventname IN ('e1', 'e2', 'e3', 'e4')
    GROUP BY 1,2
    ORDER BY 1,2
);

Но, увидев результаты, я понял, что брать СУММ из COUNT DISTINCT неверно, так как это не складывается.

Итак, я попробовал следующий запрос

SELECT
    eventname,
    date(from_unixtime(time_bucket)) AS date,
    (time_bucket % 86400)/3600 as hour,
    SUM(COUNT(DISTINCT moengageuserid)) OVER (PARTITION BY eventname, date(from_unixtime(time_bucket)) ORDER BY eventname, time_bucket) AS running_sum
FROM (
    SELECT
        eventname,
        CAST(eventtimestamp AS bigint) - CAST(eventtimestamp AS bigint) % 3600 AS time_bucket,
        moengageuserid
    FROM clickstream.moengage
    WHERE date = '2020-08-20'
    AND eventname IN ('e1', 'e2', 'e3', 'e4')
);

Но этот запрос не выполняется из-за следующей ошибки:

SYNTAX_ERROR: line 5:99: ORDER BY expression '"time_bucket"' must be an aggregate expression or appear in GROUP BY clause

sql presto trino partition-by
person nish    schedule 02.02.2021    source источник
comment
Вы используете MySQL или SQLServer? Отметьте только одну БД. Кроме того, было бы полезно, если бы вы могли добавить результат, который вы получили, и результат, который вы ожидаете. И последнее, но не менее важное: несколько примеров данных были бы отличными.   -  person Tyron78    schedule 02.02.2021

Ответы (2)


arrow_upward
0
arrow_downward

Чтобы вычислить текущее количество уникальных идентификаторов, вы можете собрать идентификаторы пользователей в набор (отдельный массив) и получить размер:

cardinality(set_agg(moengageuserid)) OVER (PARTITION BY eventname, date(from_unixtime(time_bucket)) ORDER BY eventname, time_bucket) AS running_sum

Это аналитическая функция, которая присваивает одно и то же значение всему разделу (имя события, дата), вы можете агрегировать записи в верхнем подзапросе с помощью max () и т. Д.

person leftjoin    schedule 02.02.2021

arrow_upward
0
arrow_downward

Подсчитайте первое появление пользователя для текущего подсчета уникальных объектов:

SELECT eventname, date(from_unixtime(time_bucket)) AS date,
       (time_bucket % 86400)/3600 as hour,
       COUNT(DISTINCT moengageuserid) as hour_cont,
       SUM(CASE WHEN seqnunm = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY eventname, date(from_unixtime(time_bucket)) ORDER BY time_bucket) AS running_distinct_count
FROM (SELECT eventname,
             CAST(eventtimestamp AS bigint) - CAST(eventtimestamp AS bigint) % 3600 AS time_bucket,
             moengageuserid as hour_count,
             ROW_NUMBER() OVER (PARTITION BY eventname, moengageuserid ORDER BY eventtimestamp) as seqnum
      FROM clickstream.moengage
      WHERE date = '2020-08-20' AND
            eventname IN ('e1', 'e2', 'e3', 'e4')
    ) m
GROUP BY 1, 2, 3
ORDER BY 1, 2;
person Gordon Linoff    schedule 02.02.2021