Patent Publication Research

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

taques
Posts: 3
Joined: Thu Jun 07, 2018 6:38 am

Patent Publication Research

Post by taques » Thu Jun 07, 2018 7:26 am

Hi.

I am performing a research on PATSTAT 2018 Autumn of patent publication, citation of patent publication and replenished patent publication, encompassing the following fields:

- TLS206_PERSON
- PERSON_ID
- PERSON_CTRY_CODE
- PSN_NAME
- PSN_SECTOR
- TLS211_PAT_PUBLN
- APPLN_ID
- PAT_PUBLN_ID
- PUBLN_DATE
- PUBLN_KIND
- TLS212_CITATION
- CITED_APPLN_ID
- CITED_PAT_PUBLN_ID
- CITN_REPLENISHED
- TLS227_PERS_PUBLN
- PERSON_ID
- PAT_PUBLN_ID
- TLS230_APPLN_TECHN_FIELD
- APPLN_ID
- TECHN_FIELD_NR

Where I am counting the amount of published patent, citation of published patent and replenished patent publication by 'company' (PSN Sector) only, by year (Publn_Date) from 1975-01-01 to 2017-12-31 (9999-12-31 has to be out of the sample), and all rows and tables mentioned above.
Published patent, citation of published patent and replenished patent publication field contents must not repeat or be empty (null value or '0').
For organization purpose, it is important to add a row sequential number of the line on the table (row number function) so it will be possible to properly order the information.

Thank you very much for your attention.
Appreciate it.


Flavio Taques.


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

Re: Patent Publication Research

Post by EPO / PATSTAT Support » Fri Jun 08, 2018 10:11 am

Hello Flavio,
for sample queries, kindly seach previous forum posts, there are plenty examples.

If you would write a query that results in a list that contains all your listed data attributes, then you will have plenty of duplicates at publication (citation) level. Such list can of course be used for further analysis, but with your specifications ((Publn_Date) from 1975-01-01 to 2017-12-31 + persons ) you will have 100's of millions of records.
Example:
Assume 1 patent publication with 2 applicants (BMW and BOSCH) is cited by 1 other publication with 2 applicants (Daimler and Ford) will result in 4 unique records (the Cartesian product of unique data values over al attributes): the citations for BMW - Daimler, BMW - Ford, Bosch - Daimler, Bosch Ford.
With multiple citations, this list quickly grows very large and therefore researchers should try to make the boundaries to aggregate the data as strict as possible.

Here is an example I used in our PATSTAT training course; it lists "who is citing who, in the paper industry" --> defined by IPC = C21 :

Code: Select all

SELECT p_cited.psn_name cited, p_citing.psn_name citing, Count(cited.appln_id) AS CountOfappln_id
FROM tls206_person AS p_citing JOIN tls207_pers_appln AS pa_citing ON p_citing.person_id = pa_citing.person_id
INNER JOIN tls211_pat_publn AS citing ON pa_citing.appln_id = citing.appln_id
INNER JOIN tls212_citation ON citing.pat_publn_id = tls212_citation.pat_publn_id
INNER JOIN tls211_pat_publn AS cited ON tls212_citation.cited_pat_publn_id = cited.pat_publn_id 
INNER JOIN tls224_appln_cpc AS CPC ON citing.appln_id = CPC.appln_id 
INNER JOIN tls207_pers_appln ON cited.appln_id = tls207_pers_appln.appln_id 
INNER JOIN tls206_person AS p_cited ON tls207_pers_appln.person_id = p_cited.person_id
WHERE Left(cpc_class_symbol,3)='D21'
AND tls207_pers_appln.applt_seq_nr = 1
AND pa_citing.applt_seq_nr =1 
AND cited.publn_auth = 'EP' and citing.publn_auth = 'EP'
AND p_cited.psn_name <> p_citing.psn_name
GROUP BY p_cited.psn_name, p_citing.psn_name
HAVING (((Count(cited.appln_id))>5))
ORDER BY p_cited.psn_name,Count(cited.appln_id) DESC;
(Observe that I restricted the "persons" to the first one from each citation pair, in analogy with above, I would only consider the BMW - DAIMLER pairs of citations. For a real analysis, we would of course not do this.)
One more example:

Code: Select all

SELECT p_cited.psn_name cited, p_citing.psn_name citing, Count(distinct (cited.appln_id)) AS CountOfappln_id
FROM tls206_person AS p_citing JOIN tls207_pers_appln AS pa_citing ON p_citing.person_id = pa_citing.person_id
INNER JOIN tls211_pat_publn AS citing ON pa_citing.appln_id = citing.appln_id
INNER JOIN tls212_citation ON citing.pat_publn_id = tls212_citation.pat_publn_id
INNER JOIN tls211_pat_publn AS cited ON tls212_citation.cited_pat_publn_id = cited.pat_publn_id 
INNER JOIN tls207_pers_appln pa_cited ON cited.appln_id = pa_cited.appln_id 
INNER JOIN tls206_person AS p_cited ON pa_cited.person_id = p_cited.person_id
WHERE  p_cited.psn_name in ( 'astrazeneca','NOVARTIS','MERCK & COMPANY','ROCHE','BOEHRINGER INGELHEIM INTERNATIONAL',
'SANOFI', 'PFIZER', 'BMW (BAYERISCHE MOTOREN WERKE)', 'TOYOTA MOTOR CORPORATION')
AND pa_cited.applt_seq_nr > 0
AND p_citing.psn_sector <> 'individual'
and pa_citing.applt_seq_nr > 0
and pa_citing.invt_seq_nr = 0
--AND cited.publn_auth = 'EP' and citing.publn_auth = 'EP'
AND p_cited.psn_name <> p_citing.psn_name 
--above line to exclude self-citations
GROUP BY p_cited.psn_name, p_citing.psn_name
HAVING Count(distinct (cited.appln_id))>5
ORDER BY p_cited.psn_name,Count(distinct (cited.appln_id)) DESC;
And here is the presentation that goes with the above query.
8_GB_Patent citations & non patent literatur.pdf
(624.68 KiB) Downloaded 216 times
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


taques
Posts: 3
Joined: Thu Jun 07, 2018 6:38 am

Re: Patent Publication Research

Post by taques » Mon Jun 18, 2018 5:29 am

Hi,

Thank you for replying on it. It was really appreciated.
The answer was enlightening, and of great help.

I tried to set some additional filters, which worked well. At this point I am focusing on a range of published patents (cited.pat_publn_id) by company (taking TLS206_PERSON.PSN_SECTOR='COMPANY' as a main reference), from 1975 to 2018.
When I submit the script below, it returns few records from 2008 and 2009, and nothing before or after it.
Is there any record that my script is missing?

Best regards.

Here is it:

Code: Select all

SELECT p_cited.psn_name, p_citing.psn_name, cited.publn_auth, citing.publn_auth, citing.publn_date, company.psn_sector, Count(cited.appln_id) AS CountOfappln_id
FROM tls206_person AS p_citing JOIN tls207_pers_appln AS pa_citing ON p_citing.person_id = pa_citing.person_id
INNER JOIN tls211_pat_publn AS citing ON pa_citing.appln_id = citing.appln_id
INNER JOIN tls212_citation ON citing.pat_publn_id = tls212_citation.pat_publn_id
INNER JOIN tls211_pat_publn AS cited ON tls212_citation.cited_pat_publn_id = cited.pat_publn_id 
INNER JOIN tls224_appln_cpc AS CPC ON citing.appln_id = CPC.appln_id 
INNER JOIN tls207_pers_appln ON cited.appln_id = tls207_pers_appln.appln_id 
INNER JOIN tls206_person AS p_cited ON tls207_pers_appln.person_id = p_cited.person_id
INNER JOIN tls206_person AS company ON company.person_id = cited.pat_publn_id
WHERE Left(cpc_class_symbol,0)=''
AND tls207_pers_appln.applt_seq_nr = 1
AND pa_citing.applt_seq_nr =1 
AND p_cited.psn_name <> p_citing.psn_name
AND company.psn_sector = 'COMPANY'
GROUP BY p_cited.psn_name, cited.publn_auth, p_citing.psn_name, citing.publn_auth, citing.publn_date, company.psn_sector
HAVING (((Count(cited.appln_id))>5))
ORDER BY citing.publn_date, p_cited.psn_name,Count(cited.appln_id) DESC;


Post Reply