Page 1 of 1

triadic patents -query help

Posted: Tue Apr 26, 2016 11:44 am
by ugkim
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!

Re: triadic patents -query help

Posted: Wed Apr 27, 2016 9:54 am
by Geert Boedt
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

Re: triadic patents -query help

Posted: Thu Apr 28, 2016 9:17 am
by ugkim
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!

Re: triadic patents -query help

Posted: Fri May 13, 2016 12:00 pm
by nico.rasters
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.

Re: triadic patents -query help

Posted: Thu Oct 24, 2019 10:55 am
by Thomas Oelker
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)

Re: triadic patents -query help

Posted: Fri Oct 25, 2019 10:52 am
by EPO / PATSTAT Support
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 72 times
OECD Triadic Patent Families - Sept 2017.pdf
(251.46 KiB) Downloaded 69 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))"