Получить каждый ‹тег› в String - база данных stackexchange

Код макета для моей проблемы:

SELECT Id FROM Tags WHERE TagName IN '<osx><keyboard><security><screen-lock>'

Проблема в деталях

Я пытаюсь получить теги, используемые в 2011 году, из данных apple.stackexchange. (этот запрос)

Как вы можете видеть, теги в теге изменения сохраняются в виде простого текста в поле Text. пример вывода с тегами stackexchange

<tag1><tag2><tag3>
<osx><keyboard><security><screen-lock>

Как я могу создать уникальный список тегов, чтобы искать их в таблице Tags вместо этой жестко закодированной версии:

SELECT * FROM Tags
  WHERE TagName = 'osx' 
     OR TagName = 'keyboard' 
     OR TagName = 'security'

Вот интерактивный пример.

Stackexchange использует T-SQL, моя локальная копия работает под управлением postgresql с использованием приложения Postgres версии 9.4.5.0.


postgresql sql dataexplorer set-returning-functions
person luckydonald    schedule 27.12.2015    source источник
comment
Ваша версия Postgres отсутствует.   -  person Erwin Brandstetter    schedule 28.12.2015
comment
@ErwinBrandstetter Я благодарен за любое решение, независимо от версии.   -  person luckydonald    schedule 28.12.2015
comment
WHERE TagName = ##TagName:String?osx## также не является допустимым T-SQL. Я предполагаю, что DataExplorer выполняет некоторую предварительную обработку запросов   -  person a_horse_with_no_name    schedule 28.12.2015
comment
В проводнике данных эта строка предлагает поле ввода строки со значением по умолчанию osx. См. раздел Параметры запроса в разделе Об обозревателе данных Stack Exchange.   -  person luckydonald    schedule 28.12.2015

Ответы (2)


arrow_upward
1
arrow_downward

Предполагая это определение таблицы:

CREATE TABLE posthistory(post_id int PRIMARY KEY, tags text);

В зависимости от того, что вы хотите именно:

Чтобы преобразовать строку в массив, обрежьте начальные и конечные символы «‹>», а затем используйте «>‹» как разделитель:

SELECT *, string_to_array(trim(tags, '><'), '><') AS tag_arr
FROM   posthistory;

Чтобы получить список уникальных тегов для всей таблицы (я думаю, вам это нужно):

SELECT DISTINCT tag
FROM   posthistory, unnest(string_to_array(trim(tags, '><'), '><')) tag;

Для неявного соединения LATERAL требуется Postgres 9.3 или более поздней версии.

Это должно быть значительно быстрее, чем использование регулярных выражений. Если вы хотите попробовать регулярное выражение, используйте regexp_split_to_table() вместо regexp_split_to_array(), за которым следует unnest(), как это предлагается в другом ответе:

SELECT DISTINCT tag
FROM   posthistory, regexp_split_to_table(trim(tags, '><'), '><') tag;

Также с неявным соединением LATERAL. Связанный:

Для поиска определенных тегов:

SELECT *
FROM   posthistory
WHERE  tags LIKE '%<security>%'
AND    tags LIKE '%<osx>%';

Скрипт SQL.

Применительно к вашему поиску в T-SQL в нашем проводнике данных:

SELECT TOP 100
       PostId, UserId, Text AS Tags FROM PostHistory
WHERE  year(CreationDate) = 2011
AND    PostHistoryTypeId IN (3  -- initial tags
                           , 6  -- edit tags
                           , 9) -- rollback tags
AND    Text LIKE ('%<' + ##TagName:String?postgresql## + '>%');

(В синтаксисе T-SQL используется нестандартный + вместо ||.)
https://data.stackexchange.com/apple/query/edit/417055

person Erwin Brandstetter    schedule 29.12.2015

arrow_upward
1
arrow_downward

Я упростил данные только для соответствующего столбца и назвал его tags для примера.

Пример данных

create table posthistory(tags text);
insert into posthistory values
  ('<lion><backup><time-machine>'),
  ('<spotlight><alfred><photo-booth>'),
  ('<lion><pdf><preview>'),
  ('<pdf>'),
  ('<asd>');

Запрос для получения уникального списка тегов

SELECT DISTINCT
  unnest(
    regexp_split_to_array(
      trim('><' from tags), '><'
    )
  )
FROM
  posthistory

Сначала мы удаляем все вхождения начальных и конечных знаков > и < из каждой строки, затем используем функцию regexp_split_to_array() для получения значений в массивы, а затем unnest() для расширения массива до набора строк. Наконец, DISTINCT устраняет повторяющиеся значения.

Представляем SQLFiddle, чтобы просмотреть, как оно работает.

person Kamil Gosciminski    schedule 28.12.2015