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] все равно круче всех и главное лаконичнее
:)

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

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

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

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

Как смог :)

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

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

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

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

И еще:

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

← →
Kostafey © (2009-10-16 23:37) [32]

> [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
?

← →
SELECT © (2009-10-16 23:43) [33]

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

← →
Kostafey © (2009-10-16 23:48) [34]

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

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

← →
SELECT © (2009-10-16 23:54) [35]

> Kostafey © (16.10.09 23:48) [34]

изложи как есть, без формализации, простым обывательским языком )

← →
Kostafey © (2009-10-16 23:55) [36]

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

← →
Kostafey © (2009-10-16 23:56) [37]

> [35] SELECT © (16.10.09 23:54)

Хорошо, извольте... несколько минут...

← →
SELECT © (2009-10-16 23:58) [38]

> Kostafey © (16.10.09 23:56) [37]

не ну ты будь готов к потоку уточняющих вопросов )

← →
Kostafey © (2009-10-17 00:08) [39]

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

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

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

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

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

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

← →
Kostafey © (2009-10-17 00:11) [40]

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

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

всё просто:
строишь декартово произведение всех показателей и подразделений (надеюсь, что они в справочниках),
потом это джойнишь слева собственно таблицей, где "Каждая запись представляет из себя:"

> строишь декартово произведение всех показателей и подразделений
> (надеюсь, что они в справочниках),

Убедитесь, что вы сидете устойчиво: сейчас
велика вероятность упасть со смеху.

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

Да, верно, БД даже не в 1-й нормальной форме.
Струкура БД снизошла на грешную землю с
Небес, там десятки тысяч записей и менять
ее никто не собирается.

> Kostafey © (17.10.09 00:22) [42]

тогда попробуй вычленить запросом показатели - это и будет виртуальный справочник.

ЗЫ

> Более того, сама суть этих признаков, наоснове которых формируются
> показатели зашиты в разные поля разных типов той же самой
> таблицы из которой вычленяется значение показателя для i-
> го типа.

это называется абзац. т.е. приплыли )))

"бардак систематизации не поддается" (с)

> тогда попробуй вычленить запросом показатели - это и будет
> виртуальный справочник.

А смысл? Его все равно нужно будет делать
left join, при том что для каждой записи
подразделения должно быть декартово произведение
с множеством показателей.
Можно с таким же успехом просто перечислить
их 1,2,3... как и было сделано выше.

> "бардак систематизации не поддается" (с)

Мне уже не первый раз приходится это читать
на дельфимастере в здрес тех задач, с которыми
приходится иметь дело. :))

> Kostafey © (17.10.09 00:30) [45]
> А смысл? Его все равно нужно будет
> делать left join, при том что для каждой записи подразделения
> должно быть декартово произведение с множеством показателей.

не left, а декартово.
а вообще я что-то перестал понимать - то надо, то не надо...
вот же схема:
SELECT * FROM <подразделения>, <показатели> LEFT JOIN <таблица, где "Каждая запись представляет из себя:">
непонятно, в чем успех "просто перечислить"? кого перечислить? откуда узнали, сколько перечислить?

> не left, а декартово.
> а вообще я что-то перестал понимать - то надо, то не надо...
> вот же схема:
> SELECT * FROM <подразделения>, <показатели>
> LEFT JOIN <таблица, где "Каждая запись представляет из себя:">

Надо, всегда надо :) Я просто то же самое в другом виде написал:

SELECT * FROM <подразделения> LEFT JOIN <показатели> LEFT JOIN <таблица>, где "Каждая запись представляет из себя:

где LEFT JOIN <показатели>:
left join (SELECT 1 AS ID_TYPE_INDEX UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) typeIndex on 1=1

> непонятно, в чем успех "просто перечислить"?

Я ж говорю справичника показателей нет, а их полный
перечень в любом случае должен присутствовать.

> кого перечислить?

Все показатели.

> откуда узнали, сколько перечислить?

Их перечень задан.

последние вопросы - зачем LEFT JOIN <показатели>?
и кем, когда и в какой форме "Их перечень задан"?

> и кем, когда и в какой форме "Их перечень задан"?

ТЗ, вестимо ;)
Я уже писал:
> -номер показателя (их 6, вот их

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

в смысле запроса который я исправил, и объяснил почему...
Григорьев Антон © (16.10.09 19:43) [15]
а вот почему ты это объяснение пытаешься применить к чему угодно но только не этому, загадка...

> [53] sniknik © (17.10.09 14:52)

Уф, ура, наконец-то понял. А то уж боялся так и останусь неучем :))
Спасибо!

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

Схематично:
select t.Field1, s.arbitrarySet, q.value from Table1 AS t, (SELECT 1 AS arbitrarySet UNION SELECT 2 UNION SELECT 3) AS s left join ( select CASE someVariable WHEN 1 THEN "a" WHEN 2 THEN "b" WHEN 3 THEN "c" ELSE "d" END as value ) q on q.someVariable = s.arbitrarySet

Это, конечно не работает...
Конечно, на месте "a", "b", "c" будут свои запросы.

← →
SP (2009-10-17 16:35) [56]

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

Создай таблицу с этим самым произвольным множеством.
А потом
select Table1.Field1, Table2. from Table1.arbitrarySet from Table1,Table2

Тем более что это самое "Произвольное" множество - как я понимаю не совсем произвольное и чем-то должно определяться... Вот и в данном случае будет определяться содержимым Table2
Да и не мешало бы знать цели такой задачи, тогда возможно бы и другое более подходящее решение нашлось бы...

> from Table1.arbitrarySet from Table1,Table2

Это что значит?

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

см. > [39] Kostafey © (17.10.09 00:08)

← →
SP (2009-10-17 17:19) [58]

Ой, блин... Это издержки копипаста...
Имелось ввиду:

select Table1.Field1, Table2.arbitrarySet from Table1,Table2

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