Power Query. Параметры в SQL-запросе 2

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

Описание

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

Если в вашем PQ-запросе содержится SQL-запрос с большим количеством параметров вам будет очень некомфортно работать, особенно, если в PQ-запросе помимо подключения к источнику будет еще несколько шагов.

Как организовать все таким образом, чтобы вам было удобно работать с этими запросами?

Решение

1. Объемный запрос можно перенести в отдельный запрос, который будет состоять из одного шага;
2. В 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}
)