Нашли или выдавили из себя код, который нельзя назвать нормальным,
на который без улыбки не взглянешь?
Не торопитесь его удалять или рефакторить, — запостите его на
говнокод.ру, посмеёмся вместе!
Ну тут еще другой, более напрашивающийся вопрос: зачем нужны таблицы заполненые просто целыми числами? Я как ни стараюсь, не могу придумать применения.
я не знаю как в мускуле, но в mssql можно написать примерно так
select top 10000
row_number() over(order by o1.object_id) as x
from sys.objects as o1
cross join sys.objects as o2
cross join sys.objects as o3
cross join sys.objects as o4
cross join sys.objects as o5
cross join sys.objects as o6
пытался найти что в m$ с иерархическими запросами, оказалось всё печально, какие то union all, какая то неочевидная переголова
так сложно было подсмотреть, что сделано у взрослых?..
;with tbl
(
select 1 as x from blablabla
union all
select x + 1 from tbl
);
первый запрос определяется так сказать init запрос.
далее, делаем union all то CTE, в котором она используется, т.е. tbl. и все, этот union all будет добавляется, пока хотя бы одна строка возвращается во втором запросе.
например вот
with data
as(
select DictionaryId,
ParentId,
DictionaryName,
cast(DictionaryName as nvarchar(1000)) as HierarchialName
from Warehouse.Dictionary
where ParentId is null
and TypeId in (select TypeId from Warehouse.HistoryDictionaries)
union all
select
d.DictionaryId,
d.ParentId,
d.DictionaryName,
cast(t.HierarchialName + ', ' + d.DictionaryName as nvarchar(1000)) as HierarchicalName
from Warehouse.Dictionary as d
join data as t
on d.ParentId = t.DictionaryId
)
select *
from data
и кстати, у меня этот запрос вернул 11к строк за 1 секунду, когда
select level from dual connect by level <= 10000;
на этой же сервере (да, на одной виртуальке крутятся mssql и oracle) отработал за 2 секунды
> например вот
то ли дело условия выборки корневых значений и всю вторую половину запроса заменить на start with + connect by
впрочем, оракловый sys_connect_by_path сам не без изъяна (хорошо хоть нужен редко)
> видимо select * from generate_series(1, 10000) ?
Ага. Вот только интересно, почему оно работает и без from... В доке ничего не написано о той форме записи, которую я сдуру по памяти привёл:
-- так пашет
postgres=# select generate_series(1, 3) as x, generate_series(5, 7) as y;
x | y
---+---
1 | 5
2 | 6
3 | 7
-- и так тоже пашет
postgres=# select * from generate_series(1, 2) x, generate_series(5, 6) y;
x | y
---+---
1 | 5
1 | 6
2 | 5
2 | 6
> select generate_series(1, 3) as x, generate_series(5, 7) as y;
вот это прикольно, конечно
хоть и неожиданно
если разной длины колонки будут - недостающие значения будут, видимо, null?
> если разной длины колонки будут - недостающие значения будут, видимо, null? Авотхуй! Получается наименьшее общее кратное от количества записей в каждой серии. Довольно странная фича, и недокументированная.
А если написать вот так: select *, generate_series(1, 3) from test, то получится как и при select * from generate_series(1, 3) s, test - по 3 записи на каждую запись в табличке.
P.S. Есть вероятность, что это какая-то общая фишка для функций, возвращающих множество строк, а не только generate_series.
> общая фишка для функций, возвращающих множество строк
Да, так и есть.
Currently, functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the function returning set is invoked, and an output row is generated for each element of the function's result set. Note, however, that this capability is deprecated and might be removed in future releases.
А НОК вместо декартового произведения получается из-за того, что одинаковые строки из результата повыкидывало.
> где во встроенном языке можно в шаблоны?
Ну если оракл не умеет (а у него же самое продвинутое ООП из всех СУБД, емнип), то скорее всего, что нигде. Кстати, жабья хранимка не может принимать объекты произвольных типов (ну или какого-нибудь супертипа Object)?
у меня там сейчас 1867 записей, и запрос отрабатывает меньше чем за секунду на dev сервере, который мягко говоря слабоват.
есть еще вариант
;WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
x as (select row_number() over(order by C) as Number from Pass4)
SELECT Number
FROM x
WHERE Number <= 10000
Чтобы на неё джойнить.
Допустим есть строка, в неё чего-то упаковано (дни месяца, битовые флаги), джойним пишем substring(i,i+1) и разворачиваем эту упаковку из горизонтальной в вертикальную, которую мы уже можем взять where.
> отчет с разбивкой по дням
> INSERT INTO `numbers` (`number`) VALUES('9999');
Наступил 3802 день нашей телестройки... Сегодня между ребятами присутствовало со вчерашнего отсутствовало со вчерашнего взор, и только Марина Африкантова стройке, где поссорились на стройке, где поссорились на повышел молодой отец. Напряжение между ребятами присутствовало со вчерашнего в Питер Ильи Григоренко серьёзно пола, Егор Холявин всё ещё несчастлив.
кстати, это уже обсуждалось даже на говнокоде
лучше иметь качественную таблицу dim_dates, с кучей полезных колонок (не только даты, но и дни недели, номер недели, декады, квартала и т.д.), которую заполнить один раз -
на 10 лет каких-то сраных 3600 строк в ней (для субд это слёзы, когда речь идёт о статистике на миллионы записей за заданный перид отчета),
чем постоянно на лету генерить какой-то массив
только человек, совершенно лишённый фантазии, не может играть с калькулятором (с)
в отсутствие generate_series и других инлайн-sql-способов генерации нужной пачки последовательных значений, мастерам майэскуэльного кунг-фу приходится изворачиваться такой говнотаблицей (странно, правда, что 10к записей всего, непорядок)
хотя, конечно, майскл ни разу в этом месте не оракл, и я бы поостерегся вообще на сторону этой базы переносить в sql то, что можно сделать на, прости господи, php
а в оракле ту же генерацию пачки значений я применял, применяю и буду применять в сложных запросах, когда речь идёт о перелопачивании в одном запросе тысячей значений, и контекст из базы в приложение и обратно в базу переключать совсем не охота и ни к чему
Или все же в мускуле есть какой-то аналог?
P.S. Стековерфловочка подсказывает, что нету.
так сложно было подсмотреть, что сделано у взрослых?..
> select generate_series(1, 10000);
видимо select * from generate_series(1, 10000) ?
первый запрос определяется так сказать init запрос.
далее, делаем union all то CTE, в котором она используется, т.е. tbl. и все, этот union all будет добавляется, пока хотя бы одна строка возвращается во втором запросе.
например вот
и кстати, у меня этот запрос вернул 11к строк за 1 секунду, когда на этой же сервере (да, на одной виртуальке крутятся mssql и oracle) отработал за 2 секунды
то ли дело условия выборки корневых значений и всю вторую половину запроса заменить на start with + connect by
впрочем, оракловый sys_connect_by_path сам не без изъяна (хорошо хоть нужен редко)
Ага. Вот только интересно, почему оно работает и без from... В доке ничего не написано о той форме записи, которую я сдуру по памяти привёл:
вот это прикольно, конечно
хоть и неожиданно
если разной длины колонки будут - недостающие значения будут, видимо, null?
Авотхуй! Получается наименьшее общее кратное от количества записей в каждой серии. Довольно странная фича, и недокументированная.
P.S. Есть вероятность, что это какая-то общая фишка для функций, возвращающих множество строк, а не только generate_series.
Да, так и есть.
Currently, functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the function returning set is invoked, and an output row is generated for each element of the function's result set. Note, however, that this capability is deprecated and might be removed in future releases.
А НОК вместо декартового произведения получается из-за того, что одинаковые строки из результата повыкидывало.
бтв
http://sqlfiddle.com/#!4/3a8b2/2
есть СУБД, где во встроенном языке можно в шаблоны?...
Я ее невнимательно читал раньше. Вот только сегодня заметил про таймштампы.
> http://sqlfiddle.com/#!4/3a8b2/2
Красиво. Впрочем в постгресе я тоже так могу 😉
> где во встроенном языке можно в шаблоны?
Ну если оракл не умеет (а у него же самое продвинутое ООП из всех СУБД, емнип), то скорее всего, что нигде. Кстати, жабья хранимка не может принимать объекты произвольных типов (ну или какого-нибудь супертипа Object)?
есть еще вариант
В постгресе select * from generate_series(1, 10000) отвечает за 1-2мс 🙂 Ну комп, правда, не самый слабый.
Допустим есть строка, в неё чего-то упаковано (дни месяца, битовые флаги), джойним пишем substring(i,i+1) и разворачиваем эту упаковку из горизонтальной в вертикальную, которую мы уже можем взять where.
> INSERT INTO `numbers` (`number`) VALUES('9999');
Наступил 3802 день нашей телестройки...
Сегодня между ребятами присутствовало со вчерашнего отсутствовало со вчерашнего взор, и только Марина Африкантова стройке, где поссорились на стройке, где поссорились на повышел молодой отец. Напряжение между ребятами присутствовало со вчерашнего в Питер Ильи Григоренко серьёзно пола, Егор Холявин всё ещё несчастлив.
лучше иметь качественную таблицу dim_dates, с кучей полезных колонок (не только даты, но и дни недели, номер недели, декады, квартала и т.д.), которую заполнить один раз -
на 10 лет каких-то сраных 3600 строк в ней (для субд это слёзы, когда речь идёт о статистике на миллионы записей за заданный перид отчета),
чем постоянно на лету генерить какой-то массив
вообще, там много функций есть для работы с датами http://msdn.microsoft.com/en-us/library/ms186724(v=sql.110).aspx
Выходные != суббота + воскресенье.
Тут же просто таблица из одной колонки для чисел.
в отсутствие generate_series и других инлайн-sql-способов генерации нужной пачки последовательных значений, мастерам майэскуэльного кунг-фу приходится изворачиваться такой говнотаблицей (странно, правда, что 10к записей всего, непорядок)
хотя, конечно, майскл ни разу в этом месте не оракл, и я бы поостерегся вообще на сторону этой базы переносить в sql то, что можно сделать на, прости господи, php
а в оракле ту же генерацию пачки значений я применял, применяю и буду применять в сложных запросах, когда речь идёт о перелопачивании в одном запросе тысячей значений, и контекст из базы в приложение и обратно в базу переключать совсем не охота и ни к чему