Архив

Архив раздела ‘SQL’

Функция CUME_DIST

3 Сентябрь 2010 Нет комментариев

Аналитический вариант

CUME_DIST( ) OVER ( [ query_partition_clause ] order_by_clause )

Функция CUME_DIST рассчитывает кумулятивное распределение значения в наборе данных. Возвращаемое значение находится в диапазоне от 0 до 1. Функция на вход принимает числовой тип данных, или тип данных, который может неявно преобразовать в числовой.

Читать дальше про “Функция CUME_DIST” »

Определение строк со смешанными языками (русские и английские буквы)

2 Сентябрь 2010 Нет комментариев

Вытаскиваем записи, у которых есть хотя бы одна нерусская буква

with t as
(
 select 1 id, 'Сидоров' s from dual
  union all
 select 2, 'Cидoров' from dual
  union all
 select 3, 'Пирогов' from dual
)
select * from t t0
where soundex(t0.s) is not null;

        ID S
---------- -------
         2 Cидoров

Предположительные совпадения (Пирогов добавлен для показательности неточности подхода)

with t as
(
 select 1 id, 'Сидоров' s from dual
  union all
 select 2, 'Cидoров' from dual
  union all
 select 3, 'Пирогов' from dual
)
select * from t t0, t t1
where UTL_MATCH.JARO_WINKLER_SIMILARITY(t0.s,t1.s)>=70
      and soundex(t0.s) is null
      and soundex(t1.s) is not null
      and t0.id<>t1.id;

        ID S               ID S
---------- ------- ---------- -------
         1 Сидоров          2 Cидoров
         3 Пирогов          2 Cидoров

Простой translate для вытаскивания совпадающих написаний

with t as
(
 select 1 id, 'Сидоров' s from dual
  union all
 select 2, 'Cидoров' from dual
  union all
 select 3, 'Пирогов' from dual
)
select * from t t0, t t1
where translate(t1.s,'AaBbCcEegHKkMmOoPpTuXxy','АаВьСсЕедНКкМтОоРрТиХху')=t0.s
      and t0.id<>t1.id;

        ID S               ID S
---------- ------- ---------- -------
         1 Сидоров          2 Cидoров

Вывести данные в одном языке (латиница или кирилица) + цифры и знак подчёрка:

with t as
(
 select 'Сидоров' val from dual
  union all
 select 'Cидoров' from dual
  union all
 select 'Пирогов' from dual
  union all
 select 'ASD_1' from dual 
  union all
 select 'ASD_1Я' from dual  
)
select val from t
where regexp_like(val, '^([A-z]|[0-9]|_)+$') or regexp_like(val, '^([А-я]|[0-9]|_)+$')
/

VAL
-------
Сидоров
Пирогов
ASD_1

Объединение интервалов дат

31 Август 2010 Нет комментариев
with t as (
select to_date ('01.01.2009', 'dd.mm.yyyy') beg_date,
       to_date ('10.01.2009', 'dd.mm.yyyy') end_date
 from dual
union all
select to_date ('03.01.2009', 'dd.mm.yyyy') beg_date,
       to_date ('05.01.2009', 'dd.mm.yyyy') end_date
 from dual
union all
select to_date ('10.01.2009', 'dd.mm.yyyy') beg_date,
       to_date ('12.01.2009', 'dd.mm.yyyy') end_date
 from dual
union all
select to_date ('13.01.2009', 'dd.mm.yyyy') beg_date,
       to_date ('20.01.2009', 'dd.mm.yyyy') end_date
 from dual
union all
select to_date ('01.02.2009', 'dd.mm.yyyy') beg_date,
       to_date ('10.02.2009', 'dd.mm.yyyy') end_date
 from dual
)
--
select min (beg_date) as beg_date,
       max (end_date) as end_date
from (
      select beg_date,
             end_date,
             sum(strt_grp) over (order by beg_date, end_date ) grp_num
      from (
             select  beg_date,
                     end_date,
                     case when beg_date > 1 + max(end_date) over (order by beg_date, end_date rows between unbounded preceding and 1 preceding) then 1 end strt_grp
             from t
           )
     )
group by grp_num
order by beg_date;

BEG_DATE    END_DATE
----------- -----------
01-01-2009  20-01-2009
01-02-2009  10-02-2009

Процедура SLEEP

30 Август 2010 Нет комментариев

Процедура SLEEP останавливает процесс на указанное время.

DBMS_LOCK.SLEEP(seconds  IN NUMBER);

где seconds — время в секундах (и долей секунд) на которое надо остановить процесс.

Categories: SQL Tags: , ,

Разбить период на месяца

5 Август 2010 7 comments
with t as (
  select to_date('03-05-2010','dd-mm-yyyy') d1,
         to_date('26-08-2010','dd-mm-yyyy') d2
  from dual
)
--
select decode(level,1,d1,trunc(add_months(d1,level-1),'mm')) as date_from,
       case when add_months(trunc(d1,'mm'),level)>d2 then d2
            else last_day(add_months(d1,level-1))
       end date_to
from t
connect by add_months(trunc(d1,'mm'),level-1) < d2;


DATE_FROM   DATE_TO
----------- -----------
03.05.2010  31.05.2010
01.06.2010  30.06.2010
01.07.2010  31.07.2010
01.08.2010  26.08.2010

Если хотим, чтобы в период входил 1 день последнего месяца, то :

with t as (
  select to_date('03-05-2010','dd-mm-yyyy') d1,
         to_date('01-08-2010','dd-mm-yyyy') d2
  from dual
)
--
select decode(level,1,d1,trunc(add_months(d1,level-1),'mm')) as date_from,
       case when add_months(trunc(d1,'mm'),level)>d2 then d2
            else last_day(add_months(d1,level-1))
       end date_to
from t
connect by add_months(trunc(d1,'mm'),level-1) <= d2;

DATE_FROM   DATE_TO
----------- -----------
03.05.2010  31.05.2010
01.06.2010  30.06.2010
01.07.2010  31.07.2010
01.08.2010  01.08.2010


Создать дубликаты строк

with t as
(
  select 'a' a, 2 n from dual union all
  select 'b' a, 3 n from dual union all
  select 'c' a, 5 n from dual union all
  select 'd' a, 1 n from dual union all
  select 'e' a, 0 n from dual
)
select *
 from t,
      table(select collect(level) from dual connect by level<=t.n);

A          N COLUMN_VALUE
- ---------- ------------
a          2            1
a          2            2
b          3            1
b          3            2
b          3            3
c          5            1
c          5            2
c          5            3
c          5            4
c          5            5
d          1            1
e          0            1

12 rows selected

При n=0, строка останется.

Читать дальше про “Создать дубликаты строк” »

Запретить commit в процедуре

alter session enable  commit in procedure;
alter session disable commit in procedure;

При попытке выполнить commit произойдет исключение.

Categories: SQL Tags: , ,

Select from PL/SQL table

28 Июнь 2010 1 комментарий

Пример запроса из коллекции.

-- создаем глобальный тип данных, локальный нельзя использовать
create or replace type xx_test_type is object ( id number, descr varchar2(20));
create or replace type xx_test_tab is table of xx_test_type;

Пример заполнения и вывода данных

declare
 vcollect xx_test_tab := xx_test_tab(); -- инициализация
begin
 -- заполняем данными
 vcollect.extend;
 vcollect(vcollect.count) := xx_test_type(1,'test1');

 vcollect.extend;
 vcollect(vcollect.count) := xx_test_type(2,'test2');
 -- запрос из коллекции
 for i in (select * from Table(vcollect) order by id )
 loop
   dbms_output.put_line(i.descr);
 end loop;
end;

Значение строк в одну, через xml функции

with t as (
select 1 as id, 'a' gr from dual
union all
select 2 as id, 'a' gr from dual
union all
select 4 as id, 'b' gr from dual
)
select
 rtrim(to_char(sys_xmlagg(xmlelement(id,id ||',')).extract('/ROWSET/ID/text()').getclobval()),',') TEXT
from t
group by gr;

TEXT
-------
1,2
4

Модуль GME/INV — Связка между заданиями и транзакциями

select *
from gme_batch_header          gbh,
     gme_material_details      gmd,
     mtl_material_transactions mmt
where 1=1
      and gbh.batch_type = 0
      -- gmd
      and gmd.batch_id = gbh.batch_id
      -- mmt
      and mmt.transaction_source_id = gmd.batch_id
      and mmt.trx_source_line_id    = gmd.material_detail_id
      -- for test
      --and trunc(gbh.plan_start_date) >
      --and gbh.batch_no =
      --and mmt.attribute_category =
      and rownum<100;