Как пересечь две колонки в SQL Server

Question

Как пересечь две колонки в SQL Server

У меня есть таблица данных AC в SQL Server со структурой как:

+----------+------------+-------+
| AuthorID | CoAuthorID | Year  |
+----------+------------+-------+
|  677     | 901706     |  2005 |
|  677     | 901706     |  2005 |
|  677     | 901706     |  2005 |
|  1359    | 133112     |  2005 |
|  1359    | 133112     |  2005 |
|  1359    | 133112     |  2005 |
|  1359    | 266386     |  2005 |
|  1359    | 454557     |  2005 |
|  1359    | 454557     |  2005 |
|  1359    | 454557     |  2005 |
|  1359    | 534423     |  2005 |
|  1359    | 534423     |  2005 |
|  1359    | 534423     |  2005 |
|  1359    | 578338     |  2005 |
|  1359    | 721615     |  2005 |
|  1359    | 1016805    |  2005 |
|  1359    | 1016805    |  2005 |
|  1359    | 1016805    |  2005 |
|  1359    | 1361047    |  2005 |
|  1359    | 1361047    |  2005 |
|  1359    | 1361047    |  2005 |
|  1359    | 1361320    |  2005 |
|  1359    | 1361320    |  2005 |
|  1359    | 1361320    |  2005 |
|  1359    | 1395982    |  2005 |
|  1359    | 1395982    |  2005 |
|  1359    | 1395982    |  2005 |
|  1359    | 1412785    |  2005 |
|  1359    | 1412785    |  2005 |
|  1359    | 1412785    |  2005 |
|  1359    | 1412785    |  2005 |
|  ...     |            |       |
|  ...     |            |       |
+----------+------------+-------+

Я должен рассчитать годовой Conditional Probability из AuthorID дано CoAuthorID т.е.

P(AuthorID|CoAuthorID) знак равно P(AuthorID ∩ CoAuthorID) / P(CoAuthorID)

в то время как в году 2005 где это операция пересечения.

Первоначально, например, AuthorID = 677 а также CoAuthorID = 901706 а также Year = 2005Я попробовал это:

За P(AuthorID):

SELECT COUNT(DISTINCT AuthorID) FROM AC WHERE Year = 2005

Возвращается 390 так P(AuthorID) знак равно 1/390

За P(CoAuthorID):

SELECT COUNT(DISTINCT CoAuthorID) FROM AC WHERE AuthorID = 677 AND Year = 2005

Возвращается 1 так P(CoAuthorID) знак равно 1/1

За P(AuthorID ∩ CoAuthorID):

SELECT * FROM AC WHERE AuthorID = 677 AND Year = 2005
INTERSECT 
SELECT * FROM AC WHERE CoAuthorID = 901706 AND Year = 2005

Возвращает 1 строку как:

AuthorID    CoAuthorID  Year
----------------------------
677         901706      2005

В то время как в данных есть 3 строки, это означает AuthorID а также CoAuthorID сосуществует в данных 3 раза в 2005 означает, что эти два Авторы внесли свой вклад 3 раза в 2005 году. Итак,

Какой должна быть ценность для P(AuthorID ∩ CoAuthorID)? Должно ли это быть 1 или же 1/3?
Также правильны ли другие расчеты?

Спасибо!

0

sql-server conditional probability intersection independent-set

Источник

user3495173 05 мар '16 в 09:03

2 ответа

Решение

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

DISTINCTКарл.

https://msdn.microsoft.com/en-us/library/ms188055(v=sql.120).aspx

0

Источник

user5921826 05 мар '16 в 09:38

Другие вопросы по тегам sql-server conditional probability intersection independent-set

user5459839 05 мар '16 в 10:13 2016-03-05 10:13 · Accepted Answer · 2016-03-05 10:13

Эта формула несколько верна:

P(AuthorID|CoAuthorID) = P(AuthorID ∩ CoAuthorID) / P(CoAuthorID)

Хотя вы должны также указать ограничение по году, чтобы быть более точным:

P(AuthorID|CoAuthorID ∩ Year) = P(AuthorID ∩ CoAuthorID|Year)/P(CoAuthorID|Year)

Вы даете расчет для P(AuthorID) (или на самом деле P(AuthorID|Year)), но этот термин не встречается в приведенной выше формуле, поэтому он не может быть полезным.

Во-вторых, вы не должны использовать DISTINCT, Если есть много случаев одного и того же автора, то вероятность этого автора следует считать более высокой, чем вероятность другого, менее часто встречающегося автора. Используя DISTINCT Вы бы проигнорировали эти разные частоты и приписали бы каждому автору одинаковую вероятность. Это не может быть намерением.

Вместо этого вы можете определить эти вероятности следующим образом:

`P(CoAuthorID|Year)`

Используйте этот SQL, чтобы получить эту вероятность (от 0 до 1):

SELECT SUM(CASE 
              WHEN CoAuthorID = 901706 THEN 1 
           END)       AS matching_records,
       COUNT(*)       AS considered_records,
       CAST(SUM(CASE 
              WHEN CoAuthorID = 901706 THEN 1 
           END) AS FLOAT)
           / COUNT(*) AS probability  
FROM   AC 
WHERE  Year = 2005

На самом деле это возвращает 3 значения, а вам нужно только третье. Но это поможет в анализе результата, чтобы также включить два первых значения.

Первое значение подсчитывает каждое вхождение данного CoAuthorID в 2005 году ("match_records"), второе подсчитывает общее количество записей за 2005 год ("ised_records"), а третье делит первое на второе, чтобы получить вероятность.

В крайнем случае, все записи 2005 года могут иметь этот CoAuthorID, и тогда вероятность вернется 1.

`P(AuthorID ∩ CoAuthorID|Year)`

Точно так же вы можете сделать следующее для получения этой вероятности:

SELECT SUM(CASE 
              WHEN AuthorID = 677 AND CoAuthorID = 901706 THEN 1 
           END)       AS matching_records,
       COUNT(*)       AS considered_records,
       CAST(SUM(CASE 
              WHEN AuthorID = 677 AND CoAuthorID = 901706 THEN 1 
           END) AS FLOAT)
       / COUNT(*)     AS probability  
FROM   AC 
WHERE  Year = 2005

Обратите внимание на модель, которая появляется: в общем, ограничение Y в P(X|Y) происходит в WHERE пункт, в то время как X представлен в CASE WHEN пункт.

`P(AuthorID|CoAuthorID ∩ Year)`

Хотя вы можете использовать результаты предыдущих двух запросов, вы можете пойти по более прямому пути и использовать шаблон для P(AuthorID|CoAuthorID ∩ Year):

SELECT SUM(CASE 
              WHEN AuthorID = 677 THEN 1 
           END)       AS matching_records,
       COUNT(*)       AS considered_records,
       CAST(SUM(CASE 
              WHEN AuthorID = 677 THEN 1 
           END) AS FLOAT)
       / COUNT(*)     AS probability  
FROM   AC 
WHERE  Year = 2005
AND    CoAuthorID = 901706

Ответ на первый вопрос

Какой должна быть ценность для P(AuthorID ∩ CoAuthorID)? Должно ли это быть 1 или же 1/3?

Поскольку у вас есть 3 строки в ваших данных, для которых совпадают AuthorId, CoAuthorID и Year, P(AuthorID ∩ CoAuthorID|Year) является 3/num_records, где num_records это число или записи, для которых год 2005.

Обратите внимание, что INTERSECT имеет побочный эффект, что устраняет дубликаты в результате. То, что я написал выше относительно DISTINCT также применимо и здесь: вам нужно сосчитать дубликаты при работе с вероятностями.

Приложение: некоторые примеры результатов

Учитывая эти данные:

+----------+------------+-------+
| AuthorID | CoAuthorID | Year  |
+----------+------------+-------+
|  677     | 901706     |  2005 |
|  677     | 901706     |  2005 |
|  677     | 901706     |  2005 |
|  1359    | 133112     |  2005 |
|  1359    | 133112     |  2005 |
|  1359    | 133112     |  2005 |
|  1359    | 266386     |  2005 |
|  1359    | 454557     |  2005 |
|  1359    | 454557     |  2005 |
|  1359    | 454557     |  2005 |
|  1359    | 534423     |  2005 |
|  1359    | 534423     |  2005 |
|  1359    | 534423     |  2005 |
|  1359    | 578338     |  2005 |
|  1359    | 721615     |  2005 |
|  1359    | 1016805    |  2005 |
|  1359    | 1016805    |  2005 |
|  1359    | 1016805    |  2005 |
|  1359    | 1361047    |  2005 |
|  1359    | 1361047    |  2005 |
|  1359    | 1361047    |  2005 |
|  1359    | 1361320    |  2005 |
|  1359    | 1361320    |  2005 |
|  1359    | 1361320    |  2005 |
|  1359    | 1395982    |  2005 |
|  1359    | 1395982    |  2005 |
|  1359    | 1395982    |  2005 |
|  1359    | 1412785    |  2005 |
|  1359    | 1412785    |  2005 |
|  1359    | 1412785    |  2005 |
|  1359    | 1412785    |  2005 |
+----------+------------+-------+

Мы можем посмотреть на следующие вероятности:

1. `P(AuthorID=1359 | CoAuthorID=1361047 ∩ Year=2005)`

Значение: какова вероятность AuthorID=1359 когда дано, что CoAuthor=1361047 а также Year=2005

Неофициально: какая доля сотрудничества соавтора 1361047 была с автором 1359?

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

Количество подходящих коллабораций: 3, потому что этот соавтор трижды сотрудничал с этим автором в 2005 году (т.е. не сотрудничал ни с кем другим)

Вероятность: 3/3 = 1.

2. `P(AuthorID=1359 ∩ CoAuthorID=1361047 | Year=2005)`

Значение: какова вероятность того, что сотрудничество будет между AuthorID=1359 а также CoAuthor=1361047 когда дано, что Year=2005

Неофициально: какая доля сотрудничества была между автором 1359 и соавтором 1361047?

Количество рассмотренных коллабораций: 31, потому что в 2005 году

Количество подходящих коллабораций: 3, потому что этот автор и соавтор сотрудничали три раза в 2005 году

Вероятность: 3/31.

3. `P(CoAuthorID=1361047 | AuthorID=1359 ∩ Year=2005)`

Значение: какова вероятность CoAuthor=1361047 когда дано, что AuthorID=1359 а также Year=2005

Неофициально: какая доля сотрудничества автора 1359 была с соавтором 1361047?

Количество рассматриваемых коллабораций: 28, потому что этот автор сотрудничал 28 раз (с кем бы то ни было) в 2005 году

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

Вероятность: 3/28.

наблюдение

Обратите внимание на то, как похожи три вышеупомянутых предиката, но насколько они действительно различны. Точная формулировка важна. Возьмите, например, эту фразу:

Вероятность того, что автор A и соавтор B будут вместе в 2005 году

Видя это с точки зрения соавтора B, это 100%, потому что этот человек не был соавтором ни с кем другим (случай 1 выше). Но видение этого с точки зрения автора А отличается, так как этот человек сотрудничал также со многими другими соавторами (случай 3 выше), и поэтому вероятность меньше. Но это может быть даже истолковано ни с какой точки зрения: тогда можно было бы посмотреть на все коллаборации в 2005 году и увидеть, сколько раз это было между автором A и соавтором B (случай 2). Это опять другая вероятность.

Это показывает, что утверждения легко могут быть неоднозначными, и нужно быть осторожным, чтобы быть точным: что предполагается как данность? Это должно появиться после символа трубы в P(X|Y) нотации.

Как пересечь две колонки в SQL Server

2 ответа

P(CoAuthorID|Year)

P(AuthorID ∩ CoAuthorID|Year)

P(AuthorID|CoAuthorID ∩ Year)

Ответ на первый вопрос

Приложение: некоторые примеры результатов

1. P(AuthorID=1359 | CoAuthorID=1361047 ∩ Year=2005)

2. P(AuthorID=1359 ∩ CoAuthorID=1361047 | Year=2005)

3. P(CoAuthorID=1361047 | AuthorID=1359 ∩ Year=2005)

наблюдение

`P(CoAuthorID|Year)`

`P(AuthorID ∩ CoAuthorID|Year)`

`P(AuthorID|CoAuthorID ∩ Year)`

1. `P(AuthorID=1359 | CoAuthorID=1361047 ∩ Year=2005)`

2. `P(AuthorID=1359 ∩ CoAuthorID=1361047 | Year=2005)`

3. `P(CoAuthorID=1361047 | AuthorID=1359 ∩ Year=2005)`