DOCDB Family fractional count by country of inventors
Posted: Fri Jun 18, 2021 12:25 pm
Hello everyone,
I would like some help and advices with a query aiming to execute a fractional count of DOCDB families from CPC Y02B by country of inventors (here France) on Patstat Online.
Here what I'm doing (kind of resourcefulness but it works fine) :
1) I know something is approximative in the querry as I just delete the lines docdb_family_id with no results in person_ctry_code. Thereby, I miss some families that could be associates with a french inventor and I miss also an exact fractional count of families with a french among inventors and with an indeterminated inventors. Are these no results in person_ctry_code due to a lack of informations about the only, second, third or... inventors ? If so, how to remediate to this lack of information in my case ? I know the paper of (de Rassenfosse, 2012).
2) Is there any other mistakes that I could miss ? SQL always give you a result and we can easily don't see a mistake.
3) I know the earliest_filing_date is quite unconventional but I would like the date as near as possible from the date of invention.
Thanks a lot for your help,
Valentin
I would like some help and advices with a query aiming to execute a fractional count of DOCDB families from CPC Y02B by country of inventors (here France) on Patstat Online.
Here what I'm doing (kind of resourcefulness but it works fine) :
Code: Select all
SELECT DISTINCT T2.earliest_filing_year, SUM(T2.CASTFRACOUNT)
FROM
(SELECT T.earliest_filing_year, T.docdb_family_id, COUNT(T.REPLACED) AS COUNTALL, SUM(T.SIFR) AS COUNTFR, (CAST(SUM(T.SIFR) AS DECIMAL(3,1)) / CAST(COUNT(T.REPLACED) AS DECIMAL(3,1))) AS CASTFRACOUNT
FROM
(SELECT DISTINCT table1.earliest_filing_year, REPLACE(table4.person_ctry_code, ' ','NULL') AS REPLACED, table2.docdb_family_id,
CASE
WHEN REPLACE(table4.person_ctry_code, ' ','NULL')='FR' THEN 1
ELSE 0
END SIFR
FROM tls201_appln table1
INNER JOIN tls225_docdb_fam_cpc table2 ON table1.docdb_family_id=table2.docdb_family_id
INNER JOIN tls207_pers_appln table3 ON table1.appln_id=table3.appln_id
INNER JOIN tls206_person table4 ON table4.person_id=table3.person_id
AND table2.cpc_class_symbol LIKE 'Y02B%'
AND table1.earliest_filing_year >=1970
AND table1.earliest_filing_year<=2018
AND ipr_type='PI'
AND granted='Y'
AND REPLACE(table4.person_ctry_code, ' ','NULL') LIKE '__'
GROUP BY earliest_filing_year,REPLACE(table4.person_ctry_code, ' ','NULL'), table2.docdb_family_id) T
GROUP BY T.earliest_filing_year, T.docdb_family_id) T2
GROUP BY T2.earliest_filing_year
ORDER BY T2.earliest_filing_year
2) Is there any other mistakes that I could miss ? SQL always give you a result and we can easily don't see a mistake.
3) I know the earliest_filing_date is quite unconventional but I would like the date as near as possible from the date of invention.
Thanks a lot for your help,
Valentin