1

Please login or register to vote for this query.

(click on this box to dismiss)

For the list of non-series tags, see: https://data.stackexchange.com/anime/query/1863699.

Q&A for scientists using computers to solve scientific problems

--Types: Types of tags to ignore, e.g. 'regions', 'general'; 
-- input '', '' to not ignore any type.
CREATE TABLE #NonSeriesTags (
    TagType nvarchar(50)
  , TagName nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS
)
INSERT INTO #NonSeriesTags VALUES
    ('characters', 'domo-kun')
  , ('characters', 'hatsune-miku')
  , ('characters', 'os-tan')
  , ('characters', 'super-sonico')

  , ('companies', 'anime-planet')
  , ('companies', 'crunchyroll')
  , ('companies', 'funimation')
  , ('companies', 'gainax')
  , ('companies', 'key')
  , ('companies', 'kyoto-animation')
  , ('companies', 'madhouse')
  , ('companies', 'myanimelist')
  , ('companies', 'netflix')
  , ('companies', 'pixiv')
  , ('companies', 'sentai-filmworks')
  , ('companies', 'studio-ghibli')
  , ('companies', 'type-moon')

  , ('conventions', 'anime-expo')
  , ('conventions', 'comiket')

  , ('general', 'amv')
  , ('general', 'anime-history')
  , ('general', 'anime-industry')
  , ('general', 'anime-production')
  , ('general', 'censorship')
  , ('general', 'conventions')
  , ('general', 'copyright')
  , ('general', 'cosplay')
  , ('general', 'crossovers')
  , ('general', 'culture')
  , ('general', 'doujinshi')
  , ('general', 'events')
  , ('general', 'game-history')
  , ('general', 'identification-request')
  , ('general', 'itasha')
  , ('general', 'japanese-language')
  , ('general', 'law')
  , ('general', 'licensing')
  , ('general', 'light-novel-history')
  , ('general', 'light-novel-production')
  , ('general', 'light-novel-site')
  , ('general', 'live-action-production')
  , ('general', 'localization')
  , ('general', 'mangaka')
  , ('general', 'manga-history')
  , ('general', 'manga-production')
  , ('general', 'meme')
  , ('general', 'merchandise')
  , ('general', 'music')
  , ('general', 'panel-arrangement')
  , ('general', 'promotional-videos')
  , ('general', 'quotes')
  , ('general', 'resources')
  , ('general', 'sign-language')
  , ('general', 'streaming-sites')
  , ('general', 'symbolism')
  , ('general', 'terminology')
  , ('general', 'theme-song')
  , ('general', 'tourism')
  , ('general', 'tropes')
  , ('general', 'visual-novel-production')
  , ('general', 'voice-acting')

  , ('other-reified', 'anime-no-chikara')
  , ('other-reified', 'vocaloid')

  , ('people', 'aimer')
  , ('people', 'barasui')
  , ('people', 'claris')
  , ('people', 'hayao-miyazaki')
  , ('people', 'hideaki-anno')
  , ('people', 'hiroyuki-sawano')
  , ('people', 'jun-maeda')
  , ('people', 'kajiura-yuki')
  , ('people', 'kazuki-nakashima')
  , ('people', 'kazuki-takahashi')
  , ('people', 'ken-akamatsu')
  , ('people', 'kouji-seo')
  , ('people', 'maaya-sakamoto')
  , ('people', 'makoto-shinkai')
  , ('people', 'mamiko-noto')
  , ('people', 'mamoru-hosoda')
  , ('people', 'mamoru-oshii')
  , ('people', 'masamune-shirow')
  , ('people', 'sachika-misawa')
  , ('people', 'shun-saeki')

  , ('publications', 'ribon')
  , ('publications', 'shonen-jump')

  , ('regions', 'australia')
  , ('regions', 'england')
  , ('regions', 'europe')
  , ('regions', 'germany')
  , ('regions', 'greece')
  , ('regions', 'india')
  , ('regions', 'japan')
  , ('regions', 'north-korea')
  , ('regions', 'singapore')
  , ('regions', 'spain')
  , ('regions', 'united-states')

  , ('special', 'retag')
;
WITH NonSeriesTags AS (
    SELECT
          #NonSeriesTags.TagType
        , #NonSeriesTags.TagName
        , TagSynonyms.SourceTagName 
    FROM #NonSeriesTags
    LEFT JOIN TagSynonyms
        ON #NonSeriesTags.TagName = TagSynonyms.TargetTagName
)
SELECT 
      Posts.Id AS [Post Link]
    , Posts.Tags
    , NonSeriesTags.TagName
    , COUNT(TagName) OVER (PARTITION BY TagName ORDER BY TagName) 
        AS TagSuggestCount
FROM Posts
INNER JOIN NonSeriesTags
    ON Posts.PostTypeId = 1
    AND NonSeriesTags.TagType NOT IN (##Types##)
    AND Posts.Tags NOT LIKE '%<' + NonSeriesTags.TagName + '>%'
    AND (
        LOWER(Posts.Title) LIKE '%[^a-zA-Z]' 
            + REPLACE(NonSeriesTags.TagName, '-', ' ') 
            + '[^a-zA-Z]%' 
        OR LOWER(Posts.Body) LIKE '%[^a-zA-Z]' 
            + REPLACE(NonSeriesTags.TagName, '-', ' ') 
            + '[^a-zA-Z]%' 
        /* May comment out the 2 OR conditions below to increase performance 
           and reduce false positives, though this may introduce false 
           negatives.*/
        OR (
            NonSeriesTags.SourceTagName IS NOT NULL 
            AND LOWER(Posts.Title) LIKE '%[^a-zA-Z]' 
                + REPLACE(NonSeriesTags.SourceTagName, '-', ' ') 
                + '[^a-zA-Z]%'
        )
        OR (
            NonSeriesTags.SourceTagName IS NOT NULL 
            AND LOWER(Posts.Body) LIKE '%[^a-zA-Z]' 
                + REPLACE(NonSeriesTags.SourceTagName, '-', ' ') 
                + '[^a-zA-Z]%'
        )
    )
GROUP BY
      Posts.Id
    , Posts.Tags
    , NonSeriesTags.TagName
ORDER BY 
      Posts.Id
    , NonSeriesTags.TagName

view current version

edited oct 2

created oct 1 Gao