Count Patent based on Assignee Country

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

a497652958
Posts: 4
Joined: Tue Jun 26, 2018 6:39 pm

Count Patent based on Assignee Country

Post by a497652958 » Tue Oct 09, 2018 12:51 am

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


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

Re: Count Patent based on Assignee Country

Post by EPO / PATSTAT Support » Tue Oct 09, 2018 9:08 am

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.

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
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 ?)

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


Post Reply