#mysql #sql #snowflake-cloud-data-platform
#mysql #sql #snowflake-облачная платформа для обработки данных
Вопрос:
Моя цель — иметь возможность группировать приведенные ниже примеры данных по веб-сайтам поминутно, вычислять zscore / стандартную оценку результирующих данных для количества запросов и добавлять их в отдельный столбец.
Прямо сейчас данные разбиты на временные метки с детализацией по секундам
website timestamp requests
-------- ----------------------------- -----------
espn.com 2020-08-12T16:00:01.000 00:00 4
yahoo.com 2020-08-12T16:00:01.000 00:00 5
espn.com 2020-08-12T16:00:02.000 00:00 10
yahoo.com 2020-08-12T16:00:02.000 00:00 4
espn.com 2020-08-12T16:01:01.000 00:00 1
yahoo.com 2020-08-12T16:01:01.000 00:00 2
espn.com 2020-08-12T16:01:02.000 00:00 3
yahoo.com 2020-08-12T16:01:02.000 00:00 4
Затем это будет сгруппировано в:
website timestamp requests
-------- ----------------------------- -----------
espn.com 2020-08-12 16:00:00 14
yahoo.com 2020-08-12 16:00:00 9
espn.com 2020-08-12 16:01:00 4
yahoo.com 2020-08-12 16:01:00 6
Тогда конечный результат будет выглядеть следующим образом:
website timestamp requests zscore
-------- ----------------------------- ----------- --------
espn.com 2020-08-12 16:00:00 14 0.707
yahoo.com 2020-08-12 16:00:00 9 0.707
espn.com 2020-08-12 16:01:00 4 -0.707
yahoo.com 2020-08-12 16:01:00 6 -0.707
Zscore сравнивает запрос только на текущую минуту с другими строками для соответствующего веб-сайта. Например, zscore для первой строки будет:
(14 - Average requests for espn.com (9)) / Standard Deviation for espn.com(7.07)
Ответ №1:
Вы можете использовать функции агрегирования и окна:
select website, date_trunc(minute, timestamp), sum(requests),
(sum(requests) - avg(sum(requests)) over (partition by website)) /
nullif(stddev(sum(requests)) over (partition by website), 0)
from t
group by website date_trunc(minute, timestamp)
Комментарии:
1. @LukeSchoenberger . . . Как я бы и везде, используя
NULLIF().