‘multi-threading’ shows a relation to ‘python’, ‘java’, ‘c#’, and ‘android`. ‘machine-learning’ shows a relation to ‘python’, but not the other way around. ‘javascript’ shows a relation to ‘php’, ‘html’, ‘css’, ‘node.js’, and ‘jquery’. What you see here is a co-occurrence matrix: Let’s see first a subset of these results: Then I can use that string to get a huge table, with a 1 for every time a tag co-occurs with the main one at least certain % of time. So I’m going to create a string first that will define all the columns where I want to find co-occurrence. BigQuery ML does a good job of hot-encoding strings, but it doesn’t handle arrays as I wish it did (stay tuned). CREATE OR REPLACE TABLE `deleting.stack_overflow_tag_co_ocurrence`ASWITH data AS ( SELECT * FROM `fh-bigquery.stackoverflow_archive.201906_posts_questions` WHERE creation_date > ''), active_tags AS ( SELECT tag, COUNT(*) c FROM data, UNNEST(SPLIT(tags, '|')) tag GROUP BY 1 HAVING c>180)SELECT *, questions/questions_tag1 percentFROM ( SELECT *, MAX(questions) OVER(PARTITION BY tag1) questions_tag1 FROM ( SELECT tag1, tag2, COUNT(*) questions FROM data, UNNEST(SPLIT(tags, '|')) tag1, UNNEST(SPLIT(tags, '|')) tag2 WHERE tag1 IN (SELECT tag FROM active_tags) AND tag2 IN (SELECT tag FROM active_tags) GROUP BY 1,2 HAVING questions>30 )) #Telerik treeview plus
So I’ll take these relationships and I’ll save them on an auxiliary table - plus a percentage of how frequently a relationship happens for each tag. Let’s find tags that usually go together:
# Tags with >180 questions since 2018SELECT tag, COUNT(*) questionsFROM `fh-bigquery.stackoverflow_archive.201906_posts_questions`, UNNEST(SPLIT(tags, '|')) tagWHERE creation_date > ''GROUP BY 1HAVING questions>180ORDER BY 2 DESC Hint: Co-occurring tags
0 kommentar(er)
