Запрос AWS Timestream для получения среднего показателя за первый месяц образцов

В 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

Есть ли лучший или более эффективный способ сделать это?

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
0
11
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

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

Фильтрация по агрегатам Как вы убедились на собственном опыте, 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, он находится в результате подзапроса.

  1. Наличие пункта

Если подзапрос вам не по душе, вы можете использовать предложение имея сразу после оператора 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, я получил синтаксическую ошибку, но я посмотрю, смогу ли я играть с подзапросами и оконными операторами.

Eitan 09.04.2022 23:09

Ах облом! Я не знаком с таймстримом, поэтому, возможно, он недоступен. Однако подзапрос должен привести вас к этому!

Tom Ellyatt 10.04.2022 00:16

Другие вопросы по теме