Файлы к уроку:
Описание
Вы получаете данные на лист Excel из базы данных. Вы используете запрос SQL, в который вставляются параметры с листа Excel. Ваш SQL запрос состоит из большого количества строк. В этом запросе параметры встречаются много раз.
Если в вашем PQ-запросе содержится SQL-запрос с большим количеством параметров вам будет очень некомфортно работать, особенно, если в PQ-запросе помимо подключения к источнику будет еще несколько шагов.
Как организовать все таким образом, чтобы вам было удобно работать с этими запросами?
Решение
- Объемный запрос можно перенести в отдельный запрос, который будет состоять из одного шага;
- В SQL запросе нужно использовать комментарии, чтобы не запутаться в параметрах.
Примененные функции
- Text.Format
- Excel.CurrentWorkbook
- Value.NativeQuery
- PostgreSQL.Database
Код
Так будет выглядеть основной запрос, который получает данные.
sql_query — это SQL-запрос, который находится в отдельном PQ-запросе.
let
src = Value.NativeQuery(PostgreSQL.Database("localhost", "postgres"),
sql_query,
null,
[EnableFolding=true])
in
src
Так будет выглядеть SQL-запрос, в котором используются параметры. Параметры в коде обозначены комментариями.
Text.Format(
"with
cte as (
select
city.country_id,
city.city,
customer.customer_id,
rental.rental_id,
rental.rental_date,
payment.amount,
count(rental_id) over (partition by country_id, customer.customer_id order by country_id) as max_rentals,
sum(rental_id) over (partition by country_id, customer.customer_id order by country_id) as max_amount,
row_number () over (partition by country_id order by country_id, rental_date desc) as rental_number
from payment
left join rental
using (rental_id)
left join customer
on rental.customer_id = customer.customer_id
left join address
using (address_id)
left join city
using (city_id)
where city.city in (#{0}) ---- city_param ---- {0} ПЕРЕЧЕНЬ ГОРОДОВ
and rental.rental_date::date >= #{1} ---- date_param ---- {1} ВЫБОР ДАТЫ
),
rentals as (
select
country_id,
customer_id,
'max_rentals' as attr,
max(max_rentals) as value
from cte
where cte.amount in (#{2}) ---- price_param ---- {2} ПЕРЕЧЕНЬ ЦЕН
group by
country_id,
customer_id
order by
country_id,
value desc),
amount as (
select
country_id,
customer_id,
'max_amt' as attr,
max(max_amount) as value
from cte
where cte.amount in (#{2}) ---- price_param ---- {2} ПЕРЕЧЕНЬ ЦЕН
group by
country_id,
customer_id
order by
country_id,
value desc),
last_rental as (
select
country_id,
customer_id,
'last_rental' as attr
from cte
where rental_number = 1
and cte.amount in (#{2}) ---- price_param ---- {2} ПЕРЕЧЕНЬ ЦЕН
),
maxes as (
select
country_id,
customer_id,
attr,
row_number () over (partition by country_id, attr) as num
from (
select *
from amount
union all
select *
from rentals) as sub)
select country_id, customer_id, attr
from maxes
where num = 1
union all
select *
from last_rental
order by country_id, attr",
{city_param, // {0}
date_param, // {1}
price_param} // {2}
)