ivs13 Aug 29 2011 at 16:33

Получение нулевой статистической информации при отсутствии исходных данных

2 min

2.2K

Oracle*

From sandbox

+10

Comments 42

abyrvalg Aug 29 2011 at 17:23

Есть более удобный инструмент: конвейерные функции (http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/function.htm#CJAGIGJE)
На примере простой последовательности чисел:

create function int_sequence( 
    p_from int
  , p_to   int
  )
 return sys.odcinumberlist
 pipelined
is
begin
  for i in p_from .. p_to loop
    pipe row(i);
  end loop;
  return;
end;
/

Мог малость где-нибудь с синтаксисом накосячить.

А вообще, задача довольно стандартная. Кроме способов, приведённых Вами и мной, решается, как минимум, через иерархические запросы и модели. Было бы здорово, если бы в топике были перечислены все имеющиеся способы. С удовольствием положил бы его в закладки.

ЗЫ: в качестве параметра STEP не рассматривали интервальные типы? Удобного доступа к ораклу сейчас нет, проверить хочется.

abyrvalg Aug 29 2011 at 17:26

А, ну, собственно, пользоваться так:

select * from table(int_sequence(1,10))

ivs13 Aug 29 2011 at 17:56

Да, PIPELINED-функции удобней, но они появились начиная с 9-ки, а это и в 8i работает. Потому и привел именно этот вариант функции, как более совместимый.
Можно и через иерархический запрос, вроде вот такого:

SELECT start_data + LEVEL-1 data

FROM (SELECT TRUNC(SYSDATE-7) start_data, TRUNC(SYSDATE) end_Data FROM DUAL) t

CONNECT BY start_data + LEVEL-1 <= end_Data;

Это имелось ввиду? А вот что ты имел ввиду под «моделями»? Не доводилось сталкиваться с таким или же для меня оно называется как-то по другому.
Так де понял, зачем интервальный тип для шага?

abyrvalg Aug 29 2011 at 18:09

Имелась в виду кляуза MODEL. Что-то вроде этого:
Генерация временных интервалов (любых) — здесь генерация интервала 00-00 до 23-59 с 5мин интервалом ( 5/1440)

SELECT to_char(time_val,'hh24:mi') as time_val
FROM dual
WHERE
1=2
MODEL
dimension by ( 0 as key )
measures ( trunc(sysdate) as time_val )
-- 1440 mins in Day
rules upsert ( 
    time_val[ for key from 0 to (1-(5/1440)) increment (5/1440) ] = trunc(sysdate) + cv(key)
    )

Интервальный тип, может, и правда не катит здесь. Просто я зациклился на генерации интервала, а не на конкретно поставленной задаче.

ivs13 Aug 29 2011 at 18:26

Это уже из OLAPa фишки? У меня на 10g даже не запустилось, мотивируя отсутствием табличного пространства express…
Интервальный тип по крайней мере для дат не слишком удобен, можно, конечно, получать каждый третий день, но вот получить только первые числа месяца уже не получится. Да тут много разных вариантов может быть, смотря какую задачу решать. Но принцип все равно останется тот же: вначале сформировать последовательность, потом с ней соединить имеющиеся данные. Для разовых задач наверное проще использовать голый SQL типа иерархического, для часто повторяющихся лучше создать функцию.

xtender Aug 29 2011 at 20:05

Да тут много разных вариантов может быть, смотря какую задачу решать. Но принцип все равно останется тот же: вначале сформировать последовательность, потом с ней соединить имеющиеся данные.

Модельки как раз работают после получения данных.

Для разовых задач наверное проще использовать голый SQL типа иерархического, для часто повторяющихся лучше создать функцию.

Функция практически всегда будет медленнее, а если еще и не пайплайн, то и память отжирать.

xtender Aug 29 2011 at 19:55

это и в 8i работает

Освещать сейчас банальные решения о 8i сейчас? Вы смеетесь? Давайте тогда еще пуд всяких FAQ'ов для всех версий старей 10 лет сюда перепишем?

ivs13 Aug 29 2011 at 20:15

Нет, не смеюсь. Разные подходы могут отличаться по быстродействию? Почитайте вот здесь, www.sql.ru/forum/actualthread.aspx?tid=752505&pg=1&mid=8656527#8656527, в одном из топиков — сравнение быстродействия пайплайнед и коллекций. Если читать лень, то я подскажу, что там написано: на 18% быстрее оказались коллекции. Там же и дискуссия по поводу целесообразности использования NO_DATA_NEEDED.

xtender Aug 29 2011 at 20:22

Я вполне часто сижу на sql.ru и читал эту ветку. Вы не открыли ни одной истины.

xtender Aug 29 2011 at 20:32

Заодно подумайте, что такое COUNT STOPKEY.

abyrvalg Aug 29 2011 at 21:20

Ну, про no_data_needed там был довольно сферический пример (если мы один и тот же топик имеем в виду). В доке описывается гораздо более жизненный пример: там у нас не просто тупой перебор от А до Б, а длинная математика, которая использует множество ресурсов БД. В этом случае цена перехвата исключения несоизмерима с ценой неосвобождённых ресурсов.

Ну и как уже было замечено, выбор между конвейером и коллекцией ещё стоит оценить с позиции CPU vs PGA SIZE.

Nashev Sep 1 2011 at 22:15

8-ка, кажется, самая старшая из версий Oracle, сертифицированная в России под секретные проекты.

xtender Sep 2 2011 at 10:30

И что? Поэтому надо древности публиковать более 10 лет давности?
Тем не менее, если имеете ввиду именно гос.тайну то 9.2, и вроде даже 10-ка атомщиками сертифировалась. Если же для всего остального, то вплоть до 11 сертифицировано.

Nashev Sep 2 2011 at 10:54

а у Вас proof есть?

xtender Sep 2 2011 at 11:02

На сайте фстэк.

Nashev Sep 2 2011 at 11:35

Вы про www.fstec.ru/_doc/reestr_sszi/reestr_sszi_01.htm или www.fstec.ru/_doc/reestr_sszi/reestr_sszi_11.htm?

там максимум упоминается 9-ка по первой ссылке, и (не знаю, что это значит) все просрочены…

А вот в www.fstec.ru/_doc/reestr_sszi/_reestr_sszi.xls всё получше — даже 11i встречается, правда без гостайны.

И непросроченные записи про десятку есть:

на строке 1583
28.03.2008 — 28.03.2014, Oracle 10g — Встроенные средства защиты информации от несанкционированного доступа системы управления базами данных Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise/Standard Edition на программно-аппаратной платформе ОС Solaris/Sparc — по 5 классу для СВТ, для АС до 1Г (может использоваться для защиты информации в ИСПДн до 2 класса включительно)

аттестовал ОС — Центр «Атомзащита-информ», ИЛ-ЦНИИАТОМИНФОРМ по заявке от ФГУП «СКЦ Федерального агентства по атомной энергии»

про суть этих классов — вроде бы написано здесь: www.fstec.ru/_docs/doc_3_3_004.htm

xtender Aug 29 2011 at 19:33

По идее еще надо было бы осветить NO_DATA_NEEDED и, кстати, важно еще учитывать кардинальность.

TemaMak Aug 29 2011 at 18:23

а не легче нулевые строки генерировать на клиенте?

ivs13 Aug 29 2011 at 18:29

Ну, это зависит от идеологии приложения наверное. Есть задачи, где клиент выполняет чисто презентативную работу, логики никакой не несет в себе, что сервер прислал — то и показал.

TemaMak Aug 29 2011 at 18:40

Я не совсем корректно написал. Имелось ввиду «генерировать строки в коде приложения, которое выводит отчет». Но Вы вроде понял мой вопрос верно.

Просто приведенные в посте и комментариях запросы весьма велики и не тривиальны, в приложении, на мой взгляд, можно сделать более наглядно.

ivs13 Aug 29 2011 at 18:57

Повторюсь, все зависит от идеологии и конкретной ситуации. Конечно, можно и на клиенте прописать логику для стапиццот отчетов. И при каждом чихе заказчика всем рассылать новую версию клиента. Или ковыряться в php/asp скриптах в случае web-интерфейса. И это при условии, что к этому самому клиенту есть доступ… А можно один раз научить клиент отображать полученное, и всю логику отчета держать только на сервере. Лично я всегда был и остаюсь сторонником «тонких» клиентов и «умных» серверов.

diamant Aug 29 2011 at 20:53

У вас за год всего пять строк, но вы загрузите сервер бд генерацией пустых массивов и пошлёте по сети все 365 строк? А если в каждой строке 70 полей? :)

Меня учили расчёты проводить на сервере приложений (в случае пехапе, наверное, будет и правда выгодней вести расчёты прямо в бд :), и били по рукам за ненужную нагрузку на сервер баз данных и сеть :)

ivs13 Aug 29 2011 at 21:46

Есть система, написанная бог знает кем и когда. Отчеты в ней — CLOB-записи SQL-ей в таблице. Исходников клиентов/серверов нет. Неизвестно даже, на чем оно написано. А новый отчет хотят. Вы предлагаете мне сказать заказчику, что сделать это невозможно, потому что не по науке раз в год нагрузить сервер 365 пустыми строками?

tonyzorin Aug 29 2011 at 18:42

Без PL/SQL и без MODEL:

with t as

(select sysdate-4 as data from dual union all

select sysdate-2 from dual union all

select sysdate-2 from dual union all

select sysdate from dual)



select d.data,count(t.data) 

from t right join (select trunc(sysdate-level+1)as data from dual connect by level<=7) d

on trunc(t.data)=trunc(d.data)

group by d.data

order by d.data

Nashev Sep 1 2011 at 22:18

с with, join и connect by — под 8-ку не катит. Но элегантно. :)

tonyzorin Sep 2 2011 at 16:46

Спасибо :). With использовался здесь только для примера, чтобы можно было запустить и проверить пример, не создавая таблицу в базе и не заполняя её данными.
Да, в 8-ке объединение таблиц было только через (+), а не через join, но всё же было.
А вот connect by не было :(

Вот пример для 8-ки (cube, grouping, row_number() там есть):

select d.data,count(t.data)

 from (select sysdate-4 as data from dual union all

 select sysdate-2 from dual union all

 select sysdate-2 from dual union all

 select sysdate from dual) t,

 (select sysdate-row_number() over (order by rownum)+1 as data

 from (select grouping(1) as p 

 from dual group by cube(1,1,1)) where p!= 1) d

 where trunc(t.data(+))=trunc(d.data)

group by d.data

order by d.data

ivs13 Sep 2 2011 at 19:25

В Oracle 8i (у меня был 8.1.5, даже не 8.1.7) есть connect by.

xtender Aug 29 2011 at 19:37

Еще вариант динамического генератора:
Получаем динамически необходимое кол-во дублированных строк(N штук для каждой строки)

with t as (
          select 1 n,trunc(sysdate-5) dt from dual union all
          select 2 n,trunc(sysdate-3) dt from dual union all
          select 3 n,trunc(sysdate-1) dt from dual
)
select 
      n
     ,generator.column_value
     ,dt+generator.column_value
from 
     t
     ,table(cast(multiset(select level from dual connect by level<=t.n) as sys.odcinumberlist)) generator

xtender Aug 29 2011 at 19:37

А вообще, здесь на хабре я уже освещал вроде этот вопрос и на примере оракла и на примере mysql

sergeyfast Aug 29 2011 at 19:46

И не забываем пример из postgresql — generate_series(start timestamp, stop timestamp, step interval);

xtender Aug 30 2011 at 15:54

habrahabr.ru/blogs/mysql/106815/

xtender Aug 30 2011 at 15:54

Сорри промахнулся

Aldekein Aug 30 2011 at 13:46

А где для mysql, хочу в закладки?

xtender Aug 30 2011 at 15:54

Вот: habrahabr.ru/blogs/mysql/106815/

xtender Aug 29 2011 at 19:49

Кстати, используйте case:

CREATE OR REPLACE FUNCTION Generate_Date_Sequence( -- Генератор посл-ти дат.
    Data_Beg DATE, /*Начальная дата.*/
    Data_End DATE, /*Конечная дата.*/
    Step CHAR) /*Шаг в последовательности дат: D - день, M - месяц, Y - год.*/
RETURN TDate_Sequence
AS
    data_curr DATE;
    stp CHAR:= UPPER(Step);
    Result TDate_Sequence := TDate_Sequence();
BEGIN
    data_curr := TRUNC( Data_Beg,
                        case stp
                          when 'D' then 'DD'
                          when 'M' then 'MM'
                          when 'Y' then 'YYYY'
                        end);

    -- Сформировать последовательность дат.
    WHILE data_curr <= Data_End LOOP
      Result.EXTEND;
      Result(Result.LAST) := data_curr;
      case stp
        when 'D' then data_curr := data_curr + 1;
        when 'M' then data_curr := ADD_MONTHS(data_curr, 1);
        when 'Y' then data_curr := ADD_MONTHS(data_curr, 12);
      end case;
    END LOOP;
    -- Вернуть результат.
    RETURN Result;
END Generate_Date_Sequence;

dobersoft Aug 29 2011 at 20:06

Получить набор дат можно и без процедур
(по крайней мере в MySQL это работает)
(1200 — это количество минут, за которые выводится отчёт)

(cyfxfkf cjplf`v dhtvtyye. nf,kbwe b hfdyjvthyj pfgjkyztv)
SET @start=60+UNIX_TIMESTAMP();
CREATE TEMPORARY TABLE tms (wh INT);
INSERT INTO tms SELECT @start:=@start-60 from tbl limit 1200;

(ниже мой основной запрос, использующий временную таблицу, ранее равномерно заполненную временными метками)
SELECT wh, COUNT(time) AS c FROM tms LEFT JOIN (SELECT time from tbl WHERE time>(UNIX_TIMESTAMP()-1201*60) AND) AS k1 ON ROUND(tms.wh/60)=ROUND(k1.time/60) GROUP BY wh ORDER BY wh DESC;

Razoomnick Aug 29 2011 at 20:54

Вот ещё один лаконичный способ:

WITH cte AS (SELECT 1 x UNION ALL SELECT x + 1 FROM cte WHERE x < 100)
SELECT Convert(datetime, GetDate() — x)
FROM cte

xtender Aug 29 2011 at 21:08

Вы забыли уточнить что это ms sql, а не oracle, хотя в oracle тоже есть рекурсивный with

xtender Aug 29 2011 at 21:17

Для полноты:

WITH dates ( d ) AS (
   SELECT 1 AS d FROM dual 
   UNION ALL
   SELECT d+1 AS d
   FROM   dates
   where d<11
)
SELECT date'2011-01-01'+d FROM dates

Nashev Sep 1 2011 at 22:19

Оооо! он изначально рекурсивность поддерживает?

xtender Sep 2 2011 at 10:19

Нет, с 11g

Nashev Sep 2 2011 at 11:03

аааа…