О написании SQL-скриптов

#sql #sql_скрипты #рабочее #крайний_срок_вчера

Секрет написания хорошего SQL-скрипта прост: пиши его так, как будто он предназначается жестокому социопату с огромным ножом, знающему твой домашний адрес.

То есть: заголовки столбцов должны быть понятными; расположение столбцов должно быть логичным, а не хаотичным; выборка не должна содержать лишних строк; важные моменты в скрипте должны сопровождаться комментарием.

В качестве примера приведу скрипт по выборке модели подписания, используемой каждым отдельно взятым пользователем клиента системы ДБО, который писал в рамках выполнения одной из рабочих задач:

select distinct UMP.CLIENT_ID as "[АРМ] ID", CLIENT_NAME as "[АРМ] Наименование", CLIENT_BL as "[АРМ] Блок", CLIENT_SRV as "[АРМ] Сервисы",
                UMP.CUST_ID as "[Клиент] ID", CUST_INN as "[Клиент] ИНН", CUST_NAME as "[Клиент] Наименование", listagg(NUMBER_OF_SIGNATURES, '; ') within group ( order by DOCSCHEMEID) as "[Клиент] Кол-во подписей", SIGN_GR as "[Клиент] Группа подписей",
                UMP.FIO as "[Пользователь] ФИО", PROFILE_ID as "[Пользователь] ID", SIGN_DOC_RIGHT as "[Пользователь] Право подписи",
                UMP.H2H as "[Пользователь] H2H", CRT_DF as "[Пользователь] Сертификат действует до", LAST_CONNECTION as "[Пользователь] Дата последнего входа", PRIV_DF as "[Пользователь] Полномочия действуют до"
from(
select distinct
                PO.CLIENT CLIENT_ID, replace(PO.NAME, '"', '') CLIENT_NAME,
                case(PO.FENABLED) when 0 then null when 1 then 'ДА' end CLIENT_BL,
                case(case(PO.SERVICE1) when 'X' then '1' else null end || case(PO.SERVICE2) when 'X' then '2' else null end) when '1' then 'БК' when '2' then 'ИК' when '12' then 'БК и ИК' end CLIENT_SRV,
                CU.CUSTID CUST_ID, CU.INN CUST_INN, replace(LTRIM(((select NAMESHORT from propertytype APR where CU.propertytype=APR.autokey)|| ' ' || CU.NAMESHORT)), '"', '') CUST_NAME,
				MP.DOCSCHEMEID, MP.NUMBER_OF_SIGNATURES NUMBER_OF_SIGNATURES, CNSS.SIGNSEQUENSE SIGN_GR,
                REGEXP_REPLACE(CP.DISPLAYNAME, ' +', ' ') FIO, CWP.PROFILEID PROFILE_ID,
                (case
				-- если установлена обычная подпись:
                    when CWP.SIGNDOCRIGHTS != 1 and CNSS.SIGNSEQUENSE is null and EDSR.SIGNDOCRIGHTS is null then (case(CWP.SIGNDOCRIGHTS) when 0 then 'Без права подписи' when 2 then 'Единственное' when 3 then 'Подпись 1' when 4 then 'Подпись 2' end)
				-- если установлена обычная бпп, а была расширенная с опп:    
					when CWP.SIGNDOCRIGHTS = 0 and CNSS.SIGNSEQUENSE is null and EDSR.SIGNDOCRIGHTS is not null then 'Была - Расширенная с ОПП, '||case(EDSR.SIGNDOCRIGHTS) when 3 then 'Подпись 1' when 4 then 'Подпись 2' when 0 then 'Без права подписи' else to_char(EDSR.SIGNDOCRIGHTS) end||' для документа '||(case when VIEW_SDR_EDSR.NAMELOC is null then 'Любой' else VIEW_SDR_EDSR.NAMELOC end)||'; Сейчас - Без права подписи'
				-- если установлена расширенная с опп:
					when CWP.SIGNDOCRIGHTS = 1 and CNSS.SIGNSEQUENSE is null and EDSR.SIGNDOCRIGHTS is not null then  'Расширенная с ОПП, '||case(EDSR.SIGNDOCRIGHTS) when 3 then 'Подпись 1' when 4 then 'Подпись 2' when 0 then 'Без права подписи' else to_char(EDSR.SIGNDOCRIGHTS) end||' для документа '||(case when VIEW_SDR_EDSR.NAMELOC is null then 'Любой' else VIEW_SDR_EDSR.NAMELOC end)
				-- если установлена обычная бпп, а была расширенная с рпп:
					when CWP.SIGNDOCRIGHTS = 0 and CNSS.SIGNSEQUENSE is not null and EDSRS.SIGNDOCRIGHTSEX is not null then 'Была - Расширенная с РПП, Подпись '||EDSRS.SIGNDOCRIGHTSEX||'; Сейчас - Без права подписи'
                -- если установлена расширенная с рпп:
					when CWP.SIGNDOCRIGHTS = 1 and CNSS.SIGNSEQUENSE is not null and EDSRS.SIGNDOCRIGHTSEX is not null then 'Расширенная с РПП, Подпись '||EDSRS.SIGNDOCRIGHTSEX
                -- не имеет каких либо прав подписи в CustID:
					when CWP.SIGNDOCRIGHTS = 1 and CNSS.SIGNSEQUENSE is null and EDSR.SIGNDOCRIGHTS is null then 'EXCLUDED'
                    when CWP.SIGNDOCRIGHTS = 1 and CNSS.SIGNSEQUENSE is not null and EDSRS.SIGNDOCRIGHTSEX is null then 'EXCLUDED'
                    else null
                end) SIGN_DOC_RIGHT,
                case when (WSU.REMOTEUSER) > 0 then 'ДА' end H2H,
                to_char((to_date('00010101', 'YYYYMMDD') + 1 + CUID.DATEFINISH), 'DD.MM.YYYY') CRT_DF, to_char(RU.LASTCONNECTIONDATETIME, 'DD.MM.YYYY') LAST_CONNECTION, to_char ( CPS.DATEEND, 'DD.MM.YYYY') PRIV_DF
from CUSTOMER CU
inner join POSTCLNT PO on CU.CLIENT = PO.CLIENT
left join VIEWWEBSERVICECUSTOMERS VIEW_WSCU on CU.CUSTID = VIEW_WSCU.CUSTID
left join CRYPTOWORKPLACE CWP on CU.CLIENT = CWP.АРМ ID
left join CRYPTOUID CUID on CWP.PROFILEID = CUID.PROFILEID
left join CRYPTOPROFILE CP on CUID.PROFILEID = CP.AUTOKEY
left join REMOTECRYPTOPROFILE RCP on CUID.PROFILEID = RCP.PROFILEID
left join REMOTEIDS RI on RCP.USERKEY = RI.USERKEY
left join REMOTEUSER RU on RI.USERKEY = RU.USERKEY
left join WEBSERVICEUSERS WSU on RI.USERKEY = WSU.REMOTEUSER
left join CRYPTOPERIODS CPS on PO.CLIENT = CPS.CLIENT and CUID.CRYPTOUID = CPS.ID
left join EXTDOCSIGNRIGHTS EDSR on CU.CLIENT = EDSR.CLIENT and CU.CUSTID = EDSR.CUSTID and CWP.PROFILEID = EDSR.PROFILEID and EDSR.RPLSTATUS != 3
-- определяем право подписи под конкретным документом:
left join VIEWARCHDSSANDDOCTYPES VIEW_SDR_EDSR on EDSR.DOCSCHEMEID = VIEW_SDR_EDSR.ID
left join (
select MP.AUTOKEY, CLIENT, CUSTID, MP, DOCSCHEMEID,
       ((case
	   -- актуальные для новой системы ДБО наименования документов:
        when MP.NAMELOC = 'Платежное поручение' then 'Рублевое платежное поручение'
        when MP.NAMELOC = 'Произвольный документ в банк' then 'Письма в Банк'
        when MP.NAMELOC = 'Поручение на продажу валюты' then 'Заявление на конверсионную операцию'
        when MP.NAMELOC = 'Поручение на покупку валюты' then 'Заявление на конверсионную операцию'
        when MP.NAMELOC = 'Поручение на перевод валюты' then 'Заявление на перевод'
        when MP.NAMELOC = 'Конверсия одной иностранной валюты  в другую' then 'Заявление на конверсионную операцию'
        when MP.NAMELOC = 'Распоряжение на списание средств с транзитного валютного счета' then 'Списание с транзитного счёта'
        when MP.NAMELOC = 'Паспорт сделки по контракту (138-И)' then 'Паспорт сделки (Архивный)'
        when MP.NAMELOC = 'Паспорт сделки по кредитному договору (138-И)' then 'Паспорт сделки (Архивный)'
        when MP.NAMELOC = 'Произвольный документ по аккредитивным операциям' then 'Письмо по документарным операциям в Банк'
        when MP.NAMELOC = 'Заявление на открытие аккредитива в валюте РФ на территории РФ' then 'Заявление на открытие аккредитива для расчетов в валюте РФ'
        when MP.NAMELOC = 'Заявление на изменение условий аккредитива для международных расчетов' then 'Заявление на изменение условий аккредитива для международных расчётов'
        when MP.NAMELOC = 'Заявление на изменение условий аккредитива в валюте РФ на территории РФ' then 'Заявление на изменение условий аккредитива для расчетов в валюте РФ'
        when MP.NAMELOC = 'Заявление на размещение депозита' then 'Заявление о присоединении к Условиям проведения Банком ВТБ (ПАО) депозитных операций с корпоративными клиентами'
        when MP.NAMELOC = 'Заявления о продлении срока депозитной сделки' then 'Заявление на продление депозита'
        when MP.NAMELOC = 'Уведомление о согласии на продление срока депозитной сделки' then 'Уведомление о согласии на продление срока депозита'
        when MP.NAMELOC = 'Справка о валютных операциях (138-И)' then 'Справка о валютных операциях (Архивный)'
        when MP.NAMELOC = 'Заявление о переоформлении паспорта сделки (138-И)' then 'Заявление на перевыпуск паспорта сделки (Архивный)'
        when MP.NAMELOC = 'Заявление о закрытии паспорта сделки (138-И)' then 'Заявление на закрытие паспорта сделки (Архивный)'
        when MP.NAMELOC = 'Заявление о постановке контракта на учет' then 'Заявление о постановке контракта (кредитного договора) на учет'
        when MP.NAMELOC = 'Заявление о постановке кредитного договора на учет' then 'Заявление о постановке контракта (кредитного договора) на учет'
        when MP.NAMELOC = 'Заявление о снятии контракта (кредитного договора) с учета' then 'Заявление о снятии контракта с учета'
        when MP.NAMELOC = 'Заявление о внесении изменений в раздел I ведомости банковского контроля' then 'Заявление о внесении изменений в раздел I ведомости банковского контроля'
        when MP.NAMELOC = 'Сведения о валютных операциях' then 'Сведения о валютной операции'
        else MP.NAMELOC end) ||' - '|| MP.NUMBER_OF_SIGNATURES) NUMBER_OF_SIGNATURES, 
		MP.ROW_NUM
from (select MPU.*, row_number() over (partition by MPU.CLIENT, MPU.CUSTID, MPU.DOCSCHEMEID order by MPU.CLIENT, MPU.CUSTID, MPU.NMP) ROW_NUM
from (
select distinct vCNS.AUTOKEY, vCNS.CLIENT, vCNS.CUSTID, '1' as "NMP", vCNS.DOCSCHEMEID, (case when vVIEW_SDR.NAMELOC is null then 'Любой' else vVIEW_SDR.NAMELOC end) NAMELOC, vCNS.NUMBEROFSIGNATURES NUMBER_OF_SIGNATURES
from CRYPTONUMOFSIGNS vCNS
left join VIEWARCHDSSANDDOCTYPES vVIEW_SDR on vCNS.DOCSCHEMEID = vVIEW_SDR.ID
where vCNS.CLIENT != 0 and vCNS.CUSTID != 0 and vCNS.RPLSTATUS != 3
union all
select distinct vCNS.AUTOKEY, vCU.CLIENT, vCU.CUSTID, '2' as "NMP", vCNS.DOCSCHEMEID, (case when vVIEW_SDR.NAMELOC is null then 'Любой' else vVIEW_SDR.NAMELOC end) NAMELOC, vCNS.NUMBEROFSIGNATURES NUMBER_OF_SIGNATURES
from CRYPTONUMOFSIGNS vCNS
left join VIEWARCHDSSANDDOCTYPES vVIEW_SDR on vCNS.DOCSCHEMEID = vVIEW_SDR.ID
cross join CUSTOMER vCU
where vCNS.CLIENT = 0 and vCNS.CUSTID = 0 and vCNS.RPLSTATUS != 3
and lower(vCU.NAMESHORT) not like '%отключен%'
) MPU) MP
-- убираем возможные повторы документов, приоритет у связки без использования cross join:
where MP.ROW_NUM != 2
-- только мигрируемые документы по ТЗ мигратора:
and MP.DOCSCHEMEID in (0, 1, 4, 7, 8, 9, 12, 13, 14, 17, 18, 38, 49, 51, 93, 509, 556, 558, 562, 564, 566, 570, 572, 612, 947, 948, 992, 994, 1012, 1042, 1046, 1048, 1050, 2901, 3308)
order by MP.DOCSCHEMEID ) MP on CU.CLIENT = MP.CLIENT and CU.CUSTID = MP.CUSTID
left join CRYPTONUMOFSIGNSEX CNSS on MP.AUTOKEY = CNSS.AUTOKEYMAIN and CU.CLIENT = CNSS.CLIENT and CNSS.RPLSTATUS != 3
left join EXTDOCSIGNRIGHTSEX EDSRS on EDSR.AUTOKEY = EDSRS.AUTOKEYMAIN and EDSRS.RPLSTATUS != 3
where CP.FENABLED = 1
and CUID.FACTIVE = 1
and CU.CUSTID in (
'100500'
    )
order by PO.CLIENT, CU.CUSTID, MP.DOCSCHEMEID
) UMP
-- убираем не относящихся к клиеенту пользователей:
where UMP.SIGN_DOC_RIGHT != 'EXCLUDED'
group by UMP.CLIENT_ID, CLIENT_NAME, CLIENT_BL, CLIENT_SRV, CUST_ID, CUST_INN, CUST_NAME, SIGN_GR, FIO, PROFILE_ID, SIGN_DOC_RIGHT, H2H, CRT_DF, LAST_CONNECTION, PRIV_DF
order by UMP.CLIENT_ID, CUST_ID, PROFILE_ID

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