How to get number of CPC patents by person_ctry_code?

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

sejin_an
Posts: 4
Joined: Tue Feb 26, 2019 3:49 am

How to get number of CPC patents by person_ctry_code?

Post by sejin_an » Tue Feb 26, 2019 6:51 am

Dear users.

I'm new in PATSTAT, and struggling to get some data of "specific CPC patent" by "person_ctry_code".
to get some insight from the global status of the patent activity.
By doing this, I tried to merge 3 different table of 'tls201_appln' with 'tls224_appln_cpc', 'tls226_person_orig'.
I have written some queries but not working.
"invalid column name 'appln_id'."

Please give me some idea.
my codes are as belows.

Select *
from
tls224_appln_cpc a join tls201_appln b on a.appln_id = b.appln_id
join tls226_person_orig c on a.appln_id = c.appln_id
where cpc_class_symbol like 'Y02%'


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

Re: How to get number of CPC patents by person_ctry_code?

Post by EPO / PATSTAT Support » Tue Feb 26, 2019 6:14 pm

You got the error message "invalid column name 'appln_id' because "c.appln_id" (which is your alias for "tls226_person_orig.appln_id" does not exist: There is no attribute APPLN_ID in the table TLS226_PERSON_ORIG.

I suggest to check the PATSTAT Data Catalog on http://epo.org/patstat. In the graphical data model in section 3 you see that to combine applications (in table TLS201_APPLN) and persons (in TLS206_PERSON or TLS906_PERSON) you must go via the table TLS207_PERS_APPLN. (Note: Using TLS206/TLS906 is easier than TLS226_PERSON_ORIG. This is usually sufficient, unless you want to dive into the depths of name & address analysis.)
Usually you join 2 tables - like you did - via their common attributes. More generally and correctly, you join tables via the foreign key (FK) as indicated in the diagram.

The query you are looking for might be this:

Code: Select all

select *
from tls224_appln_cpc a 
join tls201_appln b on a.appln_id = b.appln_id
join tls207_pers_appln pa on a.appln_id = pa.appln_id
join tls206_person p on pa.person_id = p.person_id
where cpc_class_symbol like 'Y02%'
order by a.appln_id
It retrieves more than 19 million rows in the PATSTAT 2018 Autumn Edition, because your query is quite general. So the query execution will take some minutes.
Note that an application might be represented by multiple rows, because each row represents a combination of application x IPC code Y02.. x persons (applicants and inventors).

As you are new to PATSTAT, working to the self study guide "Using PATSTAT with SQL for beginners" might be a good way to start.

Have success,
Martin / EPO PATSTAT


sejin_an
Posts: 4
Joined: Tue Feb 26, 2019 3:49 am

Re: How to get number of CPC patents by person_ctry_code?

Post by sejin_an » Mon Mar 04, 2019 6:42 am

Dear EPO support manager.

Thank you so much for your help.
I can grab some idea for my analysis, I've successfully run the query.

However, It causes me another question when I have checked results.
Person_ctry_code is not sufficient for my research and is seen that I need to check applicants address.
so I need tls226_person_orig as reference table and need to get address data.

Could you give me some idea about the address analysis?
and If I use PATSTAT online subscription, can I parse the address in country level?
(or Do I need bulk data?)
Thank you for your kind support.

Sejin An


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

Re: How to get number of CPC patents by person_ctry_code?

Post by EPO / PATSTAT Support » Thu Mar 07, 2019 9:20 am

Hello An,
addresses from "persons" that have PSN_SECTOR = "individual" have been removed from PATSTAT Online.
Generally spoken, address information is only available for a very limited number of patent offices.
PATSTAT Online users can not change or create extra tables. The only option is to download a PATSTAT subset or purchase PATSTAT Global an install it on a local data base platform so that you have the full freedom to manipulate and change data in the tables. PATSTAT Global still contains address data for individuals where such is available at the source.

Some researchers have developed models to migrate country data from one person to another person based on for example family members, priority filings, statistical analysis etc...
Attached you will find 2 papers explaining how it can be done (comes as-is):
new patent indicator.pdf
(238.54 KiB) Downloaded 133 times
Geert Boedt
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


sejin_an
Posts: 4
Joined: Tue Feb 26, 2019 3:49 am

Re: How to get number of CPC patents by person_ctry_code?

Post by sejin_an » Mon Mar 11, 2019 7:03 am

Dear Patstat manager,

Thank you so much for your kind reply.
I've successfully run the query and got the result that I want to try, with the previous study that you suggested.

In doing so, I got another question of distinguishing 'publication(or registration)' and 'application'.
while comparing the number of publication(publn_nr) with application(appln_id) and publn_nr has more count than appln_id.

I understand that a patent registration can be confirmed about two years after a patent has been filed with a patent institution.

If it is not, how can I count registered patent?

Thank you.
Sejin an




My query is as below.

SELECT b.publn_date, F.person_ctry_code, COUNT (DISTINCT b.publn_nr)
FROM
(SELECT p1.person_id, p1.doc_std_name_id, C.person_ctry_code
FROM
(SELECT T.doc_std_name_id, T.person_ctry_code
FROM
( SELECT
p.doc_std_name_id, p.person_ctry_code, COUNT(*) AS Tot,
RANK () OVER ( PARTITION BY p.doc_std_name_id ORDER BY COUNT (*) DESC, p.person_ctry_code ASC) AS rnk
FROM tls206_person p
WHERE p.person_ctry_code NOT LIKE ''
GROUP BY p.doc_std_name_id, p.person_ctry_code
) AS T
WHERE T.rnk=1
) AS C
RIGHT JOIN tls206_person p1 ON C.doc_std_name_id=p1.doc_std_name_id
GROUP BY p1.person_id, p1.doc_std_name_id, C.person_ctry_code
) AS F
JOIN tls207_pers_appln pa ON pa.person_id=F.person_id
JOIN tls201_appln a ON a.appln_id=pa.appln_id
JOIN tls224_appln_cpc cpc ON cpc.appln_id=a.appln_id
JOIN tls211_pat_publn b on a.appln_id=b.appln_id
where b.publn_date >= '2000-01-01' and b.publn_date < '2018-12-31'
AND cpc.cpc_class_symbol LIKE 'Y02%'
AND pa.applt_seq_nr>0
AND (F.person_ctry_code IS NULL OR F.person_ctry_code IN ('CN', 'AE', 'AF', 'AG')
GROUP BY b.publn_date, F.person_ctry_code
ORDER BY b.publn_date, F.person_ctry_code


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

Re: How to get number of CPC patents by person_ctry_code?

Post by EPO / PATSTAT Support » Mon Mar 11, 2019 12:05 pm

Hello An,

I am not sure what you mean by "registered patent".
Counting publication numbers is not a good idea and opens room for mistakes. A publication number alone does not identify a publication because patent authorities can use the same numbers, and also the person_ctry codes could overlap. So there is a risk for undercounting.
If you use the publication identification number (COUNT (b.pat_publn_id)) , then there is a risk of over-counting because 1 patent application can have multiple publication instances.

If you want to strictly count applications, then you have to use

Code: Select all

COUNT (DISTINCT b.appln_id)
as in the original code described in the paper.
Observe that the model treats applications with multiple applicants from different countries as a full count for each country (no weighing is applied). If you want to be sure that the sum per year is the same as the number of applications for that year, that one would need to weigh over the country codes from the applicant. Example: assume that 1 application has 3 applicants, 2 from CN and 1 from DE; weighing would mean that you assign 2/3 share to CN and 1/3 to DE. The current method will give 1 to CN and 1 to DE. Whether you want to do this depends on your analysis.
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


sejin_an
Posts: 4
Joined: Tue Feb 26, 2019 3:49 am

Re: How to get number of CPC patents by person_ctry_code?

Post by sejin_an » Mon Mar 18, 2019 7:30 am

Dear Martin / Geert Boedt,

This is Sejin An again.
Thank you for your insightful and appropriate comment, it was very much helpful.
I should use appln_id instead publn_id, I got it.

Before that, I need some clear understanding for 'application', and 'publication'.
What I have attempted was counting the number of only officially approved patent, not the number of patent filing in a office(It shows of patent activity in a certain country).

In Korea, for example, once we applied patent for registration office, we call it 'application'.
I am not sure the Korean words may duplicated with the terms.
and what we call registered patent is only when it acquires patent rights.
It is normally protected about 18 month for officially shown.

In a sense, what do you think, whether still use appln_id or 'granted' for further selection of approved patent?
Thank you so much and have a nice day.

Sejin An


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

Re: How to get number of CPC patents by person_ctry_code?

Post by EPO / PATSTAT Support » Thu Mar 21, 2019 5:01 pm

Hello An,
if you only want to count "granted patents", then you could used the attribute "Granted" in the tls201_appln table to identify granted patents.

At the EPO, once a patent is filed, it will normally take 18 months before the application is published.
It can take many more years before the patent is granted. A published patent provides some rights to the applicant, but this not the same as a granted patent.
Geert
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply