Блог

Главная - Вопросы по программированию - Вычислять средние значения за каждый квартал для каждого связанного значения

Вычислять средние значения за каждый квартал для каждого связанного значения

#sql #google-bigquery

#sql #google-bigquery

Вопрос:

У меня есть запрос в BigQuery. Я хочу знать средние значения за квартал. В моем текущем SQL значения за период Q1 для id1 одинаковы для id2 .

Это то, что у меня было, и значения были хорошими:

 row|averages|quarter|identifier
-----------------------------
1  | 10     | 1     | id1
2  | 20     | 2     | id1
3  | 30     | 1     | id2
4  | 40     | 2     | id2

Вот SQL, который я написал для вышеупомянутой структуры, которая дает хорошие значения:

 WITH
  index_cal AS (
  SELECT
    values-01,
    kind,
    EXTRACT (QUARTER  FROM  date) as QUARTER,
    date,
  FROM
    `project.dataset.table`,
  geom AS (
  SELECT
    identifier
  FROM
    `project.dataset.table2` )
SELECT
   AVG(values-01) AS averages,
    QUARTER AS quarter,
    geom. identifier as identifier
  FROM
    index_cal as g
INNER JOIN
  geom
ON
  INTERSECTS(g.kind,
    geom. identifier)
GROUP BY
  identifier
  quarter

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

 row | averages | quarter | identifier
----------------------------------
1   | 10       | 1       | id1
    | 20       | 2       |
----------------------------------
2   | 30       | 1       | id2
    | 40       | 2       |
----------------------------------

Чтобы получить желаемую структуру таким образом, чтобы id1 имел только 1 связанную строку и аналогично для всех идентификаторов, я написал этот SQL-запрос:

 WITH
  index_cal AS (
  SELECT
    values-01,
    kind,
    EXTRACT (QUARTER  FROM  date) as QUARTER,
    date,
  FROM
    `project.dataset.table`,
  geom AS (
  SELECT
    identifier
  FROM
    `project.dataset.table2` )
SELECT
  ARRAY(
  SELECT
    AS STRUCT AVG(values-01) AS averages,
    QUARTER AS quarter
  FROM
    index_cal
  GROUP BY
    QUARTER ) as INDEX,
  geom. identifier as identifier
FROM
  index_cal AS g
INNER JOIN
  geom
ON
  INTERSECTS(g.kind,
    geom. identifier)
GROUP BY
  identifier

При выполнении этого запроса я получаю средние значения для всех идентификаторов, сгруппированных по кварталам, так что значения (например, 15 и 25 в данном случае) повторяются для всех идентификаторов:

 row | averages | quarter | identifier
----------------------------------
1   | 15       | 1       | id1
    | 25       | 2       |
----------------------------------
2   | 15       | 1       | id2
    | 25       | 2       |
----------------------------------
2   | 15       | 1       | id3
    | 25       | 2       |
----------------------------------

В конце я хочу ответить на среднее значение values-01 за квартальный интервал на основе identifier . В настоящее время они одинаковы для любого значения identifier .

Комментарии:

1. Привет, Пранай. Я все еще пытаюсь понять вопрос. Есть ли способ упростить дело?

2. Привет, Фелипе, отредактировал мой вопрос. При необходимости я могу предоставить более подробную информацию.

Ответ №1:

Решил это с помощью ARRAY_AGG в исходном запросе, который выдавал исходные значения

 with final_cal as (WITH
  index_cal AS (
  SELECT
    values-01,
    kind,
    EXTRACT (QUARTER  FROM  date) as QUARTER,
    date,
  FROM
    `project.dataset.table`,
  geom AS (
  SELECT
    identifier
  FROM
    `project.dataset.table2` )
SELECT
   AVG(values-01) AS averages,
    QUARTER AS quarter,
    geom. identifier as identifier
  FROM
    index_cal as g
INNER JOIN
  geom
ON
  INTERSECTS(g.kind,
    geom. identifier)
GROUP BY
  identifier
  quarter)
SELECT identifier, ARRAY_AGG(STRUCT(averages, quarter)) from final_cal GROUP BY identifier