Help with backward citations and patent counts
Posted: Sun Feb 16, 2014 7:13 pm
Hello!
I am currently writing my master thesis in economics. The purpose is to examine the role of inter-industry knowledge spillovers between the technological field of photovoltaic energy and other technologies. To do this I’m using patent citation data and patent counts.
I am however new to SQL and would be most grateful for any support I can get regarding this.
I’m using PATSTAT online and want to download two data sets.
First:
I want to count all patent applications belonging to the three technological fields I refer to as PV, Solar and General, grouped by their priority year. The CPC-classification is used to distinguish the three groups, so that PV is a list of CPC-symbols that is used for photovoltaic energy technology.
The syntax I have come up with is
SELECT COUNT(tls201_appln.appln_id), tls201_appln.prior_earliest_year
FROM tls201_appln, tls224_appln_cpc
WHERE tls224_appln_cpc.appln_id=tls201_appln.appln_id
AND tls224_appln_cpc.cpc_class_symbol like "Y02E10/5%”
OR tls224_appln_cpc.cpc_class_symbo l like "H02N6%"
GROUP BY tls201_appln.prior_earliest_year
Could anyone say if this is correct? When enter this in PATSTAT the search process takes so long time I’m not sure it is ever going to finish. If the data set is too big for the computer to handle I can limit my search to only include EP-patents.
The second is the more tricky one:
I want to count the CPC-classes in each of the three categories PV, Solar and General that are given to patents that have been cited by EP-patents given the CPC-class Y02E10/50, also here grouped by the priority year of the citing patent.
Could anyone help me to find the correct syntax for this?
Necessary conditions to be fulfilled are:
1. Citing patent has the CPC-class="Y02E10/50":
- tls224_appln_cpc.cpc_class_symbol= "Y02E10/50" --
2.. Citing patent is an EP application of type A1 or A2:
- tls201_appln.appln_kind= ‘A1 ‘ or tls201_appln.appln_kind= ‘A2’
3. Cited document is a patent
- tls212_citation.pat_citn_seq_nr<>0
4. no duplicates among cited (per each citing)
- COUNT(DISTINCT(`CITED_PAT_PUBLN_ID`)) FROM `TLS212_CITATION`
I would be truly grateful if someone could help me with just a little piece of this.
Best regards,
Hanna
I am currently writing my master thesis in economics. The purpose is to examine the role of inter-industry knowledge spillovers between the technological field of photovoltaic energy and other technologies. To do this I’m using patent citation data and patent counts.
I am however new to SQL and would be most grateful for any support I can get regarding this.
I’m using PATSTAT online and want to download two data sets.
First:
I want to count all patent applications belonging to the three technological fields I refer to as PV, Solar and General, grouped by their priority year. The CPC-classification is used to distinguish the three groups, so that PV is a list of CPC-symbols that is used for photovoltaic energy technology.
The syntax I have come up with is
SELECT COUNT(tls201_appln.appln_id), tls201_appln.prior_earliest_year
FROM tls201_appln, tls224_appln_cpc
WHERE tls224_appln_cpc.appln_id=tls201_appln.appln_id
AND tls224_appln_cpc.cpc_class_symbol like "Y02E10/5%”
OR tls224_appln_cpc.cpc_class_symbo l like "H02N6%"
GROUP BY tls201_appln.prior_earliest_year
Could anyone say if this is correct? When enter this in PATSTAT the search process takes so long time I’m not sure it is ever going to finish. If the data set is too big for the computer to handle I can limit my search to only include EP-patents.
The second is the more tricky one:
I want to count the CPC-classes in each of the three categories PV, Solar and General that are given to patents that have been cited by EP-patents given the CPC-class Y02E10/50, also here grouped by the priority year of the citing patent.
Could anyone help me to find the correct syntax for this?
Necessary conditions to be fulfilled are:
1. Citing patent has the CPC-class="Y02E10/50":
- tls224_appln_cpc.cpc_class_symbol= "Y02E10/50" --
2.. Citing patent is an EP application of type A1 or A2:
- tls201_appln.appln_kind= ‘A1 ‘ or tls201_appln.appln_kind= ‘A2’
3. Cited document is a patent
- tls212_citation.pat_citn_seq_nr<>0
4. no duplicates among cited (per each citing)
- COUNT(DISTINCT(`CITED_PAT_PUBLN_ID`)) FROM `TLS212_CITATION`
I would be truly grateful if someone could help me with just a little piece of this.
Best regards,
Hanna