Daer PATSTAT Support,
I em experiencing difficulty when I try to perform counting patents based on Assignee Country. For example, when I tried to find all the patent issued by the Japanese Patent Office to Japanese companies in 2005, I used the following code:
select a1.appln_id, tls206_person.person_ctry_code
--COUNT(distinct(case when tls211_pat_publn.publn_date = 2005 then ap.appln_id end)) as "2005"
from tls201_appln a1
left join tls207_pers_appln tls207_pers_appln on a1.appln_id = tls207_pers_appln.appln_id
left join tls206_person tls206_person on tls206_person.person_id = tls207_pers_appln.person_id
left join tls211_pat_publn a2 on a2.appln_id = a1.appln_id
where a2.publn_auth = 'JP'
and a2.publn_date between '2005-01-01' and '2005-12-31'
--and tls206_person.person_ctry_code = 'JP'
and a2.publn_first_grant = '1'
Specifically, when I select tls206_person.person_ctry_code, the returned country code column in the table was almost blank. Is it because the information was not reported to the database? Or is is because my code is wrong?
Best regards,
Murry
Count Patent based on Assignee Country
-
- Posts: 425
- Joined: Thu Feb 22, 2007 5:33 pm
- Contact:
Re: Count Patent based on Assignee Country
Hello Murry,
your query is correct; it illustrates that we have no country information for applicants and inventors on applications filed in Japan.
As a comparison, here is an SQL query that gives an easy overview of as well JP as EP applications, counting the number person_id's that have (no) country code.
A possible work around to replenish some of the data might be to look at the data of the family members, and I would in first hand think about the EP patent family member (if there is one), or the priority filing.
Doing this systematically will of course bias the "more valuable" inventions where the applicant decided that protection in several patent jurisdictions is useful, and it will of course not work for inventions without family members.
Below is some SQL that illustrates it for 1 single JP application. Doing this for batches requires PATSTAT to be installed on a local server because one would store this in formation in intermediate tables.
The condition in the WHERE clause is also very strict: (fam_pers.psn_name = tls206_person.psn_name
). Increasing recall (and losing a bit of precision) via for example Levenshtein distance calculations on the applicant names will probably yield more results. (Anybody using AI to replenish data across patent families ?)
your query is correct; it illustrates that we have no country information for applicants and inventors on applications filed in Japan.
As a comparison, here is an SQL query that gives an easy overview of as well JP as EP applications, counting the number person_id's that have (no) country code.
Code: Select all
select a1.appln_auth ,a1.appln_filing_year ,
COUNT(distinct(case when tls206_person.person_ctry_code = '' then tls206_person.person_id end)) as "without_ctry",
COUNT(distinct(case when tls206_person.person_ctry_code <> '' then tls206_person.person_id end)) as "with_ctry"
from tls201_appln a1
join tls207_pers_appln tls207_pers_appln on a1.appln_id = tls207_pers_appln.appln_id
join tls206_person tls206_person on tls206_person.person_id = tls207_pers_appln.person_id
where a1.appln_auth in ('JP', 'EP') and a1.appln_filing_year between 2000 and 2017
group by a1.appln_filing_year, a1.appln_auth
order by a1.appln_auth, a1.appln_filing_year
Doing this systematically will of course bias the "more valuable" inventions where the applicant decided that protection in several patent jurisdictions is useful, and it will of course not work for inventions without family members.
Below is some SQL that illustrates it for 1 single JP application. Doing this for batches requires PATSTAT to be installed on a local server because one would store this in formation in intermediate tables.
The condition in the WHERE clause is also very strict: (fam_pers.psn_name = tls206_person.psn_name
). Increasing recall (and losing a bit of precision) via for example Levenshtein distance calculations on the applicant names will probably yield more results. (Anybody using AI to replenish data across patent families ?)
Code: Select all
SELECT tls201_appln.appln_nr_epodoc, tls201_appln.docdb_family_id, tls206_person.psn_name, tls206_person.person_address ,tls206_person.person_ctry_code,
fam_pers.psn_name name_from_family , fam_pers.person_ctry_code ctry_code_from_family, fam.appln_nr_epodoc
FROM tls201_appln
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 tls201_appln fam on tls201_appln.docdb_family_id = fam.docdb_family_id
join tls207_pers_appln fam_ap on fam.appln_id = fam_ap.appln_id
join tls206_person fam_pers on fam_ap.person_id = fam_pers.person_id and fam_pers.psn_name = tls206_person.psn_name
where tls201_appln.appln_id = 38913916 and tls207_pers_appln.applt_seq_nr > 0
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org
EPO - Vienna
patstat @ epo.org