Найдите максимальное, минимальное, среднее, процентиль количества (*) на mmdd PostgreSQL
Postgres версии 9.4.18, PostGIS версии 2.2.
Вот таблицы, с которыми я работаю (и вряд ли они могут внести существенные изменения в структуру таблицы):
Таблица ltg_data (период с 1988 по 2018 год):
Column | Type | Modifiers
----------+--------------------------+-----------
intensity | integer | not null
time | timestamp with time zone | not null
lon | numeric(9,6) | not null
lat | numeric(8,6) | not null
ltg_geom | geometry(Point,4269) |
Indexes:
"ltg_data2_ltg_geom_idx" gist (ltg_geom)
"ltg_data2_time_idx" btree ("time")
Size of ltg_data (~800M rows):
ltg=# select pg_relation_size('ltg_data');
pg_relation_size
------------------
149729288192
Столовые округа:
Column | Type | Modifiers
-----------+-----------------------------+--------------------------------- -----------------------
gid | integer | not null default
nextval('counties_gid_seq'::regclass)
objectid_1 | integer |
objectid | integer |
state | character varying(2) |
cwa | character varying(9) |
countyname | character varying(24) |
fips | character varying(5) |
time_zone | character varying(2) |
fe_area | character varying(2) |
lon | double precision |
lat | double precision |
the_geom | geometry(MultiPolygon,4269) |
Indexes:
"counties_pkey" PRIMARY KEY, btree (gid)
"counties_gix" gist (the_geom)
"county_cwa_idx" btree (cwa)
"countyname_cwa_idx" btree (countyname)
У меня есть запрос, который вычисляет общее количество строк в день в году (месяц-день) за 30 лет. С помощью Stackoverflow запрос на получение этих счетчиков работает нормально. Вот запрос и результаты с использованием следующей функции.
Функция:
CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS
$$SELECT to_char($1, 'MMDD')::int$$;
Запрос:
SELECT d.mmdd, COALESCE(ct.ct, 0) AS total_count
FROM (
SELECT f_mmdd(d::date) AS mmdd -- ignoring the year
FROM generate_series(timestamp '2018-01-01' -- any dummy year
, timestamp '2018-12-31'
, interval '1 day') d
) d
LEFT JOIN (
SELECT f_mmdd(time::date) AS mmdd, count(*) AS ct
FROM counties c
JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)
WHERE cwa = 'MFR'
GROUP BY 1
) ct USING (mmdd)
ORDER BY 1;
Полученные результаты:
mmdd total_count
725 | 2126
726 | 558
727 | 2
728 | 2
729 | 2
730 | 0
731 | 0
801 | 0
802 | 10
Желаемые результаты: я пытаюсь найти другую статистическую информацию о счетчиках дней в году. Например, 25 июля я знаю (725 в таблице ниже), что общее количество за многие годы, указанное в таблице, составляет 2126. Я ищу максимальное дневное количество на 25 июля (725), в процентах. лет, в которые этот день не равен нулю, мин, процент лет, в которых count (*) не равен нулю, процентили (10-й процентиль, 25-й процентиль, 50-й процентиль, 75-й процентиль, 90-й процентиль и стандартное отклонение также будут полезны). Было бы хорошо посмотреть, в каком году произошло max_daily. Я предполагаю, что если бы для этого дня не было никаких подсчетов за все годы, year_max_daily было бы пустым или нулевым.
mmdd total_count max daily year_max_daily percent_years_count_not_zero 10th percentile_daily 90th percentile_daily
725 | 2126 1000 1990 30 15 900
726 | 558 120 1992 20 10 80
727 | 2 1 1991 2 0 1
728 | 2 1 1990 2 0 1
729 | 2 1 1989 2 0 1
730 | 0 0 0 0 0
731 | 0 0 0 0 0
801 | 0 0 0 0 0
802 | 10 10 1990 0 1 8
То, что я пробовал до сих пор, просто не работает. Он возвращает те же результаты, что и total. Я думаю, это потому, что я просто пытаюсь получить среднее значение после того, как итоги уже подсчитаны, поэтому я на самом деле не смотрю на счетчики для каждого дня каждого года и нахожу среднее.
Пытаться:
SELECT AVG(CAST(total_count as FLOAT)), day
FROM
(
SELECT d.mmdd as day, COALESCE(ct.ct, 0) as total_count
FROM (
SELECT f_mmdd(d::date) AS mmdd
FROM generate_series(timestamp '2018-01-01', timestamp '2018-12-31', interval '1 day') d
) d
LEFT JOIN (
SELECT mmdd, avg(q.ct) FROM (
SELECT f_mmdd((time at time zone 'utc+12')::date) as mmdd, count(*) as ct
FROM counties c
JOIN ltg_data d on ST_contains(c.the_geom, d.ltg_geom)
WHERE cwa = 'MFR'
GROUP BY 1
)
) as q
ct USING (mmdd)
ORDER BY 1
Спасибо за любую помощь!
30 лет - это вся таблица.
Рассмотрим обновленная версия f_mmdd() по причинам, указанным там: stackoverflow.com/questions/50374136/…
Спасибо, Эрвин! Рассмотрим это.
Ответы 1
Я не включил расчеты для всей запрошенной статистики - в одном вопросе их слишком много, но я надеюсь, что вы сможете расширить запрос ниже и добавить дополнительную статистику, которая вам нужна.
Я использую CTE ниже, чтобы сделать запрос читаемым. Если хотите, можете собрать все в один огромный запрос. Я бы рекомендовал выполнить запрос пошагово, CTE-by-CTE и изучить промежуточные результаты, чтобы понять, как это работает.
CTE_Dates - это простой список всех возможных дат за 30 лет.
CTE_DailyCounts - это список основных подсчетов на каждый день за 30 лет (для этого я взял ваш существующий запрос).
CTE_FullStats - это снова список всех дат вместе с некоторой статистикой, рассчитанной для каждой (месяц, день) с использованием оконных функций с разделением по месяцам и дням. ROW_NUMBER используется для получения даты, когда счет был наибольшим за каждый год.
Окончательный запрос выбирает только одну строку с наибольшим количеством за год вместе с остальной информацией.
Я не пытался выполнить запрос, потому что в вопросе нет выборочных данных, поэтому возможны опечатки.
WITH
CTE_Dates
AS
(
SELECT
d::date AS dt
,EXTRACT(MONTH FROM d::date) AS dtMonth
,EXTRACT(DAY FROM d::date) AS dtDay
,EXTRACT(YEAR FROM d::date) AS dtYear
FROM
generate_series(timestamp '1988-01-01', timestamp '2018-12-31', interval '1 day') AS d
-- full range of possible dates
)
,CTE_DailyCounts
AS
(
SELECT
time::date AS dt
,count(*) AS ct
FROM
counties c
INNER JOIN ltg_data d ON ST_contains(c.the_geom, d.ltg_geom)
WHERE cwa = 'MFR'
GROUP BY time::date
)
,CTE_FullStats
AS
(
SELECT
CTE_Dates.dt
,CTE_Dates.dtMonth
,CTE_Dates.dtDay
,CTE_Dates.dtYear
,CTE_DailyCounts.ct
,SUM(CTE_DailyCounts.ct) OVER (PARTITION BY dtMonth, dtDay) AS total_count
,MAX(CTE_DailyCounts.ct) OVER (PARTITION BY dtMonth, dtDay) AS max_daily
,SUM(CASE WHEN CTE_DailyCounts.ct > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY dtMonth, dtDay) AS nonzero_day_count
,COUNT(*) OVER (PARTITION BY dtMonth, dtDay) AS years_count
,100.0 * SUM(CASE WHEN CTE_DailyCounts.ct > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY dtMonth, dtDay)
/ COUNT(*) OVER (PARTITION BY dtMonth, dtDay) AS percent_years_count_not_zero
,ROW_NUMBER() OVER (PARTITION BY dtMonth, dtDay ORDER BY CTE_DailyCounts.ct DESC) AS rn
FROM
CTE_Dates
LEFT JOIN CTE_DailyCounts ON CTE_DailyCounts.dt = CTE_Dates.dt
)
SELECT
dtMonth
,dtDay
,total_count
,max_daily
,dtYear AS year_max_daily
,percent_years_count_not_zero
FROM
CTE_FullStats
WHERE
rn = 1
ORDER BY
dtMonth
,dtDay
;
Спасибо вам большое. Я получаю удовольствие, пытаясь понять, как именно вы достигли результатов. Спасибо, что уделили этому время.
@ user1610717, добро пожаловать. Я бы рекомендовал запускать каждый шаг (CTE) запроса и изучать каждый промежуточный результат, чтобы понять, что происходит.
Мне очень трудно использовать percentile_cont в разделе запроса CTE_FullStats. Когда я добавляю в запрос percentile_cont, все, что я получаю, - это пустой столбец. Я много читал о percentile_cont, но не могу понять, как заставить его работать. Я подумал, что, возможно, "where rn = 1" мешало ему работать правильно. Однако почему должны работать части max () и sum ()? Есть ли у вас какое-нибудь представление о том, как заставить процентили работать в рамках этой структуры запросов? Спасибо!
@ user1610717, Да, where rn=1 скорее всего нарушит другие расчеты. Мне непонятно, как следует рассчитывать эти процентили (поэтому я не пытался указать их в ответе). Вы всегда можете написать второй запрос, который вычисляет только процентили, которые вам нужны для месяца / дня, а затем объединить два результата вместе для месяца и дня. Попробуйте поместить дополнительные вычисления в CTE_FullStats и сначала не используйте последний бит с where rn=1. Посмотрите, сможете ли вы получить ожидаемые результаты от CTE_FullStats. Вы также можете задать еще один вопрос проще, который касается только процентилей.
Я удалил rn = 1 и не смог расшифровать результаты. Я подумал, что, поскольку запрос успешно выполнил статистику суммы, он сможет вернуть процентиль. Я отправил еще один вопрос, посвященный процентилям. Я оставил остальные части запроса, но если процентили не могут быть включены в этот и без того длинный запрос, я понимаю. Еще раз спасибо за помощь.
@ user1610717, я думаю, вам нужно прочитать документацию и понять, что делает OVER (PARTITION BY ...). Понимание этой конструкции должно помочь вам расшифровать / понять результаты. Без этих знаний вы не можете быть уверены, что ответы, которые вы получите, содержат запросы, которые дают правильные результаты.
данные, которые вы хотите получить, целую таблицу? или всего 30 лет данных из этой таблицы? или, другими словами, таблица содержит данные за более чем 30 лет?