Вложенный SQL-запрос: как вернуть по одной выборке из каждого журнала

Question

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

1. Запросите набор журналов, которые содержат данные, относящиеся к моему анализу:

          SELECT  
      runs.object_type as object_type,  
      runs.name as log_name,  
    from project.runs.latest_runs  
    WHERE object_type = "ROCKET"  
    group by object_type, log_name

В результате получается список имен журналов, например, «имя_журнала_2021_09_01», «имя_журнала_2021_09_03» и т. Д.

2. Запрашивать не более одного события с определенным условием из одного известного журнала:

          SELECT  
      object.path_meters as pos,  
      object_speed as speed,  
      log.run as run_name,  
    FROM project.events.last30days  
    WHERE log.run = "log_name_2021_10_01"  
      AND object.speed > 0.0  
    LIMIT 1  

Приведенный выше запрос возвращает не более одного образца для указанного журнала.

Как я могу объединить эти запросы для извлечения образцов из набора журналов, возвращаемого запросом 1, и в то же время не должно быть более одной выборки в журнале?

Обновление :
скажем, БД содержит три журнала:

1. log_name_2021_09_01. Связанный object_type с журналом - РАКЕТА. Журнал содержит 100 тысяч выборок данных: 90 тысяч из них имеют object.speed = 0,0, 10 тысяч имеют скорость> 0,0.
2. log_name_2021_09_02. Связанный object_type с журналом - CAR. Журнал также содержит 100 тыс. Отсчетов с той же пропорцией, что и журнал 1.
3. log_name_2021_09_03. Связанный object_type с журналом - РАКЕТА. Журнал также содержит 100 тыс. Отсчетов с той же пропорцией, что и журнал 1.

Меня интересуют только журналы с типом объекта ROCKET. Этому условию соответствуют два журнала: имя_журнала_2021_09_01 и имя_журнала_2021_09_03. Эти имена журналов можно получить с помощью запроса 1, описанного выше. Я хотел бы извлечь только одну точку выборки (со скоростью> 0) из каждого из двух журналов. То есть, в конце концов, я хотел бы получить запрос, который возвращает два образца: один из log_name_2021_09_01 и один из log_name_2021_09_03.

Answer 1

В вашем вопросе отсутствуют фактические данные примера, поэтому мы вынуждены сделать много выводов из вашего описания. Настоятельно рекомендуется, чтобы ваши вопросы включали образцы данных и результаты, которые вы хотите получить из этих образцов данных. Это дает нам как конкретный пример, на котором основывается наше понимание, так и набор тестов, который мы можем использовать при разработке ответа.

• Вы бы доверяли любому написанному вами коду, не сравнивая его с тестовыми данными?

Тем не менее, следующее должно быть что-то вроде того, что вы ищете ... (Для каждого журнала он выбирает только одну строку с наивысшим pos.)

      WITH
  rocket_logs AS
(
  SELECT DISTINCT
    runs.object_type AS object_type,
    runs.name        AS log_name
  FROM
    project.runs.latest_runs  
  WHERE
    object_type = "ROCKET"  
),
  sorted_logs AS
(
  SELECT  
    log.run              AS run_name,
    object.path_meters   AS pos,
    object_speed         AS speed,
    ROW_NUMBER()
      OVER (
        PARTITION BY log.run
            ORDER BY object.path_meters DESC
      )
                         AS seq_num
  FROM
    project.events.last30days  
  WHERE
    object.speed > 0.0
)
SELECT
  *
FROM
  rocket_logs   r
INNER JOIN
  sorted_logs   s
    ON s.run_name = r.log_name
WHERE
  s.seq_num = 1

Для более точного ответа укажите:

• пример данных для обеих таблиц
• пример результатов для этих данных
• и того, и другого достаточно, чтобы продемонстрировать все необходимое поведение