Блог

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

Соответствующие данные здесь представлены в двух таблицах:

observations

  -------- -------- ------------- 
| obs_id | person | observed_at |
 -------- -------- ------------- 
| 1a     | John   | 1/1/20      |
| 2b     | Barry  | 2/1/20      |
| 3c     | John   | 3/1/20      |
| 4d     | Barry  | 4/1/20      |
| 5e     | John   | 5/1/20      |
| 6f     | Zach   | 7/1/20      |
 -------- -------- ------------- 
  

measurements

  -------- --------- ------- ------- 
| obs_id | meas_id | score | arc   |
 -------- --------- ------- ------- 
| 1a     | f32f    | 1     | NULL  |
| 1a     | k8787   | NULL  | NULL  |
| 1a     | 93j2    | NULL  | ARC 1 |
| 1a     | 0fkd    | 2     | NULL  |
| 2b     | 92u4    | 4     | NULL  |
| 2b     | 10fl    | NULL  | ARC 1 |
| 2b     | 42u4    | 3     | NULL  |
| 3c     | 70fl    | NULL  | ARC 1 |
| 3c     | 70fl    | 4     | NULL  |
| 3c     | 8f09    | 1     | NULL  |
| 4d     | 0987    | 4     | NULL  |
| 4d     | 0192    | 4     | NULL  |
| 4d     | 1234    | NULL  | ARC 1 |
| 5e     | 8d0s    | NULL  | ARC 2 |
| 5e     | 1112    | 3     | NULL  |
| 6f     | 0981    | 1     | NULL  |
| 6f     | fdsf    | 2     | NULL  |
| 6f     | jy4s    | 3     | NULL  |
| 6f     | 876j    | NULL  | ARC 1 |
 -------- --------- ------- ------- 
  

На английском языке это то, какой должна быть запись в выходных данных: в КАЖДОЙ дуге для КАЖДОГО человека вычислите оценку ПОСЛЕДНЕГО наблюдения. Таким образом, на выходе у Джона будет 2 записи (по 1 строке как в ДУГЕ 1, так и в ДУГЕ 2), у Барри будет 1 запись (1 в ДУГЕ 1), а у Зака также будет только 1 запись (1 в ДУГЕ 1).

В более технических терминах я бы хотел, чтобы выходные данные были с детализацией person | arc | avg_score . Часть, с которой у меня возникли проблемы, заключается в группировании данных при получении avg(score) , а также получении arc s. Вот как должен выглядеть результат:

  -------- -------- ----------- 
| person |  arc   | avg_score |
 -------- -------- ----------- 
| John   | ARC 1  | 2.5       |
| John   | ARC 2  | 3         |
| Barry  | ARC 1  | 4         |
| Zach   | ARC 1  | 2         |
 -------- -------- ----------- 
  

Это неверно, но имеет общую суть того, что я пытаюсь сделать. Это дает только 1 запись на человека, но я бы хотел, чтобы вывод был более детализированным для разделения по дуге. Я знаю, что мог бы сделать еще avg(score) один проход и min(arc) снова получить и, но у меня есть еще 2 запроса поверх этого, и я хочу свести к минимуму количество запросов.

 SELECT
  row_number() over (partition by person order by observed_at desc) as row_num,
  *
FROM
(
SELECT
  obs_id,
  person,
  observed_at,
  avg(score),
  max(arc)
FROM observations as o
INNER JOIN measurements as m
  ON o.obs_id = m.obs_id
GROUP BY obs_id, person, observed_at
) a
  

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

вы можете попробовать что-то подобное

 with
t1l as (
    select t1.*, t2.arc, ROW_NUMBER() over (partition by arc, person order by observed_at desc) n
    from observations t1
    left join measurements t2 on t1.obs_id = t2.obs_id
    where arc is not null
)
select person, t1l.arc, avg(t2.score) avg_score
from t1l
join measurements t2 on t2.obs_id = t1l.obs_id
where n=1
group by person, t1l.arc
order by person, arc