Общее количество записей в неделю
У меня есть база данных Postgres 9.1. Я пытаюсь сгенерировать количество записей в неделю (для заданного диапазона дат) и сравнить его с предыдущим годом.
У меня есть следующий код, используемый для генерации серии:
select generate_series('2013-01-01', '2013-01-31', '7 day'::interval) as series
Однако я не уверен, как соединить подсчитанные записи с сгенерированными датами.
Итак, используя следующие записи в качестве примера:
Pt_ID exam_date
====== =========
1 2012-01-02
2 2012-01-02
3 2012-01-08
4 2012-01-08
1 2013-01-02
2 2013-01-02
3 2013-01-03
4 2013-01-04
1 2013-01-08
2 2013-01-10
3 2013-01-15
4 2013-01-24
Я хотел, чтобы записи возвращались как:
series thisyr lastyr
=========== ===== =====
2013-01-01 4 2
2013-01-08 3 2
2013-01-15 1 0
2013-01-22 1 0
2013-01-29 0 0
Не уверен, как ссылаться на диапазон дат в подпоиске. Спасибо за любую помощь.
2 ответа
Используяcross joinдолжно работать, я просто собираюсь вставить вывод уценки из SQL Fiddle ниже. Может показаться, что ваш пример вывода некорректен для серии 2013-01-08: thisyr должно быть 2, а не 3. Это может быть не лучшим способом сделать это, хотя, мои знания Postgresql оставляют желать лучшего.
Настройка схемы PostgreSQL 9.2.4:
CREATE TABLE Table1
("Pt_ID" varchar(6), "exam_date" date);
INSERT INTO Table1
("Pt_ID", "exam_date")
VALUES
('1', '2012-01-02'),('2', '2012-01-02'),
('3', '2012-01-08'),('4', '2012-01-08'),
('1', '2013-01-02'),('2', '2013-01-02'),
('3', '2013-01-03'),('4', '2013-01-04'),
('1', '2013-01-08'),('2', '2013-01-10'),
('3', '2013-01-15'),('4', '2013-01-24');
Запрос 1:
select
series,
sum (
case
when exam_date
between series and series + '6 day'::interval
then 1
else 0
end
) as thisyr,
sum (
case
when exam_date + '1 year'::interval
between series and series + '6 day'::interval
then 1 else 0
end
) as lastyr
from table1
cross join generate_series('2013-01-01', '2013-01-31', '7 day'::interval) as series
group by series
order by series
| SERIES | THISYR | LASTYR |
|--------------------------------|--------|--------|
| January, 01 2013 00:00:00+0000 | 4 | 2 |
| January, 08 2013 00:00:00+0000 | 2 | 2 |
| January, 15 2013 00:00:00+0000 | 1 | 0 |
| January, 22 2013 00:00:00+0000 | 1 | 0 |
| January, 29 2013 00:00:00+0000 | 0 | 0 |
Простой подход состоит в том, чтобы решить эту проблему с помощью CROSS JOIN, как это продемонстрировал @jpw. Однако есть некоторые скрытые проблемы:
Производительность безусловная
CROSS JOINбыстро ухудшается с ростом числа рядов. Общее количество строк умножается на количество недель, которые вы тестируете, прежде чем эта огромная производная таблица может быть обработана в агрегации. Индексы не могут помочь.Начало недели с 1 января приводит к несоответствиям. Недели ISO могут быть альтернативой. Увидеть ниже.
Все следующие запросы интенсивно используют индекс exam_date, Будьте уверены, чтобы иметь один.
Присоединяйтесь только к соответствующим строкам
Должно быть намного быстрее:
SELECT d.day, d.thisyr
, count(t.exam_date) AS lastyr
FROM (
SELECT d.day::date, (d.day - '1 year'::interval)::date AS day0 -- for 2nd join
, count(t.exam_date) AS thisyr
FROM generate_series('2013-01-01'::date
, '2013-01-31'::date -- last week overlaps with Feb.
, '7 days'::interval) d(day) -- returns timestamp
LEFT JOIN tbl t ON t.exam_date >= d.day::date
AND t.exam_date < d.day::date + 7
GROUP BY d.day
) d
LEFT JOIN tbl t ON t.exam_date >= d.day0 -- repeat with last year
AND t.exam_date < d.day0 + 7
GROUP BY d.day, d.thisyr
ORDER BY d.day;
Это с неделями, начиная с 1 января, как в оригинале. Как уже отмечалось, это приводит к нескольким несоответствиям: недели начинаются в разные дни каждый год, и, поскольку мы отключаемся в конце года, последняя неделя года состоит всего из 1 или 2 дней (високосный год).
То же самое с неделями ISO
В зависимости от требований, рассмотрите недели ISO, которые начинаются по понедельникам и всегда охватывают 7 дней. Но они пересекают границу между годами. По документации на EXTRACT():
неделю
Номер недели в году, в который входит день. По определению (ISO 8601) недели начинаются по понедельникам, а первая неделя года содержит 4 января этого года. Другими словами, первый четверг года - первая неделя этого года.
В определении ИСО даты начала января могут быть частью 52-й или 53-й недели предыдущего года, а даты конца декабря - частью первой недели следующего года. Например,
2005-01-01является частью 53-й недели 2004 года, и2006-01-01является частью 52-й недели 2005 года, в то время как2012-12-31является частью первой недели 2013 года. Рекомендуется использоватьisoyearполе вместе сweekчтобы получить последовательные результаты.
Выше запрос переписан с ISO недель:
SELECT w AS isoweek
, day::text AS thisyr_monday, thisyr_ct
, day0::text AS lastyr_monday, count(t.exam_date) AS lastyr_ct
FROM (
SELECT w, day
, date_trunc('week', '2012-01-04'::date)::date + 7 * w AS day0
, count(t.exam_date) AS thisyr_ct
FROM (
SELECT w
, date_trunc('week', '2013-01-04'::date)::date + 7 * w AS day
FROM generate_series(0, 4) w
) d
LEFT JOIN tbl t ON t.exam_date >= d.day
AND t.exam_date < d.day + 7
GROUP BY d.w, d.day
) d
LEFT JOIN tbl t ON t.exam_date >= d.day0 -- repeat with last year
AND t.exam_date < d.day0 + 7
GROUP BY d.w, d.day, d.day0, d.thisyr_ct
ORDER BY d.w, d.day;
4 января всегда первая неделя года в ISO. Таким образом, это выражение получает дату понедельника первой недели ISO данного года:
date_trunc('week', '2012-01-04'::date)::date
Упростить с EXTRACT()
Поскольку недели ISO совпадают с номерами недель, возвращенными EXTRACT() Мы можем упростить запрос. Сначала короткая и простая форма:
SELECT w AS isoweek
, COALESCE(thisyr_ct, 0) AS thisyr_ct
, COALESCE(lastyr_ct, 0) AS lastyr_ct
FROM generate_series(1, 5) w
LEFT JOIN (
SELECT EXTRACT(week FROM exam_date)::int AS w, count(*) AS thisyr_ct
FROM tbl
WHERE EXTRACT(isoyear FROM exam_date)::int = 2013
GROUP BY 1
) t13 USING (w)
LEFT JOIN (
SELECT EXTRACT(week FROM exam_date)::int AS w, count(*) AS lastyr_ct
FROM tbl
WHERE EXTRACT(isoyear FROM exam_date)::int = 2012
GROUP BY 1
) t12 USING (w);
Оптимизированный запрос
То же самое с более подробной информацией и оптимизировано для производительности
WITH params AS ( -- enter parameters here, once
SELECT date_trunc('week', '2012-01-04'::date)::date AS last_start
, date_trunc('week', '2013-01-04'::date)::date AS this_start
, date_trunc('week', '2014-01-04'::date)::date AS next_start
, 1 AS week_1
, 5 AS week_n -- show weeks 1 - 5
)
SELECT w.w AS isoweek
, p.this_start + 7 * (w - 1) AS thisyr_monday
, COALESCE(t13.ct, 0) AS thisyr_ct
, p.last_start + 7 * (w - 1) AS lastyr_monday
, COALESCE(t12.ct, 0) AS lastyr_ct
FROM params p
, generate_series(p.week_1, p.week_n) w(w)
LEFT JOIN (
SELECT EXTRACT(week FROM t.exam_date)::int AS w, count(*) AS ct
FROM tbl t, params p
WHERE t.exam_date >= p.this_start -- only relevant dates
AND t.exam_date < p.this_start + 7 * (p.week_n - p.week_1 + 1)::int
-- AND t.exam_date < p.next_start -- don't cross over into next year
GROUP BY 1
) t13 USING (w)
LEFT JOIN ( -- same for last year
SELECT EXTRACT(week FROM t.exam_date)::int AS w, count(*) AS ct
FROM tbl t, params p
WHERE t.exam_date >= p.last_start
AND t.exam_date < p.last_start + 7 * (p.week_n - p.week_1 + 1)::int
-- AND t.exam_date < p.this_start
GROUP BY 1
) t12 USING (w);
Это должно быть очень быстро с поддержкой индекса и может быть легко адаптировано к выбранным интервалам. Неявный JOIN LATERAL за generate_series() в последнем запросе требуется Postgres 9.3.