1

Please login or register to vote for this query.

(click on this box to dismiss)

It calculates the angular correlation coefficients between the tag distributions of all sites in the network. It is also usable to find the most similar sites to a specified one (according to their tag distribution), which can be an argument in debates about the site migration pathes.

Q&A for amateur radio enthusiasts

DECLARE @tagDist TABLE (
  DbName VARCHAR(MAX) NOT NULL,
  Tag NVARCHAR(MAX) NOT NULL,
  Count BIGINT NOT NULL
);

DECLARE @query VARCHAR(MAX);

DECLARE @SiteLang TABLE (
  DbName VARCHAR(MAX) NOT NULL,
  Lang CHAR(2)
);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE
  name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange');

DECLARE @dbname VARCHAR(MAX);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO @SiteLang (DbName, Lang) VALUES (@dbname,
    CASE
      WHEN @dbname IN (
        'StackOverflow.Br',
        'StackOverflow.Br.Meta',
        'StackExchange.Portuguese',
        'StackExchange.Portuguese.Meta')
      THEN 'pt'

      WHEN @dbname IN (
        'StackExchange.Japanese',
        'StackExchange.Japanese.Meta',
        'StackOverflow.Ja',
        'StackOverflow.Ja.Meta')
      THEN 'ja'

      WHEN @dbname IN (
        'StackOverflow.Es',
        'StackOverflow.Es.Meta',
        'StackExchange.Spanish',
        'StackExchange.Spanish.Meta')
      THEN 'es'

      WHEN @dbname IN (
        'StackOverflow.Ru',
        'StackOverflow.Ru.Meta',
        'StackExchange.Rus',
        'StackExchange.Rus.Meta')
      THEN 'ru'

      WHEN @dbname IN (
        'StackExchange.Ukrainian',
        'StackExchange.Ukrainian.Meta')
      THEN 'uk'

      WHEN @dbname IN (
        'StackExchange.German',
        'StackExchange.German.Meta')
      THEN 'de'

      WHEN @dbname IN (
        'StackExchange.Chinese',
        'StackExchange.Chinese.Meta')
      THEN 'cn'

      WHEN @dbname IN (
        'StackExchange.Arabic',
        'StackExchange.Arabic.Meta')
      THEN 'ar'

      WHEN @dbname IN (
        'StackExchange.Latin',
        'StackExchange.Latin.Meta')
      THEN 'la'

      WHEN @dbname IN (
        'StackExchange.Italian',
        'StackExchange.Italian.Meta')
      THEN 'it'

      WHEN @dbname IN (
        'StackExchange.French',
        'StackExchange.French.Meta')
      THEN 'fr'

      WHEN @dbname IN (
        'StackExchange.Korean',
        'StackExchange.Korean.Meta')
      THEN 'ko'

      WHEN @dbname IN (
        'StackExchange.Esperanto',
        'StackExchange.Esperanto.Meta')
      THEN 'eo'

      ELSE 'en'
    END
  );

  SELECT @query = '
    SELECT
      ''' + @dbname + ''' AS DbName,
       Tag,
       SUM(Count) AS Count
    FROM (
      SELECT
        TagName AS Tag,
        Count FROM [' + @dbname + '].dbo.Tags
      UNION
      SELECT
        TagSynonyms.SourceTagName AS Tag,
        Tags.Count
      FROM [' + @dbname + '].dbo.TagSynonyms, [' + @dbname + '].dbo.Tags
      WHERE TagSynonyms.TargetTagName = Tags.TagName
    ) AS q
    GROUP BY Tag
    HAVING SUM(Count) > 0
  ';
  INSERT INTO @tagDist EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH SiteToDb AS (
  SELECT
    name AS DbName,
    LOWER(CASE
      WHEN
        name = 'StackExchange.Programmers'
      THEN
        'softwareeengineering.stackexchange.com'
      WHEN
        name = 'StackExchange.Programmers.Meta'
      THEN
        'softwareengineering.meta.stackexchange.com'
      WHEN
        name LIKE 'StackExchange.%'
      THEN
        RIGHT(name, LEN(name) - 14) + '.stackexchange.com'
      WHEN
        name = 'StackOverflow.Br'
      THEN
        'pt.stackoverflow.com'
      WHEN
        name = 'StackOverflow.Br.Meta'
      THEN
        'pt.meta.stackoverflow.com'
      WHEN
        name LIKE 'StackOverflow.__'
      THEN
        RIGHT(name, 2) + '.stackoverflow.com'
      WHEN
        name LIKE 'StackOverflow.__.Meta'
      THEN
        RIGHT(name, 7) + '.stackoverflow.com'
      WHEN
        name = 'StackOverflow'
      THEN
        'stackoverflow.com'
      WHEN
        name = 'StackOverflow.Meta'
      THEN
        'meta.stackoverflow.com'
      WHEN
        name = 'StackApps'
      THEN
        'stackapps.com'
      WHEN
        name = 'ServerFault'
      THEN
        'serverfault.com'
      WHEN
        name = 'ServerFault.Meta'
      THEN
        'meta.serverfault.com'
      WHEN
        name = 'SuperUser'
      THEN
        'superuser.com'
      WHEN
        name = 'SuperUser.Meta'
      THEN
        'meta.superuser.com'
      ELSE
        NULL
      END
    ) AS SiteName
  FROM sysdatabases
  WHERE
    name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
),
TagLength AS (
  SELECT
    DbName,
    SUM(tagDist.Count * tagDist.Count) AS Length
  FROM @tagDist AS tagDist
  GROUP BY DbName
),
CorrM AS (
  SELECT
    a.DbName AS Db1,
    b.DbName AS Db2,
    SUM(a.Count * b.Count) AS SMul
  FROM
    @tagDist AS a,
    @tagDist AS b
  WHERE
    a.DbName < b.DbName
    AND
    a.Tag = b.Tag
  GROUP BY a.DbName, b.DbName
)
SELECT
  SiteDbA.SiteName AS Site1,
  SiteA.Lang,
  SiteDbB.SiteName AS Site2,
  SiteB.Lang,

  ACOS(1.0 * SMul / SQRT(aDist.Length) / SQRT(bDist.Length))
  / 3.14159265358979 * 180 AS CorrelationAngle,

  aDist.Length AS LengthSq1,
  bDist.Length AS LengthSq2,
  SMul
FROM
  CorrM,
  TagLength AS aDist, TagLength AS bDist,
  @SiteLang SiteA,
  @SiteLang SiteB,
  SiteToDb SiteDbA,
  SiteToDb SiteDbB
WHERE
  CorrM.Db1 = aDist.DbName
  AND
  CorrM.Db2 = bDist.DbName
  AND
  (
    ##WithMetaSites:int?0## <> 0
    OR
    (
      SiteDbA.SiteName NOT LIKE '%meta%'
      AND
      SiteDbB.SiteName NOT LIKE '%meta%'
    )
  )
  AND
  (
    ##SiteFilter:string?-## = '-'
    OR
    LOWER(SiteDbA.SiteName) LIKE LOWER('%' + ##SiteFilter## + '%')
    OR
    LOWER(SiteDbB.SiteName) LIKE LOWER('%' + ##SiteFilter## + '%')
  )
  AND
  (
    ##SiteFilterNeg:string?-## = '-'
    OR
    (
      LOWER(SiteDbA.SiteName) NOT LIKE LOWER('%' + ##SiteFilterNeg## + '%')
      AND
      LOWER(SiteDbB.SiteName) NOT LIKE LOWER('%' + ##SiteFilterNeg## + '%')
    )
  )
  AND SiteA.DbName = aDist.DbName
  AND SiteB.DbName = bDist.DbName
  AND SiteDbA.DbName = aDist.DbName
  AND SiteDbB.DbName = bDist.DbName
  AND
  (
    ##OnlySameLanguage:int?1## = 0
    OR
    SiteA.Lang = SiteB.Lang
  )
ORDER BY CorrelationAngle

fork query

edited jul 8 17

created jun 15 17 peterh