Ганеш Вариар

SQL для бизнеса
(Business-Savvy SQL, by Ganesh Variar)

Источник: Oracle Magazine, no.2, 2002, http://www.oracle.com/oramag/oracle/02-mar/o22sql.html

Быстрое и эффективное формирование и исполнение сложных бизнес-запросов обеспечит аналитический SQL, включенный в Oracle9i.

Web-локатор

Дополнительная информация по отдельным функциям содержится в главах 18 и 19 руководства
“ Oracle9i Data Warehousing Guide”
www.oracle.com/oramag/mar02/waredoc.html

Еще раз об OTN
www.oracle.com/oramag/mar02/bi.html

Приобретение пакета Oracle9i
www.oracle.com/oramag/store.html

Поддержка мультимедийных средств www.oracle.com/oramag/mar02/ebiz.html

Витрина Oracle.com
www.oracle.com/oramag/mar02/oracle9i.html

Публикации Oracle
www.oracle.com/oramag/
oracle/01-jul/o41industry.html

Пытались ли Вы когда-нибудь написать SQL-запрос, который бы составил список лучших 20% товаров Вашей фирмы? А как насчет того, чтобы сравнить объемы продаж в текущем месяце с аналогичным показателем годичной давности? Если Вам доводилось создавать такие или подобные им, популярные в области коммерции запросы, то Вы, наверняка, знаете о несовершенстве SQL, как средства анализа данных.

К счастью для делового мира, SQL сделал заметный шаг в своем развитии, выразившийся в появлении новых аналитических возможностей, впервые реализованных в Oracle8i Release 2 и улучшенных с выходом Oracle9i. Они упрощают программирование сложных аналитических запросов и повышают эффективность их выполнения.

В данной статье описываются наиболее важные особенности аналитического (analytic) SQL, уделяется внимание усовершенствованиям Oracle9i, а также приводятся примеры применения новых SQL-функций для решения сложных практических задач.

Важнейшие преимущества аналитического SQL

Аналитический SQL в Oracle9i предоставляет ряд существенных преимуществ, включая:

Снижение затрат на написание запросов. Теперь пространные PL/SQL-блоки могут быть заменены простыми запросами.

Более оперативный доступ к таблицам. В большинстве случаев новый SQL уменьшает количество просматриваемых данных, что обуславливает десятикратное сокращение времени доступа, особенно если поиск осуществляется в крупной таблице.

Кэширование результатов. Результаты выполненного запроса остаются в кэше, так что впоследствии схожие запросы могут ими воспользоваться.

Сложные задачи становятся проще

Вот некоторые задачи, выполнение которых становится более простым и эффективным, благодаря этому SQL.

Многомерный анализ данных и кросс-табуляция (Multidimensional analysis and cross-tabulation). Функция GROUPING и дополнительные опции CUBE и ROLLUP фразы GROUP BY позволяют рассчитывать многоуровневые агрегации данных для совокупности столбцов, извлекая тем самым информацию, обычно необходимую при составлении кросс-табличных отчетов. В Oracle9i введены понятия составных (composite) столбцов и конкатенированных группировок (concatenated groupings), а также выражение GROUPING SETS и функции GROUPING_ID и GROUP_ID.

Ранжирование (ranking). С помощью функций ранжирования Вы можете вычислять ранги, сотые (percentiles - процентили) и другие n-ые доли величин в результирующем множестве и осуществлять последовательную нумерацию строк. В Oracle9i добавлены две новые важные возможности в области ранжирования: инверсные процентные функции (inverse percentile functions) и функции условного ранжирования (hypothetical ranking functions).

Кумулятивное и перемещаемое агрегирование (оконность) (Cumulative and moving aggregations (windowing)). Используя фразу windowing и соответствующие функции, можно задать окно из нескольких последовательных строк, для которых будут применяться агрегатные функции, что позволит Вам рассчитать не только текущие и обобщенные средние значения, но и другую статистику.

Разбиение множества результов (partitioned result sets). Применив фразу PARTITION BY совместно с какой-либо аналитической функцией, нетрудно разделить множество результов по группам, основанных на одном или нескольких значениях.

Есть два важных момента, на которые следует обратить внимание при использовании аналитических функций:

Применять аналитические функции в запросах Вы можете только в предложениях SELECT, включающих фразы ORDER BY, поскольку эти функции оперируют с множеством результаов, полученным после соединений (joins) и выполнения фраз WHERE, GROUP BY и HAVING. Заметим также, что расширения GROUP BY используются только в фразе GROUP BY.

Вы не можете использовать одну аналитическую функцию напрямую “поверх” другой. Чтоне запрещает, однако, обойти это ограничение с помощью нескольких вложенных предложений SELECT. Вложением предложений SELECT можно ограничить (constrain) результаты аналитической функции.

Примеры из реальной жизни

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

SALES
trans_date date
product_id varchar(10)
customer_id varchar(10)
sales_amt number

Каждая покупка клиента отражается в таблице в виде записи. Для простоты предположим, что в таблице отсутствуют столбцы, содержащие NULL-значения. Однако хочу заметить, что аналитические функции умеют достаточно элегантно обращаться и с такими объектами.

Определение пятерки самых популярных продуктов среди наиболее активной группы покупателей. “Какие пять товаров чаще всего приобретаются “лучшими” 15% наших клиентов?” - классический вопрос из области анализа потребительского спроса. Функции ранжирования CUME_DIST и RANK помогут реализовать этот запрос в два этапа. После Вы сможете провести небольшой условный анализ, воспользовавшись Oracle9i-механизмом условного ранжирования.

Для начала, используя запрос, приведенный в листинге 1, выявим 15% самых “ценных” клиентов (то есть тех, чей суммарный объем покупок превышает аналогичный показатель оставшихся 85% покупателей). Данный запрос с помощью фразы GROUP BY подсчитывает общий объем покупок по каждому клиенту. Затем применяется функция CUME_DIST, призванная найти значение процентиля для каждого клиента (процент клиентов, чей объем покупок меньше или равен аналогичному значению для текущего клиента). Поскольку Вы не можете использовать аналитическую функцию в фразе WHERE, Вам придется составить внешний запрос с тем, чтобы наложить на результаты CUME_DIST необходимые ограничения. Этот запрос производит выборку значений, удовлетворяющих поставленному выше условию, из результатов внутреннего запроса, отсеивая всех, кроме интересующих нас 15%.

Далее расширим первоначальный запрос и с помощью его модернизированного варианта, приведенного в листинге 2, найдем те пять продуктов, которые чаще всего приобретаются группой “элитных” покупателей. Два внутренних уровня вложенности те же, что и в первой части нашего примера. Список наиболее ценных клиентов служит источником информации для внешнего запроса, расставляющего продукты по их популярности на основе объемов продаж среди тех самых 15%, посредством функции RANK. Самый внешний запрос выделяет из полученного перечня пятерку наиболее ходовых товаров. Финальная фраза ORDER BY располагает итоговую информацию в порядке возрастания популярности продукта.

Продолжая пример, можно провести условный анализ, применив для этого функции условного ранжирования Oracle9i, аргументами которых служат гипотетические величины. Листинг 3 иллюстрирует ситуацию, в которой оказался бы новый продукт, если 15% “самых-самых” покупают товары стоимостью $100.000.

Результаты запроса показывают, что потенциальный продукт занял бы седьмое место по объемам продаж среди таких клиентов. Чтобы полностью оценить пример, представьте, как бы Вы достигли тех же результатов без использования аналитических функций. Сначала Вам пришлось бы с помощью курсора вычислить 15% “ценной ”клиентуры, а затем, используя дополнительные курсоры и временные таблицы, определить пятерку “народных” товаров и рейтинг гипотетического продукта. Или же обойтись без базы данных вообще. На практике большинство пользователей вычисляют подобные запросы с применением средств анализа, требующих значительно более мощного технического оснащения, что способствуют увеличению сетевого трафика и, как следствие, ведет к достаточно слабой производительности.

Определение самого удачного, с точки зрения объемов продаж, квартала. Очень часто компании интересует вопрос, какой период времени принес им наибольший доход. Листинг 4 содержит пример запроса, который в качестве результата выводит список последовательных трехмесячных временных отрезков, в течение которых прибыль составляла 10 и более миллионов долларов. Запрос сообщает первый и последний месяцы интервала плюс общую сумму выручки за данный промежуток. Этот пример иллюстрирует применение скользящих окон, создаваемых с помощью фразы windowing и аналитических функций FIRST_VALUE, LAST_VALUE и SUM OVER.

Кроме того, в листинге 4 показано, каким образом вложенные предложения SELECT образуют запрос, порождающий нужный результат. Самый внутренний запрос вычисляет общий доход за каждый месяц. Следующий по вложенности запрос использует полученный результат в качестве входных данных. Для каждой входной строки запрос использует оконные функции, чтобы создать трех месячное окно, охватывающее текущий месяц вместе с двумя его предшественниками. Затем для определения первого и последнего месяцев и общей суммы выручки данного трехмесячного периода каждое окно обрабатывается функциями FIRST_VALUE, LAST_VALUE и SUM OVER. Наконец, внешний запрос отсекает все временные интервалы, прибыль за которые не превышала 10 миллионов долларов, располагая оставшиеся в хронологическом порядке.

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

Выявление тенденций потребления. Многие компании хотят иметь в своем распоряжении анализ активности клиентов за определенный период времени. Обычно подобная информация отображается посредством кросс-табличных отчетов, составление которых иногда представляет собой непростую задачу. На листинге 5 приведен пример, где все это достигается путем одного единственного запроса с привлечением функции NTILE и расширений CUBE (или ROLLUP) в фразе GROUP BY. Там же показывается, как через Oracle9i-механизм конкатенации можно добыть более детальную информацию.

Как и прежде, применение в запросе нескольких аналитических функций реализуется с помощью последовательности вложенных предложений SELECT. Сначала внутренний запрос расчитывает для каждого клиента совокупный объем сделанных им покупок. Затем, в зависимости от полученных для каждого покупателя цифр, все они делятся функцией NTILE на четыре группы или квартала. В первую группу входят 25% наиболее активных клиентов компании, во вторую группа – следующие 25 и так далее.

Внешний запрос проецирует результаты внутреннего на статистику продаж, определяя общее количество сделок, инициированных представителями каждой группы в течение года. Затем с помощью расширения CUBE фразы GROUP BY вычисляются промежуточные суммарные значения объемов продаж по всем группам за каждый год и по каждой группе за все годы. В результате функция CUBE возвращает конечную сумму по всем группам и годам, оставляя неопределенные значения в строках, содержащих конечную и промежуточные суммы. Внешний запрос использует функции GROUPING и DECODE, чтобы в итоговом отчете заменить отсутствующие значения на о смысленные заголовки, что хорошо видно из примерного отчета на листинге 5.

Если Вы хотите уделить особое внимание тенденциям продаж и сбыта товаров в течение нескольких лет, как, впрочем, и месяцев, то можно воспользоваться Oracle9i-механизмом конкатенации для сопряжения двух выражений GROUPING SETS в следующем операторе GROUP BY:

group by
GROUPING SETS (to char(trans date, ‘YYYY’),
to_char(trans_date, ‘YYYY-MM’)),
GROUPING SETS (band, product_id)

Этот запрос вычисляет следующие четыре группировки: (Year,band), (Year,product_id), (Month,band), (Month,product_id).

В отличие от функций CUBE и ROLLUP, которые вычисляют промежуточные результаты на разных этапах, этот запрос выдает только те группы, которые Вы запрашивали, причем делает это за один единственных проход. Чтобы получить те же результаты без использования механизма конкатенации, Вам потребуется четыре предложения SELECT …GROUP BY, соединенных фразами UNION ALL и четыре раза произвести сканирование таблицы.

Вернемся к началу

Аналитические функции в составе Oracle8i и Oracle9i помогают SQL идти в ногу с растущими требованиями, предъявляемыми к оперативному анализу данных. Их наличие ведет к значительному сокращению времени разработки и заметному повышению эффективности Ваших информационно-аналитических запросов. И если грамотно сочетать их между собой, что не раз было продемонстрировано в данной статье, то можно добиться от них максимальной пользы.

 
Листинг 1. Нахождение 15% самых “ценных” клиентов 
select * from 
 (select customer_id as cust_id, sum(sales_amt) as 
 cust_sales, round(100 * CUME_DIST() over(order by 
 sum(sales_amt) asc)) as cust_percentile
 from sales group by customer_id)
 where cust_percentile > 85
 order by cust_percentile desc;
 
CUST_ID CUST_SALES CUST_PERCENTILE
-------- ----------- ---------------
133461 2175523 100
270570 1805256 99
160160 1801329 98
...... ....... ..
...... ....... ..
133508 1124910 86
 
Листинг 2. Пять продуктов наиболее покупаемых “элитой” 
 
select * from 
 (select product_id, sum(sales_amt) as product_sales, 
 RANK() over(order by sum(sales_amt) desc) as 
 product_rank
 from sales 
 where customer_id in
 (select customer_id from
 (select customer_id,
 100*CUME_DIST() over(order by sum(sales_amt) asc) as 
 cust_percentile
 from sales group by customer_id)
 where cust_percentile > 85)
 group by product_id)
 where product_rank <= 5
 order by product_rank;
 
PRODUCT_ID PRODUCT_SALES PRODUCT_RANK
---------------- ---------------------- -----------------
SO-1278 3569871 1
SE-9087 2998109 2
SI-7652 2587397 3
SD-0386 2001298  4
CO-8925 1098763 5
 
Листинг 3.Нахождение гипотетического рейтинга
 
select RANK(100000)within group (order by product_sales desc) as HRANK from 
 (select product_id, sum(sales_amt) as product_sales 
 from sales 
 where customer_id in
 (select customer_id from 
 (select customer_id,
 100*CUME_DIST() over(order by sum(sales_amt) asc) as 
 cust_percentile
 from sales group by customer_id)
 where cust_percentile > 85)
 group by product_id);
 
HRANK
-----
7
 
Листинг 4. Нахождение главных трех месячных периодов
 
select * from
 (select FIRST_VALUE(to_char(month,'YYYY-MON')) 
 over (order by month range interval '2' month preceding) 
 as start_month,
 LAST_VALUE(to_char(month,'YYYY-MON')) 
 over (order by month range interval '2' month preceding) 
 as end_month,
 SUM(monthly_sales) over (order by month range interval '2' month preceding)
 as three_month_sales from 
 (select trunc(trans_date, 'MM') as month, sum(sales_amt) 
 as monthly_sales
 from sales group by trunc(trans_date, 'MM')))
 where three_month_sales > 10000000
 order by to_date(start_month, 'YYYY-MON');
 
START_MONTH END_MONTH THREE_MONTH_SALES
------------ --------- -----------------
1999-APR 1999-JUN 10107234
2000-MAY 2000-JUL 11213478
2000-JUN 2000-AUG 10987251
2000-SEP 2000-NOV 11334987
 
Листинг 5. Выявление тенденций потребления.
 
select decode(GROUPING(to_char(trans_date, 'YYYY')), 1, 'All 
Years',to_char(trans_date, 'YYYY')) as Year,
decode(GROUPING(band),1, 'All Bands', band) as Band,
sum(sales_amt) as total_sales_amt
from
 (select customer_id, NTILE(4) over(order by sum(sales_amt)) as band
 from sales group by customer_id) a, sales b
where a.customer_id = b.customer_id
group by cube(to_char(trans_date, 'YYYY'), band);
 
YEAR BAND TOTAL_SALES_AMT
------- ------- ---------------
1999 1 13506897
1999 2 10987237
1999 3 5629814
1999 4 3328976
1999 All Bands 33452924
2000 1 20987154
2000 2 9852395
2000 3 5680923
2000 4 2984109
2000 All Bands 39504581
All Years 1 34494051
All Years 2  20839632
All Years 3 11310737
All Years 4 6313085 
All Years All Bands 72957505

Ганеш Вариар ( ganesh_variar@yahoo.com ), cпециалист консалтинговой компании Saama Technologies Inc.(www.saama.com), располагающейся в Силиконовой долине.