А теперь давайте поговорим о выборках. Хотя, вроде бы, что о них говорить? И Мускуль тоже много чего может... Да, может. Ничего против него не имею :) За поддержку стандарта SQL в селекте можно сразу ставить 1:1. Но фишка в том, что у Постгре есть свою "плюшки", которые очень сильно расширяют возможности селекта. И это я еще пока не говорю про ДЖОЙНы, про них лучше говорить отдельно.
Тут я хочу акцентировать внимание на "подзапросах". Которые могут делаться к разным таблицам, на основании разных критериев. Мускуль их позволяет делать. Но вот только даже среди апологетов Мускуля, насколько я понимаю, до сих пор не утихают споры о том, как обрабатываются одинаковые подзапросы внутри одного запроса: то ли они делаются многократно, то ли один раз... То ли может быть и так, и эдак.
Что же предоставляет Постгре, какие "плюхи"?
В самом общем виде это выглядит так:
WITH ....
SELECT ....
Где после WITH определяются запросы, в любом количестве, которые потом могут быть использованы в основном запросе. Более того, любой подзапрос может использовать другой подзапросе, который был определен ранее. Существенно то, что всё это - один запрос!!!
Замечу, что что-то очень близкое есть в MSSQL (прошу не путать с MySQL!!!), вроде как в Оракле и, возможно, в некоторых других БД.
Что это нам дает?
Ну, вот пример... Взят он из реального скрипта, в ПХП добавляются некоторые данные - поэтому в запросе есть переменные "с долларом". Комментарии я оставил, как они были сделаны ранее.
Свернутый текст
with
/* запрос данных из конфигурации */
cfg as
(
select regions.name_ as Reg, bsc.name_ as Bsc, date_, '250-01-' || lac || '-' || cellid as GCI, HoppRate, lac, cellid, id2
from
(
select date_, lac, cellid, case when hoppingtype = 'noHopping' then 0. else 1. end as HoppRate, get_regid( lac ) as regid, bsc
from cells
where date_ = '$date'
) ini_sel
left join
(
select * from regions where regid < 100 and producer='alc'
) regions
using (regid )
left join bsc
using( regid, bsc)
where regid > 0
),
/* запрос данных из общей статистики */
traffic as
(
select lac, cellid, date_,
round( (1.00*sum(MC380A)/3600+ 1.00*sum(MC380B)/3600), 3) as Traff,
sum(mc642 + mc652) as InterCellHoInc, sum(mc646+mc656) as InterCellHoOut, sum(mc662) as IntraCellHo,
sum(MC701E) as count_data, sum(MC702A) as count_fr, sum(MC702B) as count_hr, sum(MC702C) as count_efr, sum(MC704A) as count_amr_fr,
sum(MC704B) as count_amr_hr, sum(MC701E)+sum(MC702A)+sum(MC702B)+sum(MC702C)+sum(MC704A)+sum(MC704B) as count_calls
from
(
select date_, h, lac, cellid, mc380a, mc380b, mc701e, mc702a, mc702b, mc702c, mc704a, mc704b, mc642, mc646, mc652, mc656, mc662
from stat_alc_bts where h=24 and date_ = '$date'
and cellid>0
) ini_selection
where mc380a > 0
group by lac, cellid, date_
)
/* объединение статистик в единую таблицу и нахождение разных сумм и процентов */
select
*,
get_rxqual_dl_ul( date_, lac, cellid) as rxqual /* данные по RXQUAL */
from
(
select * from traffic
full join cfg using (date_, lac, cellid)
) ini_sel
where count_calls > 0
В данном случае можно было бы и без WITH построить запрос. Но тогда он был бы "нечитабельный". Тем более, что этот запрос является частью более сложного запроса.
Итак, что же мы видим? Основная часть находится в конце, очень легко читается. А подзапросы "traffic" и "cfg" можно найти и тоже разобрать на части.
select
*,
get_rxqual_dl_ul( date_, lac, cellid) as rxqual /* данные по RXQUAL */
from
(
select * from traffic
full join cfg using (date_, lac, cellid)
) ini_sel
where count_calls > 0
Я этот запрос делал несколько лет назад, но тем не менее, такая его структура позволила с одного взгляда легко его понять, что тут делается. А если представить, что вместо "traffic" и "cfg" тут были бы те самые большие запросы, что показаны ранее? Уже сложнее было бы разобраться.
И это удобство весьма существенно, т.к. когда много работаешь с разными запросами, то все их в голове не удержишь. Надо уметь быстро вспомнить, о чем идет речь, как работает определенный запрос.
Но это был пример просто с удобством.
Рассмотрим другой пример, где подзапросы играют более важную роль. Этот пример взят также из реального проекта. Некоторые данные также подставляются в ПХП при формировании запроса.
Свернутый текст
with
ini_sel as
(
select
trx_number, date_, cellid, lac,
tpr_timing_advance_1*max_timing_advance/254.0 as tpr_timing_advance_1,
tpr_timing_advance_2*max_timing_advance/254.0 as tpr_timing_advance_2,
tpr_timing_advance_3*max_timing_advance/254.0 as tpr_timing_advance_3,
tpr_timing_advance_4*max_timing_advance/254.0 as tpr_timing_advance_4,
tpr_timing_advance_5*max_timing_advance/254.0 as tpr_timing_advance_5,
tpr_timing_advance_6*max_timing_advance/254.0 as tpr_timing_advance_6,
tpr_timing_advance_7*max_timing_advance/254.0 as tpr_timing_advance_7,
tpr_timing_advance_8*max_timing_advance/254.0 as tpr_timing_advance_8,
tpr_timing_advance_9*max_timing_advance/254.0 as tpr_timing_advance_9,
tpr_timing_advance_10*max_timing_advance/254.0 as tpr_timing_advance_10,
tpr_ul_rxqual_ta_band_1, tpr_ul_rxqual_ta_band_2,
tpr_ul_rxqual_ta_band_3, tpr_ul_rxqual_ta_band_4,
tpr_ul_rxqual_ta_band_5, tpr_ul_rxqual_ta_band_6,
tpr_ul_rxqual_ta_band_7, tpr_ul_rxqual_ta_band_8,
tpr_ul_rxqual_ta_band_9, tpr_ul_rxqual_ta_band_10
from block_3116
where $where_date
and lac between $lac_begin and $lac_end
)
,
trx_sel as
(
select lac, cellid,
(
select count(trx_number)
from ini_sel
where date_= '$date' and lac=i.lac and cellid=i.cellid
) as trxs
from ini_sel i
group by lac, cellid
)
select
count(*) as days,
lac, cellid, trx_number,
(
select trxs
from trx_sel
where lac=sec_sel.lac and cellid=sec_sel.cellid
) as trxs,
sum(tpr_timing_advance_1) as tpr_timing_advance_1,
sum(tpr_timing_advance_2) as tpr_timing_advance_2,
avg(tpr_ul_rxqual_ta_band_1) as tpr_ul_rxqual_ta_band_1,
avg(tpr_ul_rxqual_ta_band_2) as tpr_ul_rxqual_ta_band_2
from
(
select
date_, trx_number, cellid, lac,
sum(tpr_timing_advance_1) as tpr_timing_advance_1,
sum(tpr_timing_advance_2) as tpr_timing_advance_2,
sum(tpr_timing_advance_3) as tpr_timing_advance_3,
sum(tpr_timing_advance_4) as tpr_timing_advance_4,
sum(tpr_timing_advance_5) as tpr_timing_advance_5,
sum(tpr_timing_advance_6) as tpr_timing_advance_6,
sum(tpr_timing_advance_7) as tpr_timing_advance_7,
sum(tpr_timing_advance_8) as tpr_timing_advance_8,
sum(tpr_timing_advance_9) as tpr_timing_advance_9,
sum(tpr_timing_advance_10) as tpr_timing_advance_10,
avg(tpr_ul_rxqual_ta_band_1) as tpr_ul_rxqual_ta_band_1,
avg(tpr_ul_rxqual_ta_band_2) as tpr_ul_rxqual_ta_band_2,
avg(tpr_ul_rxqual_ta_band_3) as tpr_ul_rxqual_ta_band_3,
avg(tpr_ul_rxqual_ta_band_4) as tpr_ul_rxqual_ta_band_4,
avg(tpr_ul_rxqual_ta_band_5) as tpr_ul_rxqual_ta_band_5,
avg(tpr_ul_rxqual_ta_band_6) as tpr_ul_rxqual_ta_band_6,
avg(tpr_ul_rxqual_ta_band_7) as tpr_ul_rxqual_ta_band_7,
avg(tpr_ul_rxqual_ta_band_8) as tpr_ul_rxqual_ta_band_8,
avg(tpr_ul_rxqual_ta_band_9) as tpr_ul_rxqual_ta_band_9
from
ini_sel
group by lac, cellid, trx_number , date_
having
avg(tpr_ul_rxqual_ta_band_1) > avg(tpr_ul_rxqual_ta_band_2) and
avg(tpr_ul_rxqual_ta_band_1) > 100 and
1.0 * sum(tpr_timing_advance_1) / (sum(tpr_timing_advance_2 ) + 1) > 0.5 and
1.0 * ( sum(tpr_timing_advance_1) + sum(tpr_timing_advance_2) ) /
( sum(tpr_timing_advance_1) + sum(tpr_timing_advance_2) +
sum(tpr_timing_advance_3) + sum(tpr_timing_advance_4) +
sum(tpr_timing_advance_5) + sum(tpr_timing_advance_6) +
sum(tpr_timing_advance_7) + sum(tpr_timing_advance_8) +
sum(tpr_timing_advance_9) + sum(tpr_timing_advance_10) + 1 ) > 0.1
and
sum(tpr_timing_advance_2) > 100
) sec_sel
group by lac, cellid, trx_number
order by cellid, trx_number
Запрос "хитронавороченный", на первый взгляд, но его структура достаточно проста:
Свернутый текст
with
ini_sel as
( select ... ) ,
trx_sel as
(
select .... /* берутся данные из подзапроса ini_sel*/
)
select /* основной запрос*/
... /* тут используется подзапрос trxs, при формировании данных в одном столбце */
from
(
select ...
from ini_sel /* используем подзапрос ini_sel */
group by... having ....
) sec_sel
group by ... order by ...
А теперь попробуйте представить этот же запрос, если в каждом месте, где нужны используемые подзапросы, писать их целиком и запустить это в Мускуле :) И попробуйте понять, сколько раз в таком случае будут выполнены подзапросы. В случае с Постгре, в том виде, как я дал пример - гарантированно всего один раз каждый подзапрос..
И, наконец, рекурсия с использованием подзапросов в WITH. WITH RECURSIVE. Тут я не буду много писать, а просто дам ссылку на другую тему форума, где я об этом ранее говорил: http://phpforum.su/index.php?showtopic=31806
Там рассматривается обращение подзапроса к самому себе, что позволяет, например, выбрать данные для построения древовидной структуры, пользуясь всего лишь одним запросом.
На всякий случай уточню, что я могу задать условия, и выбрать не всё дерево, а только его часть. В той теме по ссылке об этом не сказано, по-моему. Но подразумевалось - это точно :)
-------
И еще пара слов про DISTINCT, т.к. он тоже относится к SELECT. Дабы отдельную тему не поднимать :)
Да, в Мускуле есть DISTINCT. Но он дает уникальность только по всей строке. В Постгре DISTINCT имеет чуть больше возможностей. Можно указывать, по каким строкам должна быть уникальность.
Рассмотрим такой запрос:
Свернутый текст
select distinct on (cellid) cellid, tch_traf, edge_traf, ts
from ....
order by cellid, tch_traf+edge_traf desc
Что он делает? Выбирает данные из некой таблицы (не суть важно откуда), берет строки только с уникальными cellid, сортирует по убыванию суммы 2-х параметров...
В итоге имеем данные по каждому cellid с максимальным трафиком.
Вот еще пример запроса с DISTINCT ON (этот запрос является подзапросом в более сложной структуре)
Свернутый текст
select
distinct on( date_, lac, cellid, trx_number)
date_, lac, cellid, trx_number, get_rxqual_dl_ul_trx( b3110.date_, b3110.lac, b3110.cellid, b3110.trx_number) as dl
from
block_3110 b3110
where date_='$date' and lac between $lac_begin and $lac_end
Здесь требуется уникальность по 4-м параметрам сразу, и для каждой такой уникальной комбинации считается некий параметр, согласно функции из Постгре. Заодно замечу, кстати, что из функции возвращается массив из 18 элементов, а не одна величина. Этот массив используется в том запросе, который использует данный подзапрос (это к вопросу о бОльшем количестве типов данных в Постгре, что рассматривали чуть ранее ). Почему функция, говорите? Да потому, что она используется в разных запросах, сходных по структуре, но все-таки разных. Проще (и намного надежнее) было написать функцию, чем копипасить подзапросы.
----------
Предлагаю выписать счет примерно 4:3 в пользу Постгре. Потому что Мускуль имеет много возможностей с селектом, но у Постгре их больше. И кстати, еще будет отдельная тема про ДЖОЙНы, у них тоже есть различия.
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
