SQL: Декартово произведение с произвольным множеством

← →
Kostafey © (2009-10-16 16:29) [0]

Сейчас понял, что это единственный путь разрешить требуемый запрос.

Итак, упрощаяю задачу до уровня сферического коня в вакууме.
Имеем Table1. В ней единственное поле Field1 с некоторыми данными.

выполняем:
select Table1.Field1 from Table1

получаем:
Field1 ------ Мама мыла раму

Необходимо сформулировать запрос, чтобы получить декартово прооизведение
с произволным множеством, например 1,2,3, т.е.
Field1 arbitrarySet ------ ------------ Мама 1 Мама 2 Мама 3 мыла 1 мыла 2 мыла 3 раму 1 раму 2 раму 3

Подскажите, пожалуйста, как написать такой запрос.

← →
Kostafey © (2009-10-16 16:33) [1]

СУБД: MS SQL Server 2005, если это имеет значение.

← →
Рамиль © (2009-10-16 16:38) [2]

А почему нельзя множество загнать в табличку?

← →
Kostafey © (2009-10-16 16:43) [3]

> [2] Рамиль © (16.10.09 16:38)
> А почему нельзя множество загнать в табличку?

Можно и загнать. Просто создавать табличку ради нескольких цифр как-то...
Есть другие варианты?

← →
картман © (2009-10-16 16:45) [4]

creaTE table #t(st VARCHAR(10))
insert into #t (st)
values ("1")

insert into #t (st)
values ("2")

insert into #t (st)
values ("3")

select a.*
from #t a, #t b

drop table #t

← →
Kostafey © (2009-10-16 16:47) [5]

> [4] картман © (16.10.09 16:45)

Эх. Ладно. Спасибо. Будем делать так.

← →
картман © (2009-10-16 16:50) [6]

> Kostafey © (16.10.09 16:47) [5]

почему эх?

← →
картман © (2009-10-16 16:53) [7]

Или надо было посчитать повторы?

create table #t(st VARCHAR(10))

insert into #t (st)
values ("a")

insert into #t (st)
values ("b")

insert into #t (st)
values ("c")

select a.* Field1, row_number() over (partition by a.st order by a.st) arbitrarySet
from #t a, #t b

drop table #t

← →
Kostafey © (2009-10-16 17:06) [8]

> [6] картман © (16.10.09 16:50)
> почему эх?

Думал можно без временной таблицы.
Раз нет - вы мне сэкономили кучу времени.
Спасибо.

← →
картман © (2009-10-16 17:30) [9]

> Kostafey © (16.10.09 17:06) [8]

да временная таблица просто так - напиши любую свою

← →
картман © (2009-10-16 17:31) [10]

select t1.field1
from table1 t1, table1 t2

← →
b z (2009-10-16 17:37) [11]

> Думал можно без временной таблицы.
CTE возможно тут поможет.

← →
Медвежонок Пятачок © (2009-10-16 18:27) [12]

declare @x xml;
set @x = "<item/><item/><item/><item/>";
select t.fieldname
from tablename t, @x.nodes("/item") as A(B)

← →
Медвежонок Пятачок © (2009-10-16 18:39) [13]

причем тело xml не обязательно формировать динамически.
можно один раз создать в бд xml схему или просто таблицу c одним полем.
засунуть туда документ типа
<item count="1"/>
<item count="2"/>
<item count="2"/>
<item count="3"/>
<item count="3"/>
<item count="3"/>
...
<item count="n"/>
.....
<item count="n"/>

и для .nodes указывать "/item[@count=n]"

← →
Медвежонок Пятачок © (2009-10-16 19:21) [14]

либо вообще вот так

declare @x xml;
set @x = replicate("<item/>",3);
select t.fieldname
from tablename t, @x.nodes("/item") as A(B)

← →
Григорьев Антон © (2009-10-16 19:43) [15]

Не уверен, что это пройдёт в именно в MS SQL, но что-то типа такого

SELECT Table1.Field1 LEFT JOIN (SELECT 1 FROM Table1 UNION SELECT 2 FROM Table1 UNION SELECT 3 FROM Table1) ON 1=1

← →
sniknik © (2009-10-16 21:18) [16]

> Не уверен, что это пройдёт в именно в MS SQL
вообще, для mssql подзапросы это "родное", как и временные таблицы, хотя с самим синтаксисом не уверен, указание таблиц в подзапросе лишнее, а алиасов нет (главным образом для подзапроса), без алиасов оно не будет знать что с чем вязать, нужны обязательно.
должно сработать такое

select t.Field1, s.arbitrarySet
from Table1 AS t, (SELECT 1 AS arbitrarySet UNION SELECT 2 UNION SELECT 3) AS s

← →
Kostafey © (2009-10-16 22:27) [17]

> [15] Григорьев Антон © (16.10.09 19:43)
> [16] sniknik © (16.10.09 21:18)
> select t.Field1, s.arbitrarySet
> from Table1 AS t, (SELECT 1 AS arbitrarySet UNION SELECT
> 2 UNION SELECT 3) AS s

Вай класс! Спасибо! То что нужно.
Только я не понял зачем нужен алиас "основной" таблицы?
Так прекрасно работает:
select Table1.Field1, s.arbitrarySet from Table1, (SELECT 1 AS arbitrarySet UNION SELECT 2 UNION SELECT 3) AS s

← →
sniknik © (2009-10-16 22:32) [18]

> нужен алиас "основной" таблицы?
для однообразия, + запись короче, вот будет вместо одного поля нужда указать двадцать поймешь, а попользуешься привыкнешь. (что советую сделать побыстрее, т.к. сам видишь есть варианты когда это не просто удобство, но и синтаксис)

← →
sniknik © (2009-10-16 22:35) [19]

кстати AS можно не писать, короткая форма указания алиаса без него.

← →
Kostafey © (2009-10-16 22:38) [20]

> [18] sniknik © (16.10.09 22:32)
> для однообразия, + запись короче, вот будет вместо одного
> поля нужда указать двадцать поймешь

Я не пытаюсь спорить, но без дополнительных алиасов мне
как-то читать проще. Меньше искусственных элементов.
Впредь буду учитывать вашу точку зрения.

Кстати, так тоже работает:

select t.Field1, s.arbitrarySet from Table1 as t left join (SELECT 1 AS arbitrarySet UNION SELECT 2 UNION SELECT 3) s on 1=1

← →
Игорь Шевченко © (2009-10-16 22:44) [21]

Завидуйте:

WITH foo AS (SELECT /*+ materialize */ level FROM dual CONNECT BY level <= 100000) SELECT rownum id FROM foo foo1, foo foo2 WHERE rownum < 2000000000

Два миллиарда записей без всяких объектов в схеме :)

← →
Медвежонок Пятачок © (2009-10-16 22:44) [22]

ну и в чем радость формирования многоэтажных юнионов, если все решается проще фиксированным запросом с единственным параметром, определющим размерность?

← →
sniknik © (2009-10-16 22:44) [23]

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

и кстати заранее, подставишь таблицу тоже не перестанет, она там лишняя из-за внесения лишних действий, а не из-за синтассиса.

← →
sniknik © (2009-10-16 22:47) [24]

> Завидуйте:
хм. в 2005м тоже ввели порядковый номер... надо попробовать.

← →
Kostafey © (2009-10-16 22:51) [25]

> WITH foo AS (SELECT /*+ materialize */ level FROM dual CONNECT
> BY level <= 100000)
> SELECT rownum id FROM foo foo1, foo foo2 WHERE rownum <
> 2000000000

А прокомментировать как это работает можно?

> [23] sniknik © (16.10.09 22:44)
> но вот ты алиас убери...

То поставь, то убери.
Ничего не понимаю! (с)

> и кстати заранее, подставишь таблицу тоже не перестанет,
> она там лишняя из-за внесения лишних действий, а не из-
> за синтассиса.

Я честно пытался понять смысл фразы, но не смог.
Можно это как-то попроще сказать? :)

← →
Медвежонок Пятачок © (2009-10-16 22:55) [26]

А прокомментировать как это работает можно?

Орокол.

← →
sniknik © (2009-10-16 23:00) [27]

> Можно это как-то попроще сказать? :)
выборка из таблицы повторит значение по количеству строк, а union после уберет повторяющиеся, т.е. в итоге получишь тоже самое но после выполнения ненужных операций.

← →
Медвежонок Пятачок © (2009-10-16 23:07) [28]

[14] все равно круче всех и главное лаконичнее
:)

> А прокомментировать как это работает можно?

можно.
оператор CONNECT BY LEVEL увеличивает LEVEL на единицу (начиная с 1) и помещает новый результат в результирующий набор данных, пока будет истинно условие (в данном случае, пока LEVEL <= 100000), в итоге, получаем запрос, генерирующий 100000 записей. WITH ... AS (SELECT ... ) - это присвоение алиаса результату запроса, для того, чтобы результирующий набор данных мог участвовать во внешних запросах по этому алиасу (разумеется, можно SELECT для этого алиаса написать два раза в явном виде, но громоздко).

Во внешнем запросе обычное декартово произведение "двух таблиц". rownum - псевдостолбец, присваиваемый каждой записи результирующего набора (до выполнения сортировки), его можно использовать в условиях, учитывая, что начинает он присваиваться с 1 и для того, чтобы он увеличивался, необходимо, чтобы все условия запроса были истинными (то есть, rownum > 10 не сработает никогда)

Как смог :)

Кстати, хотел спросить....
Если уж вы извращаетесь с юнионами, то не проще ли извращаться по простому?

select * from table
union all
select * from table
union all
select * from table
union all
select * from table
union all
select * from table

И еще:

Подсказка /*+ materialize */ предлагает сформировать набор из 100000 записей один раз, а не высчитывать два раза (по количеству наборов в декартовом произведении)

> [29] Игорь Шевченко © (16.10.09 23:23)

Спасибо, но до оракла мы пока не доросли-сс. :)

> [27] sniknik © (16.10.09 23:00)
> выборка из таблицы повторит значение по количеству строк,
> а union после уберет повторяющиеся, т.е. в итоге получишь
> тоже самое но после выполнения ненужных операций.

Да что ж я такой тупой-то!?
Т.е. это относится к обсуждению различий между

select Table1.Field1, s.arbitrarySet from Table1 left join (SELECT 1 AS arbitrarySet UNION SELECT 2 UNION SELECT 3) s on 1=1
и
select Table1.Field1, s.arbitrarySet from Table1, (SELECT 1 AS arbitrarySet UNION SELECT 2 UNION SELECT 3) AS s
?

вообще есть предположение, что ты уже предлагаешь (отвратительное) решение задачи, которую не озвучил.
почему ты думаешь, что твоё вИденье решения достойно хоть какого-то обсуждения?
и м.б. всё же сформулировать её, задачу?

> [33] SELECT © (16.10.09 23:43)

Я не против такой точки зрения, но стиль ее изложения
попахивает троллизмом.
Да, я, конечно, думал о том, чтобы изложить задачу.
Вопрос в том до какого уровня абстракции?
До уровня реальных таблиц и запросов?
Обсуждаение потеряет наглядность.
В упрощенном виде?

Есть еще кто-то, кто бы придерживался того же мнения,
что необходимо сформулировать задачу ПОЛНОСТЬЮ?

Необходимо периодически отсылать накапливаемые сведения.
Формат пересылки ужасен до безобразия, но
"Делай, что сказал Купец, вот и будешь молодец!
А поссоришься с купцом и окажешься глупцом."

Каждая запись представляет из себя:
-дата
-номер подразделения
-номер показателя (их 6, вот их я и обозвал произвольным множеством)
-значение показателя для 1-го типа
...
-значение показателя для 8-го типа

Подзапросы для значения i-го показателя могут вернуть
и пустые наборы данных. Но результирующий набор данных
должен содержать все значения.

Например, если количество подразделений x, а номеров показателей y,
то в результирующем наборе данных за сутки должно быть x*y записей
- хоть тресни.
Событий, формирующих значение показателя для i-го типа за эти сутки
может и не произойти. Будет просто null, но номера показателей должны
присутствовать все.

Моя стратегия:
Высчитывать значение показателя для i-го типа в подзапросе в зависимости
от текущего номера показателя.

Кто-нибудь что-нибудь понял? :)

> [38] SELECT © (16.10.09 23:58)
> не ну ты будь готов к потоку уточняющих вопросов )

Это всегда пожалуйста, конечно. :)

SQL: Декартово произведение с произвольным множеством Найти похожие ветки