Как разработать запрос нескольких тегов в базе данных аналитики

Question

Я хотел бы сохранить пользовательские теги покупки для каждой транзакции, например, если пользователь купил обувь, теги "SPORTS", "NIKE", SHOES, COLOUR_BLACK, SIZE_12,..

Эти теги - это то, что продавец заинтересован в ответах, чтобы понять продажи.

Моя идея заключается в том, что когда появляется новый тег, создайте новый код (что-то вроде хэш-кода, но последовательный) для этого тега, и код начинается с "a-z" 26 букв тогда "aa, ab, ac...zz" продолжается. Теперь сохраните все теги, указанные для одной транзакции, в одном столбце с именем tag (varchar) отделяя с "|",

Давайте предположим, что отображение (на уровне приложения)

"SPORTS" = a
"TENNIS" = b
"CRICKET" = c
...
...
"NIKE"  = z        //Brands company
"ADIDAS" = aa
"WOODLAND" = ab
...
...
SHOES   = ay
...
...
COLOUR_BLACK = bc
COLOUR_RED = bd
COLOUR_BLUE = be
...
SIZE_12 = cq
...

Таким образом, сохраняя вышеуказанную транзакцию покупки, тег будет выглядеть так: tag="|a|z|ay|bc|cq|" И теперь позволяет продавцу искать количество проданной обуви, добавив WHERE состояние tag LIKE %|ay|%, Теперь проблема в том, что я не могу использовать индекс (ключ сортировки в dds красного смещения) для "LIKE начинается с%". Итак, как решить эту проблему, поскольку у меня может быть 100 миллионов записей? не хочу полного сканирования таблицы..

Любое решение, чтобы это исправить?

Update_1: я не подписался bridge table Концепция (таблица перекрестных ссылок), так как я хочу выполнить группирование по результатам после поиска по указанным тегам. Мое решение даст только одну строку, когда два тега совпадают в одной транзакции, но таблица моста даст мне две строки? тогда моя сумма () будет удвоена.

Я получил предложение как ниже

EXISTS (ВЫБРАТЬ 1 ИЗ транзакции_tag WHERE tag_id = 'zz' и trans_id = tr.trans_id) в предложении WHERE один раз для каждого тега (примечание: предполагается, что tr является псевдонимом таблицы транзакций в окружающем запросе)

Я не следовал за этим; так как я должен выполнить условия И и ИЛИ на тегах, пример ("СПОРТ" И "АДИДАС") ---- "ОБУВЬ" И ("НИКЕ" ИЛИ "АДИДАС")

Update_2: я не следил за битовым полем, так как не знаю, что redshift имеет такую ​​поддержку, и я предполагаю, будет ли в моей системе минимум 3500 тегов, и выделяю один бит для каждого; что приводит к 437 байтам для каждой транзакции, хотя для транзакции может быть задано не более 5 тегов. Любая оптимизация здесь?

Solution_1:

Я думал о добавлении min (SMALL_INT) и max value (SMALL_INT) вместе со столбцом тегов, и применил индекс к этому.

так как то так

"SPORTS" = a = 1
"TENNIS" = b = 2
"CRICKET" = c = 3
...
...
"NIKE"  = z  = 26
"ADIDAS" = aa = 27

Таким образом, мои значения столбца

`tag="|a|z|ay|bc|cq|"` //sorted?
`minTag=1`
`maxTag=95` //for cq

И запрос для поиска обуви (ay=51)

maxTag <= 51 AND tag LIKE %|ay|%

И запрос для поиска обуви (ay=51) И РАЗМЕР_12 (cq=95)

minTag >= 51 AND maxTag <= 95 AND tag LIKE %|ay|%|cq|%

Это даст какую-то пользу? Просьба предложить любые альтернативы.

Answer 1

Вы можете реализовать автоматическую пометку, пока файлы загружаются в S3. Маркировка на уровне БД в этом процессе слишком запоздала. Утомительно и требует много жесткого кодирования

1. При загрузке в тег S3, используя приведенный ниже пример AWS s3API aws s3api put-object-tagging --bucket --key --tagging "TagSet=[{Key=Addidas,Value=AY}]]"

захватывать теги динамически, отправляя и в качестве параметра

2. загрузить теги в Dynamodb в качестве хранилища метаданных

3. загрузить данные в Redshift с помощью команды S3 COPY

Answer 2

Вы можете сохранить столбец тегов как битовую маску varchar, то есть строго определенную битовую последовательность 1 или 0, так что если покупка помечена тегом, будет 1, а если нет, то будет 0 и т. Д. Для каждой строки вы будет иметь последовательность из 0 и 1, которая имеет ту же длину, что и количество имеющихся у вас тегов. Эта последовательность сортируема, однако вам все равно потребуется поиск в середине, но вы будете знать, на какую конкретную позицию смотреть, так что вам не нужно like, просто substring, Для дальнейшей оптимизации вы можете преобразовать эту битовую маску в целочисленные значения (она будет уникальной для каждой последовательности) и выполнить сопоставление на основе этого, но AFAIK Redshift пока не поддерживает это из коробки, вам придется самостоятельно определять правила.

UPD: Похоже, что лучшим вариантом здесь является сохранение тегов в отдельной таблице и создание процесса ETL, который разворачивает теги в табличную структуру order_id, tag_id, распространяется order_id и отсортировано по tag_id, При желании вы можете создать представление, которое объединяет это с таблицей заказов. Тогда поиск заказов с определенным тегом и дальнейшая агрегация заказов должны быть эффективными. Не существует серебряной пули для оптимизации этого на плоском столе, по крайней мере, я не знаю такого, который не принес бы много ненужной сложности по сравнению с "реляционным" решением.