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,
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:
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))"
Re: triadic patents -query help
Posted: Fri Aug 19, 2022 11:48 am
by mmazaheri
Hi Dear Team
I have a question regarding the Triadic query that you mentioned, I tested it, and it does not work. I need data on Triadic family patents for some IPC and CPC codes, and I am stuck in this step
because it seems there is no way to have this database! I am a Ph.D. student who needs this data for a research paper.
Thanks alot.
Re: triadic patents -query help
Posted: Tue Aug 30, 2022 7:18 am
by EPO / PATSTAT Support
Have a look at the PATSTAT webpages and request the 1 month free subscription from PATSTAT Online. (
https://www.epo.org/searching-for-paten ... tstat.html) That might be sufficient for you to extract the data you need. It is rather difficult to know "what does not work" without any further details or SQL code.
Docdb families will always have the same CPC codes. You will find the CPC codes (linked via the docdb_family_id) in the tls225_docdb_fam_cpc table. IPC codes might indeed be different for different docdb family members, but they should in principle be very closely related as the patent are linked via priority filings. Researchers can easily define themselves what criteria to adapt for the the IPC codes. In linke with CPC, you could use an approach that requires the family members to have the same classifications. Or an alternative (less stringent) approach is to accept that only 1 triadic member has the required IPC code. All those options are possible but you have to do the SQL script writing.