Примеры сложных запросов (формирование данных под шаблон)

adhoc1.sql

----Фильтрация данных под шаблон
WITH cte as (
SELECT e.CREATED_ON,e.DATE_,e.AXIS_1,'D_1' as AXIS_2,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(AXIS_3,'"',''),'дата продажи',''),'.',''),' ',''),',',''),'АББ','АБВ') as AXIS_3,
e.AXIS_4, 'Имя компании' as AXIS_5, e.AXIS_6,  REPLACE(SUBSTRING(AXIS_3,0,CHARINDEX(',',AXIS_3)),'АББ','АБВ') as AXIS_7
FROM etls as e WHERE 
e.AXIS_6 != 'TEST' or e.AXIS_6 is NULL
UNION ALL
SELECT e.CREATED_ON,e.DATE_,e.AXIS_1,'D_2' as AXIS_2,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(AXIS_3,'"',''),'дата продажи',''),'.',''),' ',''),',',''),'АББ','АБВ') as AXIS_3,
e.AXIS_4, 'Адрес проданного объекта' as AXIS_5, e.AXIS_6,  e.AXIS_7
FROM etls as e WHERE 
e.AXIS_6 != 'TEST' or e.AXIS_6 is NULL
UNION ALL
SELECT e.CREATED_ON,e.DATE_,e.AXIS_1,'D_3' as AXIS_2,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(AXIS_3,'"',''),'дата продажи',''),'.',''),' ',''),',',''),'АББ','АБВ') as AXIS_3,
e.AXIS_4, 'Дата продажи' as AXIS_5, e.AXIS_6,
RIGHT(REPLACE(REPLACE(REPLACE(REPLACE(AXIS_3,'"',''),'дата продажи',''),' ',''),',',''),10) as AXIS_7
FROM etls as e WHERE 
e.AXIS_6 != 'TEST' or e.AXIS_6 is NULL)

Select top(3) REPLACE(CONVERT(varchar(20),cte.CREATED_ON, 105),'-','.')+' '+CONVERT(varchar(20),cte.CREATED_ON, 108) as CREATED_ON,
CONVERT(varchar(20),cte.DATE_,104) as DATE_, cte.AXIS_1,cte.AXIS_2,cte.AXIS_3,cte.AXIS_4,
cte.AXIS_5,cte.AXIS_6,cte.AXIS_7 from cte
ORDER by cte.DATE_ DESC,cte.CREATED_ON DESC,cte.AXIS_1 DESC

--Оконная функция (отклонение от общего значения)
SELECT to_char(tradedate, 'DD.MM.YYYY')as DATAS,ROUND(results ::decimal,4) as DELTAS FROM(
SELECT*,first_value(closed) OVER (
            ORDER BY tradedate
            ),
closed*100/first_value(closed) OVER (
            ORDER BY tradedate
            )-100 as results
FROM mb where tradedate in
 (SELECT max(tradedate)
FROM mb
GROUP BY DATE_PART('year', tradedate),DATE_PART('month', tradedate))
)as x;