Page 1 of 1
Help with IPC Co-Classifikation
Posted: Tue Jul 26, 2016 11:31 am
by techno
It is the first time I use PATSTAT for research purposes and I'm not quite sure how to go along. I used an offline version.
I want to do a simple co classification analysis on IPC Codes. Therefore I create a table trough a join (table named IPC2) which include the information of tls209_appln_ipc and another table of 35 technology fields (TechF). All IPC Codes grouped by the 35 Fields.
Now I want to know: How many Patents are co classified by groups? E.g. how many Patents classified as TechF ‘1’ and TechF ‘2’
I hope you understand my problem and someone can helps me.
Re: Help with IPC Co-Classifikation
Posted: Tue Jul 26, 2016 11:58 am
by mkracker
Short answer: Have a look at table TLS230_APPLN_TECHN_FIELD, which has the data about to which degree an application is related to a technical field. This table will already solve half of your problem.
This table is based on the reference table TLS901_TECHN_FIELD_IPC. The Data Catalog gives you more detail.
Re: Help with IPC Co-Classifikation
Posted: Wed Jul 27, 2016 2:08 pm
by techno
Thanks for your answer. I worked with an old version. Now I tried it with the new version but it doesn’t works. I hope you can help me again.
That’s my query:
select count(distinct t1.APPLN_ID) as Quantity
from TLS230_APPLN_TECHN_FIELD t1
inner join TLS230_APPLN_TECHN_FIELD t2 on t1.APPLN_ID = t2.APPLN_ID
where t1.TECHN_FIELD_NR = '1' and t2.TECHN_FIELD_NR = '2',
Re: Help with IPC Co-Classifikation
Posted: Thu Jul 28, 2016 10:28 pm
by mkracker
IMO your query is fine. Just the comma at the end must be removed. And you do not need to put the attribute values in quotes because TECHN_FIELD_NR is not a string attribute but a numerical attribute.
So why are you not happy with your result?
Re: Help with IPC Co-Classifikation
Posted: Wed Aug 24, 2016 2:04 pm
by techno
Thanks for your answer!
My request will be one table that has the information for all Co Classification pairs. But I don’t know how this will be work.
Re: Help with IPC Co-Classifikation
Posted: Wed Aug 24, 2016 4:33 pm
by Geert Boedt
It is fairly simple, you need to specify in your "WHERE" clause the conditions to be fulfilled.
If I understood correctly, you are looking for applications that have
at least technical field 1 (Electrical machinery, apparatus, energy) and technical field 2 (Audio-visual technology)
Here is the query, I restricted it also with a date, otherwise you would have millions of records.
The "distinct" clause is needed because 1 technical field occurs many times in the tls901 table, linked to the various IPC main group symbols. Because the PATSTAT team has pre-created the link between applications and technological fields (tls230), you don't need the tls209 (IPC) table at all.
Here is the code:
Code: Select all
SELECT distinct tls201_appln.appln_id
,appln_auth
,appln_nr
,appln_kind
,appln_filing_date
,appln_nr_epodoc
,weight
,tls901_techn_field_ipc.techn_field_nr
,techn_sector
,techn_field
FROM tls201_appln join tls230_appln_techn_field on tls201_appln.appln_id = tls230_appln_techn_field.appln_id
join tls901_techn_field_ipc on tls230_appln_techn_field.techn_field_nr = tls901_techn_field_ipc.techn_field_nr
where tls201_appln.appln_id in (select distinct appln_id from tls230_appln_techn_field where techn_field_nr = 1)
and tls201_appln.appln_id in (select distinct appln_id from tls230_appln_techn_field where techn_field_nr = 2)
and appln_filing_date = '2014-04-01'
order by tls201_appln.appln_id
If you now want to count the applications for each of the technical fields, then you could use the following query. (Real number or weighted; you could also exclude the other technological fields that enter the count because of other co-classifications.)
Code: Select all
SELECT tls230_appln_techn_field.techn_field_nr,techn_field, count(tls201_appln.appln_id) #applications, sum(weight)
weighted FROM tls201_appln join tls230_appln_techn_field on tls201_appln.appln_id = tls230_appln_techn_field.appln_id
join tls901_techn_field_ipc on tls230_appln_techn_field.techn_field_nr = tls901_techn_field_ipc.techn_field_nr
where tls201_appln.appln_id in (select distinct appln_id from tls230_appln_techn_field where techn_field_nr = 1)
and tls201_appln.appln_id in (select distinct appln_id from tls230_appln_techn_field where techn_field_nr = 2)
and appln_filing_date = '2014-04-01'
group by tls230_appln_techn_field.techn_field_nr,techn_field
order by sum(weight) desc
Re: Help with IPC Co-Classifikation
Posted: Thu Aug 25, 2016 10:13 am
by techno
Thank you very much for your time and help! The query helps me very much.