Но практическая применимость результатов под вопросом - цены будут непонятно откуда, и актуальность информации так же неизвестная, все зависит от тех данных, на которых обучена модель. Чтобы собрать предложенный моделью конфиг в точности, нужно будет проделать огромную работу по поиску, или же что-то осознанно менять.
Для новичка будет полезнее зайти в конфигуратор в каком-нибудь тематическом магазине, для начала поискать что-то готовое из пользовательских конфигураций, почитать отзывы и советы, а потом и что-то поменять под себя. Заодно там же это все можно будет и заказать, в том числе со сборкой. Не сравнить с советами неизвестной свежести от нейросети.
Разбирающийся же сам себе все подберет, найдет и соберет, ему советы GPT не нужны...
В целом я не занимался оптимизацией, т.к. эффект от нее будет заметен на объемных иерархиях (1 млн строк и более), а необходимость их визуализации довольно сомнительна. Но почему бы не оптимизировать то, что может быть оптимизировано?
Отвечу по пунктам:
В блоке Levels UNION, который даёт доп. сортировку и DISTINCT, кажется, что можно смело заменить на UNION ALL, потому как по крайней мере колонка node_level точно ни на каком уровне иерархии не совпадет.
Принято, и в Tree тоже! node_id должен быть уникален в иерархии, поэтому DISTINCT здесь никогда не даст эффекта. Интересно, что в случае PostgreSQL план запроса одинаковый для ALL и DISTINCT, а в MySQL для ALL эффект виден - на плане запроса, выигрыш в быстродействии заметить я не смог на иерархии в 85000 строк
Фрагмент concat(coalesce(concat(prev.parents, '-'), ''), cast(Mapping.parent_node_id as varchar)) Можно бы упростить: CONCAT игнорирует NULL и COALESCE выглядит просто лишним.
Нет, здесь вы не совсем правы. В моем варианте в PostgreSQL поле parents у узлов первого уровня выглядит как '-0' вместо '0' в MySQL, что не критично для партиционирования в окне по значению в этом поле. Но в MySQL CONCAT()не игнорирует NULL, и в вашем варианте все значения в parents будут NULL. Моя задача была написать максимально универсальный код, поэтому оставлю свой вариант.
В блоке Branches два схожих фрагмента ...
Полностью соглашусь, переписал изначально использовавшийся мною тернарный IF() не самым удачным образом
Множественные джойны на RootNodes кажется, что не нужны - у всех корневых узлов nest_level = 0 и можно пользоваться этим знанием. Избавиться от этого джойна возможно в Branches и в Tree.
Да, здесь можно пожертвовать единообразием (определения начальных узлов соединением с RootNodes) ради экономии 2 строк кода, плюс в MySQL план запроса сокращается на 2 строки.
В реальных условиях это может быть не столь однозначно - например, если в таблице создан индекс по полю node_id, соединение с RootNodes по нему будет происходить быстро, в отличие от применения условия node_level = 0 - это вычисляемое поле, все записи будут сканироваться.
У меня изначально была идея сохранять уровень node_level оригинальным, т.е. если в качестве начального указан узел с уровнем 4, то именно это значение и будет у него в поле node_level, но я отказался от этой идеи, так как в RootNodes может быть передано несколько начальных узлов из разных уровней, но визуализированы они будут как одинаковые по уровню - поэтому принимаю предложение.
Вместе с этим можно будет выкинуть два CTE: Mapping и RootNodes.
Здесь не могу согласиться. И одно, и другое служит для параметризации запроса, по сути это интерфейсные CTE, их удобно располагать в начале, чтобы не "трогать" смысловую часть. Mapping позволяет использовать запрос без каких-либо изменений с любой структурой данных - это более дружественная для новичков реализация, а опытные в любом случае адаптируют запрос к своим условиям.
И очень желательно во всех запросах колонкам алиас источника расставить: СУБД разберётся, где чьё, а человеку читать тяжело. Большую часть, если не всё, из того, что происходит в Branches и Tree, как будто можно сделать ещё в Levels и повторно большую рекурсию не гонять.
Каждый CTE использует только предыдущий, указанный в FROM - как сформулировано в задаче. При таком варианте алиасы избыточны кмк.
Идею "все сделать в Levels" предоставлю возможность реализовать кому-то другому, мне по душе подход с разделением большого на меньшее и решение "слона по частям" ))
В целом интересно проверить на большой иерархии, от 1 млн. строк. Видимо, придется продолжить...
Собственно, результат оптимизации:
PostgreSQL и SQLite
with recursive
Mapping as (
select
id as node_id,
parent_id as parent_node_id,
concat(name, ' (', bytes, ')') as node_name
from ZipArchive
),
RootNodes as (
select node_id as root_node_id
from Mapping
where -- Exactly one line below should be uncommented
parent_node_id is null -- Uncomment to build from root(s)
-- node_id in (27233) -- Uncomment to add node_id(s) into the brackets
),
Levels as (
select
node_id,
parent_node_id,
node_name,
cast(parent_node_id as varchar) as parents,
cast(node_name as varchar) as full_path,
0 as node_level
from
Mapping
inner join RootNodes on node_id = root_node_id
union all
select
Mapping.node_id,
Mapping.parent_node_id,
Mapping.node_name,
concat(coalesce(concat(prev.parents, '-'), ''), cast(Mapping.parent_node_id as varchar)),
concat_ws(' ', prev.full_path, Mapping.node_name),
prev.node_level + 1
from
Levels as prev
inner join Mapping on Mapping.parent_node_id = prev.node_id
),
Branches as (
select
node_id,
parent_node_id,
node_name,
parents,
full_path,
node_level,
case
when node_level = 0 then ''
when node_id = last_value(node_id) over WindowByParents then '└── '
else '├── '
end as node_branch,
case
when node_level = 0 then ''
when node_id = last_value(node_id) over WindowByParents then ' '
else '│ '
end as branch_through
from Levels
window WindowByParents as (
partition by parents
order by node_name
rows between current row and unbounded following
)
order by full_path
),
Tree as (
select
node_id,
parent_node_id,
node_name,
parents,
full_path,
node_level,
node_branch,
cast(branch_through as varchar) as all_through
from Branches
where node_level = 0
union all
select
Branches.node_id,
Branches.parent_node_id,
Branches.node_name,
Branches.parents,
Branches.full_path,
Branches.node_level,
Branches.node_branch,
concat(prev.all_through, Branches.branch_through)
from
Tree as prev
inner join Branches on Branches.parent_node_id = prev.node_id
),
FineTree as (
select
tr.node_id,
tr.parent_node_id,
tr.node_name,
tr.parents,
tr.full_path,
tr.node_level,
concat(coalesce(parent.all_through, ''), tr.node_branch, tr.node_name) as fine_tree
from
Tree as tr
left join Tree as parent on
parent.node_id = tr.parent_node_id
order by tr.full_path
)
select fine_tree, node_id from FineTree
;
MySQL и SQLite
with recursive
Mapping as (
select
id as node_id,
parent_id as parent_node_id,
concat(name, ' (', bytes, ')') as node_name
from ZipArchive
),
RootNodes as (
select node_id as root_node_id
from Mapping
where -- Keep uncommented exactly one line below
parent_node_id is null -- Uncomment to build from root(s)
-- node_id in (27233) -- Uncomment to add node_id(s) into the brackets
),
Levels as (
select
node_id,
parent_node_id,
node_name,
cast(parent_node_id as char(2000)) as parents,
cast(node_name as char(2000)) as full_path,
0 as node_level
from
Mapping
inner join RootNodes on node_id = root_node_id
union all
select
Mapping.node_id,
Mapping.parent_node_id,
Mapping.node_name,
concat(coalesce(concat(prev.parents, '-'), ''), cast(Mapping.parent_node_id as char(2000))),
concat_ws(' ', prev.full_path, Mapping.node_name),
prev.node_level + 1
from
Levels as prev
inner join Mapping on Mapping.parent_node_id = prev.node_id
),
Branches as (
select
node_id,
parent_node_id,
node_name,
parents,
full_path,
node_level,
case
when node_level = 0 then ''
when node_id = last_value(node_id) over WindowByParents then '└── '
else '├── '
end as node_branch,
case
when node_level = 0 then ''
when node_id = last_value(node_id) over WindowByParents then ' '
else '│ '
end as branch_through
from Levels
window WindowByParents as (
partition by parents
order by node_name
rows between current row and unbounded following
)
order by full_path
),
Tree as (
select
node_id,
parent_node_id,
node_name,
parents,
full_path,
node_level,
node_branch,
cast(branch_through as char(2000)) as all_through
from Branches
where node_level = 0
union all
select
Branches.node_id,
Branches.parent_node_id,
Branches.node_name,
Branches.parents,
Branches.full_path,
Branches.node_level,
Branches.node_branch,
concat(prev.all_through, Branches.branch_through)
from
Tree as prev
inner join Branches on Branches.parent_node_id = prev.node_id
),
FineTree as (
select
tr.node_id,
tr.parent_node_id,
tr.node_name,
tr.parents,
tr.full_path,
tr.node_level,
concat(coalesce(parent.all_through, ''), tr.node_branch, tr.node_name) as fine_tree
from
Tree as tr
left join Tree as parent on
parent.node_id = tr.parent_node_id
order by tr.full_path
)
select fine_tree, node_id from FineTree
;
SQLite всеяден, оба варианта работают в нем одинаково
Похоже, я несколько ввел всех в заблуждение - последний вариант запроса для PostgreSQL корректно исполняется в SQLite, но не в MySQL, что-то я вчера в ночи попутал. Исправлю текст в статье.
Хорошая идея, в запросе выше использовано аж 6 разных CTE ))
Надеюсь, разобраться с ним сможет любой, освоивший рекурсивные CTE и основы оконных функций SQL. А неразобравшийся сможет его просто использовать, настроив Mapping на свой источник.
С сортировкой разобрался - на https://sqlize.online/s/az так отрабатывает сортировка по full_path, собранному через табуляцию. Проверял на нескольких платформах, и на своем домашнем MySQL 8.2, такой проблемы не встречал.
Заменил в статье CHAR(9) на пробел CHAR(32), так будет работать с более высокой вероятностью, проверьте
DDR5 у "за 100 тыс. руб" не модно ))
Интересный эксперимент.
Но практическая применимость результатов под вопросом - цены будут непонятно откуда, и актуальность информации так же неизвестная, все зависит от тех данных, на которых обучена модель. Чтобы собрать предложенный моделью конфиг в точности, нужно будет проделать огромную работу по поиску, или же что-то осознанно менять.
Для новичка будет полезнее зайти в конфигуратор в каком-нибудь тематическом магазине, для начала поискать что-то готовое из пользовательских конфигураций, почитать отзывы и советы, а потом и что-то поменять под себя. Заодно там же это все можно будет и заказать, в том числе со сборкой. Не сравнить с советами неизвестной свежести от нейросети.
Разбирающийся же сам себе все подберет, найдет и соберет, ему советы GPT не нужны...
...
Спасибо, по большей части замечания по делу!
В целом я не занимался оптимизацией, т.к. эффект от нее будет заметен на объемных иерархиях (1 млн строк и более), а необходимость их визуализации довольно сомнительна. Но почему бы не оптимизировать то, что может быть оптимизировано?
Отвечу по пунктам:
Принято, и в Tree тоже!
node_id
должен быть уникален в иерархии, поэтомуDISTINCT
здесь никогда не даст эффекта.Интересно, что в случае PostgreSQL план запроса одинаковый для
ALL
иDISTINCT
, а в MySQL дляALL
эффект виден - на плане запроса, выигрыш в быстродействии заметить я не смог на иерархии в 85000 строкНет, здесь вы не совсем правы. В моем варианте в PostgreSQL поле
parents
у узлов первого уровня выглядит как'-0'
вместо'0'
в MySQL, что не критично для партиционирования в окне по значению в этом поле. Но в MySQLCONCAT()
не игнорируетNULL
, и в вашем варианте все значения вparents
будутNULL
. Моя задача была написать максимально универсальный код, поэтому оставлю свой вариант.Полностью соглашусь, переписал изначально использовавшийся мною тернарный
IF()
не самым удачным образомДа, здесь можно пожертвовать единообразием (определения начальных узлов соединением с
RootNodes
) ради экономии 2 строк кода, плюс в MySQL план запроса сокращается на 2 строки.В реальных условиях это может быть не столь однозначно - например, если в таблице создан индекс по полю
node_id
, соединение сRootNodes
по нему будет происходить быстро, в отличие от применения условияnode_level = 0
- это вычисляемое поле, все записи будут сканироваться.У меня изначально была идея сохранять уровень
node_level
оригинальным, т.е. если в качестве начального указан узел с уровнем 4, то именно это значение и будет у него в полеnode_level
, но я отказался от этой идеи, так как в RootNodes может быть передано несколько начальных узлов из разных уровней, но визуализированы они будут как одинаковые по уровню - поэтому принимаю предложение.Здесь не могу согласиться. И одно, и другое служит для параметризации запроса, по сути это интерфейсные CTE, их удобно располагать в начале, чтобы не "трогать" смысловую часть.
Mapping
позволяет использовать запрос без каких-либо изменений с любой структурой данных - это более дружественная для новичков реализация, а опытные в любом случае адаптируют запрос к своим условиям.Каждый CTE использует только предыдущий, указанный в FROM - как сформулировано в задаче. При таком варианте алиасы избыточны кмк.
Идею "все сделать в
Levels
" предоставлю возможность реализовать кому-то другому, мне по душе подход с разделением большого на меньшее и решение "слона по частям" ))В целом интересно проверить на большой иерархии, от 1 млн. строк. Видимо, придется продолжить...
Собственно, результат оптимизации:
PostgreSQL и SQLite
MySQL и SQLite
SQLite всеяден, оба варианта работают в нем одинаково
Похоже, я несколько ввел всех в заблуждение - последний вариант запроса для PostgreSQL корректно исполняется в SQLite, но не в MySQL, что-то я вчера в ночи попутал. Исправлю текст в статье.
Сортировка производится по full_path, для этих узлов поле содержит:
IdealGraphVisualizer (DIR) branding (DIR) src (DIR) main (DIR) nbm (DIR)
и
IdealGraphVisualizer (DIR) branding (DIR) src (DIR) main (DIR) nbm-branding (DIR)
У меня стабильно по-разному выводятся, но я подписи перепутал, сейчас исправлю ))
Хорошая идея, в запросе выше использовано аж 6 разных CTE ))
Надеюсь, разобраться с ним сможет любой, освоивший рекурсивные CTE и основы оконных функций SQL. А неразобравшийся сможет его просто использовать, настроив Mapping на свой источник.
Спасибо за отзыв!
С сортировкой разобрался - на https://sqlize.online/s/az так отрабатывает сортировка по
full_path
, собранному через табуляцию. Проверял на нескольких платформах, и на своем домашнем MySQL 8.2, такой проблемы не встречал.Заменил в статье
CHAR(9)
на пробелCHAR(32)
, так будет работать с более высокой вероятностью, проверьтеНу это вообще на решение не тенет. А если у вас "черный ящик" с проверкой типов переданных аргументов?
Я пользуюсь Prompt, запрос может быть от
"three little piggies"
до"3 pigs"
- на количестве поросят в результате это никак не сказывается.Как вам удалось получить троих, поделитесь?
100% )))
А если попросить
"octopus DJ"
, на результате не будет или одного, или другогоТак же сложно добиться чего-то одного на изображении, или в необходимом количестве.
"exactly three piggies":
Неплохо, конечно, но получить именно троих у меня не получилось ))