SQL — как добиться подсчета очков и сопоставления категорий?
Я пытаюсь добиться оценки и сопоставления в MySQL. У меня есть таблица с 3 уровнями категории level1 (конечный родитель), level2 (родительский) и level3 (дочерний).
Я пытаюсь присвоить оценку каждой новой категории данных, которую я получаю извне, и присвоить ее определенному идентификатору категории из моей таблицы score += r.score * weight
+----+------------------------------------+-----------------------------+--------------------+
| ID | LEVEL1 | LEVEL2 | LEVEL3 |
+----+------------------------------------+-----------------------------+--------------------+
| 1 | Arts and Entertainment Businesses | Casinos | NULL |
| 1 | Arts and Entertainment Businesses | Performing Arts Businesses | Radio Stations |
| 2 | Auto Sales Businesses | Motorcycle Dealers | Motorcycle Parts |
| 2 | Auto Sales Businesses | RVs and Motor Home Dealers | NULL |
| 2 | Auto Sales Businesses | Car Dealers | Used Cars Dealers |
| 3 | Bars and Lounges | Pubs and Dive Bars | Pubs |
| 3 | Bars and Lounges | Wine Bars | NULL |
| 4 | Restaurants | American Restaurants | Barbeque |
+----+------------------------------------+-----------------------------+--------------------+
Выше приведена моя основная таблица с категорией.
Что я пытаюсь сделать:
If input = 'Radio',
Then match to LEVEL3 'Radio Station' with score less than 1.0
If LEVEL3 is NULL, Move up to LEVEL2
Then Match to LEVEL2
IF LEVEL2 is NULL, Move up to LEVEl1
Then Match to LEVEL1
scores: 0.0(No Match) to 1.00 (Exact match)
0.8 - 0.99 (Very good Match)
Я пытаюсь рассчитать оценку близости для всех входных переменных, а затем присвоить им идентификатор. Если нет данных на уровне 3 и уровне 2, то для каждого УРОВНЯ 1 есть строка, где УРОВЕНЬ 2 и УРОВЕНЬ 3 обозначены Other, что будет соответствовать наименьшему количеству очков.
Я действительно пытаюсь обернуть это SQL, не переходя к Python ML/AI и не переделывая его. (если это невозможно в SQL, я перейду на Python) Любые мысли будут полезны.
Ожидаемый результат:
Input = Used Cars
Output = [ID: 2 ,LEVEL1 : Auto Sales Business]
Пожалуйста, посмотри: Я понимаю, что это может быть не совсем техническая проблема/ошибка кода. Я понимаю правила stackoverflow, запрещающие публиковать обсуждение, и сосредотачиваюсь на получении ответа. Любые указатели/код SQL/скрипт Python мне очень помогут. Спасибо.
Добавил ожидаемый результат. Образец данных — это таблица (подмножество основной таблицы)
Ответы 1
вот один из вариантов в sql, использующий длину символа для присвоения оценки.-
DECLARE @imput varchar(300) = 'Radio';
WITH Data AS (
SELECT 1 as id, 'Arts and Entertainment Businesses' AS Level1, 'Casinos' AS Level2, NULL AS Level3 Union
SELECT 1 as id, 'Arts and Entertainment Businesses' AS Level1, 'Performing Arts Businesses' AS Level2, 'Radio Stations' AS Level3 Union
SELECT 2 as id, 'Auto Sales Businesses' AS Level1, 'Motorcycle Dealers' AS Level2, 'Motorcycle Parts' AS Level3 Union
SELECT 2 as id, 'Auto Sales Businesses' AS Level1, 'RVs and Motor Home Dealers' AS Level2, NULL AS Level3 Union
SELECT 2 as id, 'Auto Sales Businesses' AS Level1, 'Car Dealers' AS Level2, 'Used Cars Dealers'AS Level3 Union
SELECT 3 as id, 'Bars and Lounges' AS Level1, 'Pubs and Dive Bars' AS Level2, 'Pubs' AS Level3 Union
SELECT 3 as id, 'Bars and Lounges' AS Level1, 'Wine Bars' AS Level2, NULL AS Level3 Union
SELECT 4 as id, 'Restaurants' AS Level1, 'American Restaurants' AS Level2, 'Barbeque' AS Level3
)
SELECT *
,CAST(Len(@imput) AS numeric(18,2))/Len(COALESCE(Level3,Level2,Level1)) AS Score
FROM data
WHERE COALESCE(Level3,Level2,Level1) LIKE '%'+ @imput + '%'
Спасибо, я использую это как отправную точку
добавить правильный образец данных и ожидаемый результат