Arhn - архитектура программирования

Вычисление сходства Жаккара в улье

У меня были данные, как показано ниже:

CustomerId   Category
  100            2
  100            2
  100            3
  100            6
  100            4
  200            3
  200            6
  200            7
  300            2

Итак, мне нужен индекс подобия Jaccard:

  • Жаккард (100 200) = 2 (общие предметы) / 5 (объединение предметов)
  • Jaccard(100 300) = 1 (общие предметы) / 4 (объединение предметов)
  • Jaccard(200,300) = 0 (общие предметы) / 4 (объединение предметов).

Сначала я пытался найти союз и пересечение терминов, но я не уверен, что это самый эффективный способ. Также я хочу, чтобы дубликаты, такие как Jaccard (100 300) и Jaccard (300 100), не появлялись вместе. Может ли кто-нибудь помочь с этим?

     select t1.customer_id, t2.customer_id,
              sum(case when t1.category_id = t2.category_id then 1 else 0 end) intersection,
sum(case when t1.category = t2.category then 1
         when t1.category <> t2.category then 1 else 0 end)
union
    from t t1 cross join
         t t2
  Where t1.customer_id <> t2.customer_id
    group by t1.customer_id, t2.customer_id

К сожалению, я также проверил, что у меня есть один клиент, покупающий несколько товаров в одной категории. Поэтому я отредактировал таблицу, чтобы отразить, что у клиента 100 есть два товара в категории 2. Однако это не должно изменить значения метрики сходства Jaccard.

sql hive
22.05.2017

  • поэтому сходство для (100 200) должно быть 2/5, а не 2/4, верно? 22.05.2017
  • Используйте 1_; который избегает (100,100) и (300,100). 22.05.2017
  • Я не знаю, как выбрать похожие наборы в SQL добавляет что-то для вас — вероятно, нет. 22.05.2017
  • В Hive вышеуказанное не работает, так как не работают соединения без равенства. 24.05.2017

Ответы:


1

Вам не нужен cross join. Получите знаменатель, вычислив общее количество различных идентификаторов category_id для пары и вычтя из него пересекающиеся идентификаторы category_id.

SELECT t1.customer_id AS id1,
       t2.customer_id AS id2,
       1.0*sum(CASE WHEN t1.category_id = t2.category_id THEN 1 ELSE 0 END)
     / (count(DISTINCT t1.category_id)+count(DISTINCT t2.category_id)-sum(CASE WHEN t1.category_id = t2.category_id THEN 1 ELSE 0 END)) AS jaccard_similarity
FROM t t1
JOIN t t2 ON t1.customer_id<t2.customer_id
GROUP BY t1.customer_id, t2.customer_id

Если неравенства не поддерживаются в join, используйте

SELECT t1.customer_id AS id1,
       t2.customer_id AS id2,
       1.0*sum(CASE WHEN t1.category_id = t2.category_id THEN 1 ELSE 0 END)
       / (count(DISTINCT t1.category_id)+count(DISTINCT t2.category_id)-sum(CASE WHEN t1.category_id = t2.category_id THEN 1 ELSE 0 END)) AS jaccard_similarity
FROM t t1
CROSS JOIN t t2 
WHERE t1.customer_id<t2.customer_id
GROUP BY t1.customer_id, t2.customer_id

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

select t1.customer_id as id1, t2.customer_id as id2
,sum(case when t1.category_id = t2.category_id then 1 else 0 end) as intersection
from t t1 
join t t2 on t1.customer_id<t2.customer_id
group by t1.customer_id, t2.customer_id

Изменить: на основе комментария ОП о том, что клиент может иметь одну и ту же категорию несколько раз, но должен учитываться только один раз.

SELECT t1.customer_id AS id1,
       t2.customer_id AS id2,
       1.0*COUNT(DISTINCT CASE WHEN t1.category_id = t2.category_id THEN t1.category_id END)
       / (COUNT(DISTINCT t1.category_id)+COUNT(DISTINCT t2.category_id)
         -COUNT(DISTINCT CASE WHEN t1.category_id = t2.category_id THEN t1.category_id END)) AS jaccard_similarity
FROM t t1
CROSS JOIN t t2 
WHERE t1.customer_id<t2.customer_id
GROUP BY t1.customer_id, t2.customer_id
22.05.2017
  • что не работает? неравенство в соединении? я отредактировал ответ с помощью cross join .. он должен работать 24.05.2017
  • Да, я думаю, я могу использовать перекрестное соединение с условием where? 24.05.2017
  • проблема в том, что сходство Jaccard выходит на 1, на самом деле в моем случае, потому что один и тот же покупатель покупает несколько товаров. Я предполагаю, что пересечение не вычислено точно. 24.05.2017
  • @ vkaul11 .. я отредактировал ответ, чтобы подсчитывать только отдельные элементы в паре. это должно дать ожидаемые результаты. 24.05.2017
    • Новые материалы
    Коллекции публикаций по глубокому обучению
    Последние пару месяцев я создавал коллекции последних академических публикаций по различным подполям глубокого обучения в моем блоге https://amundtveit.com - эта публикация дает обзор 25..
    Представляем: Pepita
    Фреймворк JavaScript с открытым исходным кодом Я знаю, что недостатка в фреймворках JavaScript нет. Но я просто не мог остановиться. Я хотел написать что-то сам, со своими собственными..
    Советы по коду Laravel #2
    1-) Найти // You can specify the columns you need // in when you use the find method on a model User::find(‘id’, [‘email’,’name’]); // You can increment or decrement // a field in..
    Работа с временными рядами спутниковых изображений, часть 3 (аналитика данных)
    Анализ временных рядов спутниковых изображений для данных наблюдений за большой Землей (arXiv) Автор: Рольф Симоэс , Жильберто Камара , Жильберто Кейрос , Фелипе Соуза , Педро Р. Андраде ,..
    3 способа решить квадратное уравнение (3-й мой любимый) -
    1. Методом факторизации — 2. Используя квадратичную формулу — 3. Заполнив квадрат — Давайте поймем это, решив это простое уравнение: Мы пытаемся сделать LHS,..
    Создание VR-миров с A-Frame
    Виртуальная реальность (и дополненная реальность) стали главными модными терминами в образовательных технологиях. С недорогими VR-гарнитурами, такими как Google Cardboard , и использованием..
    Демистификация рекурсии
    КОДЕКС Демистификация рекурсии Упрощенная концепция ошеломляющей О чем весь этот шум? Рекурсия, кажется, единственная тема, от которой у каждого начинающего студента-информатика..