Patent count & forward citations for entire CPC category
Posted: Fri Jul 03, 2015 5:42 am
Hi,
I would like to have some very rough idea of the state of innovation in specific technological fields. I have two goals:
1. Observe the patenting trends for an entire CPC category
2. Calculate average forward citations per patent family for an entire CPC category
I'm new to SQL and Patstats so unsurprisingly I've run into some difficulties.
I've tried queries such as this:
which, just to confirm, I gives me a table with:
a) How do I merge the citation data so that I get one row per family? I'm not interested in keeping the appln_id in the final output but I understand it's necessary to use it... yet it also seems to be causing problems. I tried using GROUP BY, which gives me the following "Error Code: 8120, SQL State: S0001] Column 'tls224_appln_cpc.appln_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
b) Can I use the aggregate function AVG for my purposes or am I better off exporting the data to Excel?
In addition, I have an odd problem:
c) if I replace the category by a more precise one, such as 'Y02E 10/72%', results are 0.I've checked WIPO Standards and Patstat's instructions for this to no avail. Does anyone have any examples of how this works?
Any comments on any aspect of this would be most welcome!
Thank you very much.
Best regards
Alfonso
I would like to have some very rough idea of the state of innovation in specific technological fields. I have two goals:
1. Observe the patenting trends for an entire CPC category
2. Calculate average forward citations per patent family for an entire CPC category
I'm new to SQL and Patstats so unsurprisingly I've run into some difficulties.
I've tried queries such as this:
Code: Select all
SELECT DISTINCT t.appln_id, nb_citing_docdb_fam, prior_earliest_date, a.docdb_family_id
FROM tls224_appln_cpc t
JOIN tls201_appln ap ON ap.appln_id = t.appln_id
JOIN tls218_docdb_fam a ON t.appln_id = a.appln_id
JOIN tls228_docdb_fam_citn c ON c.docdb_family_id = a.docdb_family_id
WHERE t.cpc_class_symbol LIKE 'Y02C%'
AND ap.nb_citing_docdb_fam > 0
- all applications that have been cited at least once (also implying they're granted, right?)
the patent families they belong to,
the number of forward citations per application,
the first date the first application was filed.
a) How do I merge the citation data so that I get one row per family? I'm not interested in keeping the appln_id in the final output but I understand it's necessary to use it... yet it also seems to be causing problems. I tried using GROUP BY, which gives me the following "Error Code: 8120, SQL State: S0001] Column 'tls224_appln_cpc.appln_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
b) Can I use the aggregate function AVG for my purposes or am I better off exporting the data to Excel?
In addition, I have an odd problem:
c) if I replace the category by a more precise one, such as 'Y02E 10/72%', results are 0.I've checked WIPO Standards and Patstat's instructions for this to no avail. Does anyone have any examples of how this works?
Any comments on any aspect of this would be most welcome!
Thank you very much.
Best regards
Alfonso