ivs1329 авг 2011 в 12:33

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

2 мин

2.3K

Oracle *

Из песочницы

+10

Комментарии 42

abyrvalg 29 авг 2011 в 13: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 29 авг 2011 в 13:26

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

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

ivs13 29 авг 2011 в 13: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 29 авг 2011 в 14: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 29 авг 2011 в 14:26

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

xtender 29 авг 2011 в 16:05

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

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

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

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

xtender 29 авг 2011 в 15:55

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

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

ivs13 29 авг 2011 в 16:15

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

xtender 29 авг 2011 в 16:22

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

xtender 29 авг 2011 в 16:32

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

abyrvalg 29 авг 2011 в 17:20

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

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

Nashev 1 сен 2011 в 18:15

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

xtender 2 сен 2011 в 06:30

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

Nashev 2 сен 2011 в 06:54

а у Вас proof есть?

xtender 2 сен 2011 в 07:02

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

Nashev 2 сен 2011 в 07: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 29 авг 2011 в 15:33

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

TemaMak 29 авг 2011 в 14:23

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

ivs13 29 авг 2011 в 14:29

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

TemaMak 29 авг 2011 в 14:40

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

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

ivs13 29 авг 2011 в 14:57

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

diamant 29 авг 2011 в 16:53

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

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

ivs13 29 авг 2011 в 17:46

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

tonyzorin 29 авг 2011 в 14: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 1 сен 2011 в 18:18

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

tonyzorin 2 сен 2011 в 12: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 2 сен 2011 в 15:25

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

xtender 29 авг 2011 в 15: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 29 авг 2011 в 15:37

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

sergeyfast 29 авг 2011 в 15:46

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

xtender 30 авг 2011 в 11:54

habrahabr.ru/blogs/mysql/106815/

xtender 30 авг 2011 в 11:54

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

Aldekein 30 авг 2011 в 09:46

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

xtender 30 авг 2011 в 11:54

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

xtender 29 авг 2011 в 15: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 29 авг 2011 в 16: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 29 авг 2011 в 16: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 29 авг 2011 в 17:08

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

xtender 29 авг 2011 в 17: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 1 сен 2011 в 18:19

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

xtender 2 сен 2011 в 06:19

Нет, с 11g

Nashev 2 сен 2011 в 07:03

аааа…

Зарегистрируйтесь на Хабре, чтобы оставить комментарий