Сообщение Re[2]: Оптимизация работы с sql от 06.01.2024 4:16
Изменено 06.01.2024 10:45 MaximVK
Re[2]: Оптимизация работы с sql
Здравствуйте, Sinclair, Вы писали:
S>Пока непонятно, в чём именно однообразие. Вот эти вот sumIf(xxx, isFinite(xxx)) as xxx? Ну, напишите свой микро-DSL, который будет metric0.SumF() раскрывать в sumIf(metric0, isFinite(metric0)) as metric0.
Такие простые сценарии можно упаковать в функции. Я, конечно, криво описал проблему (особенно это заметно по другим ответам). И, видимо, если смогу ее хорошо описать, то решение окажется на поверхности. Поэтому, правильнее было бы мой запрос сформулировать как "помогите мне понять мою проблему". У меня есть жгучее чувство избыточности процесса и собственной интеллектуальной беспомощности, чтобы эту проблему как-то системно осмыслить
Поэтому попробую сделать более структурированный дамп раздражений на примере более конкретного запроса к кликхаузу.
Сначала сетап:
Одна из моих ключевых ролей — это анализ данных. В этой роли 80% кодинга проходит в SQL консоли. Я пробовал различные repl среды: Python, Scala, R и даже Julia, но скорость работы начинает ощутимо падать (возможно, я плохо их готовлю). Кстати, Julia — прекрасна!
1) Написание финального запроса проходит через большое количество экспериментов "гипотеза — проверка". Это обычно довольно простые запросы и их значительно проще делать в SQL. Типовой рабочий файл: много маленьких запросов, которые можно быстро запустить выделив код и нажав Ctrl-Enter.
2) Вывод результата в удобную таблицу в тех же jetbrains-овских продуктах очень важен. Даже R Studio не дает такого удобства, хотя из всего, с чем я работал — это самый удобный REPL инструмент для работы с данными. Удобнее чем jupyter notebook.
3) SQL запрос часто вставляется в Tableau, чтобы быстро покрутить и "почувствовать" данные.
4) Intellisense очень важен. В процессе работы постоянно создается большое количество промежуточных и временных таблиц и view, схема постоянно меняется. Опять же JetBrain-овские продукты умеют быстро перечитывать схему, чтобы заработал автокомлит (на самом деле у меня есть претензии к тому, как это сделано и что можно улучшить).
5) Возможности различных data manipulations фреймворков (pandas и иже с ними) раскрываются лишь в случае, когда все данные можешь затянуть в память. Все опробованные мною lazy решения имеют кучу ограничений. Про спарк — я там в отдельном топике расписал.
В результате, что ни пробуешь, все равно скатываешься в sql консоль. Даже свой data quality framework я сейчас переделываю под написание тестов на sql.
Я попробовал написать более реалистичный пример SQL запроса:
Что раздражает: + возможные решения
1. Дублирование названия колонок в секции group by
— можно сделать макрос, чтобы автомкомплитил эту часть (в принципе, copilot это худо-бедно может)
2. Необходимость писать sum(metric) as metric для целой пачки метрик
— сделал простой vim-омский макрос, который делает это за меня
3. Типовые операции вида join rates и потом умножение на rate, чтобы перевести значение в eur. Или sumIf(...).
— сделал функцию to_eur(ccy, date, amount) (работает быстро, так как rates живет в памяти и обращение идет как в dictionary)
— сделал функцию которая возвращает 0, если значение не определено
4. Для любого запроса к таблице transactions есть набор основных полей который вытаскивается почти всегда. Т.е. если уж я написал select ... from transactions, то в 99% случаев я напишу select date, client_name, client_region, ... from transactions
— сделать view не подойдет, будет комбинаторный взрыв, так как дополнительные к основным полям всегда будут разными
— сделать макрос, который будет генерировать шаблон селекта с ключевыми полями из таблицы — вариант, но звучит как костыль
— натренировать бесплатный chatgpt 2 на истории запросов — вариант, но я как-то пока за него не готов взяться (хотя там ничего суперсложного вроде нет)
5. Тоже самое с метриками. В 90% случаев тащатся одни и те же. И агрегаты применяются одни и те же.
— см выше п. 4
6. Одни и те же join-ы
— да, можно сделать вью, которые инкапсулируют логику джойна. так оно и работает, но есть одно "но"
— точно такие же джойны встречаются во многих других местах, но к другим таблицам. Это специфика работы с денормализованными данными
7. Трансформации более высокого уровня. Это самый сложная тема. Мне нужно, наверное, подумать хорошо, чтобы браться это описать. Но попробую привести несколько примеров, чтобы пояснить идею:
7.1 переписать вышеприведенный пример в запрос, который вместо группировки по годам и кварталам будет выдавать ytd, prior_ytd, last_30_days для каждой метрики
7.2 для каждой строчки в агрегированном представлении добавь 10 строчек входящих в него top 10 by sales_amount записей. По факту нужно написать union all, скопировать оригинальный запрос, убрать агрегаты, убрать group by и в конце добавить order by sales_amount limit 10 by и опять скопипейстить все поля (кроме метрик)
7.3 удобный пивот запроса через трансформацию запроса, а не через использование конструкцию pivot/unpivot
Как это может выглядеть с точки зрения UX (фантазирую сходу в качестве иллюстрации):
1. Я остаюсь в sql консоли intellij или VS Code
2. Печатаю trades и нажимаю tab по аналогии с live templates
=> Появляется типовой запрос к таблице trades с основными атрибутами (возможно пробегаюсь табом и заполняю какие-то поля как в live templates тоже)
4. Выделю trades, нажимаю ctrl+j, пишу clients (j — join)
=> Появляется стандартный join на таблицу clients и стандартные атрибуты которые обычно берутся из clients
5. Выделяю весь запрос, нажимаю ctrl+g, ввожу year, quarter в промпте, нажимаю submit (g — group)
=> Получаю group by по всем измерениям, transaction_date изчезает и вместо него появляется toYear и toQuarter
6. Выделяю метрики sales_amount, total_shipping_cost нажимаю ctrl+a, выбираю to_eur (a — apply)
=> Появляется новые метрики рассчитанные в euro + join на табличку с eur_rates
7. Выделяю весь запрос, нажимаю ctrl+t, выбираю ytd (t-transform, template), ввожу дополнительные параметры, submit
=> Запрос превращается в новое представление с расчетом ytd, prior_ytd и так далее
Важно, что все трансформации происходят в sql консоли. Я всегда могу выделить запрос и его запустить и посмотреть результат.
Не уверен, что дочитаешь до конца, но если да, то снимаю шляпу.
Возможно, я изобретаю велосипед, но ничего похожего я пока не нашел.
Буду признателен за любой отзыв.
В любом случае — это было очень полезное для меня самого упражнение.
S>Пока непонятно, в чём именно однообразие. Вот эти вот sumIf(xxx, isFinite(xxx)) as xxx? Ну, напишите свой микро-DSL, который будет metric0.SumF() раскрывать в sumIf(metric0, isFinite(metric0)) as metric0.
Такие простые сценарии можно упаковать в функции. Я, конечно, криво описал проблему (особенно это заметно по другим ответам). И, видимо, если смогу ее хорошо описать, то решение окажется на поверхности. Поэтому, правильнее было бы мой запрос сформулировать как "помогите мне понять мою проблему". У меня есть жгучее чувство избыточности процесса и собственной интеллектуальной беспомощности, чтобы эту проблему как-то системно осмыслить
Поэтому попробую сделать более структурированный дамп раздражений на примере более конкретного запроса к кликхаузу.
Сначала сетап:
Одна из моих ключевых ролей — это анализ данных. В этой роли 80% кодинга проходит в SQL консоли. Я пробовал различные repl среды: Python, Scala, R и даже Julia, но скорость работы начинает ощутимо падать (возможно, я плохо их готовлю). Кстати, Julia — прекрасна!
1) Написание финального запроса проходит через большое количество экспериментов "гипотеза — проверка". Это обычно довольно простые запросы и их значительно проще делать в SQL. Типовой рабочий файл: много маленьких запросов, которые можно быстро запустить выделив код и нажав Ctrl-Enter.
2) Вывод результата в удобную таблицу в тех же jetbrains-овских продуктах очень важен. Даже R Studio не дает такого удобства, хотя из всего, с чем я работал — это самый удобный REPL инструмент для работы с данными. Удобнее чем jupyter notebook.
3) SQL запрос часто вставляется в Tableau, чтобы быстро покрутить и "почувствовать" данные.
4) Intellisense очень важен. В процессе работы постоянно создается большое количество промежуточных и временных таблиц и view, схема постоянно меняется. Опять же JetBrain-овские продукты умеют быстро перечитывать схему, чтобы заработал автокомлит (на самом деле у меня есть претензии к тому, как это сделано и что можно улучшить).
5) Возможности различных data manipulations фреймворков (pandas и иже с ними) раскрываются лишь в случае, когда все данные можешь затянуть в память. Все опробованные мною lazy решения имеют кучу ограничений. Про спарк — я там в отдельном топике расписал.
В результате, что ни пробуешь, все равно скатываешься в sql консоль. Даже свой data quality framework я сейчас переделываю под написание тестов на sql.
Я попробовал написать более реалистичный пример SQL запроса:
select
toYear(transaction_date) as order_year,
toQuarter(transaction_date) as order_quarter,
--client dimensions
client_name,
rg_c.region as client_region,
-- product dimensions
product_name,
p.product_type,
-- sales departments
rg_s.country as sales_country,
rg_s.region as sales_region,
-- metrics
sum(product_qty) as total_product_qty,
sum(product_price * product_qty) as total_sales_amount,
sumIf(shipping_cost, isFinite(shipping_cost)) as total_shipping_cost,
-- metrics avg
sum(product_price)/sum(product_qty) as avg_product_price,
sumId(shipping_cost, isFinite(shipping_cost))/sumIf(product_qty, isFinite(shipping_cost)) as avg_shipping_cost,
-- eur metrics
sum(eur_rate * product_price) as total_sales_amount_eur,
sumIf(eur_rate * shipping_cost, isFinite(shipping_cost)) as total_shipping_cost_eur,
-- eur metrics avg
sum(eur_rate * product_price)/sum(product_qty) as avg_product_price_eur,
sumIf(eur_rate * shipping_cost, isFinite(shipping_cost))/sumIf(product_qty, isFinite(shipping_cost)) as avg_shipping_cost_eur,
-- quality checks
if(isFinite(rt.eur_rate), 'OK', 'EUR RATE IS NOT AVAILABLE') as eur_rate_check
from transactions t
left join regions rg_c on t.client_country_code == rg_c.country_code
left join regions rg_s on t.sales_country_code == rg_s.country_code
left join product_catalog pc on t.product_id == pc.product_id
left join rates rt on t.transaction_ccy = rt.currency and t.transaction_date == rt.date
where order_year >= 2020
and transaction_status != 'Cancelled'
and product_qty > 0
group by
order_year,
order_quarter,
client_name,
client_region,
product_name,
product_type,
sales_country,
sales_region;Что раздражает: + возможные решения
1. Дублирование названия колонок в секции group by
— можно сделать макрос, чтобы автомкомплитил эту часть (в принципе, copilot это худо-бедно может)
2. Необходимость писать sum(metric) as metric для целой пачки метрик
— сделал простой vim-омский макрос, который делает это за меня
3. Типовые операции вида join rates и потом умножение на rate, чтобы перевести значение в eur. Или sumIf(...).
— сделал функцию to_eur(ccy, date, amount) (работает быстро, так как rates живет в памяти и обращение идет как в dictionary)
— сделал функцию которая возвращает 0, если значение не определено
4. Для любого запроса к таблице transactions есть набор основных полей который вытаскивается почти всегда. Т.е. если уж я написал select ... from transactions, то в 99% случаев я напишу select date, client_name, client_region, ... from transactions
— сделать view не подойдет, будет комбинаторный взрыв, так как дополнительные к основным полям всегда будут разными
— сделать макрос, который будет генерировать шаблон селекта с ключевыми полями из таблицы — вариант, но звучит как костыль
— натренировать бесплатный chatgpt 2 на истории запросов — вариант, но я как-то пока за него не готов взяться (хотя там ничего суперсложного вроде нет)
5. Тоже самое с метриками. В 90% случаев тащатся одни и те же. И агрегаты применяются одни и те же.
— см выше п. 4
6. Одни и те же join-ы
— да, можно сделать вью, которые инкапсулируют логику джойна. так оно и работает, но есть одно "но"
— точно такие же джойны встречаются во многих других местах, но к другим таблицам. Это специфика работы с денормализованными данными
7. Трансформации более высокого уровня. Это самый сложная тема. Мне нужно, наверное, подумать хорошо, чтобы браться это описать. Но попробую привести несколько примеров, чтобы пояснить идею:
7.1 переписать вышеприведенный пример в запрос, который вместо группировки по годам и кварталам будет выдавать ytd, prior_ytd, last_30_days для каждой метрики
7.2 для каждой строчки в агрегированном представлении добавь 10 строчек входящих в него top 10 by sales_amount записей. По факту нужно написать union all, скопировать оригинальный запрос, убрать агрегаты, убрать group by и в конце добавить order by sales_amount limit 10 by и опять скопипейстить все поля (кроме метрик)
7.3 удобный пивот запроса через трансформацию запроса, а не через использование конструкцию pivot/unpivot
Как это может выглядеть с точки зрения UX (фантазирую сходу в качестве иллюстрации):
1. Я остаюсь в sql консоли intellij или VS Code
2. Печатаю trades и нажимаю tab по аналогии с live templates
=> Появляется типовой запрос к таблице trades с основными атрибутами (возможно пробегаюсь табом и заполняю какие-то поля как в live templates тоже)
4. Выделю trades, нажимаю ctrl+j, пишу clients (j — join)
=> Появляется стандартный join на таблицу clients и стандартные атрибуты которые обычно берутся из clients
5. Выделяю весь запрос, нажимаю ctrl+g, ввожу year, quarter в промпте, нажимаю submit (g — group)
=> Получаю group by по всем измерениям, transaction_date изчезает и вместо него появляется toYear и toQuarter
6. Выделяю метрики sales_amount, total_shipping_cost нажимаю ctrl+a, выбираю to_eur (a — apply)
=> Появляется новые метрики рассчитанные в euro + join на табличку с eur_rates
7. Выделяю весь запрос, нажимаю ctrl+t, выбираю ytd (t-transform, template), ввожу дополнительные параметры, submit
=> Запрос превращается в новое представление с расчетом ytd, prior_ytd и так далее
Важно, что все трансформации происходят в sql консоли. Я всегда могу выделить запрос и его запустить и посмотреть результат.
Не уверен, что дочитаешь до конца, но если да, то снимаю шляпу.
Возможно, я изобретаю велосипед, но ничего похожего я пока не нашел.
Буду признателен за любой отзыв.
В любом случае — это было очень полезное для меня самого упражнение.
Re[2]: Оптимизация работы с sql
Здравствуйте, Sinclair, Вы писали:
S>Пока непонятно, в чём именно однообразие. Вот эти вот sumIf(xxx, isFinite(xxx)) as xxx? Ну, напишите свой микро-DSL, который будет metric0.SumF() раскрывать в sumIf(metric0, isFinite(metric0)) as metric0.
Такие простые сценарии можно упаковать в функции. Я, конечно, криво описал проблему (особенно это заметно по другим ответам). И, видимо, если смогу ее хорошо описать, то решение окажется на поверхности. Поэтому, правильнее было бы мой запрос сформулировать как "помогите мне понять мою проблему". У меня есть жгучее чувство избыточности процесса и собственной интеллектуальной беспомощности, чтобы эту проблему как-то системно осмыслить
Поэтому попробую сделать более структурированный дамп раздражений на примере более конкретного запроса к кликхаузу.
Сначала сетап:
Одна из моих ключевых ролей — это анализ данных. В этой роли 80% кодинга проходит в SQL консоли. Я пробовал различные repl среды: Python, Scala, R и даже Julia, но скорость работы начинает ощутимо падать (возможно, я плохо их готовлю). Кстати, Julia — прекрасна!
1) Написание финального запроса проходит через большое количество экспериментов "гипотеза — проверка". Это обычно довольно простые запросы и их значительно проще делать в SQL. Типовой рабочий файл: много маленьких запросов, которые можно быстро запустить выделив код и нажав Ctrl-Enter.
2) Вывод результата в удобную таблицу в тех же jetbrains-овских продуктах очень важен. Даже R Studio не дает такого удобства, хотя из всего, с чем я работал — это самый удобный REPL инструмент для работы с данными. Удобнее чем jupyter notebook.
3) SQL запрос часто вставляется в Tableau, чтобы быстро покрутить и "почувствовать" данные.
4) Intellisense очень важен. В процессе работы постоянно создается большое количество промежуточных и временных таблиц и view, схема постоянно меняется. Опять же JetBrain-овские продукты умеют быстро перечитывать схему, чтобы заработал автокомлит (на самом деле у меня есть претензии к тому, как это сделано и что можно улучшить).
5) Возможности различных data manipulations фреймворков (pandas и иже с ними) раскрываются лишь в случае, когда все данные можешь затянуть в память. Все опробованные мною lazy решения имеют кучу ограничений. Про спарк — я там в отдельном топике расписал.
В результате, что ни пробуешь, все равно скатываешься в sql консоль. Даже свой data quality framework я сейчас переделываю под написание тестов на sql.
Я попробовал написать более реалистичный пример SQL запроса:
Что раздражает: + возможные решения
1. Дублирование названия колонок в секции group by
— можно сделать макрос, чтобы автомкомплитил эту часть (в принципе, copilot это худо-бедно может)
2. Необходимость писать sum(metric) as metric для целой пачки метрик
— сделал простой vim-омский макрос, который делает это за меня
3. Типовые операции вида join rates и потом умножение на rate, чтобы перевести значение в eur. Или sumIf(...).
— сделал функцию to_eur(ccy, date, amount) (работает быстро, так как rates живет в памяти и обращение идет как в dictionary)
— сделал функцию которая возвращает 0, если значение не определено
4. Для любого запроса к таблице transactions есть набор основных полей который вытаскивается почти всегда. Т.е. если уж я написал select ... from transactions, то в 99% случаев я напишу select date, client_name, client_region, ... from transactions
— сделать view не подойдет, будет комбинаторный взрыв, так как дополнительные к основным полям всегда будут разными
— сделать макрос, который будет генерировать шаблон селекта с ключевыми полями из таблицы — вариант, но звучит как костыль
— натренировать бесплатный chatgpt 2 на истории запросов — вариант, но я как-то пока за него не готов взяться (хотя там ничего суперсложного вроде нет)
5. Тоже самое с метриками. В 90% случаев тащатся одни и те же. И агрегаты применяются одни и те же.
— см выше п. 4
6. Одни и те же join-ы
— да, можно сделать вью, которые инкапсулируют логику джойна. так оно и работает, но есть одно "но"
— точно такие же джойны встречаются во многих других местах, но к другим таблицам. Это специфика работы с денормализованными данными
7. Трансформации более высокого уровня. Это самая сложная тема. Мне нужно, наверное, подумать хорошо, чтобы браться это описать. Но попробую привести несколько примеров, чтобы пояснить идею:
7.1 переписать вышеприведенный пример в запрос, который вместо группировки по годам и кварталам будет выдавать ytd, prior_ytd, last_30_days для каждой метрики
7.2 для каждой строчки в агрегированном представлении добавить 10 строчек входящих в него top 10 by sales_amount записей. По факту нужно написать union all, скопировать оригинальный запрос, убрать агрегаты, убрать group by и в конце добавить order by sales_amount limit 10 by и опять скопипейстить все поля (кроме метрик)
7.3 удобный пивот запроса через трансформацию запроса, а не через использование конструкцию pivot/unpivot
Как это может выглядеть с точки зрения UX (фантазирую сходу в качестве иллюстрации):
1. Я остаюсь в sql консоли intellij или VS Code
2. Печатаю trades и нажимаю tab по аналогии с live templates
=> Появляется типовой запрос к таблице trades с основными атрибутами (возможно пробегаюсь табом и заполняю какие-то поля как в live templates тоже)
4. Выделю trades, нажимаю ctrl+j, пишу clients (j — join)
=> Появляется стандартный join на таблицу clients и стандартные атрибуты которые обычно берутся из clients
5. Выделяю весь запрос, нажимаю ctrl+g, ввожу year, quarter в промпте, нажимаю submit (g — group)
=> Получаю group by по всем измерениям, transaction_date изчезает и вместо него появляется toYear и toQuarter
6. Выделяю метрики sales_amount, total_shipping_cost нажимаю ctrl+a, выбираю to_eur (a — apply)
=> Появляется новые метрики рассчитанные в euro + join на табличку с eur_rates
7. Выделяю весь запрос, нажимаю ctrl+t, выбираю ytd (t-transform, template), ввожу дополнительные параметры, submit
=> Запрос превращается в новое представление с расчетом ytd, prior_ytd и так далее
Важно, что все трансформации происходят в sql консоли. Я всегда могу выделить запрос и его запустить и посмотреть результат.
Не уверен, что дочитаешь до конца, но если да, то снимаю шляпу.
Возможно, я изобретаю велосипед, но ничего похожего я пока не нашел.
Буду признателен за любой отзыв.
В любом случае — это было очень полезное для меня самого упражнение.
S>Пока непонятно, в чём именно однообразие. Вот эти вот sumIf(xxx, isFinite(xxx)) as xxx? Ну, напишите свой микро-DSL, который будет metric0.SumF() раскрывать в sumIf(metric0, isFinite(metric0)) as metric0.
Такие простые сценарии можно упаковать в функции. Я, конечно, криво описал проблему (особенно это заметно по другим ответам). И, видимо, если смогу ее хорошо описать, то решение окажется на поверхности. Поэтому, правильнее было бы мой запрос сформулировать как "помогите мне понять мою проблему". У меня есть жгучее чувство избыточности процесса и собственной интеллектуальной беспомощности, чтобы эту проблему как-то системно осмыслить
Поэтому попробую сделать более структурированный дамп раздражений на примере более конкретного запроса к кликхаузу.
Сначала сетап:
Одна из моих ключевых ролей — это анализ данных. В этой роли 80% кодинга проходит в SQL консоли. Я пробовал различные repl среды: Python, Scala, R и даже Julia, но скорость работы начинает ощутимо падать (возможно, я плохо их готовлю). Кстати, Julia — прекрасна!
1) Написание финального запроса проходит через большое количество экспериментов "гипотеза — проверка". Это обычно довольно простые запросы и их значительно проще делать в SQL. Типовой рабочий файл: много маленьких запросов, которые можно быстро запустить выделив код и нажав Ctrl-Enter.
2) Вывод результата в удобную таблицу в тех же jetbrains-овских продуктах очень важен. Даже R Studio не дает такого удобства, хотя из всего, с чем я работал — это самый удобный REPL инструмент для работы с данными. Удобнее чем jupyter notebook.
3) SQL запрос часто вставляется в Tableau, чтобы быстро покрутить и "почувствовать" данные.
4) Intellisense очень важен. В процессе работы постоянно создается большое количество промежуточных и временных таблиц и view, схема постоянно меняется. Опять же JetBrain-овские продукты умеют быстро перечитывать схему, чтобы заработал автокомлит (на самом деле у меня есть претензии к тому, как это сделано и что можно улучшить).
5) Возможности различных data manipulations фреймворков (pandas и иже с ними) раскрываются лишь в случае, когда все данные можешь затянуть в память. Все опробованные мною lazy решения имеют кучу ограничений. Про спарк — я там в отдельном топике расписал.
В результате, что ни пробуешь, все равно скатываешься в sql консоль. Даже свой data quality framework я сейчас переделываю под написание тестов на sql.
Я попробовал написать более реалистичный пример SQL запроса:
select
toYear(transaction_date) as order_year,
toQuarter(transaction_date) as order_quarter,
--client dimensions
client_name,
rg_c.region as client_region,
-- product dimensions
product_name,
p.product_type,
-- sales departments
rg_s.country as sales_country,
rg_s.region as sales_region,
-- metrics
sum(product_qty) as total_product_qty,
sum(product_price * product_qty) as total_sales_amount,
sumIf(shipping_cost, isFinite(shipping_cost)) as total_shipping_cost,
-- metrics avg
sum(product_price)/sum(product_qty) as avg_product_price,
sumId(shipping_cost, isFinite(shipping_cost))/sumIf(product_qty, isFinite(shipping_cost)) as avg_shipping_cost,
-- eur metrics
sum(eur_rate * product_price) as total_sales_amount_eur,
sumIf(eur_rate * shipping_cost, isFinite(shipping_cost)) as total_shipping_cost_eur,
-- eur metrics avg
sum(eur_rate * product_price)/sum(product_qty) as avg_product_price_eur,
sumIf(eur_rate * shipping_cost, isFinite(shipping_cost))/sumIf(product_qty, isFinite(shipping_cost)) as avg_shipping_cost_eur,
-- quality checks
if(isFinite(rt.eur_rate), 'OK', 'EUR RATE IS NOT AVAILABLE') as eur_rate_check
from transactions t
left join regions rg_c on t.client_country_code == rg_c.country_code
left join regions rg_s on t.sales_country_code == rg_s.country_code
left join product_catalog pc on t.product_id == pc.product_id
left join rates rt on t.transaction_ccy = rt.currency and t.transaction_date == rt.date
where order_year >= 2020
and transaction_status != 'Cancelled'
and product_qty > 0
group by
order_year,
order_quarter,
client_name,
client_region,
product_name,
product_type,
sales_country,
sales_region;Что раздражает: + возможные решения
1. Дублирование названия колонок в секции group by
— можно сделать макрос, чтобы автомкомплитил эту часть (в принципе, copilot это худо-бедно может)
2. Необходимость писать sum(metric) as metric для целой пачки метрик
— сделал простой vim-омский макрос, который делает это за меня
3. Типовые операции вида join rates и потом умножение на rate, чтобы перевести значение в eur. Или sumIf(...).
— сделал функцию to_eur(ccy, date, amount) (работает быстро, так как rates живет в памяти и обращение идет как в dictionary)
— сделал функцию которая возвращает 0, если значение не определено
4. Для любого запроса к таблице transactions есть набор основных полей который вытаскивается почти всегда. Т.е. если уж я написал select ... from transactions, то в 99% случаев я напишу select date, client_name, client_region, ... from transactions
— сделать view не подойдет, будет комбинаторный взрыв, так как дополнительные к основным полям всегда будут разными
— сделать макрос, который будет генерировать шаблон селекта с ключевыми полями из таблицы — вариант, но звучит как костыль
— натренировать бесплатный chatgpt 2 на истории запросов — вариант, но я как-то пока за него не готов взяться (хотя там ничего суперсложного вроде нет)
5. Тоже самое с метриками. В 90% случаев тащатся одни и те же. И агрегаты применяются одни и те же.
— см выше п. 4
6. Одни и те же join-ы
— да, можно сделать вью, которые инкапсулируют логику джойна. так оно и работает, но есть одно "но"
— точно такие же джойны встречаются во многих других местах, но к другим таблицам. Это специфика работы с денормализованными данными
7. Трансформации более высокого уровня. Это самая сложная тема. Мне нужно, наверное, подумать хорошо, чтобы браться это описать. Но попробую привести несколько примеров, чтобы пояснить идею:
7.1 переписать вышеприведенный пример в запрос, который вместо группировки по годам и кварталам будет выдавать ytd, prior_ytd, last_30_days для каждой метрики
7.2 для каждой строчки в агрегированном представлении добавить 10 строчек входящих в него top 10 by sales_amount записей. По факту нужно написать union all, скопировать оригинальный запрос, убрать агрегаты, убрать group by и в конце добавить order by sales_amount limit 10 by и опять скопипейстить все поля (кроме метрик)
7.3 удобный пивот запроса через трансформацию запроса, а не через использование конструкцию pivot/unpivot
Как это может выглядеть с точки зрения UX (фантазирую сходу в качестве иллюстрации):
1. Я остаюсь в sql консоли intellij или VS Code
2. Печатаю trades и нажимаю tab по аналогии с live templates
=> Появляется типовой запрос к таблице trades с основными атрибутами (возможно пробегаюсь табом и заполняю какие-то поля как в live templates тоже)
4. Выделю trades, нажимаю ctrl+j, пишу clients (j — join)
=> Появляется стандартный join на таблицу clients и стандартные атрибуты которые обычно берутся из clients
5. Выделяю весь запрос, нажимаю ctrl+g, ввожу year, quarter в промпте, нажимаю submit (g — group)
=> Получаю group by по всем измерениям, transaction_date изчезает и вместо него появляется toYear и toQuarter
6. Выделяю метрики sales_amount, total_shipping_cost нажимаю ctrl+a, выбираю to_eur (a — apply)
=> Появляется новые метрики рассчитанные в euro + join на табличку с eur_rates
7. Выделяю весь запрос, нажимаю ctrl+t, выбираю ytd (t-transform, template), ввожу дополнительные параметры, submit
=> Запрос превращается в новое представление с расчетом ytd, prior_ytd и так далее
Важно, что все трансформации происходят в sql консоли. Я всегда могу выделить запрос и его запустить и посмотреть результат.
Не уверен, что дочитаешь до конца, но если да, то снимаю шляпу.
Возможно, я изобретаю велосипед, но ничего похожего я пока не нашел.
Буду признателен за любой отзыв.
В любом случае — это было очень полезное для меня самого упражнение.