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

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

Ссылки:

Описание

Вы получаете данные на лист 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}
)

Power Query разное

Номер урока	Урок	Описание
1	Power Query. Знакомство с Power Query	В этом уроке мы познакомимся в Power Query. Зачем нужен Power Query Как установить Power Query Как его Настроить Как изменить запрос
2	Power Query. Подключение XML	В этом уроке мы научимся подключаться к файлам в формате XML и импортировать эти данные в Excel.
3	Power Query. Уникальные значения двух столбцов	В этом уроке мы получим уникальные значения из двух столбцов таблицы.
4	Power Query. Импорт таблиц PDF	Импорт таблиц из файла PDF, импорт таблиц из множества PDF файлов с объединением в один датасет.
5	Power Query. Собрать разбитую строку	В этом практическом уроке мы научимся соединять разбитую строку. Этот пример взят из реальной практики одного из спонсоров канала.
6	Power Query. Пивот со счетом	В этом уроке мы создадим пивот, в котором будут пронумерованы столбцы.
7	Power Query. Минимальное значение в диапазоне	В этом уроке мы найдем минимальное значение в диапазоне строк.
8	Power Query. Нарастающий итог 2	В этом уроке мы изучим еще один способ сделать нарастающий итог в Power Query.
9	Power Query. Нарастающий итог 3	В этом уроке мы разберем еще один способ выполнить нарастающий итог в Power Query.
10	Power Query. Прирост населения Китая	В этом уроке мы сравним прирост населения Китая с приростом населения мира в целом за последние 200 лет.
11	Power Query. Повторяющиеся значения в строке	В этом уроке разберем как определить есть ли в строке повторения.
12	Power Query. Таблица навигации по функциям М	В этом уроке вы узнаете как создать таблицу навигации по всем функциям языка Power Query.
13	Power Query. Удалить запросы и модель данных из книги	Разберем как быстро удалить все запросы и модель данных из текущей книги.
14	Power Query. Открыть еще 1 Excel и еще 3 трюка	В этом видео я покажу как открыть еще 1 файл Excel, если у вас уже запущен Power Query.
15	Power Query. Подключиться к ZIP архиву	Пользовательская функция для подключения к zip файлу. Подключимся к txt файлу, который находится в zip архиве.
16	Power Query. Импорт Word	Импортируем таблицу из документа Word. Для спонсоров разберем импорт таблицы с объединенными ячейками.
17	Power Query. Фильтрация списком	В этом уроке мы хотим отфильтровать таблицу при помощи списка, например, хотим получить продажи определенных товаров.
18	Power Query. Пользовательская функция Switch	В этом уроке мы создадим пользовательскую функцию Switch.
19	Power Query. Информация о формате, Чтение zip	В этом уроке мы узнаем как получить информацию о формате ячеек при помощи Power Query.
20	Power Query. Импорт данных из gz	В этом уроке мы разберем как импортировать файл в формате gz.
21	Power Query. Удалить лишние пробелы, Text.Split	В этом уроке мы научимся удалять лишние пробелы в текстовом столбце таблицы.
22	Power Query. Параметры в SQL-запросе	Вы хотите, чтобы в ваш SQL-запрос подставлялось значение из параметра, источником которого является ячейка с листа Excel.
23	Power Query. Параметры в SQL-запросе 2	Ваш запрос очень большой и количество параметров в нем большое. Как организовать все так, чтобы было удобно работать.
24	Power Query. Добавить столбец в каждую таблицу табличного столбца	В этом уроке вы узнаете как трансформировать табличный столбец, например, вы сможете добавить столбец индекса внутрь каждой таблицы табличного столбца.
25	Power Query. Интервальный просмотр 1 (ВПР 1)	Объединить 2 таблицы с интервальным просмотром = 1.
26	Power Query. Относительный путь к файлу и папке	Если ваш источник находится в той же папке, что и отчет, то вы можете указать относительный путь. В таком случае подключение не будет ломаться, если вы запустите файл на другом компьютере.
27	Power Query. Нарастающий итог в каждой категории	Применим функцию нарастающего итога не ко всей таблице, а к определенному окну.
28	Power Query. ВПР без Merge или Join	Вам нужно подставить данные из столбца другой таблицы. Как это сделать без объединения таблиц.

предыдущая запись

Excel Функции. Функции баз данных Урок №2 (Таблица данных, ПРАВСИМВ, ТЕКСТ, БДСУММ, НАИБОЛЬШИЙ, ЕСЛИ)

следующая запись