SQL-запрос для сворачивания дублирующихся значений диапазоном дат
Вы выполняете синхронизацию с переменной queue, но вызываете wait() и notify() для объекта this. Вам нужно либо удерживать блокировку кнопками synchornized(this), либо позвонить в queue.wait() и queue.notify(), чтобы убедиться, что вы уведомляете тот же монитор, для которого у вас есть блокировка. Вы можете взглянуть на Документы охраняемых блоков .
Обратите внимание, что вам не нужно создавать очередь самостоятельно. JDK предоставляет несколько реализаций java.util.concurrent.BlockingQueue :
Очередь, которая дополнительно поддерживает операции, которые ожидают, что очередь станет непустой при извлечении элемента, и ожидают места для становятся доступными в очереди при хранении элемента.
BLOCKQUOTE>
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 (задержка)
Я не мог заставить ответ от ngz работать, когда входная таблица содержит несколько идентификаторов и диапазонов даты тот промежуток годы. У меня есть решение, которое действительно работает, но с квалификациями. Это только даст Вам корректные ответы, если Вы будете знать, что ссоритесь для каждой комбинации месяца/года/идентификатора в диапазоне. Если будут "дыры", то это не будет работать. Если у Вас есть дыры, я знаю о, знают хороший способ сделать это кроме записи некоторых МН / 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
Я собираюсь разработать свое решение инкрементно, разлагая каждое преобразование на представление. Это и помогает объяснить, что делается и помогает в отладке и тестировании. Это по существу применяет принцип функционального разложения к запросам базы данных.
Я также собираюсь сделать это, не используя расширения Oracle с SQL, который должен работать на любом современном RBDMS. Таким образом, никакие не сохраняют, раздел, просто подзапросы и группа bys. (Сообщите мне в комментариях, если это не работает над Вашим RDBMS.)
Во-первых, таблица, которую, так как я являюсь нетворческим, я назову month_value. Так как идентификатор не является на самом деле уникальным идентификатором, я назову его "eid". Другие столбцы являются "m" onth, "y" ухо и "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. Это является пустым, где существует не позднее месяц, для этого eid, в таблице.
Так как last_am является тем же в течение всех месяцев, ведя до изменения в v (который происходит в last_am), мы можем сгруппироваться на last_am и v (и eid, конечно), и в любой группе, минута, абсолютный месяц первого месяца подряд, который имел то значение:
> 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 )
Поскольку представления анализируют проблему, мы могли добавить в этой "заглушке" месяцем ранее путем добавления другого представления, но я просто вставлю это в объединение. То, которое было бы самым эффективным, зависит от того, как Ваш RDBMS оптимизирует запросы.
Для получения месяца прежде мы присоединимся (cm_result_data.last_am - 1 = cm_abs_month.am)
Везде, где у нас есть пустой указатель, OP хочет "к" месяцу совпасть с "с" месяца, таким образом, мы будем просто использовать, объединяют на этом: объедините (last_am). С тех пор в последний раз устраняет любого, аннулирует, наши соединения не должны быть внешними объединениями.
> 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;
И теперь мы получаем данные, отформатированные на 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 |
+-----+------+------+------+------+------+
И существуют данные, которые хочет OP. Все в SQL, который должен работать на любом RDBMS и разложен на простой, легкое для понимания и легкий протестировать представления.
Лучше, чтобы возразить или повторно вычислить? Я оставлю это (это - вопрос о приеме) читателю.
(Если Ваш RDBMS не разрешает группу bys в представлениях, необходимо будет присоединиться сначала и затем группа или группа и затем вытянуть в месяце и год со связанными подзапросами. Это оставляют как осуществление для читателя.)
Jonathan Leffler спрашивает в комментариях,
Что происходит с Вашим запросом, если существуют разрывы в данных (скажите, что существует запись на 2007-12 со значением 80, и другой на 2007-10, но не один на 2007-11? Вопрос не ясен, что должно произойти там.
Ну, Вы точно правы, OP не указывает. Возможно, существует (неупомянутое) предварительное условие, что нет никаких разрывов. В отсутствие требования мы не должны пытаться кодировать вокруг чего-то, что не могло бы быть там. Но, факт, разрывы делают "присоединение назад" сбоем стратегии; "повторно вычислить" стратегия не перестала работать при тех условиях. Я сказал бы больше, но который покажет прием в вопросе о приеме, я сослался на вышеупомянутый.
Этот использует только одно сканирование таблицы и работает в течение многих лет. Тем не менее, лучше смоделировать ваш столбец месяца и года как один столбец типа данных даты:
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.
С уважением, Роб.