Postgres возвращает несколько идентичных результатов в зависимости от количества лет

У меня есть запрос Postgres, содержащий подзапросы, и выводит только 12 строк данных, именно так, как я хотел бы, чтобы он выглядел. Каждая из этих строк представляет месячное усреднение данных за все годы в базе данных, по одной строке на каждый месяц. Запрос:

SELECT
  to_char(to_timestamp(to_char(subquery2.month, '999'), 'MM'), 'Mon') AS Month,
  subquery2.month AS Month_num,
  round(AVG(subquery2.all_use_tot), 2) AS Average_Withdrawals,
  round(AVG(subquery2.all_cu_tot), 2) AS Average_Consumptive_Use
FROM
(SELECT
  subquery1.month,
  subquery1.year,
  sum(subquery1.liv_tot) AS all_use_tot,
  sum(subquery1.CU_total) AS all_cu_tot
FROM
    (SELECT 
        EXTRACT('Month' FROM withdrawals.begintime) AS month,
        EXTRACT(YEAR FROM withdrawals.begintime)::int AS year,
        tdx_ic_use_type.use_type_code AS Use_type,
        sum(withdrawals.withdrawalvalue_mgd) AS liv_tot,
        (tdx_ic_use_type.cufactor)*(sum(withdrawals.withdrawalvalue_mgd)) AS CU_total
    FROM 
        medford.tdx_ic_use_type,
        medford.withdrawalsites
        JOIN medford.subshed2 ON ST_Contains(medford.subshed2.the_geom, medford.withdrawalsites.the_geom)
        JOIN medford.withdrawals ON medford.withdrawals.ic_site_id = medford.withdrawalsites.ic_site_id
    WHERE 
        subshed2.id = '${param.shed_id}' AND
        withdrawalsites.ic_pr_use_type_id = tdx_ic_use_type.use_type_code AND
        withdrawals.intervalcodes_id = 2

    GROUP BY
        year, 
        extract('Month' from withdrawals.begintime),
        tdx_ic_use_type.cufactor,
        Use_type
    ORDER BY
        year, extract('Month' from withdrawals.begintime) ASC
        ) AS subquery1
GROUP BY
  subquery1.year,
  subquery1.month
  ) AS subquery2
GROUP BY
  subquery2.month
ORDER BY
  subquery2.month

Как выглядит результат:

"Jan"  1  1426.50  472.65
"Feb"  2  1449.00  482.10
"Mar"  3  1459.50  485.55
"Apr"  4  1470.00  489.00
"May"  5  1480.50  492.45
"Jun"  6  1491.00  495.90
"Jul"  7  1489.50  493.35
"Aug"  8  1512.00  502.80
"Sep"  9  1510.50  500.25
"Oct" 10  1533.00  509.70
"Nov" 11  1543.50  513.15
"Dec" 12  1542.00  510.60

Столбец месяца, который я извлек из столбца даты и времени в базе данных. Я использую результаты этого запроса и сохраняю их в массиве, но я хотел бы повторить эти точные результаты один раз для каждого года, который существует в таблице. Итак, если в базе данных есть данные за 2 года, должно быть 24 выходных строки или 12 одинаковых строк, повторяющихся дважды. Если в базе данных есть данные за 3 года, выходных строк должно быть 36, или 12 одинаковых строк, повторяющихся 3 раза. Как мне это сделать в моем запросе? Есть ли способ зациклить запрос на основе значений в столбце (т.е. количества лет, присутствующих в поле даты и времени?)


postgresql date data-generation
person Jan    schedule 13.10.2013    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Вы должны изменить group by month на group by year, month в своем запросе. (Но это даст вам только результаты, где есть данные за данный месяц.)

Для итерации по месяцам:

select a::date
from generate_series(
    '2011-06-01'::date,
    '2012-06-01',
    '1 month'
) s(a)

(на основе этого: Написание функции на SQL для перебора диапазона дат в пользовательской функции)

person Lajos Veres    schedule 13.10.2013
comment
Спасибо за ваши мысли. Я обновил свой вопрос фактическим запросом. Я попробовал ваше предложение несколькими способами, но значения меняются в разные годы, поэтому один и тот же набор данных за 12 месяцев не повторяется, он меняется. Есть ли способ зациклить запрос на основе значений в столбце? - person Jan; 14.10.2013
comment
Я добавил пример для генерации месяцев. Вы должны присоединиться к этому набору результатов. - person Lajos Veres; 14.10.2013