Combining CPC-classes & keywords

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

lennartschott
Posts: 6
Joined: Mon Jun 01, 2020 3:45 pm

Combining CPC-classes & keywords

Post by lennartschott » Thu Jun 11, 2020 3:59 pm

Hello everyone,

I would like to run a query on PATSTAT that combines CPC classes with certain keywords that could be found in the respective abstract. As a result, I would like to retrieve a table with the columns family_id, earliest_filing_year (everything from 2000 on), person_country_code, CPC-classes (grouped/aggregated into one cell).
The CPC-classes I am looking for are:
  • G05D 1/021/% or
  • G05D 1/02 or
  • G01S 17/936 or
  • G01S 17/93 or
  • Y02T 90/%
I would like to retrieve all family_ids on which these CPC-classes are combined with one of the following words in the abstract: autonomous or unmanned AND ground or street. Furthermore, I would like to exclude IDs with the words air, aer, drone, flight, flies or fly in the abstract.

I tried this query, but it does not seem to be working:

Code: Select all

SELECT DISTINCT tls201_appln.docdb_family_id, earliest_filing_year, tls206_person.person_ctry_code, CPC.CPCgrouped
FROM tls201_appln
JOIN TLS203_APPLN_ABSTR ON tls201_appln.appln_id = TLS203_APPLN_ABSTR.appln_id
JOIN tls207_pers_appln ON tls201_appln.appln_id = tls207_pers_appln.appln_id
JOIN tls206_person ON tls207_pers_appln.person_id= tls206_person.person_id
JOIN (select docdb_family_id , STRING_AGG(CAST(cpc_class_symbol AS VARCHAR(MAX)), ',')CPCgrouped from tls225_docdb_fam_cpc group by docdb_family_id ) CPC ON tls201_appln.docdb_family_id = CPC.docdb_family_id
WHERE tls201_appln.earliest_filing_year >= 2000
AND tls201_appln.earliest_filing_year < 9999
AND cpc.docdb_family_id in (select distinct docdb_family_id from tls225_docdb_fam_cpc where cpc_class_symbol = 'G05D   1/021'
OR cpc_class_symbol = 'G05D   1/02'
OR cpc_class_symbol = 'G01S  17/936'
OR cpc_class_symbol = 'G01S  17/93'
OR cpc_class_symbol LIKE 'Y02T  90/%')
AND (applt_seq_nr > 0)
AND CONTAINS (appln_abstract, 'autonomous')
OR CONTAINS (appln_abstract, 'unmanned')
AND CONTAINS (appln_abstract, 'ground')
OR CONTAINS (appln_abstract, 'street')
AND CONTAINS NOT (appln_abstract, 'air')
OR CONTAINS NOT (appln_abstract, 'aer')
OR CONTAINS NOT (appln_abstract, 'drone')
OR CONTAINS NOT (appln_abstract, 'flight')
OR CONTAINS NOT (appln_abstract, 'flies')
OR CONTAINS NOT(appln_abstract, 'fly')
group by tls201_appln.docdb_family_id, earliest_filing_year, tls206_person.person_ctry_code, CPC.CPCgrouped
order by tls201_appln.docdb_family_id, tls206_person.person_ctry_code
I would appreciate any help. Thanks in advance!

Best,
L.


EPO / PATSTAT Support
Posts: 216
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Combining CPC-classes & keywords

Post by EPO / PATSTAT Support » Fri Jun 12, 2020 9:25 pm

Hello Lennart,
there were a couple of brackets missing in your WHERE clause.

Code: Select all

SELECT DISTINCT tls201_appln.docdb_family_id, earliest_filing_year, tls206_person.person_ctry_code, CPC.CPCgrouped
FROM tls201_appln
JOIN TLS203_APPLN_ABSTR ON tls201_appln.appln_id = TLS203_APPLN_ABSTR.appln_id
JOIN tls207_pers_appln ON tls201_appln.appln_id = tls207_pers_appln.appln_id
JOIN tls206_person ON tls207_pers_appln.person_id= tls206_person.person_id
JOIN (select docdb_family_id , STRING_AGG(CAST(cpc_class_symbol AS VARCHAR(MAX)), ',')CPCgrouped from tls225_docdb_fam_cpc group by docdb_family_id ) CPC ON tls201_appln.docdb_family_id = CPC.docdb_family_id
WHERE tls201_appln.earliest_filing_year >= 2000
AND tls201_appln.earliest_filing_year < 9999
AND cpc.docdb_family_id in (select distinct docdb_family_id from tls225_docdb_fam_cpc where cpc_class_symbol = 'G05D   1/021'
OR cpc_class_symbol = 'G05D   1/02'
OR cpc_class_symbol = 'G01S  17/936'
OR cpc_class_symbol = 'G01S  17/93'
OR cpc_class_symbol LIKE 'Y02T  90/%')
AND (applt_seq_nr > 0)
AND ( CONTAINS (appln_abstract, 'autonomous')
OR CONTAINS (appln_abstract, 'unmanned'))
AND ( CONTAINS (appln_abstract, 'ground')
OR CONTAINS (appln_abstract, 'street'))
AND (NOT CONTAINS (appln_abstract, 'air')
OR NOT CONTAINS  (appln_abstract, 'aer')
OR NOT CONTAINS  (appln_abstract, 'drone')
OR  NOT CONTAINS  (appln_abstract, 'flight')
OR NOT  CONTAINS  (appln_abstract, 'flies')
OR  NOT CONTAINS (appln_abstract, 'fly'))
group by tls201_appln.docdb_family_id, earliest_filing_year, tls206_person.person_ctry_code, CPC.CPCgrouped
order by tls201_appln.docdb_family_id, tls206_person.person_ctry_code
Keep in mind that 1 patent family will have multiple "country codes" and possibly multiple " earliest_filing_year"s.

Your query does not return many results, in such case it might be better to simply extract the data and then do any further counting and aggregation outside PATSTAT.
You could use the query below to create your extraction. (...but maybe it is worth to have another look at your WHERE clause to see if it is really what you want...)

Code: Select all

SELECT DISTINCT tls201_appln.appln_id
FROM tls201_appln
JOIN TLS203_APPLN_ABSTR ON tls201_appln.appln_id = TLS203_APPLN_ABSTR.appln_id
WHERE tls201_appln.earliest_filing_year >= 2000
AND tls201_appln.earliest_filing_year < 9999
AND tls201_appln.docdb_family_id in 
(select distinct docdb_family_id from tls225_docdb_fam_cpc where cpc_class_symbol = 'G05D   1/021'
OR cpc_class_symbol = 'G05D   1/02'
OR cpc_class_symbol = 'G01S  17/936'
OR cpc_class_symbol = 'G01S  17/93'
OR cpc_class_symbol LIKE 'Y02T  90/%')
AND ( CONTAINS (appln_abstract, 'autonomous')
OR CONTAINS (appln_abstract, 'unmanned'))

AND ( CONTAINS (appln_abstract, 'ground')
OR CONTAINS (appln_abstract, 'street'))

AND (NOT CONTAINS (appln_abstract, 'air')
OR NOT CONTAINS  (appln_abstract, 'aer')
OR NOT CONTAINS  (appln_abstract, 'drone')
OR  NOT CONTAINS  (appln_abstract, 'flight')
OR NOT  CONTAINS  (appln_abstract, 'flies')
OR  NOT CONTAINS (appln_abstract, 'fly'))
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply