В AWS Timestream я пытаюсь получить среднюю частоту сердечных сокращений за первый месяц, так как мы получили образцы частоты сердечных сокращений для конкретного пользователя и среднее значение за последнюю неделю. У меня возникли проблемы с запросом, чтобы получить часть первого месяца. Когда я пытаюсь использовать MIN(time)
в предложении where, я получаю сообщение об ошибке: Предложение WHERE не может содержать агрегации, оконные функции или группирующие операции..
SELECT * FROM "DATABASE"."TABLE"
WHERE measure_name = 'heart_rate' AND time < min(time) + 30
Если я добавлю его как столбец и попытаюсь запросить столбец, я получу ошибку: Столбец first_sample_time не существует
SELECT MIN(time) AS first_sample_time FROM "DATABASE"."TABLE"
WHERE measure_name = 'heart_rate' AND time > first_sample_time
Также, если я пытаюсь добавить в MIN(time)
, я получаю сообщение об ошибке: строка 1:18: «+» не может применяться к отметке времени, целому числу
SELECT MIN(time) + 30 AS first_sample_time FROM "DATABASE"."TABLE"
Вот что я наконец придумал, но мне интересно, есть ли лучший способ сделать это?
WITH first_month AS (
SELECT
Min(time) AS creation_date,
From_milliseconds(
To_milliseconds(
Min(time)
) + 2628000000
) AS end_of_first_month,
USER
FROM
"DATABASE"."TABLE"
WHERE
USER = 'xxx'
AND measure_name = 'heart_rate'
GROUP BY
USER
),
first_month_avg AS (
SELECT
Avg(hm.measure_value :: DOUBLE) AS first_month_average,
fm.USER
FROM
"DATABASE"."TABLE" hm
JOIN first_month fm ON hm.USER = fm.USER
WHERE
measure_name = 'heart_rate'
AND hm.time BETWEEN fm.creation_date
AND fm.end_of_first_month
GROUP BY
fm.USER
),
last_week_avg AS (
SELECT
Avg(measure_value :: DOUBLE) AS last_week_average,
USER
FROM
"DATABASE"."TABLE"
WHERE
measure_name = 'heart_rate'
AND time > ago(14d)
AND USER = 'xxx'
GROUP BY
USER
)
SELECT
lwa.last_week_average,
fma.first_month_average,
lwa.USER
FROM
first_month_avg fma
JOIN last_week_avg lwa ON fma.USER = lwa.USER
Есть ли лучший или более эффективный способ сделать это?
Я вижу, что вы столкнулись с несколькими проблемами на пути к вашему решению, и, надеюсь, я смогу прояснить их для вас, а также предложить более чистый способ достижения вашего решения.
Фильтрация по агрегатам Как вы убедились на собственном опыте, SQL не разрешает агрегаты в операторе where, и вы также не можете фильтровать столбцы новый, которые вы создали в операторе select, такие как агрегаты или операторы case, поскольку эти столбцы/результаты не присутствует в запрашиваемой таблице.
К счастью, есть способы обойти это, например:
Сделайте ваш основной запрос подзапросом, а затем отфильтруйте результат этого запроса, как показано ниже.
Select * from (select *,count(that_good_stuff) as total_good_stuff from tasty_table group by 1,2,3) where total_good_stuff > 69
Это работает, потому что совокупный столбец (количество) больше не является агрегатом во время его вызова в операторе where, он находится в результате подзапроса.
Если подзапрос вам не по душе, вы можете использовать предложение имея сразу после оператора group by, который действует как оператор where, за исключением того, что он предназначен исключительно для обработки агрегатов.
В большинстве случаев это лучше, чем прибегать к подзапросу, так как он более удобочитаем и, как мне кажется, более эффективен.
select *,count(that_good_stuff) as total_good_stuff from tasty_table group by 1,2,3 having total_good_stuff > 69
Наконец, оконные операторы — это фантастика... они действительно помогли сжать многие запросы, которые я делал в прошлом, устранив необходимость в подзапросах/выражениях. Если бы вы могли поделиться некоторыми примерами необработанных данных (конечно, удалить все pii), я был бы рад поделиться примером для вашего варианта использования.
Тем не менее, надеюсь, что это поможет! Том
Ах облом! Я не знаком с таймстримом, поэтому, возможно, он недоступен. Однако подзапрос должен привести вас к этому!
Спасибо! Когда я пытался «иметь» ранее в Timestream, я получил синтаксическую ошибку, но я посмотрю, смогу ли я играть с подзапросами и оконными операторами.