У меня есть таблица со следующей структурой: идентификатор, месяц, год, значение со значениями для одной записи на идентификатор в месяц, большинство месяцев имеют одинаковое значение.
Я хотел бы создать представление для этой таблицы, которое сворачивает одни и те же значения, например: идентификатор, месяц начала, месяц окончания, год начала, год окончания, значение, с одной строкой на идентификатор для каждого значения.
Загвоздка в том, что если значение изменяется, а затем возвращается к исходному, оно должно иметь две строки в таблице.
So:
- 100 1 2008 80
- 100 2 2008 80
- 100 3 2008 90
- 100 4 2008 80
должен производить
- 100 1 2008 2 2008 80
- 100 3 2008 3 2008 90
- 100 4 2008 4 2008 80
Следующий запрос работает для всего, кроме этого особого случая, когда значение возвращается к исходному.
select distinct id, min(month) keep (dense_rank first order by month)
over (partition by id, value) startMonth,
max(month) keep (dense_rank first order by month desc) over (partition
by id, value) endMonth,
value
База данных — это оракул.
Решение проблемы
Я собираюсь развивать свое решение постепенно, разлагая каждое преобразование на представление. Это помогает объяснить, что делается, и помогает в отладке и тестировании. По сути, это применение принципа функциональной декомпозиции к запросам к базе данных.
Я также собираюсь сделать это без использования расширений Oracle, с SQL, который должен работать на любой современной СУБД. Так что не храните, не разделяйте, а просто подзапросы и группы. (Сообщите мне в комментариях, если это не работает в вашей СУБД.)
Во-первых, таблица, которую, поскольку я не креативен, я назову month_value. Поскольку идентификатор на самом деле не является уникальным идентификатором, я назову его «eid». Другие столбцы — «месяц», «год» и «v»значение:
create table month_value(
eid int not null, m int, y int, v int );
После вставки данных для двух eids у меня есть:
> select * from month_value;
+-----+------+------+------+
| eid | m | y | v |
+-----+------+------+------+
| 100 | 1 | 2008 | 80 |
| 100 | 2 | 2008 | 80 |
| 100 | 3 | 2008 | 90 |
| 100 | 4 | 2008 | 80 |
| 200 | 1 | 2008 | 80 |
| 200 | 2 | 2008 | 80 |
| 200 | 3 | 2008 | 90 |
| 200 | 4 | 2008 | 80 |
+-----+------+------+------+
8 rows in set (0.00 sec)
Далее у нас есть одна сущность, месяц, представленная двумя переменными. На самом деле это должен быть один столбец (либо дата, либо дата и время, либо, может быть, даже внешний ключ к таблице дат), поэтому мы сделаем его одним столбцом. Мы сделаем это как линейное преобразование, чтобы оно сортировалось так же, как (y, m), и чтобы для любого кортежа (y, m) было одно-единственное значение, и все значения были последовательными:
> create view cm_abs_month as
select *, y * 12 + m as am from month_value;
Это дает нам:
> select * from cm_abs_month;
+-----+------+------+------+-------+
| eid | m | y | v | am |
+-----+------+------+------+-------+
| 100 | 1 | 2008 | 80 | 24097 |
| 100 | 2 | 2008 | 80 | 24098 |
| 100 | 3 | 2008 | 90 | 24099 |
| 100 | 4 | 2008 | 80 | 24100 |
| 200 | 1 | 2008 | 80 | 24097 |
| 200 | 2 | 2008 | 80 | 24098 |
| 200 | 3 | 2008 | 90 | 24099 |
| 200 | 4 | 2008 | 80 | 24100 |
+-----+------+------+------+-------+
8 rows in set (0.00 sec)
Теперь мы воспользуемся самообъединением в коррелированном подзапросе, чтобы найти для каждой строки самый ранний последующий месяц, в котором изменилось значение. Мы будем основывать это представление на предыдущем созданном нами представлении:
> create view cm_last_am as
select a.*,
( select min(b.am) from cm_abs_month b
where b.eid = a.eid and b.am > a.am and b.v <> a.v)
as last_am
from cm_abs_month a;
> select * from cm_last_am;
+-----+------+------+------+-------+---------+
| eid | m | y | v | am | last_am |
+-----+------+------+------+-------+---------+
| 100 | 1 | 2008 | 80 | 24097 | 24099 |
| 100 | 2 | 2008 | 80 | 24098 | 24099 |
| 100 | 3 | 2008 | 90 | 24099 | 24100 |
| 100 | 4 | 2008 | 80 | 24100 | NULL |
| 200 | 1 | 2008 | 80 | 24097 | 24099 |
| 200 | 2 | 2008 | 80 | 24098 | 24099 |
| 200 | 3 | 2008 | 90 | 24099 | 24100 |
| 200 | 4 | 2008 | 80 | 24100 | NULL |
+-----+------+------+------+-------+---------+
8 rows in set (0.01 sec)
last_am теперь является «абсолютным месяцем» первого (самого раннего) месяца (после месяца текущей строки), в котором значение v изменяется. Это нуль, если в таблице нет более позднего месяца для этого праздника.
Поскольку last_am является одним и тем же для всех месяцев, предшествующих изменению v (которое происходит в last_am), мы можем сгруппировать по last_am и v (и, конечно, по eid), и в любой группе min(am) является абсолютным месяц первого месяца подряд, который имел это значение:
> create view cm_result_data as
select eid, min(am) as am, last_am, v
from cm_last_am group by eid, last_am, v;
> select * from cm_result_data;
+-----+-------+---------+------+
| eid | am | last_am | v |
+-----+-------+---------+------+
| 100 | 24100 | NULL | 80 |
| 100 | 24097 | 24099 | 80 |
| 100 | 24099 | 24100 | 90 |
| 200 | 24100 | NULL | 80 |
| 200 | 24097 | 24099 | 80 |
| 200 | 24099 | 24100 | 90 |
+-----+-------+---------+------+
6 rows in set (0.00 sec)
Теперь это набор результатов, который нам нужен, поэтому это представление называется cm_result_data. Все, чего не хватает, это преобразовать абсолютные месяцы обратно в кортежи (y,m).
Для этого мы просто присоединимся к таблице month_value.
Есть только две проблемы: 1) мы хотим, чтобы в нашем выводе был месяц до last_am, и 2) у нас есть нули, когда в наших данных нет следующего месяца; чтобы соответствовать спецификации OP, это должны быть диапазоны в один месяц.
РЕДАКТИРОВАТЬ: На самом деле это могут быть более длинные диапазоны, чем один месяц, но в каждом случае это означает, что нам нужно найти последний месяц для eid, а именно:
(select max(am) from cm_abs_month d where d.eid = a.eid )
Поскольку представления разлагают проблему, мы могли бы добавить эту «конечную шапку» месяцем ранее, добавив еще одно представление, но я просто вставлю это в объединение. Что будет наиболее эффективным, зависит от того, как ваша СУБД оптимизирует запросы.
Чтобы получить месяц раньше, присоединяемся (cm_result_data.last_am - 1 = cm_abs_month.am)
Везде, где у нас есть ноль, OP хочет, чтобы месяц «до» был таким же, как месяц «от», поэтому мы просто используем объединение для этого: объединение (last_am, am). Поскольку last устраняет любые нули, наши соединения не обязательно должны быть внешними соединениями.
> select a.eid, b.m, b.y, c.m, c.y, a.v
from cm_result_data a
join cm_abs_month b
on ( a.eid = b.eid and a.am = b.am)
join cm_abs_month c
on ( a.eid = c.eid and
coalesce( a.last_am - 1,
(select max(am) from cm_abs_month d where d.eid = a.eid )
) = c.am)
order by 1, 3, 2, 5, 4;
+-----+------+------+------+------+------+
| eid | m | y | m | y | v |
+-----+------+------+------+------+------+
| 100 | 1 | 2008 | 2 | 2008 | 80 |
| 100 | 3 | 2008 | 3 | 2008 | 90 |
| 100 | 4 | 2008 | 4 | 2008 | 80 |
| 200 | 1 | 2008 | 2 | 2008 | 80 |
| 200 | 3 | 2008 | 3 | 2008 | 90 |
| 200 | 4 | 2008 | 4 | 2008 | 80 |
+-----+------+------+------+------+------+
Присоединяясь, мы получаем результат, который хочет ОП.
Не то, чтобы мы должны присоединиться обратно. Как оказалось, наша функция absolute_month является двунаправленной, поэтому мы можем просто пересчитать год и сместить месяц из него.
Во-первых, давайте позаботимся о добавлении месяца «конечная крышка»:
> create or replace view cm_capped_result as
select eid, am,
coalesce(
last_am - 1,
(select max(b.am) from cm_abs_month b where b.eid = a.eid)
) as last_am, v
from cm_result_data a;
И теперь мы получаем данные, отформатированные в соответствии с OP:
select eid,
( (am - 1) % 12 ) + 1 as sm,
floor( ( am - 1 ) / 12 ) as sy,
( (last_am - 1) % 12 ) + 1 as em,
floor( ( last_am - 1 ) / 12 ) as ey, v
from cm_capped_result
order by 1, 3, 2, 5, 4;
+-----+------+------+------+------+------+
| eid | sm | sy | em | ey | v |
+-----+------+------+------+------+------+
| 100 | 1 | 2008 | 2 | 2008 | 80 |
| 100 | 3 | 2008 | 3 | 2008 | 90 |
| 100 | 4 | 2008 | 4 | 2008 | 80 |
| 200 | 1 | 2008 | 2 | 2008 | 80 |
| 200 | 3 | 2008 | 3 | 2008 | 90 |
| 200 | 4 | 2008 | 4 | 2008 | 80 |
+-----+------+------+------+------+------+
И есть данные, которые хочет ОП. Все на SQL, который должен работать на любой СУБД, и разбит на простые, легкие для понимания и простые для тестирования представления.
Лучше пересобрать или пересчитать? Я оставлю это (это вопрос с подвохом) читателю.
(Если ваша СУБД не допускает группировки в представлениях, вам придется сначала присоединиться, а затем сгруппировать, или сгруппировать, а затем получить месяц и год с коррелированными подзапросами. Это остается в качестве упражнения для читателя.)
Джонатан Леффлер спрашивает в комментариях:
Что произойдет с вашим запросом, если в данных есть пробелы (скажем, есть запись за 2007–2012 годы со значением 80, а другая — за 2007–10 годы, но не за 2007–2011 годы? Вопрос не ясен, что там должно произойти.
Ну, вы совершенно правы, ОП не уточняет. Возможно, есть (не упомянутое) предварительное условие отсутствия пробелов. В отсутствие требования мы не должны пытаться кодировать что-то, чего может и не быть. Но дело в том, что из-за пробелов стратегия «воссоединения» терпит неудачу; стратегия «пересчета» не дает сбоев в этих условиях. Я бы сказал больше, но это раскрыло бы хитрость в вопросе с подвохом, о котором я упоминал выше.
Комментариев нет:
Отправить комментарий