# create an auxiliary table
# entity_frequencies_per_year
#
# s_cui, year, frequency
#
# concept_frequencies_per_year
# reference_frequencies_per_year
#
# 2/10/2015
# 2/13/2015 refined with composite index (pmid, s_cui) => 2,855,839 s_cui
# 56 min 39 sec
#
# CREATE TABLE patent_citations_per_year SELECT cite_to_patent_id as patent_id, cite_from_patent_gyr as year, count(*) as citations FROM patent_citations GROUP BY cited_patent_id, year ORDER BY year;
USE semmedver24;
SELECT current_timestamp();
CREATE TABLE _concept_freq_per_year SELECT s_cui as concept_id, pyear as year, count(*) as counts FROM predication_aggregate, citations WHERE citations.pmid=predication_aggregate.pmid GROUP BY s_cui, year ORDER BY year;
SELECT current_timestamp();
