Открыть главное меню

Изменения

Vitaphoto: Облака тегов

575 байтов добавлено, 00:04, 7 ноября 2009
→‎SQL
Есть таблица-отношение <tt>'''tag_image'''</tt> с двумя полями «ID тега» (<tt>'''ti_tag'''</tt>) и «ID изображения» (<tt>'''ti_image'''</tt>). Каждая строчка означает, что фотография с соответствующим ID имеет соответствующий тег.
Ответ ужасенТак вот, чтобы нормально отвечать на этот вопрос, сначала нужно создать таблицу, содержащую все возможные сочетания тегов (тег1, тег2) и строку (тег1, 0), если существует фотография, имеющая только тег1 и ни одного тега более:
<code-sql>SELECT DISTINCT tp_tag1CREATE TABLE tag_pair ASFROM (SELECT DISTINCT t1.ti_tag tp_tag1, IFNULL(t2.ti_tag ,0) tp_tag2 FROM tag_image t1 LEFT JOIN tag_image t2 ON t2.ti_image=t1.ti_image AND t2.ti_tag!=t1.ti_tag) tag_pairJOIN (SELECT t1.ti_tag tpi_tag1, t1.ti_image tpi_image, t2.ti_tag tpi_tag2 FROM tag_image t1 LEFT JOIN tag_image t2 ON t2.ti_image=t1.ti_image AND t2.ti_tag!=t1.ti_tag) tag_pair_imageON tpi_tag1=tp_tag1 AND (tpi_tag2!=tp_tag2 OR tp_tag2 IS NULL)</code-sql>
UNIONА дальше начинается полёт мысли:
SELECT DISTINCT ti_tag FROM tag_image WHERE ti_tag NOT IN ''"Выбрать все теги A из пар ( SELECT tp_tag1 FROM A, B), для которых существует изображение, имеющее A, но не имеющее B; а также все теги A из пар (SELECT DISTINCT t1.ti_tag tp_tag1A, t2.ti_tag tp_tag2 FROM tag_image t1 LEFT JOIN tag_image t2 ON t2.ti_image=t1.ti_image AND t2.ti_tag!=t1.ti_tagB) tag_pair LEFT JOIN tag_image h1 ON h1.ti_tag IN , для которых не существует ни одной пары (tp_tag1A, tp_tag2C) LEFT JOIN tag_image n1 ON n1.ti_image=h1.ti_image AND n1.ti_tag != h1.ti_tag AND n1.ti_tag IN или (tp_tag1B, tp_tag2C) WHERE n1.ti_tag IS NULL)</code-sql> Самое интересное, что если таблицы <tt>tag_pair</tt> где C ≠ A и <tt>tag_pair_image</tt> материализовать и поддерживать в актуальном состоянии, такое решение выполняется за вполне приемлемое времяC ≠ B. Да и выглядит посимпатичнее:"''
<code-sql>SELECT DISTINCT tp_tag1 FROM tag_pair
LEFT JOIN tag_pair_imagetag_image t1 ON tpi_tag1tp_tag2!=tp_tag1 0 AND (tpi_tag2t1.ti_tag=tp_tag1LEFT JOIN tag_image t2 ON tp_tag2!=0 AND t2.ti_image=t1.ti_image AND t2.ti_tag=tp_tag2 OR tp_tag2 WHERE t2.ti_tag IS NULL)
UNION
SELECT DISTINCT ti_tag t1.tp_tag1 FROM tag_image tag_pair t1LEFT JOIN tag_pair t2 ON t2.tp_tag1=t1.tp_tag1 AND t2.tp_tag2!=t1.tp_tag2LEFT JOIN tag_pair t3 ON t3.tp_tag1=t1.tp_tag2 AND t2.tp_tag2!=t1.tp_tag1WHERE ti_tag NOT IN (t2.tp_tag1 IS NULL AND t3.tp_tag1 IS NULL</code-sql> При использовании MySQL здесь мы наталкиваемся на феномен выполнения UNION - каждый запрос по отдельности выполняется меньше сотых долей секунды, а объединённый запрос - почти полсекунды. Поэтому представляем оптимизированный вариант: <code-sql>SELECT DISTINCT t0.tp_tag1 FROM tag_pairt0 LEFT JOIN tag_image h1 t1 ON h1tp_tag2!=0 AND t1.ti_tag IN (=tp_tag1, tp_tag2) LEFT JOIN tag_image n1 t2 ON n1tp_tag2!=0 AND t2.ti_image=h1t1.ti_image AND n1t2.ti_tag != h1.ti_tag AND n1.ti_tag IN (tp_tag1, tp_tag2) WHERE n1tp_tag2=0 OR t2.ti_tag IS NULLORNOT EXISTS (SELECT * FROM tag_pair t3 WHERE t3.tp_tag1=t0.tp_tag1 AND t3.tp_tag2!=t0.tp_tag2 OR t3.tp_tag1=t0.tp_tag2 AND t3.tp_tag2!=t0.tp_tag1)</code-sql>
== 3D-облака тегов ==