triadic patents -query help

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

ugkim
Posts: 6
Joined: Tue Apr 26, 2016 11:32 am

triadic patents -query help

Post by ugkim » Tue Apr 26, 2016 11:44 am

I want to count number of inventions (based on the docdb family) which is filed in US and EP and JP.
is it possible to count easily? and how?
help me please!


Geert Boedt
Posts: 178
Joined: Tue Oct 19, 2004 10:36 am
Location: Vienna

Re: triadic patents -query help

Post by Geert Boedt » Wed Apr 27, 2016 9:54 am

It depends how you want to count, and what you call easy.
If you use a definition that a triadic family is a docdb family having at least a filing in US, JP and EP, then one can very easily select/count those families.

Something like this:

Code: Select all

select count(distinct(docdb_family_id))
from tls201_appln
where docdb_family_id in (select docdb_family_id from tls201_appln US where appln_auth = 'US')
and docdb_family_id in (select docdb_family_id from tls201_appln EP where appln_auth = 'EP')
and docdb_family_id in (select docdb_family_id from tls201_appln JP where appln_auth = 'JP')
But this only tells you how many triadic families there are in PATSTAT; not very useful as such.

You probably want to count "per year", and then the question arises what date to use as "representative date" for a triadic family. The query then becomes a bit more difficult because there are multiple filing, priority and publication dates.
Here is an example where I used the earliest priority date from all priorities used by the triadic members of the family. (filed > 1990)

Code: Select all

select source.first_filing_year , count(distinct(source.docdb_family_id))
from (select  docdb_family_id, min(earliest_filing_year) first_filing_year from tls201_appln group by docdb_family_id) source
where source.docdb_family_id in (select docdb_family_id from tls201_appln US where appln_auth = 'US')
and source.docdb_family_id in (select docdb_family_id from tls201_appln EP where appln_auth = 'EP')
and source.docdb_family_id in (select docdb_family_id from tls201_appln JP where appln_auth = 'JP')
and source.first_filing_year > 1990
group by source.first_filing_year
order by source.first_filing_year
You can adapt this query to your needs.
Another option is to use the OECD definition and data.
http://www.oecd-ilibrary.org/science-an ... 3844125004
Best regards,

Geert Boedt
PATSTAT support
Business Use of Patent Information
EPO Vienna


ugkim
Posts: 6
Joined: Tue Apr 26, 2016 11:32 am

Re: triadic patents -query help

Post by ugkim » Thu Apr 28, 2016 9:17 am

thanks for the very big help, :D I got key idea !!

my next question is

I noticed that some applications in the same DOCDB-family group have different IPC codes(slightly),
now i am collecting inventions based on the CPC, Climate change mitigation technology Y code,
Is it possible that
an application filed in US has Y02E %%, but an application in the same family group filed in JP is not tagged same CPC code? (I really do not know how tagging process is done)

if i want give a CPC condition to the query what you suggest, then better add into each sub-query or once in main query is enough? or depends on me?

thank you again!


nico.rasters
Posts: 140
Joined: Wed Jul 08, 2009 5:51 pm
Contact:

Re: triadic patents -query help

Post by nico.rasters » Fri May 13, 2016 12:00 pm

Yes, patents in the same family can have slightly different characteristics.
You can take the union of all classes, the intersection, or you can select a particular patent as your single source of truth. For example the priority filing.
As you are looking at Triadic Patents, there will always be a filing at the EP. So you could also decide to look at the EP patents only with regards to the patent classes. That is probably what I would do in this case.
________________________________________
Nico Doranov
Data Manager

Daigu Academic Services & Data Stewardship
http://www.daigu.nl/


Thomas Oelker
Posts: 3
Joined: Fri Sep 20, 2019 10:50 am

Re: triadic patents -query help

Post by Thomas Oelker » Thu Oct 24, 2019 10:55 am

Hi there,

I tried to twist the code that makes use of the first filing year to retrieve the number of triadic patents by country per year per ipc classification (see below). so, in a way, I try to replicate the oecd definition for triadic patents.

however I get counts that seem unrealistically high to me.
What could be the root of this problem?

and is there a good intro to the 'source' command?

thanks a lot in advance for your help - I highly appreciate it!

Code: Select all

select 
source.first_filing_year, person_ctry_code, left (ipc_class_symbol,4),  count(*)

from  tls201_appln join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls206_person.person_id = tls207_pers_appln.person_id
join tls209_appln_ipc on tls201_appln.appln_id = tls209_appln_ipc.appln_id,
(select  tls201_appln.docdb_family_id, min(tls201_appln.earliest_filing_year) first_filing_year from tls201_appln group by tls201_appln.docdb_family_id) source 

where (
tls201_appln.docdb_family_id in (select tls201_appln.docdb_family_id from tls201_appln US where appln_auth = 'US')
and tls201_appln.docdb_family_id in (select tls201_appln.docdb_family_id from tls201_appln EP where appln_auth = 'EP')
and tls201_appln.docdb_family_id in (select tls201_appln.docdb_family_id from tls201_appln JP where appln_auth = 'JP')
and source.first_filing_year = 1992
and person_ctry_code in ('ET')
and invt_seq_nr >0
)
group by source.first_filing_year, person_ctry_code, left (ipc_class_symbol,4)
order by person_ctry_code, source.first_filing_year, left (ipc_class_symbol,4)


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

Re: triadic patents -query help

Post by EPO / PATSTAT Support » Fri Oct 25, 2019 10:52 am

Hello Thomas,
the concept of OECD triadic families is not based on EPO (DocDB % INPADOC) patent families.
For more information on the OECD approach, kindly have a look at the attached documents:
OECD Triadic patent families methodology.pdf
(183.21 KiB) Downloaded 48 times
OECD Triadic Patent Families - Sept 2017.pdf
(251.46 KiB) Downloaded 54 times


Replicating the same methodology on PATSTAT Online will (reasonably spoken) not be possible because it requires storage of intermediate data sets during the data aggregation (based on priorities & grants)
My advice would be to contact OECD and ask them for the source data, which they (used to) share with researchers.
Your results are very high because you count records instead of patent families.
Use this as count --> "count(distinct (tls201_appln.docdb_family_id))"
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply