SQL-запрос для свертывания дублирующихся значений по диапазону дат
У меня есть таблица со следующей структурой: идентификатор, месяц, год, значение со значениями для одной записи на идентификатор в месяц, большинство месяцев имеют одинаковое значение.
Я хотел бы создать представление для этой таблицы, которое объединяет те же значения, как это: ID, Начальный месяц, Конечный месяц, Начальный год, Конечный год, Значение, с одной строкой на идентификатор на значение.
Уловка в том, что если значение изменяется, а затем возвращается к исходному, в таблице должно быть две строки
Так:
- 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
4 ответа
Я получил это работать следующим образом. Он тяжел для аналитических функций и специфичен для Oracle.
select distinct id, value,
decode(startMonth, null,
lag(startMonth) over(partition by id, value order by startMonth, endMonth), --if start is null, it's an end so take from the row before
startMonth) startMonth,
decode(endMonth, null,
lead(endMonth) over(partition by id, value order by startMonth, endMonth), --if end is null, it's an start so take from the row after
endMonth) endMonth
from (
select id, value, startMonth, endMonth from(
select id, value,
decode(month+1, lead(month) over(partition by id,value order by month), null, month)
startMonth, --get the beginning month for each interval
decode(month-1, lag(month) over(partition by id,value order by month), null, month)
endMonth --get the end month for each interval from Tbl
) a
where startMonth is not null or endMonth is not null --remain with start and ends only
)b
Может быть возможно несколько упростить некоторые внутренние запросы
Внутренний запрос проверяет, является ли месяц первым / последним месяцем интервала следующим образом: если месяц + 1 == следующий месяц (лаг) для этой группировки, то, поскольку есть следующий месяц, этот месяц, очевидно, не является конец месяца В противном случае это последний месяц интервала. Эта же концепция используется для проверки в течение первого месяца.
Внешний запрос сначала отфильтровывает все строки, которые не являются ни начальным, ни конечным месяцем (where startMonth is not null or endMonth is not null). Затем каждая строка является либо начальным месяцем, либо конечным месяцем (или обоими), определяемыми тем, является ли начало или конец не нулевым). Если месяц является начальным месяцем, получите соответствующий конечный месяц, получив следующий (ведущий) endMonth для этого идентификатора, значение, упорядоченное по endMonth, а если это endMonth, то получите startMonth путем поиска предыдущего startMonth (lag)
Я собираюсь разрабатывать свое решение постепенно, разлагая каждое преобразование в представление. Это помогает объяснить, что делается, и помогает в отладке и тестировании. По сути, он применяет принцип функциональной декомпозиции к запросам к базе данных.
Я также собираюсь сделать это без использования расширений Oracle, с SQL, который должен работать на любой современной RBDMS. Таким образом, нет хранения, разделения, просто подзапросы и групповые байсы. (Сообщите мне в комментариях, если это не работает на вашей RDBMS.)
Во-первых, таблицу, которая, поскольку я не творческая, я буду называть month_value. Поскольку идентификатор на самом деле не является уникальным идентификатором, я назову его "Ид". Другими столбцами являются "m", "y" ear и "v" alue:
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. Это ноль, где нет более позднего месяца, для этого eid, в таблице.
Поскольку 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. Все, чего не хватает, это что-то, чтобы преобразовать абсолютные месяцы обратно в (у, м) кортежи.
Для этого мы просто присоединимся к таблице month_value.
Есть только две проблемы: 1) мы хотим, чтобы в нашем выводе был месяц до last_am, и 2) у нас есть нули, которых нет в следующем месяце в наших данных; чтобы соответствовать спецификации OP, это должны быть месячные диапазоны.
РЕДАКТИРОВАТЬ: На самом деле это могут быть более длинные диапазоны, чем один месяц, но в каждом случае они означают, что мы должны найти последний месяц для ИД, который является:
(select max(am) from cm_abs_month d where d.eid = a.eid )
Поскольку представления разлагают проблему, мы могли бы добавить эту "заглушку" месяцем ранее, добавив другое представление, но я просто вставлю это в объединение. Что будет наиболее эффективным, зависит от того, как ваша СУБД оптимизирует запросы.
Чтобы получить месяц раньше, мы присоединимся (cm_result_data.last_am - 1 = cm_abs_month.am)
Везде, где у нас есть ноль, OP хочет, чтобы месяц "до" был таким же, как и месяц "с", поэтому мы просто используем для этого coalesce: coalesce( 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 |
+-----+------+------+------+------+------+
Присоединившись, мы получим результат, который хочет OP.
Не то чтобы мы должны были вернуться. Как это происходит, наша функция 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;
И теперь мы получаем данные, отформатированные в соответствии с ОП:
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-12 со значением 80, а другая для 2007-10, но не для 2007-11? Вопрос не ясен, что там должно произойти,
Ну, вы совершенно правы, ОП не указывает. Возможно, есть (не упомянутое) предварительное условие, что нет пробелов. В отсутствие требования мы не должны пытаться кодировать что-то, чего может не быть. Но факт в том, что из-за пробелов стратегия "присоединения назад" терпит неудачу; стратегия пересчета не терпит неудачу в этих условиях. Я бы сказал больше, но это раскрыло бы уловку в вопросе об уловке, который я упоминал выше.
Этот использует только одно сканирование таблицы и работает по годам. Тем не менее, лучше смоделировать ваш столбец месяца и года как один столбец с датой:
SQL> create table tbl (id,month,year,value)
2 as
3 select 100,12,2007,80 from dual union all
4 select 100,1,2008,80 from dual union all
5 select 100,2,2008,80 from dual union all
6 select 100,3,2008,90 from dual union all
7 select 100,4,2008,80 from dual union all
8 select 200,12,2007,50 from dual union all
9 select 200,1,2008,50 from dual union all
10 select 200,2,2008,40 from dual union all
11 select 200,3,2008,50 from dual union all
12 select 200,4,2008,50 from dual union all
13 select 200,5,2008,50 from dual
14 /
Tabel is aangemaakt.
SQL> select id
2 , mod(min(year*12+month-1),12)+1 startmonth
3 , trunc(min(year*12+month-1)/12) startyear
4 , mod(max(year*12+month-1),12)+1 endmonth
5 , trunc(max(year*12+month-1)/12) endyear
6 , value
7 from ( select id
8 , month
9 , year
10 , value
11 , max(rn) over (partition by id order by year,month) maxrn
12 from ( select id
13 , month
14 , year
15 , value
16 , case lag(value) over (partition by id order by year,month)
17 when value then null
18 else rownum
19 end rn
20 from tbl
21 ) inner
22 )
23 group by id
24 , maxrn
25 , value
26 order by id
27 , startyear
28 , startmonth
29 /
ID STARTMONTH STARTYEAR ENDMONTH ENDYEAR VALUE
---------- ---------- ---------- ---------- ---------- ----------
100 12 2007 2 2008 80
100 3 2008 3 2008 90
100 4 2008 4 2008 80
200 12 2007 1 2008 50
200 2 2008 2 2008 40
200 3 2008 5 2008 50
6 rijen zijn geselecteerd.
С уважением, Роб.
Я не смог получить ответ от ngz, чтобы работать, когда входная таблица содержит несколько идентификаторов и диапазонов дат, охватывающих годы. У меня есть решение, которое работает, но с квалификацией. Он даст вам правильные ответы, только если вы знаете, что у вас есть строка для каждой комбинации месяц / год / идентификатор в пределах диапазона. Если есть "дыры", это не сработает. Если у вас есть дыры, я знаю, что знаю хороший способ сделать это, кроме написания некоторого PL/SQL и использования цикла курсора для создания новой таблицы в нужном вам формате.
Кстати, именно поэтому данные, смоделированные таким образом, являются мерзостью. Вы всегда должны хранить данные как записи начала / из диапазона, а не как записи с дискретным периодом времени. Преобразовать первое в второе с помощью таблицы "множителей" тривиально, но почти невозможно (как вы видели) пойти в другом направлении.
SELECT ID
, VALUE
, start_date
, end_date
FROM (SELECT ID
, VALUE
, start_date
, CASE
WHEN is_last = 0
THEN LEAD(end_date) OVER(PARTITION BY ID ORDER BY start_date)
ELSE end_date
END end_date
, is_first
FROM (SELECT ID
, VALUE
, TO_CHAR(the_date, 'YYYY.MM') start_date
, TO_CHAR(NVL(LEAD(the_date - 31) OVER(PARTITION BY ID ORDER BY YEAR
, MONTH), the_date), 'YYYY.MM') end_date
, is_first
, is_last
FROM (SELECT ID
, YEAR
, MONTH
, TO_DATE(TO_CHAR(YEAR) || '.' || TO_CHAR(MONTH) || '.' || '15', 'YYYY.MM.DD') the_date
, VALUE
, ABS(SIGN(VALUE -(NVL(LAG(VALUE) OVER(PARTITION BY ID ORDER BY YEAR
, MONTH), VALUE - 1)))) is_first
, ABS(SIGN(VALUE -(NVL(LEAD(VALUE) OVER(PARTITION BY ID ORDER BY YEAR
, MONTH), VALUE - 1)))) is_last
FROM test_table)
WHERE is_first = 1
OR is_last = 1))
WHERE is_first = 1